Views:

Scenario Overview

This example shows how to set up a Multi-Sheet Decision Table to calculate the yearly holiday entitlements for employees based on their age and length of service.

This has previously been solved here using a single Decision Table, however with a Multi-Sheet Decision Table we can improve readability and reduce complexity to implement the business rules.

For this article it is assumed that you have at least basic familiarity with Decision Tables and/or have read the following articles:

Note: We will not detail step-by-step instructions here on how to set up Conditions, Actions or Multi-sheet Decision Tables, please read the above articles if you need detailed configuration steps.

Holiday Entitlement Rules

Rule #	Description
1	Every employee receives at least 22 holiday days a year
2	Only employees younger than 18 or at least 60 years, or employees with at least 30 years of service will receive 5 extra days.
3	Employees with at least 30 years of service and also employees of age 60 or more, receive 3 extra days, on top of possible additional days already given.
4	If an employee has at least 15 but less then 30 years, 2 extra days are given. These 2 days are also provided for employees of age 45 or more. These 2 days cannot be combined with the extra 5 days.

Each Contact record has 4 additional fields added to it.

Start Date
Next Anniversary Date
Total Annual Holidays
Total Years Service

Start Date is manually entered when the employee joins the company, but the other fields must be updated automatically by the Decision Table

To model these requirements we will create 4 Decision Tables Sheets:

Eligible for 2 Extra Days
Eligible for 3 Extra Days
Eligible for 5 Extra Days
Calculate Vacation Days

North52 Decision Suite

The BPA solution works like this:

A formula of type Save - Perform Action is created on the Contact entity
The Source Properties are set to Birthday and Start Date
The Event is set to Create & Update
Exit this Decision Table on First Match is turned On (checked) for each of the 'Eligible' Decision Table Sheets, it is turned Off (unchecked) for the Calculate Vacation Days Decision Table Sheet
Exit all Decision Tables on First Match is turned Off (unchecked) on all Decision Table Sheets

Formula

Setup

Create four new fields on the Contact entity and add them to the form:

Start Date
Next Anniversary Date
Total Annual Holidays
Total Years Service

Create Formula

Create a new formula, setting the following values in the Formula Guide
- Source Entity set to Contact
- Set Formula Type to Save - Perform Action
- Select the Decision Table editor
Change the Name of the formula to Contact - Calculate Vacation Days
Control-click to set the Source Properties to Birthday and Start Date
Click Save

Global Calculations Sheet

Before we create the Decision Table sheets we need to set up 3 Global Calculations. If the Global Calculations sheet is not visible right-click on the Decision Table canvas and select Operations > Toggle Advanced Mode

Create the following calculations:

AgeCheck - used to calculate the current age of the Contact
```
DateDiff([contact.birthdate], UtcDate(), 'y')
```
YearsOfService - used to calculate the how many years the employee has worked for the company
```
DateDiff([contact.north52_startdate], UtcDate(), 'y')
```

AnniversaryCalc - used to calculate the next employment anniversary for the employee

if ( AddYears([contact.north52_startdate], DateDiff([contact.north52_startdate], UtcDate(), 'y') ) > UtcDate(),
     AddYears([contact.north52_startdate], DateDiff([contact.north52_startdate], UtcDate(), 'y') ),
     AddYears([contact.north52_startdate], DateDiff([contact.north52_startdate], UtcDate(), 'y') +1 ))

Sheet 1: Eligible for 2 Extra Days

Double-click the first sheet name and type Eligible 2 Days to rename it
Select cell A2, then from the Source tab, expand Global Calculations
Click on AgeCheck
Select column B, right-click and select Insert > Insert Condition
Select cell B2, then from the Source tab, expand Global Calculations
Click on YearsOfService
In cell A5, enter >=45
In cell B4, enter ((15,30]) i.e. greater than or equal to 15 and less than 30
Select column C, right-click and select Delete > Delete Column to remove the default Action column
While in column C, right-click again and select Insert > Insert Inline Calculation (==)
Replace the Set Name default text with Eligible for 2 Days
In cells C4 and C5 type TRUE
In cell C6 type FALSE
Right-click on the Decision Table canvas and select Operations > Toggle Advanced Mode to hide row 3 and Global Calculations and Global Actions sheets
Click Save

