Scenario Overview
In this business scenario we need to update open Opportunities on a daily basis to set a Remaining Business Days field.
We want this Remaining Business Days field to factor in our custom Calendar so that we know how many working days are left. This will provide useful information for reporting and scheduling.
We will also set up a Schedule that will periodically run this formula over all Open Opportunities to ensure fully updated Opportunities.
North52 Decision Suite Solution
The North52 Decision Suite solution works like this:
- A formula of type Save - Perform Action is created on the Opportunity entity
- When the Est. Closed Date changes, the formula is triggered
- The formula checks to see does Est. Closed Date contain data
- If it does, the formula sets the default Calendar for this formula to be Super Holidays
- It then calculates the remaining business days using the DiffWorkingDays function
- The formula then updates the Remaining Business Days field with the result of the calculation
North52 Decision Suite Steps
The following set of steps outline how to create this formula:
Note: you will need to create a new whole number field on the Opportunity entity called Remaining Business Days for this solution.
- Create a new formula, setting the following values in the Formula Guide:
- Source Entity set to Opportunity
- Set Formula Type to Save - Perform Action
- Select the Decision Table editor
- Change the Name of the formula to Update Remaining Business Days using Custom Calendar
- Click Save
- Make sure the Mode is set to Server Side
- Make sure the Event is set to Create & Update
- Expand the Source & Target section
- For Source Property, select Est. Close Date
- Select cell A2
- Go to the Source tab and search for Est
- Expand the Source node and click on Est. Close Date to add it to the Condition column
- Select cell A4
- Select the Functions tab, type contains in the search field and press enter
- Ctrl-click the ContainsData field
- Select cell B2 and right-click Insert > Insert Inline Calculation
- Type Set the Calendar for this Formula
- Select cell B4 and enter SetDefaultBusinessCalendar('Super Holidays')
- Note that Super Holidays is the custom Calendar, you will need to change this to your calendar name
- Select cell C2
- Click on the Source tab and search for Remaining
- Expand the Source node and click on Remaining Business Days to add it to the Action column
- Select cell C4
- Select the Functions tab, type Diff in the search field and press enter
- Shift-click the DiffWorkingDays function to open the function wizard:
- Leave the Friendly Name field blank as we are entering the function directly in the cell
- For From Date, enter UtcDate()
- For To Date, select Est. Close Date
- Click Generate
- Click Save
- Take note of the shortcode, it will be needed for the Schedule
Testing
- Create an Opportunity and set the Est. Close Date field to test that the formula is functioning correctly
Scheduler
The following set of steps outline how to set up the Schedule:
- Create a new Schedule
- Set the Name to Update Open Opportunities - Remaining Business Days
- Set the Frequency to Daily
- Set the Schedule Type to Entity Update
- Set the Start DateTime to tomorrow morning at 7am
- Open the Advanced Find:
- Set the Look For to Opportunities
- Set query criteria:
- Status Equals Open
- Est. Close Date Contains Data
- Click on Edit Columns
- Configure the columns so that only Est. Close Date is the only column. IMPORTANT: It is important that only the columns you wish to trigger an update on are included in the query.
- Click on the Download Fetch XML button
- Open the downloaded Fetch-Xml file in Notepad (not a browser like Internet Explorer)
- Copy all the Fetch-Xml text
- Paste the Fetch-Xml into the Fetch Xml Query section
- Click Save
- Select N52 Commands > Start Schedule to start the Schedule
Review the Results
Look at the Open Opportunity records in your system the following day to make sure that the Remaining Business Days fields have been updated.