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.

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

Wizard - FindSum

 

Did you know?

You can Connect to 1000s of Web Services as part of your Decision Logic

When your advanced business rules and complex logic requries that you integrate with other systems North52's Web Fusion functionality can help.  It enables two-way data communication between your Dynamics 365/CRM system and another service or application.

Learn more and see examples of Web Fusion