The Scalable Way to Create Roll-Up Summaries Using Salesforce Flow

Roll-ups, as we all know, are an extremely powerful tool inside Salesforce. However, Salesforce’s out-of-the-box functionality only supports roll-ups in a master-detail relationship. Master-details, while helpful in certain scenarios, can also bring limitations in most desired relationships between two objects.

We’re going to discuss the various options for expanding the functionality of roll-ups, as well as how to create an invocable action in flows to run a roll-up summary declaratively – even if there is no lookup relationship between the two objects.

Roll-Up Summary Fields in Salesforce

The default roll-up summary fields are some of the first things that new Salesforce Admins learn. However, due to the drawbacks mentioned above, there have been several attempts to bring this power to standard lookup relationships, not just master-details.

Enter many tools, such as the open-source Declarative Lookup Roll-Up Summaries and the more productized Roll-Up Helper. These allow you to declaratively roll-up fields between two related objects with custom filters. All of these roll-up tools create even more power for admins and users.

However, three big drawbacks remain with all roll-up tools – the first is flexibility. With DLRS you can almost write your own SOQL, but you’re not able to fully customize the whole query. The second is that there must be a relationship between the two objects to do the roll-up inside these tools. The third is that you have to execute the roll-up in a separate transaction after you save the record, so the roll-up executes independent of any automations.

A lot of this came to a head at Strong CRM when we were creating complex quota management systems inside Salesforce. We didn’t want to create multiple lookup relationships for various quota types (Company, Manager, User, SDR, etc.), and we wanted the roll-up to execute in a specific order inside the flow so that we could roll-up from Fiscal Quarter to Fiscal Year quotas.

So, we wrote an invocable action that allows you to write custom SOQL inside a formula resource in a flow – executing that SOQL, and sending back the aggregate result to a variable. This invocable action creates a new way to do declarative roll-ups, and by the nature of being invocable, can be triggered when you want it inside of any flow. We’ve now expanded its use beyond Quota Management and have started using it in all flows.

Create an Invocable Action to Complete Roll-Ups Within a Flow

Here’s how it works:

1. Create a Flow and New Action

Inside a flow, create a new action and select Custom SOQL Query:

In the queryString box, create a new Formula resource. This is where you write your SOQL. The raw text output of the formula will need to be the exact SOQL query. Also, this is built to only spit out one number, so make sure the query is:

• An aggregate function
• Gives a number back
• Gives only one variable back

Put all the SOQL query items in quotes, close the quotes and add the ‘&’s to add in variables, and then reopen the quotes for more text.

Here’s an example of a correct SOQL Query:

Back in the Action Menu, open the Advanced dropdown, check Manually Assign Variables and assign the variable that the result should go to:

Make sure that this result variable is available for input and output since it will be used outside of the flow.

Click Done. And that’s it – the query will run in the flow when requested, and it will spit out the aggregate number when done. You can do this as many times as you like inside the flow (as long as it doesn’t hit Salesforce’s limits) with as much SOQL as you want to write in that box, as long as each query only returns one number.

2. Create a New Apex Class

To Install: In the Developer Console in a Sandbox, create a new Apex Class for the Custom SOQL Query invocable action. Call it CustomSOQLQuery.apxc.

global class CustomSOQLQuery {
	@InvocableMethod (label = 'Custom SOQL Query')
    global static List<Decimal> customQueries(List<QueryRequest> requests) {
        List<Decimal> results = new List<Decimal>();
        //loop through list of requests
        for (QueryRequest request : requests) {
      		results.add(customQuery(request));
    	}
        return results;
    }
	
    //actual work happens inside this method
    public static Decimal customQuery(QueryRequest request) {
        System.assert(request.queryString != '');
        
        List<AggregateResult> result = Database.query(request.queryString);
        
        Decimal sumAmount = 0;
        for (AggregateResult ar : result) {
			sumAmount = (Decimal)ar.get('expr0');
        }
        
        return sumAmount;
    }
    
    //custom class for input
    global class QueryRequest {
        @InvocableVariable(required=true)
        public String queryString;
    }
}

3. Create a Test Class

Then you’ll need a test class. Here’s one we’ve written for this:

@isTest
public class CustomSOQLQueryTest {
	@isTest
    private static void customQueryTest() {
        List<CustomSOQLQuery.QueryRequest> requests = new List<CustomSOQLQuery.QueryRequest>();
    	CustomSOQLQuery.QueryRequest request = new CustomSOQLQuery.QueryRequest();
        
        String testQuery = 'SELECT SUM(Amount) FROM Opportunity WHERE Id='0068c00000rTePSAA0'';
        request.queryString = testQuery;
        requests.add(request);
        
        CustomSOQLQuery.customQueries(requests);
        Decimal summedField = CustomSOQLQuery.customQuery(request);
    }
}

For the highlighted ID, make sure it’s an ID from an Opportunity in your dev instance. Run the test to ensure 100% code coverage (the test should give you 100%) and then deploy it to production.

Summary

There you go — you can now run fully custom declarative SOQL Queries inside flows, rolling up whatever data you like!

What has been your experience so far? Let us know in the comments.