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.
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.
Add two Filter snaps to filter out employees that are no longer active and employees that need to be excluded (based on requirements).
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.
Add a mapper to map the ID as $Employee.
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.
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).
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
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.
--Add a JSON Splitter Snap with json path of $group.
--Add a Sort Snap with Sort path of $Employee, Sort order of global and Sort order(global) of ascending.
--Add a Group by Fields Snap with fields of $Employee and $EmployeeHID.
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.
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.
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.
...
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.
...
In this article
Problem
A company uses Exact Online to manage the payroll process by ensuring that their employees book the work hours in a timely manner and on the correct projects. For consultancy-related projects, the timely booking of hours also ensures that the correct invoices are sent to customers in a timely manner. The process of checking on the timely and complete booking of hours is tedious and time-consuming. Although there are some build-in notification options for this issue, they have their limitations.
A general reminder can be sent at the end of the week when an employee has not booked all their hours for the current week. However, this message does not provide further details on the booked hours; as in whether they are rejected hours or drafted hours or need to be submitted. Also, the reminder is sent only once, there are no options for follow-ups or for checks on a longer period (for example the end-of-month closure).
Solution
Using the Exact Online Snap Pack, the organization can 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 personalized email is sent 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 the Exact Online portal is added, so that the employee can rectify the issue right away from the email notification.
Prerequisites
A valid Exact Online Account
A valid Email Account for Email Sender
Understanding the Solution
Following are the key steps in this use case:
Send email notifications to inform the employees about booking the hours.
Check the scheduled hours.
Send email notifications to employees and their managers who do not book any hours.
Send email notifications to employees and their managers who do not book enough hours.
Send email notifications to inform the employees about booking the hours
This Pipeline checks the booked hours for all the employees per week. Based on the contract working hours per week, the Pipeline sends email notifications to employees and their managers if they do not book sufficient hours or do not book any hours.
...
Download this Pipeline.
| Expand |
|---|
| title | Pipeline Configuration |
|---|
|
No. | Step | Configuration | Result/Output |
|---|
Snap 1 | Add and configure an Exact Online Read Snap to query Payroll - Employments data. | Select EndDate, Employee, EmployeeHID and ID fields under the Output Field Selection field set. | All records of Employments data from the ExactOnline API. | Snap 2, 3 | Add two Filter Snaps to filter employees that are no longer active and employees that need to be excluded (based on requirements). | Filter expression for non-active members:
$EndDate == null || (($EndDate.replace("/Date(","").replace(")/","")) > Date.now().getTime()) Filter expression for excluded members:
_EmployeesExcluded.indexOf($EmployeeHID) == -1 | All records of active Employees. | Snap 4 | Add and configure an Exact Online Read Snap to query Payroll - Employees data. | Filter EmployeeHID using the Filter Records |
|
...
field set.
Select ID and EmployeeHID fields using the Output |
|
...
Field Selection field set. | All records of active Employees data from the ExactOnline API. | Snap 5 | Add a Mapper Snap to map the ID as $Employee. | Map the $ID with $Employee. | Data of all IDs of active employees. | Snap 6 | Add and configure an Exact Online Read Snap to query Project - TimeTransactions data. | Filter Employee |
|
...
field using the Filter Records |
|
...
field set and filter the Date field to only return data for the last 5 weeks. Select Employee, Quantity, |
|
...
...
Created, and Project fields using Output |
|
...
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).
...
Field Selection field set. | TimeTransactions data of active employees from ExactOnline API. | Snap 7 | Add a Router Snap to route the hours based on their status. | Add four output views in the Router Snap. | Routes the output from the upstream Snap to four output views. | Snap 8,17, 18, 19. 20 | Add three Aggregate Snaps and one Mapper Snap for each status of the hours, summing up the number of hours grouped per employee. |
|
...
| For the three Aggregate Snaps configure the settings as follows: Function: SUM Field: $Quantity Result field:
|
|
...
...
...
GROUP-BY fields Field $Employee $original.EmployeeHID
Output field
For the output3, an employee has booked no hours at all), add the child pipeline to process the no hours notification. |
|
...
...
HourStatuses) and create an overview per employee. |
|
...
--Add a JSON Splitter Snap with json path of $group.
...
--Add a Sort Snap with Sort path of $Employee, Sort order of global and Sort order(global) of ascending.
...
--Add a Group by Fields Snap with fields of $Employee and $EmployeeHID.
...
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.
...
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.
...
Finally add a join snap to merge the response from email sending child pipelines.
...
Building a pipeline to check the scheduled hours
...
| Combined data of Employee and EmployeeHID with fields of HoursBooked(10 --and 20), HoursRejected(2), HoursDrafted(1). | Snap 14 | Add the child Pipeline Booked Hours Notifications. | Check Hours to check the scheduled hours. Learn more, Check the scheduled hours. | The data of employeeID with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours). | Snap 15 | Add the child Pipeline Booked Hours Notifications. | Send Email to send notifications via email. Learn more,Send emails to employees who don't book enough hours and their managers. | Response Data of email sending result from Pipeline Learn more, Send emails to employees who don't book enough hours and their managers. | Snap 16 | Finally add a join snap to merge the response from email sending child Pipelines. | | Joined result data of child Pipelines Send emails to employees and their managers who do not book any hours. Send emails to employees and their managers who do not book enough hours . |
|
Check the scheduled hours
This Pipeline checks if the number of hours submitted by employees every week meet meets the amount number 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.Pipeline of the parent Pipeline of Booked Hours Notifications.
...
Download this Pipeline.
| Expand |
|---|
| title | Pipeline Configuration |
|---|
|
Snap | Step | Configuration | Result/Output |
|---|
Snap 1 | Add and configure an Exact Online Read Snap to query Payroll - Employments data. |
|
...
| Filter EmployeeHID from the |
|
...
parent Pipeline using Filter Records table. Select ID and StartDate fields using Output |
|
...
Field Selection table. | All records of Employments data filtered by EmployeeHID from ExactOnline API. | Snap 2 | Add a |
|
...
Mapper Snap to map the field from the step 1. |
|
...
| Configure the StartDate field using the expression: $StartDate.replace("/Date(","").replace(")/","") |
|
...
, and map it to $StartDate. | All records of mapped Employments data with reformed StartDate field. | Snap 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. | All records of Schedules data filtered with employeeID from previous steps. | Snap 4 | Add a Router Snap to distinguish if an employee has been employed for less than 5 weeks. As we are looking back on a 5 week period, it is possible that an employee has been employed for less |
|
...
than 5 weeks. In this case we want to compare the booked hours with the expected hours booked of this smaller period. |
|
...
| We use a Router Snap to distinguish between the |
|
...
two 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); |
|
...
...
Mapper Snap to output0 (Employee has been working 5 weeks or more) to calculate Average Hours. | Check the mapper settings in the Pipeline. Note that the Average Hours is mapped as $AverageHours*5 -> $AverageHours, as the AverageHours field from the output is an average per week, we need to multiply it by 5, to reflect the 5 weeks period. We also include the employee details and booked hours overview from the input. | Employee details data with employee hours and average hours per week. | Snap 6, 7, 8 | Add three new Mapper Snaps to output1 (Employee has been working for less than 5 weeks), they are used to add current date time, |
|
...
calculate the contract hours and compute the average hours. | The first Mapper Snap determines the starting date of the employment and today. The second Mapper Snap calculates the number of hours in a day an employee is working (contractually) and the number of days the employee has been employed. In the line of (Math.floor(($Today-$original.StartDate) / 86400000)) - ((Math.floor(($Today-$original.StartDate) / 86400000)/7)*2) -> $DaysInService, this line of Mapper determines the date-difference between the starting date of the employee and today. Then the weekend-days are distracted to reflect the number of working days. The third Mapper Snap determines both the contract hours and the AverageHours, based on the DaysInService. In the line of $ContractHoursPerDay * $DaysInService -> $AverageHours,by this line of mapper, the contract-hours per day * the number of working days for this period, determines the number of hours an employee should have booked during the checked period (contractually) | Employee details data with employee hours, average hours per week, and contract hours. | Snap 9 Snap 5, 6, 7, 8 Snap 4 | Add a Join Snap () with Join Type as Merge to merge the data from mappers after output0 and |
|
...
output1 of Router Snap. | Joined data for previous mappers. | NA | Snap 10 | Add a |
|
...
Mapper Snap (to map the hour types |
|
...
. This step groups the hours to the different statuses and calculates the contract hours (if available). | ContractHours' in $ ? ($ContractHours*5) :
|
|
...
Add a Filter Snap to filter the hour types with the Filter expression $HoursBooked.HoursBooked < $AverageHours.
...
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
Image Removed
Building a pipeline to send emails to employees who don't book any hour and their managers
...
null -> $ContractHours indicates if an employee has contract hours, they are timed by 5 to reflect the 5 week-period. If not, the field remains null.
| Employee details data with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours). | Snap 11 | Add a Filter Snap to filter the hour types with the Filter expression $HoursBooked.HoursBooked < $AverageHours. This step filters out the employees that booked less hours over the last 5 weeks then their contract specifies. | | Employee details data with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours) filtered by expression $HoursBooked.HoursBooked < $AverageHours. | Snap 12 | Finally, add a Structure Snap () to restructure the data stream. | | Restructured employee details data with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours). |
|
Send emails to employees and their managers who do not book any hours
This Pipeline retrieves the employees that have not submitted hours yet and sends them reminder emails about submitting the hours. It is a sub-pipeline of the parental pipeline of Booked Hours Notifications.Pipeline of the parent Pipeline of Booked Hours Notifications.
...
Download this Pipeline.
| Expand |
|---|
| title | Pipeline configuration |
|---|
|
Snap | Step | Configuration | Result/Output |
|---|
Snap 1 | Add and configure an Exact Online Read |
|
...
Snap to query Payroll - Employments data. | Filter |
|
...
...
parent Pipeline using Filter Records table. Select ID and StartDate fields using Output |
|
...
...
All records of Employments data EmployeeHID from ExactOnline API. | Snap 2 | Add a Structure Snap to restructure the fields from step1. |
|
...
| Configure the StartDate field using the expression: $StartDate.replace("/Date(","").replace(")/","") |
|
...
, and map it to $StartDate. | All records of mapped Employments data with reformed StartDate field. | Snap 3 | Add and configure a new Exact Online Read Snap to query HRM - Schedules data. | Filter Employment as |
|
...
$EmploymentId from the mapper of step 2 using Filter Records table. Select AverageHoursfields using Output |
|
...
Field Selection table. | All records of Schedules data filtered by employeeId from ExactOnline API. | Snap 4 | Add a Filter |
|
...
Snap to filter the data with |
|
...
condition of $AverageHours > $original.Hours to ensure that the number of contract hours is larger than the booked hours. | Schedules data filtered by the expression $AverageHours > $original.Hours. | N/A | Snap 5 | Add and configure an Exact Online Read Snap to query Payroll - Employees data. | Filter EmployeeHID from the |
|
...
parent Pipeline using Filter Records table. Select ID, BusinessEmail, FullName, Managerand FirstName fields using Output |
|
...
Field Selection table. | All records of Employees data filtered by EmployeeHID from ExactOnline API. | Snap 6 | Add a Router |
|
...
...
the outputs. | Route the output based on the following conditions: if the Manager is not null, route to the output0, else then route to the output1.
| N/A | Snap 7 | Once the Manager is not null, then add and configure a new Exact Online |
|
...
Read 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. | The record of Manager data from ExactOnline API. | Snap 8 | Add a Structure |
|
...
Snap to restructure the data stream after Read Snap. |
|
...
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
...
| Restructured data of the manager. | N/A | Snap 6 Snap 11 | Add another Structure Snap after Router Snap’s output1. | | Restructured data of the employee. | Snap 9, 12 | Add Email Sender Snaps respectively |
|
...
. | Send emails to the employees and the managers if available of the employees. | Result data of sending email to the employee and manager(if have). | Snap 10 | Finally add a |
|
...
Building a pipeline to send emails to employees who don't book enough hours and their managers
...
Join Snap. | Join the responses from Email Senders Snap. | Joined data of Result of sending email to the employee and manager. |
|
Send emails to employees and their managers who do not book enough hours
This Pipeline retrieves the employees that did not submit enough hours and send them reminder emails to remind them for submitting the hours. It is a sub-pipeline Pipeline of the parental pipeline of Booked Hours Notifications.parent Pipeline of Booked Hours notifications.
...
Download this Pipeline.
| Expand |
|---|
| title | Pipeline Configuration |
|---|
|
Snap | Step | Configuration | Result/Output |
|---|
Snap 1 | Add and configure an Exact Online Read Snap to query Payroll - Employees data. | Filter EmployeeHID from the |
|
...
parent Pipeline using Filter Records table. Select ID, BusinessEmail, FullName, Manager and FirstName fields using Output field selection table. | All records of Employments data filtered by EmployeeHID from ExactOnline API. | Snap 2 | Add a Router |
|
...
...
output. | Route the output based on the following conditions: |
|
...
...
Mapper Snap after output0. | Map the employee data. | The mapped employments data with new field names. | Snap 4 | Once the Manager is not null, then add |
|
...
...
Read Snap. | Configure the Snap to retrieve the manager information |
|
...
through Payroll - Employees endpoint. Filter the ID |
|
...
$Manager from Filter Records table. Select BusinessEmail fields using Output |
|
...
Field Selection table. | The record of Manager data from ExactOnline API. | Snap 5 | Add a Structure Snap. | Configure the Snap to restructure the data stream after Read Snap. |
|
...
| Restructured data of the manager. | Snap 9 | Add another Structure |
|
...
Snap after Router Snap output1. |
|
...
Add two Filter Snaps to Filter that if the Employee email is not null.
...
Add Email Sender Snaps respectively after two Filter Snaps and send emails to the employees and the managers of the employees.
...
Finally add a Join Snap to join the responses from Emails Senders.
...
See Also
...
| | Restructured data of the employee. | Snap 6, 10 | Add two Filter Snaps. | Configure the Snaps to filter the employee emails that are not null. | The employee data with a valid email address. | Snap 7, 10 | Add Email Sender Snaps after two Filter Snaps. | Configure the Snaps to send emails to the employees and their respective managers. | Result data of sending email to the employee and manager. | Snap 8 | Add a Join Snap. | Join the responses from Email Sender Snap. | Joined data of Result of sending email to the employee and manager. |
|
Downloads
| Info |
|---|
Important Steps to Successfully Reuse Pipelines Download and import the Pipeline into SnapLogic. Configure Snap accounts as applicable. Provide Pipeline parameters as applicable.
|
...
Related Links
