Developing Custom Apex Roll-up Summary Field Logic

In this article, we will walk through creating custom roll-up summary field logic and how to avoid some common pitfalls. We will also explore various options for initiating a roll-up routine with tools such as Apex Triggers, Batch Apex, and Process Builder.

Advantages of Custom Apex Roll-up Summaries

Before we get into how to build custom apex roll-up summaries lets first sell the idea by highlighting some of the advantages of this technique. Here are just a few that come to mind but just know that there are a lot more, and this list could go on.

1. Summarize information for any parent-child relationship even if it is not a master-parent-child relationship.
2. Summarize information that is more than 1 level deep in your data hierarchy.
3. Perform other types of summaries not related to numbers such concatenating a set of values into a single text field.
4. Perform time-based summaries based on a specific date time.
5. Take a snapshot of information so that you can visualize how that information changes over time.

Properly Structuring Your Summary Logic

Interestingly, Salesforce’s roll-up summary fields make roll-ups seem trivial. From the UI, you indicate what to summarize, add selection criteria, and determine whether to count, sum or average your data. However, with Apex code you have a lot more flexibility in how and when information should be summarized adding complexity to your solution.

Here are some things to consider when thinking through how you will implement summary logic:

1. When performing real-time roll-up summaries using Apex Triggers, you will need to account for inserts, updates, deletes, and undeletes.
2. Do you have existing data in the system that will need to have summary information back-filled?
3. Will your data be time-dependent? Meaning a roll-up summary will need to occur when a record reaches a certain age or similar date time scenario.

As you can see from this short (but incomplete) list of things-to-consider implementing a custom rollup summary solution isn’t as simple as just querying the information, looping through it, and then performing an update. You will need to do it in a way that can accommodate different scenarios.

Here is a sample block of code that will serve as the entry point for our roll-up summary logic. For simplicity, we will use the scenario of rolling up closed/won opportunity amounts to a field on the account.

public static void rollupOpportunities(List<Opportunity> opportunities) {
    Set<Id> accountIds = new Set<Id>();

    //Get account Ids from specified opportunities
    for (Opportunity opp : opportunities) {
        accountIds.add(opp.AccountId);
    }

    if (accountIds.isEmpty() == false) {
        /*Execute as a future call so that the user doesn't have to wait around for
        the rollup to finish. Unless, already in a future or batch call state then
        just perform the rollup.*/
        if (System.isFuture() == false && System.isBatch() == false) {
            summarizeOpportunitiesAsync(accountIds);
        }
        else {
            new SampleRollupSummary().summarizeOpportunities(accountIds);
        }
    }
}

You may notice that I am just grabbing all of the account ids associated with the opportunities passed to the method. Here is where trying to do too much logic at this point could introduce a bug in your code. For instance, you might be tempted only to grab account ids for opportunities that are closed/won, but you have to consider all the different scenarios that could occur.

As an example, what if the user had accidentally set the opportunity to closed won, noticed their mistake, and then changed it back. Now, because it was closed/won your code will have already included the amount as part of the summary. If you only pull account ids for opportunities where the stage is closed/won, then you will miss the chance to remove the opportunity's amount from the summary.

One possible solution would be to look at the values of the opportunity before it was last modified. However, the only mechanism that provides those values are Apex Triggers, which means your code is highly dependent on triggers to function. Also, our scenario is fairly simple but when you have to start evaluating multiple criteria and summary fields the complexity of your code increases. With increased complexity comes higher maintenance cost especially since you are duplicating your criteria logic. First, to determine which opportunities to evaluate and secondly in your aggregate query or custom roll-up logic. Now you are left maintaining this logic in two places.

My solution to this problem is to evaluate all of the opportunities. It means the code runs for every change to opportunities and in the grand scheme of things might not be the most efficient method but it will be the most accurate. That is the balancing act we all have to undertake when developing solutions on the Force.com platform. In this case, I am going to value accuracy over performance. But that doesn’t mean there aren't things I can to do improve performance.

The next thing you should notice about this code is the methods that I call once I have the account ids that I need. You will notice that I check to see if my code is being executed as part of a future or batch transaction. If it isn’t, then I can go ahead and initiate my future (or asynchronous) method call to perform the roll-up summary logic. Using this technique the user can move on to other things while my code executes in the background.

So now let's take a look at the methods that do the actual work.

@future
public static void summarizeOpportunitiesAsync(Set<Id> accountIds) {
    new SampleRollupSummary().summarizeOpportunities(accountIds);
}

