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 FindSum 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

Create the Formula Detail record

The benefit of using a Fetch-Xml query to find records is that you can make the query (using the Advanced Find) as complex as you like, and you can also pass parameters into the query to make it fully dynamic.

• Select the Fetch-Xml tab, and click the + button to create a new Formula Detail Record
• Set the name to Find Open Opportunities for Account
• Click on the Advanced Find button:
  • Set the Look For to Opportunities
  • Set query criteria:
    • Status Equals Open
    • Account Equals [select any Account]
  • Click on Edit Columns
    • Configure the columns so that only Est. Revenue is the only column shown (by selecting only the columns you needs makes the query more efficient).
    • Click OK
  • 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
• Replace the value="{GUID HERE}" with value="@accountid@" - this means that when the Fetch-Xml query is executed it will replace @parentaccountid@ with the GUID of the Account which is calling the formula
  • For tidyness you can remove the uiname and uitype parameters as they are not needed as below:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="estimatedvalue" />
    <attribute name="opportunityid" />
    <order attribute="estimatedvalue" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="parentaccountid" operator="eq" value="@parentaccountid@" />
    </filter>
  </entity>
</fetch>

• Click Save & Close to return to the formula 

Configure the Decision Table

• 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 FindSumFD 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 Formula Detail (Query) field and select the Find Open Opportunities for Account option
  • Click inside the Field Name field and then select the Fetch-Xml tab (we are going to specify that the estimatedvalue field of the is returned from our query)
    • Expand the Query Details node
    • Expand the Find Open Opportunities for Account node
    • Click on estimatedvalue
  • Set the Default Value field to 0 
  • Leave No-Lock set to true
  • Click Generate
• You are now ready to test

Wizard - FindSum