In this article

Problem

Your company is using Exact Online to manage the payroll process by ensuring that your employees book the work hours in a timely manner and on the correct project(s). For consultancy related projects, the timely booking of hours also ensures that the correct invoices can be sent to your customers in a timely manner. 

The process of checking up on the timely and complete booking of hours can be tedious and time-consuming. Although there are some build-in notification options for this issue, these are quite limited. A general reminder can be sent at the end of the week when an employee has not booked all their hours for this week. However, this message does not provide further details on the booked hours; are their rejected hours? Are hours still "drafted" and need to be submitted? Also, the reminder is only send once, there are no options for follow-ups or for checks on a longer period (for example the end-of-month closure). For the purpose of improving on this standard functionality, we have developed an integration that incorporates these functionalities:

In this use-case we check on all the registered hours over a period of the last 5 weeks, and group them by employee and hour-status (booked, drafted, rejected). We then compare the booked hours with the employee-contract to determine if all expected hours have been booked in a correct manner. If this is not the case, a personalised email is send out to the employee and their manager (if applicable) to notify them of this discrepancy. The email contains an overview of the hours booked for the different hour-statuses and details the missing hours. Also, a link to Exact Online is added, so the employee can rectify the issue straight from the notification email.

Solutions 

Understanding the Exact Online Pipeline

Prerequisites

• A valid Exact Online Account

• A valid Email Account for Email Sender

Building a pipeline to send notifications using email to inform the employees booking the hours

This pipeline is used to check the booked hours for all the employees per week, and based on the contract working hours per week, we will send employees and their managers notifications via email once they do not book sufficient hours or they do not book any hours. 

1. Add and configure an Exact Online Read Snap to query Payroll - Employments data. 
   Select EndDate, Employee, EmployeeHID and ID fields using Output field selection table.

2. Add two Filter snaps to filter out employees that are no longer active and employees that need to be excluded (based on requirements). 

3. Add and configure an Exact Online Read Snap to query Payroll - Employees data. 
   Filter EmployeeHID using the Filter Records table. Select ID and EmployeeHID fields using Output field selection table.

4. Add a mapper to map the ID as $Employee.

5. Add and configure an Exact Online Read Snap to query Project - TimeTransactions data. 
   Filter Employee field using Filter Records table and filter the Date field to only return data for the last 5 weeks. Select Employee, Quantity, HourStatus, Date, Created, and Project fields using Output field selection table.

6. Add a Router to split up the different HourStatuses:
   Booked hours (status 10 & 20), Rejected Hours (status 2), Drafted Hours (status 1),  No Hours (no status).
   

7. Add a Group Snaps and Mapper Snap for each HourStatus group, summing up the number of hours grouped per employee.
   --For three Group Snaps after output0, output1, output2 with HourStatus as 10(submitted), 20(final), 1(drafted), 2(rejected), apply the Function of SUM, enter the Field of $Quantity, and name the Result field as HoursBooked(10 --and 20), HoursRejected(2), HoursDrafted(1). And group by fields of $Employee with output field Employee and $original.EmployeeHID with output field EmployeeHID. 
   For the output3 (an employee has booked no hours at all), add the child pipeline to process the no hours notification. Please see the below section Building a pipeline to send emails to employees who don't book any hour and their managers

8. Add a Union Snap to combine the output from the different routes (HourStatuses) and create an overview per employee. This step also includes Group by N Snap, JSON Splitter snap, Sort snap and Group by Fields snap.

9. --Add a JSON Splitter Snap with json path of $group. 

10. --Add a Sort Snap with Sort path of $Employee, Sort order of global and Sort order(global) of ascending. 

11. --Add a Group by Fields Snap with fields of $Employee and $EmployeeHID.

12. Add the child pipeline Booked Hours Notifications - Check Hours to check the scheduled hours. Please see the below section Building a pipeline to check the scheduled hours.

13. Add the child pipeline Booked Hours Notifications - Send Email to send notifications via email. Please see the below section Building a pipeline to send emails to employees who don't book enough hours and their managers.

14. Finally add a join snap to merge the response from email sending child pipelines.

Configuration

Building a pipeline to send notifications using email to inform the employees booking the hours

This pipeline is used to check the booked hours for all the employees per week, and based on the contract working hours per week, we will send employees and their managers notifications via email once they do not book sufficient hours or they do not book any hours. 

1. Add and configure an Exact Online Read Snap to query Payroll - Employments data.  Select EndDate, Employee, EmployeeHID and ID fields using Output field selection table.

2. Add two Filter snaps to filter out employees that are no longer active and employees that need to be excluded (based on requirements). 

3. Add and configure an Exact Online Read Snap to query Payroll - Employees data.  Filter EmployeeHID using the Filter Records table. Select ID and EmployeeHID fields using Output field selection table.

4. Add a mapper to map the ID as $Employee.

5. Add and configure an Exact Online Read Snap to query Project - TimeTransactions data.  Filter Employee field using Filter Records table and filter the Date field to only return data for the last 5 weeks. Select Employee, Quantity, HourStatus, Date, Created, and Project fields using Output field selection table.

