Views:

Scenario Overview

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.

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 Decision Tables and the FindSum 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 of type 'Save - To Parent'
  • Set the Event field to 'Create, Update & Delete'
  • Set the Source entity to 'Opportunity'  
  • Set the Source Property to 'Est. Revenue'    
  • Set the Relationship field to 'customerid (account)'
  • Set the Target Property field to 'Total open Opps'.
  • Copy & paste the formula below into the formula description field & click save
  • You are ready to test

Formula

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 1: You will need to put the two SetFindAnd lines in manually (or copy from the formula above)