public void summarizeOpportunities(Set<Id> accountIds) {
    //Get Accounts to Update
    List<Account> accounts = queryAccountsById(accountIds);

    Map<Id, double> results = getOpportunityAmountsByAccountId(accountIds);

    //Loop Accounts and set Won Amount
    List<Account> accountsToUpdate = new List<Account>();
    for (Account acct : accounts) {
        double total = 0;

        if (results.containsKey(acct.Id)) {
            total = results.get(acct.Id);
        }

        //Determine if Total Amount has Changed
        if (acct.Total_Amount_Won__c != total) {
            acct.Total_Amount_Won__c = total;
            accountsToUpdate.add(acct); //Add account to collection to be updated
        }
    }

    if(accountsToUpdate.isEmpty() == false) {
        Database.SaveResult[] saveResults = Database.update(accountsToUpdate, false);
        System.debug(saveResults);
    }
}

The summarizeOpportunitiesAsync method is used to initiate our summary logic in an asynchronous transaction. This technique is primarily useful when using this code within the context of a trigger by performing our logic asynchronously; the system can then return control to the user improving response time. However, if your roll-up summary values are critical to the user real-time, you will want to make sure to skip using the asynchronous method and just call the synchronous method summarizeOpportunities directly.

The summary logic that I wrote follows a very specific recipe that requires two query method calls. The first is to retrieve the accounts that I will be updating. I do this so that I can get the current value of the account summary field we will update. That way I can perform the roll-up and then compare it to the existing value. If they match, then there is no reason to perform an update for that account, improving performance.

The second thing I do is to let Salesforce do most of the work by using an aggregate query that sums opportunity amounts grouped by account id. Once I have the required information, I can go ahead and run my logic to determine which account records need to be updated.

So now if we wanted to execute this roll-up summary logic we could easily just create a trigger on the opportunity object that will execute our code. Here is a sample of that.

trigger SampleRollupOppTrigger on Opportunity (after insert, after update, after delete, after undelete) {
    if (trigger.isAfter && (trigger.isInsert || trigger.isUpdate || trigger.isUndelete)) {
        SampleRollupSummary.rollupOpportunities(trigger.new);
    }
    else if (trigger.isAfter && trigger.isDelete) {
        SampleRollupSummary.rollupOpportunities(trigger.old);
    }
}

Notice that I can use the same method call for all DML operations by modifying where I pull the list of affected opportunity records. In the case of delete DML operations, the list of deleted opportunities is specified in the trigger.old property.

Performing Batch and Scheduled Roll-ups

So now let us discuss how we can execute a roll-up summary as a batch or scheduled job. We are going to use the same code that we used in the previous section, but we will be extending our SampleRollupSummary class with the Batchable and Schedulable interfaces.

Here is a partial sample of the class with the Batchable and Schedulable interfaces implemented.

global class SampleRollupSummary implements Database.Batchable<sObject>, Schedulable {
    //Batchable Methods
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([SELECT Id FROM Account]);
    }

    global void execute(Database.BatchableContext context, List<sObject> batch){
        Set<Id> accountIds = new Set<Id>();

        for (sObject acct : batch) {
            accountIds.add(acct.Id);
        }

        summarizeOpportunities(accountIds);
    }

    global void finish(Database.BatchableContext context) {}

    //Schedulable Methods
    global void execute(SchedulableContext context){
        SampleRollupSummary batchJob = new SampleRollupSummary();
        Database.executeBatch(batchJob);
    }
} 

So first a bit of caution, for the sake of simplicity my batch method is querying all accounts and executing the roll-up summary logic. The filter criteria you use will depend greatly on your use-case, and where possible you should have a filter to limit the number of records you process. But for simplicity we will just assume you need to update all accounts.

The methods in our batchable and schedulable interface methods are fairly straightforward. If executing in the context of a schedule, we just initiate a batch request. When running in the context of a batch, we collect the account ids for the records in the batch and then call our synchronous logic. You could also base your batch dataset on opportunities in this scenario and then call the rollupOpportunities method instead.

Executing Your Rollup Summary Logic from the Process Builder

Now let's look at how we can execute our roll-up summary logic using the process builder. As you will see in the example below because of the way we architected our solution, making our code available to the process builder is somewhat trivial.

Here is a partial sample of our class from the previous sections with an invocable method defined, which enables our roll-up summary logic in the process builder.

global class SampleRollupSummary implements Database.Batchable, Schedulable {

    //Invocable Method
    @InvocableMethod(label='Rollup Closed Won Opportunities')
    global static void rollupClosedWonOpportunities(List opportunities) {
        rollupOpportunities(opportunities);
    }
}

