Views:

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.

N52 Formula Manager Solution

The Formula Manager 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

N52 Formula Manager 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
    • Leave No-Lock set to true
    • Click Generate
  • You are now ready to test

Wizard - FindSum