Create Advanced Roll-Up Summaries Using Salesforce Flow & Invocable Actions

In my last article about roll-ups for Salesforce Ben, I talked about how we developed a scalable way to do roll-up summaries within flows with a simple bit of Apex code deployed as an invocable action. Right after I wrote that article, however, I had a new challenge.

I was building a complex quota management system inside Salesforce for a client, and they wanted to split quotas across a various number of people due to an acquisition, while also rolling up data across multiple product lines and forecast categories. This was a significant amount of data being rolled up at once and became too heavy of a load for a single SOQL query at a time.

So, we put our heads together and wrote a variation of our invocable action – this time allowing for a roll-up of 10 aggregate fields at a time. This took what used to take 10 invocable actions, or 10 DLRS summaries, and collapsed them into one Flow transaction.

The one catch is that you have to be rolling up the same set of records in that one transaction, which works as long as you create formula fields to only fill in values if the conditions are met – then you roll up the formula fields. Let’s take a closer look at that…

An Example of Why You Need This

Let’s say we have a quota management tracker that we want to build. This is a very typical engagement for us – it’s usually one of the first things we do in our managed services engagements because it makes tracking individual rep performance towards goal much easier. This is also a perfect candidate for rolling up data because quota trackers don’t just need to track your closed won deals, they should also track your pipeline. Do you have a chance of hitting your quota that month, quarter, or year? What if you weighed your pipeline? You want to get those metrics in there too.

Multiple Roll-Ups in One Transaction in a Flow

Here’s how to use invocable actions to run 10 roll-up summaries at a time in a flow.

Step 1: Create a New Apex Class

To install: In the Developer Console in a Sandbox, create a new Apex Class for the Multiple SOQL Queries invocable action. Call it MultipleSumsSOQLQuery.apxc.

global class MultipleSumsSOQLQuery {
  @InvocableMethod (label = 'Multiple Sums SOQL Query')
    global static List<QuerySumsResult> customQueries(List<QueryRequest> requests) {
        List<QuerySumsResult> results = new List<QuerySumsResult>();
        //loop through list of requests
        for (QueryRequest request : requests) {
          results.add(customQuery(request));
      }
        return results;
    }
  
    //actual work happens inside this method
    public static QuerySumsResult customQuery(QueryRequest request) {
        System.assert(request.queryString != '');
    System.debug(request.queryString);        
        List<AggregateResult> result = Database.query(request.queryString);
        System.debug(result);
        
        Decimal sum0, sum1, sum2, sum3, sum4, sum5, sum6, sum7, sum8, sum9;
        for (AggregateResult ar : result) {
      sum0 = getExprNum(ar, '0');
            sum1 = getExprNum(ar, '1');
            sum2 = getExprNum(ar, '2');
            sum3 = getExprNum(ar, '3');
            sum4 = getExprNum(ar, '4');
            sum5 = getExprNum(ar, '5');
            sum6 = getExprNum(ar, '6');
            sum7 = getExprNum(ar, '7');
            sum8 = getExprNum(ar, '8');
            sum9 = getExprNum(ar, '9');
        }
        QuerySumsResult sumsResult = new QuerySumsResult();
        sumsResult.sum0 = sum0;
        sumsResult.sum1 = sum1;
        sumsResult.sum2 = sum2;
        sumsResult.sum3 = sum3;
        sumsResult.sum4 = sum4;
        sumsResult.sum5 = sum5;
        sumsResult.sum6 = sum6;
        sumsResult.sum7 = sum7;
        sumsResult.sum8 = sum8;
        sumsResult.sum9 = sum9;
        
        /*AggregateResult[] testResults = [
            SELECT SUM(Amount) FROM Opportunity
        ];
        for (AggregateResult ar : testResults) {
            System.debug(ar);
        }*/
        
        return sumsResult;
    }
    
    public static Decimal getExprNum(AggregateResult ar, string num) {
        try {
            return (Decimal)ar.get('expr' + num);
        } catch (Exception e) {
            return 0;
        }
    }
    
    //custom class for input
    global class QueryRequest {
        @InvocableVariable(required=true)
        public String queryString;
    }
    //custom class for output
    global class QuerySumsResult {
        @InvocableVariable
        global Decimal sum0;
    
        @InvocableVariable
        global Decimal sum1;
    
        @InvocableVariable
        global Decimal sum2;
        
        @InvocableVariable
        global Decimal sum3;
        
        @InvocableVariable
        global Decimal sum4;
        
        @InvocableVariable
        global Decimal sum5;
        
        @InvocableVariable
        global Decimal sum6;
        
        @InvocableVariable
        global Decimal sum7;
        
        @InvocableVariable
        global Decimal sum8;
        
        @InvocableVariable
        global Decimal sum9;
    }
}

roll-up summaries

Step 2: Create the Fields You Need to Roll Up

All the fields should be on the same object and should be available in the same filters – so use formula fields to provide different values. Below is an example of showing ARR (which is just a function of Amount/Term) that is only on Opportunities with the Forecast Category: Commit.

Step 3: Create a New Flow and New Action

Inside the flow, create a new action and select MultipleSumsSOQLQuery.

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. Since this query is built to give you up to 10 numbers, make sure your query has:

• Up to 10 aggregate functions, ordered in the same way you want to get the results back.
• Aggregate functions that give a number 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 variables that the results should go to. Remember they will come back in the order that you requested them in the SOQL query, so make sure that your variables stay in that order.

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

And that’s it – the query will run in the flow when requested, and it will spit out the aggregate numbers 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.

Step 4: Create a Test Class

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

@isTest
public class MultipleSumsSOQLQueryTest {
  @isTest
    private static void customQueryTest() {
        List<MultipleSumsSOQLQuery.QueryRequest> requests = new List<MultipleSumsSOQLQuery.QueryRequest>();
      MultipleSumsSOQLQuery.QueryRequest request = new MultipleSumsSOQLQuery.QueryRequest();
        
        String testQuery = 'SELECT SUM(Amount), SUM(ExpectedRevenue) FROM Opportunity';
        request.queryString = testQuery;
        requests.add(request);
        
        MultipleSumsSOQLQuery.customQueries(requests);
        MultipleSumsSOQLQuery.QuerySumsResult summedFields = MultipleSumsSOQLQuery.customQuery(request);
        //System.debug(summedFields.sum0 + ' ' + summedFields.sum2);
    }
}

roll-up summaries

Run the test to ensure 100% code coverage (the test should give you 100%) and then deploy it to production.

Summary

That’s it! Now you can run up to 10 aggregate queries at a time in a flow, which drastically reduces the load on your Salesforce instance, and enables significant functionality with a couple of clicks. Who knew there was an even more scalable way to create roll-up summaries using Flow – you do now!