Scenario Overview
In this business scenario we need to update open Opportunities on a daily basis to set a Remaining Business Days field. This provides useful information for reporting.
The solution is achieved by a formula combined with a daily Entity Update Schedule. The formula updates the Remaining Business Days field when the Estimate Close Date field changes. The schedule targets all open Opportunities and updates the Estimated Close field to its same value which then triggers the Formula to recalculate the date.
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 Estimate Close Date changes the formula 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. Always do a Publish All Customizations to ensure that any new fields are fully picked up by both Microsoft and North52
- 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 Opportunity - Update Remaining Business Days
- On the Register tab, expand the Source & Target section
- Select the Est. Close Date field in the Source Property list
- Click on cell A2, select the Source tab, type Est in the search field and press enter
- Expand the Source node and click on Est. Close Date to add it to the Condition column
- Click on cell A4, select the Functions tab, type contains in the search field and press enter
- Ctrl-click the ContainsData field
- Click on cell B2, type Remaining in the Source search field and press enter
- Expand the Source node and click on Remaining Business Days to add it to the Action column
- Click on cell B4, 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 going to insert the function directly into the cell for this example. If we wanted to show a more friendly name and move the function logic to the Global Actions sheet we would enter a name here and select Action from the Type drop down
- Click inside the From Date field. We are going to add a function to return Today's date rather than use a date field from the Opportunity entity.
- On the Function tab, type utcdate into the search field and press enter. Click on UtcDate to add this function to the From Date field of the wizard
- Click the search icon of the To Date field and select Est. Close Date
- Click Generate
- Click Save
Test the Formula
- Create an Opportunity and set the Est. Close Date field to test that the formula is functioning correctly
Scheduler
The following set of step 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 +1 days at 7am
- Click on the Advanced Find button:
- 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
