Exact Online Use Case: Sending Emails to Employees (Defaulters) per Week

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

Understanding the Solution 

Following are the key steps in this use case:

  1. Send email notifications to inform the employees about booking the hours.

  2. Check the scheduled hours.

  3. Send email notifications to employees and their managers who do not book any hours.

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

No.

Step

Configuration

Result/Output

No.

Step

Configuration

Result/Output

Snap 1

Add and configure an Exact Online Read Snap to query Payroll - Employments data. 

Select EndDateEmployeeEmployeeHID 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, QuantityHourStatus, DateCreated, 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 .

Check the scheduled hours 

This Pipeline checks if the number of hours submitted by employees every week meets the number of hours per week agreed in the employee's contract. It is a sub-Pipeline of the parent Pipeline of Booked Hours Notifications.

Download this Pipeline.

Snap

Step

Configuration

Result/Output

Snap

Step

Configuration

Result/Output