The following screenshot illustrates our roll-up summary logic method implemented in a process builder flow.

For the process flow, I configured it so that it will always run my roll-up summary logic. But the beauty of using the process builder is now you can declaratively control how and when the roll-up occurs. For instance, you could create criteria that only executes the rollup for certain record types or even just deactivate the process to stop performing the roll-up altogether. Compare this to triggers where you have to delete the trigger in a sandbox environment and then deploy that deletion request to production.

I hope you enjoyed this article and found it valuable. If you have any questions or comments feel free to leave a comment below. Also, for your benefit, below is a full copy of the sample rollup class used in this article.

global class SampleRollupSummary implements Database.Batchable<sObject>, Schedulable {

    //Invocable Method
    @InvocableMethod(label='Rollup Closed Won Opportunities')
    global static void rollupClosedWonOpportunities(List<Opportunity> opportunities) {
        rollupOpportunities(opportunities);
    }

    //Batchable Methods
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([SELECT Id FROM Account]);
    }

    global void execute(Database.BatchableContext context, List<sObject> batch){
        Set<Id> accountIds = new Set<Id>();

        for (sObject acct : batch) {
            accountIds.add(acct.Id);
        }

        summarizeOpportunities(accountIds);
    }

    global void finish(Database.BatchableContext context) {}

    //Schedulable Methods
    global void execute(SchedulableContext context){
        SampleRollupSummary batchJob = new SampleRollupSummary();
        Database.executeBatch(batchJob);
    }

    //Static Methods
    public static void rollupOpportunities(List<Opportunity> opportunities) {
        Set<Id> accountIds = new Set<Id>();

        //Get account Ids from specified opportunities
        for (Opportunity opp : opportunities) {
            accountIds.add(opp.AccountId);
        }

        if (accountIds.isEmpty() == false) {
            /*Execute as a future call so that the user doesn't have to wait around for
            the rollup to finish. Unless, already in a future or batch call state then
            just perform the rollup.*/
            if (System.isFuture() == false && System.isBatch() == false) {
                summarizeOpportunitiesAsync(accountIds);
            }
            else {
                new SampleRollupSummary().summarizeOpportunities(accountIds);
            }
        }
    }

    @future
    public static void summarizeOpportunitiesAsync(Set<Id> accountIds) {
        new SampleRollupSummary().summarizeOpportunities(accountIds);
    }

    //Public Methods
    public void summarizeOpportunities(Set<Id> accountIds) {
        //Get Accounts to Update
        List<Account> accounts = queryAccountsById(accountIds);

        Map<Id, double> results = getOpportunityAmountsByAccountId(accountIds);

        //Loop Accounts and set Won Amount
        List<Account> accountsToUpdate = new List<Account>();
        for (Account acct : accounts) {
            double total = 0;

            if (results.containsKey(acct.Id)) {
                total = results.get(acct.Id);
            }

            //Determine if Total Amount has Changed
            if (acct.Total_Amount_Won__c != total) {
                acct.Total_Amount_Won__c = total;
                accountsToUpdate.add(acct); //Add account to collection to be updated
            }
        }

        if(accountsToUpdate.isEmpty() == false) {
            Database.SaveResult[] saveResults = Database.update(accountsToUpdate, false);
            System.debug(saveResults);
        }
    }

    //Private Methods
    public Map<Id, double> getOpportunityAmountsByAccountId(Set<Id> accountIds) {
        Map<Id, double> resultsByAccountId = new Map<Id, double>();

        //Summarize Won Opportunity Amounts by Account Id
        AggregateResult[] results = aggregateOpportunityAmounts(accountIds);
        for (AggregateResult result : results) {
            Id accountId = (Id) result.get('Account');
            double total = (double) result.get('Total');

            resultsByAccountId.put(accountId, total);
        }
        return resultsByAccountId;
    }

    //Query Methods
    private List<Account> queryAccountsById(Set<Id> accountIds) {
        return [SELECT 
                Id 
                ,Total_Amount_Won__c
            FROM 
                Account 
            WHERE 
                Id IN :accountIds];
    }

    private AggregateResult[] aggregateOpportunityAmounts(Set<Id> accountIds) {
        return [SELECT 
                 AccountId Account
                ,SUM(Amount) Total 
            FROM 
                Opportunity
            WHERE
                IsClosed = true 
                AND 
                IsWon = true
                AND 
                AccountId IN :accountIds
            GROUP BY 
                AccountId];
    }
}