Sheet 2: Eligible for 3 Extra Days

Right-click on the Decision Table canvas and select Insert> Insert Duplicate Decision Table
Click on the Eligible 2 Days sheet's name and drag it to the left of the duplicated sheet Copy - Eligible 2 Days - 0 to reorder them
Double-click the DecisionTable0 sheet name and type Eligible 3 Days to rename it
Change cell A5 to >=60
Change cell B4 to >=30
Change cell C2 to Eligible for 3 Days
Click Save

Sheet 3: Eligible for 5 Extra Days

Right-click on the Decision Table canvas and select Insert> Insert Duplicate Decision Table
Re-order the sheet so the newly copied sheet Copy - Eligible 3 Days - 1 is to the right of the Eligible 3 Days sheet
Double-click the Copy - Eligible 3 Days - 1 sheet name and type Eligible 5 Days to rename it
In cell A4 add <18
Clear cell B4
In cell B6 add >=30
Change cell C6 to TRUE
In cell C7 add FALSE
Change cell C2 to Eligible for 5 Days
Click Save

Sheet 4: Calculate Vacation Days

Right-click on the Decision Table canvas and select Insert > Insert Decision Table
Re-order the sheet so the newly copied sheet DecisionTable3 is to the right of the Eligible 5 Days sheet
Double-click the DecisionTable3 sheet name and type Calculate Vacation Days to rename it
Right-click on the Decision Table canvas and select Hit Policy > Exit this Decision Table on First Match to uncheck this setting. This is an important step as we want the Decision Table to process all rows even after a match has been found
Insert 2 more Condition columns
Select cell A2, then select the Source tab and expand Decision Table Calculations
Expand the Eligible 5 Days node and click on Eligible for 5 Days
Select cell B2, expand the Eligible 3 Days node and click on Eligible for 3 Days
Select cell C2, expand the Eligible 2 Days node and click on Eligible for 2 Days
Select column D, right-click and select Insert > Insert Increment Calculation (+=)
Replace the Set Name default text with Accumulated Holiday Days
Add 2 more Action columns after column E
Select cell E2, then select the Source tab and expand the Source node
Type total years in the Search Source input box and press enter
Click on Total Years Service form the Source node
Select cell F2, type next ann in the Search Source input box and press enter
Click on Next Anniversary Date form the Source node
Select cell G2, type total ann in the Search Source input box and press enter
Click on Total Annual Holidays form the Source node
Select cell D4, and type 22
Select cell A5, and type TRUE
Select cell D5, and type 5
Select cell B6, and type TRUE
Select cell D6, and type 3
Select cell A7, and type FALSE
Select cell C7, and type TRUE
Select cell D7 and type 2
Clear the search for the Source tab
Expand the Global Calculations and Decision Table Calculations nodes
Select cell E8, then click on YearsOfService from Global Calculations
Select cell F8, then click on AnniversaryCalc from Global Calculations
Select cell G8, then expand the Calculate Vacation Days node and click on Accumulated Holiday Days from Decision Table Calculations (you may need to hit the refresh icon beside the search box to see it)
Click Save

This table uses a Inline-Calculation (Column D) field to keep a running sum of the holiday entitlements based on the results of the individual Decision Table Sheets. You can think of this as a variable result which is then used to update the Total Annual Holidays field on the Contact entity.

Test

Create/update a contact record setting the Birthday and Start Date fields. The Next Anniversary Date, Total Annual Holidays and Total Years Service fields will be updated according to the rules in the Decision Tables.

Did you know?

xCache can help with advanced Case prioritization rules

Every organization has different rules for managing case prioritization, some can be very complex. With North52's business rules engine for Microsoft Dynamics 365 you can use the xCache functionality for impelementing highly efficient case prioritization rules.

Learn how we do it in this knowledge base article

Keywords: Decision Table, AddYears, DateDiff, UtcDate, DecisionTable, Formula Manager

xRM Formula #199 - Vacation Days part 2