In this business scenario you have a requirement to set & continually update a custom field on the Account entity called 'Total Open Opportunities'. This field is the sum of the estimated revenue field from all the opportunity records associated to the Account. This is similar to xRM Formula #18 but this one executes the formula via a workflow.
A key part of this requirement is that roll-up must run in real-time.
North52 Decision Suite Solution
The North52 Decision Suite solution works like this,
- We create a formula of type 'Process Genie' for the Opportunity entity
- This formula will be executed via a workflow
- The workflow will be configured to execute each time an opportunity is Created, Updated ('Est. Revenue' or status field changes) or Deleted the formula will execute
- The execution consists of summing up all the related opportunities that are currently set to Open
- The result of the sum will be stored in the custom field of the parent Account record
North52 Decision Suite Steps
- Create a new formula of type 'Process Genie'
- Set the Display Format field to 'String'
- Set the Source entity to 'Opportunity'
- Copy & paste the formula below into the formula description field & click save
- Next create a new workflow as shown in the screenshot below.
- Set the the Record fields change to just the 'Estimated Revenue' field
- When you add the Process Genie (custom workflow activity) to the workflow click the 'View Properties' link & set the short-code to be that of the formula in this case iDX
- You are ready to test
Process Genie Configuration
UpdateRecord('account', [opportunity.customerid], SetAttribute('n52_openopportunities', FindSum('opportunity', SetFindAnd('customerid', 'statecode'), SetFindAnd([opportunity.customerid], '0'), 'estimatedvalue', '0')) )
Wizard - FindSum
Please see below the wizard you can use to create the FindSum() function call used in this formula.
Note that you will need to put the two SetFindAnd lines in manually (or copy from the formula above)
Did you know?
xCache can help with advanced Case prioritization rules
Every organization has different rules for managing case prioritization, some can be very complex. With North52's business rules engine for Microsoft Dynamics 365 you can use the xCache functionality for impelementing highly efficient case prioritization rules.