Rollup Summary for Account Hierarchy with Flow | Salesforce Developer Guide

Introduction

Salesforce Account Hierarchies are great for modelling global organisations or large corporations with a complex structure and multiple subsidiaries. However, there is a catch in the way the hierarchy is modelled. You might have thought "Great, I'll create all the accounts and then we'll roll everything up the hierarchy". Guess what! You can't really do that out of the box with roll-up summary fields. Rollup summaries are easy to set up but they only work for Master-Detail relationships and the account hierarchy is not built on Master-Detail relationships. There are 2 very popular (and old) ideas on enabling rollup summaries for account hierarchy and lookup relationships [1][2] but, based on the replies from Salesforce, it seems that this functionality won't be coming anytime soon. 

There are 3 ways one can work around this. 

1. An AppExchange app
2. A flow
3. Apex Triggers

Each of the methods has its own pros and cons:

Solution	Pros	Cons
AppExchange App	Easy setup Tested No coding skills required More efficient for multiple fields More advanced functionality	Potential Licensing costs
Flow	No licensing No coding skills required	Effort to build and test Not triggered on deletion
Trigger	No licensing More flexible compared to flow	Coding skills needed Effort to build and test

These solutions work for the account hierarchy but also for standard lookup relationships between standard and custom objects. In this article, we'll follow method #2 and build a set of flows to tackle the problem. Before going through the solution, keep in mind that we can't trigger flows on object deletion so there is one limitation we won't be able to workaround.

The Scenario

Let's work with a relatively common scenario. You have a global hierarchy of accounts and you'd like to know how the amount of closed opportunities rolls up the hierarchy. Another scenario you might come across are accounts that belong to a holdings company.

There are a couple of things to consider before starting to build our flow. We can roll up the number of closed opportunities to the related account with a rollup summary field. Let’s call that field Closed Deals (Closed_Deals__c). The rollup type should be SUM of the Amount field and we’ll also need to filter the Opportunity records with Won equals True.

Fig. 1: Creating the Closed Deals roll-up summary field.

Before starting creating more fields and building our flow, let’s think a bit about how to roll up the Closed Deals field. I have set up an example in Figure 2 to make it easier. 

Fig. 2: Example account hierarchy and closed opportunities.

Acme is a global account with a number of entities around the world. European country accounts fall under the Europe account and the Europe account falls under the global entity. There is also a US account under Acme Global. Each account has its own Closed Deals based on the opportunities directly related to it. Acme Spain has Closed Deals = $240k and Acme Europe has Closed Deals = 100k since only one opportunity is directly related to it. I’m assuming that every account in the hierarchy can have direct opportunities. Therefore we need 2 more fields. One to capture the opportunities of the account’s children (Acme Germany and Acme Spain for Acme Europe) and one to capture the sum of opportunities of children and direct opportunities.

• Closed Deals of Children (Closed_Deals_of_Children__c): sum of opportunities of all the children related to the account. This is a simple Currency field with a default value of 0.
• Closed Deals Total (Closed_Deals_Total__c): opportunities of all the children plus the direct opportunities. This is a simple formula field with the formula Closed_Deals_of_Children__c + Closed_Deals__c

Using the 3 fields that we have defined, we can start building our flow.

Building the Flow(s)

Let’s start by understanding the logic of our flow. When an opportunity of Acme Spain closes or its amount is modified, the rollup summary field Closed Deals on the account changes. This change should fire our flow which should recalculate the Closed Deals of Children for Acme Europe. The change in Acme Europe should fire another instance of the flow to calculate the sums for Acme Global. This way all the sums would be kept up to date. The logic can be summarised in the following steps:

1. Opportunity Amount or Status is modified
2. Closed Deals on Account is modified
3. Flow is triggered by the change
4. Find the Account’s parent
5. Loop through its children and sum up the Closed Deals Total field of all the children
6. Update the Closed Deals of Children field of the Parent Account record
7. The change in the Closed Deals of Children triggers another instance of the flow (step 3 for the parent).
8. Steps 3-7 are repeated until there is no parent account for the record that triggered the flow.

This can be covered with one flow but there is one small complication. We have to cover the possibility of reparenting the account. Let’s say that Acme restructures its hierarchy and we want to capture that. If we change the parent of an account, our flow would run for the new parent but “forget” to run for the old parent and update it. This might not be the most common scenario but it makes sense to cover it since we can.

We basically have to call the flow we described earlier one more time for the previous parent.

We’ll build 2 flows: one for calculating and updating the Closed Deals of Children values and one to decide if the parent was modified and we need to run it one more time for the previous parent.

Don't forget to check out: Schedule Your Path in Salesforce Flow | Developer Guide

Updating Our Roll-up Summary Field

Let’s start building the inner flow that we’ll call Calculate Closed Deals. Below is what our flow will look like. First, let’s start by creating an Auto-launched flow. We will be able to call this flow from the outer flow when the record is updated.

