Pipelines: Sending emails to employees that have not reported enough hours via Exact Online per week
In this article
- Scenario
- Requirements
- Configuration
- Building a pipeline to send notifications using email to inform the employees booking the hours
- Building a pipeline to check the scheduled hours
- Building a pipeline to send emails to employees who don't book any hour and their managers
- Building a pipeline to send emails to employees who don't book enough hours and their managers
Scenario
Your company is using Exact Online to manage the payroll process by collecting ......
Requirements
Snaps Used
For this scenario, the following Snaps are used:
- Exact Online Read Snap
- Filter Snap
- Mapper Snap
- Router Snap
- Aggregate Snap
- Union Snap
- Join Snap
- Group By N Snap
- Group By Fields Snap
- JSON Splitter Snap
- Sort Snap
- Pipeline Execute Snap
- Restructure Snap
- Email Sender Snap
Other Requirements
- A valid Exact Online Account
- A valid Email Account for Email Sender
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.
- 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 HourStatusses: 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 (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.
- --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
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.
- 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.
- 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
- 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.
- 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); .
- 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 ..............
- 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 ..............
- Add a Join Snap with Join Type as Merge to merge the data from mappers after output0 and output1 of Router Snap.
- 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 ..............
- 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
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.
- 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.
- Add a restructure snap to restructure the fields from step1. $original.Employee → $EmployeeId $original.Hours → $Hours $ID → $EmploymentId ($StartDate.replace("/Date(","").replace(")/",""))→ $StartDate
- 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.
- Add a Filter snap to filter the data with condition of $AverageHours > $original.Hours.
- 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.
- Add a Router snap to route to outputs, if the Manager is not null, route to the output0, else then route to the output1.
- 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.
- 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
- 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
- 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.
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.
- 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.
- Add a Router snap to route to outputs, if the Manager is not null, route to the output0, else then route to the output1.
- 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
- 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.
- 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
- 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
- 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.