You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 11
Next »
In this article
Problem
XYZ 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 project(s). 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 XYZ 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 Exact Online portal is added, so that the employee can rectify the issue right-away from the email notification.
Prerequisites
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.
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
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, HourStatus, Date, Created, and Project fields using Output 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: HoursBooked(10 --and 20) HoursRejected(2) HoursDrafted(1)
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. Learn more, Send email notifications to employees and their managers who do not book any hour. | Employee and EmployeeHID data based on the group by result. |
Snap 9, 10, 11, 12, 13 | Add a Union Snap, Group by N Snap, JSON Splitter snap, Sort Snap and Group by Fields Snap. | Combine the output from the different routes (HourStatuses) and create an overview per employee. | 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 amount of hours submitted by employees every week meet the amount of hours per week agreed in the employee's contract. It is a sub-Pipeline of the parent Pipeline of Booked Hours Notifications.
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); | |
Snap 5 | Add a 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. | |
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) : 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 send them reminder emails about submitting the hours. It is a sub-Pipeline of the Parental Pipeline of Booked Hours Notifications.
Pipeline configuration
Snap | Step | Configuration | Result/Output |
|---|
Snap 1 | Add and configure an Exact Online Read Snap ( to query Payroll - Employments data. | Filter Employee from the parent Pipeline using Filter Records table. Select ID and StartDate fields using Output Field Selection table. | 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. | |
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 Snap to route 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.
| - |
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. | Restructured data of the manager. | |
Snap 6 Snap 11 | Add another Structure Snap ( after Router Snap output1. | | Restructured data of the employee. |
Snap 9,12 | Add Email Sender Snaps respectively ( to 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 Join Snap to join the responses from Emails Senders. | | Joined data of Result of sending email to the employee and manager. |
Send emails to employees who do not book enough hours and their managers
This Pipeline retrieves the employees that did not submit enough hours and send them reminder emails for submitting the hours. It is a sub-Pipeline of the Parent Pipeline of Booked Hours notifications.
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 Snap to route to output. | Route the output based on the following conditions: If the Manager is not null, route to the output0, else route to the output1.
| |
Snap 3 | Add a 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 a new Exact Online 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. | | 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 to join the responses from Email Sender Snap. | | Joined data of Result of sending email to the employee and manager. |
Downloads
No files shared here yet.
