Views:

Overview

In this business scenario we want to measure the duration of the lunch break on Appointments. We have two custom option set fields, Lunch-break start time and Lunch-break finish time, that are made up of 15 minute time slots, for example: 12:00, 12:15, 12:30, 12:45, 13:00.

We want to put the difference of these two "times" into another custom field called Lunch-break duration.

We will do this using North52's Split function in conjunction with its DateDiff function. 

 

North52 BPA Solution

The North52 BPA solution works like this:

  • A Formula is set up that triggers whenever one of the Appointment's Lunch-break start time or Lunch-break finish time fields are changed
    • This formula will split the hours from the minutes for each of the option sets
    • It will then create a DateTime type for each of these values
      • Both DateTimes will have the same year, month and day - only the hour and minutes will be different
    • The formula will calculate the difference between these two DateTimes
    • Finally, the Lunch-break duration field will be updated with the difference between both DateTimes

Formula

  • Create a new formula, setting the following values in the Formula Guide:
    • Set Entity to Appointment
    • Set Formula Type to Save - Perform Action
    • Set Editor to Decision Table
  • Click Create
  • Change the Name of the formula to Calculate time spent on lunch break using option sets
  • Expand Source & Target
    • Under Source Property, select Lunch-break start time and Lunch-break finish time 
      •  Note 1: you multi-select fields by holding down Ctrl when clicking
      •  Note 2: These are custom fields and they will not appear unless you've already created them
  • Click onto the Decision table editor
    • Select cell A2
      • Right-click and select Delete > Delete Column
    • Right-click and select Operations > Toggle Advanced Mode
    • Select the Global Calculations sheet
      • For Calculation Name enter Start and Finish time differences 
      • For Calculation Value, click the + in the top-right corner and enter the formula below:
        Smartflow(
        
          SetVar('Start_Hour',Split([appointment.new_lunchbreakstarttimename],':',0)),
          SetVar('Start_Minute',Split([appointment.new_lunchbreakstarttimename],':',1)),
          SetVar('Start_Time', CreateDate(2019,1,1,GetVar('Start_Hour'),GetVar('Start_Minute'))),
          
          SetVar('Finish_Hour',Split([appointment.new_lunchbreakfinishtimename],':',0)),
          SetVar('Finish_Minute',Split([appointment.new_lunchbreakfinishtimename],':',1)),
          SetVar('Finish_Time', CreateDate(2019,1,1,GetVar('Finish_Hour'),GetVar('Finish_Minute'))),
          
          DateDiff(GetVar('Finish_Time'), GetVar('Start_Time'),'mi'))
      • Close the advanced editor by clicking on the + again
  • Select cell A2
    • Go to the Source tab and expand Source.
    • Find and select Lunch-break duration
  • Select cell cell A4
    • Go to the Source tab and collapse Source
    • Expand Global Calculations and select Start and Finish times difference
  • Click Save

Testing

To test this formula, simply create or update an Appointment's Lunch-break fields.

Note you may need to add the fields to the Appointment's form. 

Did you know?

North52's Data Packager helps move configuration data between instances

North52 Data Packager is an add-on for North52 Business Process Activities which enables you to easily move data from one instance to another.  Users simply select the records they would like to package up and North52 Data Packager adds the data to a data package sheet, ready for export. Moving the records to another instance requires the package solution to be imported and ‘unpacked’ on the target instance.

North52 Data Packager is ideal for:

  • Moving Dynamics configuration records
  • Copying Dynamics Portal records
  • Distributing demonstration data

Learn more about the North52 Data Packager