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 ConditionsActions or Multi-sheet Decision Tables, please read the above articles if you need detailed configuration steps.

The rules around holiday allotments are detailed below:

Rule 1
Every employee receives at least 22 holiday days a year.
Rule 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.
Rule 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.
Rule 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 BPA Solution

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 DateTotal Annual Holidays and Total Years Service fields will be updated according to the rules in the Decision Tables.