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.

• Booked hours (status 10 & 20)

• Rejected Hours (status 2)

• Drafted Hours (status 1)

• No Hours (no status)

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

  • Employee

  • EmployeeHID

For the output3, an employee has booked no hours at all), add the child pipeline to process the no hours notification.

• Expression:

  • $original.Exmploye

  • 0

• Target path

  • $Employee

  • $Hours

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 .

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);

NA

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.

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) : 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).

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.

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 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.

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.

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 Join Snap.

Join the responses from Email Senders Snap.

Joined data of Result of sending email to the employee and manager.

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.

N/A

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.

Join the responses from Email Sender Snap.

Joined data of Result of sending email to the employee and manager.