Fig. 3: Calculate Closed Deals Flow overview.

First, let’s create an input variable for the Parent Account record and name it ParentAccount. Don’t forget to mark it as Available for Input so that the outer flow will be able to pass the parent account record to it.

Fig. 4: Create ParentAccount input variable.

The next step is to get all the children accounts of our parent account. We’ll use a Get element for that.

Fig. 5: Get children accounts.

This will create a collection of all the children of the parent account so that we can loop through them. Next, we need to create a loop element to do that. 

Fig. 6: Loop through the accounts.

At this point, we should also create another variable to store the value of the sum while looping. We’ll create a currency variable called Closed_Deals_of_Children with an initial value of 0. 

Fig. 7: Creating a variable to store the sum of closed deals.

Every time we loop through an element, we’ll add the value of Closed_Deals_Total__c of the loop item to the Closed_Deals_Of_Children variable with an Assignment element. 

• Variable: {!Closed_Deals_Of_Children}
• Operator: Add
• Value: {!Sum_Loop.Closed_Deals_Total__c}

Fig. 8: Add Closed Deals Total from each account to the Closed_Deals_of_Children variable.

After the last element, we’ll use one more Assignment element to assign the value of the Closed_Deals_Of_Children variable to the Closed_Deals_of_Children__c field of the parent account.

Fig. 9: Assign the sum to the parent account.

Last, we need to update the parent account record in the database. We’ll use an Update element to do that.

Fig. 10: Update the parent in the database.

Our first flow is done. We can test, save and activate it. All that’s left now is to call it at the right time for the right records.

Calling the Flow

The second flow must be record triggered and run every time an account is updated. We’ll also add some criteria to make the flow run only when any of the following apply:

• Closed Deals is changed: a change in an opportunity 
• Closed Deals of Children is changed: a child account is updated 
• ParentId is changed: the account is reparented

We’ll also select the optimise the flow for Actions and Related Records since we want the flow to act on the parent of the account that triggered it and not the account itself.

Fig. 11: Configure starting conditions for the flow.

Our flow will look like this:

Fig. 12: Flow overview.

We need to check a couple of things before calling the Calculate Closed Deals flow. First, we need to check if the Account has a parent. If it does, we’ll get the parent and pass it to the Calculate Closed Deals flow. If it doesn’t we’ll skip this part. Next, we need to check if the account has been reparented and had a parent before the parent was modified. If yes we’ll call the flow for the previous parent.

Let’s dive into the details.

First, we’ll use a Decision element to check if there is a parent by checking if the ParentId field is null.

Fig. 13: Decision element configuration.

Next, we use a Get element to get the parent account from the database.

Fig. 14: Getting the parent account record.

Check out another amazing blog by Vasilis here: Bypassing the “Number of Iterations Exceeded” Error in Salesforce Flows with Platform Events

Now that we have the parent account we can pass it to a subflow element that calls the Calculate Closed Deals flow. Don’t forget to set the input value for the flow.

Fig. 15: Calling the Calculate Closed Deals subflow.

Next, we need to check if the prior record’s parent is the same as the record’s parent. In other words, if the record used to have a parent and has been reparented or used to have a parent and now has none. We’ll do that with a Decision element. The conditions we’re checking for is: 

• $Record.ParentId is changed 
• $Record__Prior.ParentId is not null.

Fig. 16: Decision element for previous parent.

If the conditions are satisfied (Updated path) we’ll have to get the previous parent using another Get element.

Fig. 17: Getting the previous parent account record.

Now we can call the Calculate_Closed_Deals flow for the previous parent using a Subflow element.

Fig. 18: Calling the Calculate Closed Deals subflow for the previous parent.

And that’s it. Our flow is done! Now every time we update an opportunity or account, the account hierarchy is updated. We can also refactor these flows in the future to make them evaluate more than one rollup field since the logic is very similar. The only case that is not covered is when an account is deleted since the flow we built won't be triggered. The rollup summaries that we calculated wouldn’t be correct until another account with the same parent is updated and triggers the process for the parent. If this is not a show stopper, a flow can be an easy way to solve the problem without resorting to development or an AppExchange app.

Vasilis Papanikolaou

Vasilis is a Salesforce consultant, developer and Partner at Robin Consulting. At Robin, we provide strategic advice, process definition and technology solutions that help companies grow.

Follow us on LinkedIn here https://www.linkedin.com/company/robin-consulting-gr/

References

[1] Roll-up Summary Fields for Account - Account (Hierarchy) Relationship,
https://ideas.salesforce.com/s/idea/a0B8W00000GdopmUAB/rollup-summary-fields-for-account-account-hierarchy-relationship

[2] Eliminate Need for Master-Detail Relationship for Roll-ups, https://ideas.salesforce.com/s/idea/a0B8W00000Gdb2FUAR/eliminate-need-for-masterdetail-relationship-for-rollups