6. Add a Router to split up the different HourStatusses: Booked hours (status 10 & 20), Rejected Hours (status 2), Drafted Hours (status 1),  No Hours (no status).

7. Add a Group Snaps and Mapper Snap for each HourStatus group, summing up the number of hours grouped per employee. --For three Group Snaps after output0, output1, output2 with HourStatus as 10(submitted), 20(final), 1(drafted), 2(rejected), apply the Function of SUM, enter the Field of $Quantity, and name the Result field as HoursBooked(10 --and 20), HoursRejected(2), HoursDrafted(1). And group by fields of $Employee with output field Employee and $original.EmployeeHID with output field EmployeeHID.  For the output3 (an employee has booked no hours at all), add the child pipeline to process the no hours notification. Please see the below section Building a pipeline to send emails to employees who don't book any hour and their managers

8. Add a Union Snap to combine the output from the different routes (HourStatusses) and create an overview per employee. This step also includes Group by N Snap, JSON Splitter snap, Sort snap and Group by Fields snap.

9. --Add a JSON Splitter Snap with json path of $group. 

10. --Add a Sort Snap with Sort path of $Employee, Sort order of global and Sort order(global) of ascending. 

11. --Add a Group by Fields Snap with fields of $Employee and $EmployeeHID.

12. Add the child pipeline Booked Hours Notifications - Check Hours to check the scheduled hours. Please see the below section Building a pipeline to check the scheduled hours.

13. Add the child pipeline Booked Hours Notifications - Send Email to send notifications via email. Please see the below section Building a pipeline to send emails to employees who don't book enough hours and their managers.

14. Finally add a join snap to merge the response from email sending child pipelines. 

Building a pipeline to check the scheduled hours 

To check that if the amount of hours submitted by employees every week meet the amount of hours per week agreed in the employee's contract, we build this pipeline. It is a sub-pipeline of the parental pipeline of Booked Hours Notifications.

1. Add and configure an Exact Online Read Snap to query Payroll - Employments data.  Filter EmployeeHID from the parental pipeline using Filter Records table. Select ID and StartDate fields using Output field selection table.

2. Add a mapper to map the field from the step 1. $original.group → $group ($StartDate.replace("/Date(","").replace(")/","")) → $StartDate $original.groupBy → $groupBy $ID → $groupBy.EmploymentId

3. Add and configure a new Exact Online Read Snap to query HRM - Schedules data.  Filter Employment as $groupBy.EmploymentId from the mapper of step 2 using Filter Records table. Select AverageHours fields using Output field selection table. 

4. As we are looking back on a 5 week period, it is possible that an employee has been employed for less then 5 weeks. In this case we want to compare the booked hours with the expected hours booked of this smaller period. Therefore, we use a Router Snap to distinguish between the 2 scenarios:  if ($original.StartDate.replace("/Date(","").replace(")/","") < (Date.now().minusDays(35).getTime()) ), route to the output0 (employee startdate >= 5 weeks) ; if(!($original.StartDate.replace("/Date(","").replace(")/","") < (Date.now().minusDays(35).getTime()))), route to the output1 (employee startdate < 5 weeks); .

5. Add a mapper to output0 to calculate Average Hours $AverageHours*5 → $AverageHours $original.groupBy → $EmployeeDetails $original.group → $EmployeeHours ...... more description here, need to ask Marc ..............

6. Add three new mappers to output1, there are used to add current date time, calculate the contract hours and compute the average hours.  Add Dates Mapper:  $AverageHours → $ContractHours $original.group → $original.group ($original.StartDate.replace("/Date(","").replace(")/","")) → $original.StartDate $original.groupBy → $original.groupBy Date.now().getTime() → $Today Determine Hours Mapper: $original.group → $original.group $original.groupBy→ $original.groupBy $ContractHours/5 → $ContractHoursPerDay $ContractHours → $ContractHours (Math.floor(($Today-$original.StartDate) / 86400000)) - ((Math.floor(($Today-$original.StartDate) / 86400000)/7)*2) → $DaysInService Average Hours Mapper: $original.group → $EmployeeHours $original.groupBy→ $EmployeeDetails $ContractHoursPerDay * $DaysInService → $AverageHours $ContractHours → $ContractHours ...... more description here, need to ask Marc ..............

7. Add a Join Snap with Join Type as Merge to merge the data from mappers after output0 and output1 of Router Snap.

8. Add a mapper to map the hour types $EmployeeDetails → $EmployeeDetails $EmployeeHours.find(x => ('HoursBooked' in x)) → $HoursBooked $EmployeeHours.find(x => ('HoursRejected' in x)) → $HoursRejected $EmployeeHours.find(x => ('HoursDrafted' in x))→ $AverageHours $AverageHours.toFixed() → $DaysInService 'ContractHours'in $ ? ($ContractHours*5) : null → $ContractHours ...... more description here, need to ask Marc ..............

9. Add a Filter Snap to filter the hour types with the Filter expression $HoursBooked.HoursBooked < $AverageHours.

