Scenario Overview

In this business scenario we 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 Opportunity records associated to the Account.

Whenever an Opportunity is transferred from one Account to another, we want this Total Open Opportunities to be recalculated for both the previous Account and the new Account. 

A key part of this requirement is that these two calculations must run in real-time. We can do this by creating two formulas, one to store the old Accounts accountid and the other to update the previous and new Accounts. 

North52 Decision Suite Solution

The North52 Decision Suite solution works like this:

• Each time an Opportunity's Account is changed, two formulas will trigger
• The first formula will store the previous Account's accountid.
• The second formula will calculate and update the Total Open Opportunities for both the old and new Account
  • The calculation 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 Total Open Opportunities 

North52 Decision Suite Steps

First Formula

• 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 Classic editor
• Change the Name of the formula to Store Previous Accountid
• Set the Event field to Create, Update & Delete
• Expand the Source & Target section
  • Set the Source Property to Account
• Expand the Deployment Settings section
  • Set Pipeline Stage to Pre-Operation (Synchronous)
• Copy and paste the following formula into the formula editor:

  SetSharedVariable('Previous Accountid', 
    
                    FindValue('opportunity',
                                 'opportunityid',
                                 [opportunity.opportunityid],
                                 'customerid',
                                 'Not Found')
                      )
  

• Click Save

Second Formula

• 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 Update both Accounts Total Open Opportunities
• Expand the Source & Target section
  • Set the Source Property to Account
• Click Save
• Select the Condition column, right-click then select Delete > Delete Column
• Select cell A2 in the Decision table and type Update old Account
• Select cell A4, expand the formula editor, copy and paste the formula:

  UpdateRecord('account',
              GetSharedVariable('Previous Accountid'), 
              SetAttribute('new_totalopenopportunites',
                            FindSum('opportunity',
                                    SetFindAnd('customerid', 'statecode'), 
                                    SetFindAnd(GetSharedVariable('Previous Accountid'), '0'),
                                    'estimatedvalue','0')))

• Close the cell formula editor
• Double click sheet tab DecisionTable1 and rename to Old Account
• Your Decision Table should look like this:
  
  
• Right-click and select Insert > Insert Decision Table
• Double click the sheet tab and rename the new sheet to New Account
• Select the New Account sheet
• Select the Condition column, right-click then select Delete > Delete Column
• Select cell A2, and type Update old Account 
• Select cell A4, expand the formula editor, copy and paste the formula:

  FindSum('opportunity',
          SetFindAnd('customerid', 'statecode'), 
          SetFindAnd([opportunity.customerid], '0'),
          'estimatedvalue','0')
  

• Click Save
• Your Decision Table should look like this:
  
  

Wizard - FindSum

Please see below the wizard you can use to create the FindSum() function call used in this formula if you wish to do it by hand

Note 1: You will need to put the two SetFindAnd lines in manually (or copy from the formula above)