Views:

Scenario Overview

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

Formula

UpdateRecord('opportunity',
              [opportunity.opportunityid],
              SetAttribute('new_daysopen', DateDiff([opportunity.createdon], UtcDateTime(), 's') / 86400)
)

Workflow Steps

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

Configuration of Workflow 
 
Configuration of Process Genie Step

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>

Wizard - DateDiff()

Please see below the wizard you can use to create the DateDiff() function call used in this formula.