10. Add a Restructure Snap to restructure the data stream.

$EmployeeDetails.Employee → $EmployeeId match $HoursBooked {null => "0",_=>$HoursBooked.HoursBooked} → $HoursBooked match $HoursDrafted {null => "0",_=>$HoursDrafted.HoursDrafted} → $HoursDrafted match $HoursRejected {null => "0",_=>$HoursRejected.HoursRejected} → $HoursRejected $AverageHours → $AverageHours $ContractHours → $ContractHours

Building a pipeline to send emails to employees who don't book any hour and their managers

To retrieve the employee that has not submitted hours yet and send them emails to remind them submitting the hours. It is a sub-pipeline of the parental pipeline of Booked Hours Notifications.

1. Add and configure an Exact Online Read Snap to query Payroll - Employments data.  Filter EmployeeHID from the parental pipeline using Filter Records table. Select ID and StartDate fields using Output field selection table.

2. Add a restructure snap to restructure the fields from step1. $original.Employee → $EmployeeId $original.Hours → $Hours $ID → $EmploymentId ($StartDate.replace("/Date(","").replace(")/",""))→ $StartDate

3. Add and configure a new Exact Online Read Snap to query HRM - Schedules data.  Filter Employment as $groupBy.EmploymentId from the mapper of step 2 using Filter Records table. Select AverageHours fields using Output field selection table. 

4. Add a Filter snap to filter the data with condition of $AverageHours > $original.Hours.

5. Add and configure an Exact Online Read Snap to query Payroll - Employees data.  Filter EmployeeHID from the parental pipeline using Filter Records table. Select ID,BusinessEmail, FullName, Manager and FirstName fields using Output field selection table.

6. Add a Router snap to route to outputs, if the Manager is not null, route to the output0, else then route to the output1.

7. Once the Manager is not null, then add and configure a new Exact Online snap to retrieve the manager information via Payroll - Employees endpoint.  Filter the ID as $Manager from Filter Records table. Select BusinessEmail fields using Output field selection table. 

8. Add a Structure Snap to restructure the data stream after Read Snap. $original.Email → $EmailEmployee $BusinessEmail→ $EmailManager $original.original.AverageHours → $ContractHours $original.original.AverageHours*5 → $ContractHoursPeriod $original.original.original.Hours → $HoursBooked

9. Add another Structure Snap after Router Snap output1. $original.Email → $EmailEmployee $BusinessEmail→ $EmailManager $original.original.AverageHours → $ContractHours $original.original.AverageHours*5 → $ContractHoursPeriod $original.original.original.Hours → $HoursBooked

10. Add two Filter Snaps to Filter that if the Employee email is not null.

11. Add Email Sender Snaps respectively after two Filter Snaps and send emails to the employees and the managers of the employees. 

12. Finally add a Join Snap to join the responses from Emails Senders.

Building a pipeline to send emails to employees who don't book enough hours and their managers

To retrieve the employee that did not submit enough hours and send them emails to remind them submitting the hours. It is a sub-pipeline of the parental pipeline of Booked Hours Notifications.

1. Add and configure an Exact Online Read Snap to query Payroll - Employees data.  Filter EmployeeHID from the parental pipeline using Filter Records table. Select ID,BusinessEmail, FullName, Manager and FirstName fields using Output field selection table.

2. Add a Router snap to route to outputs, if the Manager is not null, route to the output0, else then route to the output1.

3. Add a mapper after output0 to map the employee data.  $BusinessEmail → $Email $FirstName→ $FirstName $ID → $ID $Manager → $Manager $original.HoursBooked → $HoursBooked $original.HoursDrafted → $HoursDrafted $original.HoursRejected→$HoursRejected $original.AverageHours → $AverageHours $original.ContractHours → $ContractHours

4. Once the Manager is not null, then add and configure a new Exact Online snap to retrieve the manager information via Payroll - Employees endpoint.  Filter the ID as $Manager from Filter Records table. Select BusinessEmail fields using Output field selection table. 

5. Add a Structure Snap to restructure the data stream after Read Snap. $BusinessEmail→ $EmailManager $original.Email → $EmailEmployee $original.FirstName → $FirstName $original.HoursBooked → $HoursBooked $original.HoursDrafted → $HoursDrafted $original.HoursRejected → $HoursRejected

6. Add another Structure Snap after Router Snap output1. $BusinessEmail→ $EmailManager $original.Email → $EmailEmployee $original.FirstName → $FirstName $original.HoursBooked → $HoursBooked $original.HoursDrafted → $HoursDrafted $original.HoursRejected → $HoursRejected $original.AverageHours → $ContractPeriod $original.ContractHours == null ? ($original.AverageHours/5) : ($original.ContractHours/5) → $ContractWeek

7. Add two Filter Snaps to Filter that if the Employee email is not null.

8. Add Email Sender Snaps respectively after two Filter Snaps and send emails to the employees and the managers of the employees. 

9. Finally add a Join Snap to join the responses from Emails Senders.

See Also

• SnapLogic Product Glossary

• Getting started with SnapLogic