Scenario Overview

In this business scenario you have a requirement to set and 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 open Opportunity records associated to the Account.

A key part of this requirement is that roll-up must run in real-time.

Note: This scenario is also outlined in other articles using the Classic editor and FindSumFD (Fetch-Xml) methods.

North52 Decision Suite Solution

The North52 Decision Suite solution works like this:

• We create a formula of type Save To Parent for the Opportunity entity
• Each time an Opportunity is Created, Updated (only when  'Est. Revenue' 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, 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 Total Open Opportunities on Account
• Change the Event field to Create, Update & Delete
• Expand the Source & Target section, and ctrl-select Est. Revenue and Status Reason
• Click Save
• Click on cell A2
• Select the Source tab, enter est into the search field and press enter
• Expand the Source node and select Est. Revenue to add it to cell A2
• Click on cell A4
• Select the Functions tab, type contains in the search field and press enter
• Ctrl-click the ContainsData function to add it to cell A4
• Click on cell B2
• Select the Source tab, click the X icon to clear any previous search
• Expand the Related (N:1) node
• Expand the the account (parentaccountid) node
• Expand the (Attributes) node
• Enter Total Open in the search field and press enter to filter down the attributes
• Select Total Open Opportunities to add it to cell B2
• Click on cell B4
• Select the Functions tab, type findsum in the search field and press enter
  • Shift-click the FindSum function to open the function wizard (see screen capture below)
  • Leave the Friendly Name field blank as we are going to insert the function directly into the cell for this example
  • Click the Search Icon for the Entity Name field and type opp, select Opportunity from the filtered list
  • Click inside the Where Attribute field and then select the Functions tab (our query checks two parameters so we cannot use the simply search icon in this scenario)
    • Enter setfind in the search field and press enter 
    • Click the SetFindAnd function to add the placeholder parameters to the Where Attribute field
    • Remove all text to the right of the opening ( bracket
    • Select the Source tab, enter Account in the search field and press enter
    • Expand the Source node if necessary and ctrl-click on Account (Value) to add 'parentaccountid'
    • Type a comma
    • Enter status in the search field and press enter
    • Ctrl-click on Status to add 'statecode'
    • Type a close bracket )
    • The Where Attribute should have the text: SetFindAnd('parentaccountid',  'statecode')
  • Click inside the Equals Value field and then select the Functions tab
    • Enter setfind in the search field and press enter 
    • Click the SetFindAnd function to add the placeholder parameters to the Where Attribute field
    • Remove all text to the right of the opening ( bracket
    • Select the Source tab, enter Account in the search field and press enter
    • Expand the Source node if necessary and click on Account (Value) to add [opportunity.parentaccountid]
    • Type a comma
    • Enter status in the search field and press enter
    • Expand the Status node
    • Click on Open to add 0
    • Type a close bracket )
    • The Equals Value should have the text: SetFindAnd([opportunity.parentaccountid],  0)
  • Click the Search Icon for the Select Attribute field and type est, select Est. Revenue from the filtered list
  • Set the Default Value field to 0 
  • Leave No-Lock set to true
  • Click Generate
• You are now ready to test

Wizard - FindSum