In this scenario we needed to implement a business rule that will calculate the number of days between two dates on the Opportunity entity. The result of this calculation to be place into a field called Days Open on the Opportunity entity.
A schedule will also be created to execute each night which will keep this field continuously updated.
N52 Formula Manager Solution
The Formula Manager solution works like this,
- Use the DateDiff() function
- The first parameter defines the startdate, which in this example is a field on the Opportunity entity
- The second parameter is the enddate, which uses the UtcDateTime() to return the current date & time
- The third parameter defines the time interval to return, in this case seconds. This allows us to accurately calculate (2 decimal places) the the number of days between the 2 dates
- We divide the result by 86400 (seconds in a day) to get out final result
- This result is then used to update the field 'new_days' field on the Opportunity entity
- Now we can create a workflow to call this Process Genie
- The workflow will execute the Process Genie
- Finally a North52 Schedule will be used to find all open opportunities & execute the workflow above for each Opportunity record.
N52 Formula Manager Steps
The following set of steps outline how to create this Formula
- Create a new formula of type 'Process Genie'
- Set the Source Entity to 'Opportunity'
- Copy & paste the formula below into the formula editor
- Click save
- You can now execute this formula using the N52 Process Genie (i.e. custom workflow activity) in a Dialog, Workflow or Action
UpdateRecord('opportunity', [opportunity.opportunityid], SetAttribute('new_daysopen', DateDiff([opportunity.createdon], UtcDateTime(), 's') / 86400) )
The following set of steps outline how to create the required workflow
- Create a new workflow called 'Daily Update of Days'
- And configure is as per the 2 screenshots below
- Note the shortcode will need to be the one generated on your formula
N52 Scheduler Manager Steps
The following set of steps outline how to create this schedule
- Create a new schedule & name it 'Daily Update of Days'
- Set the Frequency to 'Daily'
- Set the Schedule Type to 'Workflow'
- Set the Workflow field to 'Daily Update of Days'
- Set the start datetime
- Copy & paste the fetch-xml below into the query field
- Click save
- Finally click 'N52 Commands -> Start Schedule' to start the schedule running
Fetch - XML
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="opportunity"> <attribute name="name" /> <attribute name="customerid" /> <attribute name="estimatedvalue" /> <attribute name="statuscode" /> <attribute name="opportunityid" /> <order attribute="name" descending="false" /> <filter type="and"> <condition attribute="statecode" operator="eq" value="0" /> </filter> </entity> </fetch>