Designed and built a suite of 11 Nintex workflows that automate the entire data lifecycle between an applicant tracking system, a centralised reporting database, and SharePoint — replacing hours of manual data entry with batch-processed, error-handled automations that keep employee records synchronised across all systems in real time.
Client: Enterprise Client
The challenge
The client managed a high-volume recruitment operation where candidate and employee data lived across three separate systems: an applicant tracking system (ATS), a centralised SQL Server reporting database (operational data), and SharePoint (day-to-day team management and onboarding workflows).
Every time a new hire was processed, data had to be manually copied between these systems. Application statuses, shift preferences, screening question responses, cohort assignments, and Day 1 onboarding details all needed to be entered in multiple places. With hundreds of new starters per week across multiple sites, the operations team was drowning in repetitive data entry.
The problems were predictable: records fell out of sync, updates were missed, and the team spent more time maintaining spreadsheets than actually onboarding people. When a candidate's status changed in the ATS, it could take days before that change was reflected in SharePoint — by which time the team was working from stale information.
They needed a system that would keep all three platforms synchronised automatically, handle bulk imports without manual intervention, and give the team confidence that the data they were looking at was current and correct.
Process flow: how data stays synchronised across the ATS, reporting database, and SharePointClick to enlarge
What we built
We designed and built a suite of 11 interconnected Nintex workflows running on SharePoint that fully automate data synchronisation between all three systems. The suite handles four distinct operational areas: importing new records from the reporting database, updating existing records when data changes, uploading ATS application data into SharePoint, and managing cohort assignments.
Import Pipeline (Controller + Processor): The Import Controller workflow queries the SQL reporting database for new application records that match the current project's requisition IDs. It calculates how many records need processing, divides them into configurable batches (default: 150 records per batch), and orchestrates the Import Processor workflow for each batch. The Processor handles the actual record creation in SharePoint — pulling employee details, application data, site codes, and status information from the database and writing them into the correct SharePoint lists.
Update Pipeline (Controller + Processor): The Update Controller identifies records that have changed since the last sync — new status updates, location changes, contact detail updates — and batches them for processing. The Update Processor compares each record's current state against what's stored in SharePoint, identifies what's changed, and applies only the differences. This means the system doesn't blindly overwrite data; it makes surgical updates only where needed.
Upload Workflows (6 specialised workflows): Each upload workflow handles a specific data type from the ATS: application status changes, Day 1 onboarding details, hire shift preferences, maximum tenure information, and screening question responses. Each workflow queries the relevant upload list, iterates through records, validates site codes match, pulls supplementary data from the reporting database (employee details, badge numbers, department information), and writes the consolidated record into the master transfer list.
Cohort Management: The Update Cohort workflow manages group assignments — when employees are assigned to training cohorts or shift groups, this workflow ensures the assignments are reflected across all systems.
Every workflow includes structured error handling. SQL query failures, missing data, and validation errors are all caught, logged to the workflow history, and flagged for review — the system never silently drops a record.
SharePoint transfer list: consolidated employee records from all sources
Screenshot coming soon
Nintex workflow designer: import controller with batch calculation logic
Screenshot coming soon
Technical detail
This section is for readers with a technical background who want to understand the architecture and implementation choices.
Architecture diagram: controller/processor pattern synchronising the ATS, reporting database, and SharePointClick to enlarge
The suite is built on Nintex Workflow for SharePoint 2013/2016, using site-level workflows triggered manually or on schedule. All workflows communicate with an external SQL Server reporting database via integrated Windows authentication.
Architecture Pattern — Controller/Processor: The Import and Update pipelines use a controller/processor pattern. The Controller calculates batch boundaries (start row, end row) using configurable batch sizes (default: 150), then invokes the Processor workflow for each batch via Nintex's "Start Workflow" action. This prevents timeout issues on large datasets and allows parallel processing.
Data Sources: - SQL Server (AWS-hosted): A centralised reporting database with dedicated views for application and employee data. Queries filter on requisition IDs (from both the ATS and internal hiring system) and validate that records have employee IDs and location codes populated. - SharePoint Lists: Project Statement list (site configuration, requisition mappings), ATS Upload list (incoming application data), Transfer List (master employee records), and various lookup lists for cohorts and screening questions. - Applicant Tracking System: Data arrives via scheduled exports into SharePoint lists, which the Upload workflows then process.
Key Workflow Variables (Upload Workflow — 55+ variables): Application data (App ID, Requisition ID, Step, Sub-step), Employee data (Employee ID, Login, First Name, Last Name, Badge Number), Location data (Site Code, Location Name, HR Location), Employment data (Job Title, Shift Pattern, Crew Name, Department, Cost Centre, Company Code, Employment Type, Employee Class), Status data (HR Status, Employee Status, Termination Date, Contract End Date), and comparison variables for change detection.
CAML Queries: SharePoint list queries use CAML (Collaborative Application Markup Language) to filter records. The Project Statement list is filtered by a Lookup Value field to retrieve the active project configuration (Country, ATS Req ID, Internal Req ID, Site Code).
SQL Query Pattern: Queries select application numbers from a persistent applications view, filtering on requisition ID (supporting both ATS and internal hiring system IDs), and validating that critical fields (employee ID, location code, application number) are populated and non-empty. An active status field is checked to identify current applications.
Batch Processing Logic: 1. Execute SQL to retrieve all matching application numbers into a collection 2. Count the collection to get total rows 3. Divide total rows by batch size to calculate number of batches 4. Apply ceiling function to handle partial batches 5. Loop through batches, calculating start/end row indices 6. Invoke Processor workflow for each batch with row boundaries as parameters
Error Handling Pattern: Each SQL execution and web service call uses Nintex's error handling mode — capturing success/failure into a boolean flag and error text into a string variable. After each operation, a conditional check evaluates the error flag. On failure: the error is logged to workflow history with full context, the flag is reset, and processing continues (fail-forward pattern rather than fail-stop).
Change Detection (Update Processor): Before updating a SharePoint record, the workflow reads the current values into comparison variables. It then compares these against the fresh data from the database. Only fields that have actually changed are written back — reducing unnecessary list item updates and preserving SharePoint version history accuracy.
Cross-System Validation: The Upload workflows validate that the site code from the ATS upload matches the site code configured in the Project Statement. Records that don't match the current site are skipped — this prevents data from one site accidentally being written into another site's workspace.
The results
Interested in something similar?
Book a free 30-minute discovery call. We'll listen to what you need, tell you what's realistic, and give you a straight answer on whether we can help.