Salesforce Roll-up Summary Utility for Lookup Fields with Filter

  • submit to reddit
Salesforce Roll-Up Summary Utility

Salesforce Roll-Up Summary Utility

A while back I wrote a Salesforce roll-up summary trigger which mimics the declarative roll-up summary functionality provided by Salesforce.com.  I received several comments from the community, some relating to additional functionality (such as MAX, MIN, and COUNT) others about filters and some about code structure. So I decided to revisit my original solution and put together something a bit more generic and robust.

*Note: I should mention that this utility can be used for objects with Master-Detail relationships as well as Look-up relationships. Also, this utility only provides summary for numeric fields (double, integer, decimal primitive types), it DOES NOT support text, date or any other primitive types. Finally, while this utility and the examples shown here are written for “bulk” processing, they have not been tested with large data sets (50K records or more).

Update (03/16/2012): I’ve updated the utility to support multiple fields with multiple operations. The utility is optimized for bulk processing.  This video outlines the changes:

Using the Salesforce sObject Method and Dynamic SOQL

In order to provide maximum flexibility for this utility class, I am employing the use of the sObject methods and dynamic SOQL.  Why is this useful? Good question. First, this allows the utility to be used across any sObject type (e.g. Standard and Custom Objects) without having to write more code (or rather, without having to write object specific code). Second, I wanted to build something that a beginner could use with minimal effort.

Salesforce Roll-up Summary Trigger with Utility Example

Below is a usage example of how this utility can be leveraged to create a simple roll-up summary trigger. For this example we go back to the Opportunity (parent) and Payments__c (child) scenario: we want to populate the Opportunity.Total_Payments__c field with sum of all Payments__c.Amount__c fields.

trigger OpportunityRollUpPayments on Payment__c (after delete, after insert, 
after update, after undelete) {
	
    if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
		
		list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
		new list<RollUpSummaryUtility.fieldDefinition> {
			new RollUpSummaryUtility.fieldDefinition('SUM', 'Amount__c', 
			'Total_Payments__c'), 
			new RollUpSummaryUtility.fieldDefinition('MAX', 'Amount_2__c', 
			'Total_Payments_2__c')
		};
        
        RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new, 
        'Payment__c', 'Opportunity__c', 'Opportunity', '');
        
    }
    
    if(trigger.isDelete){
        
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('SUM', 'Amount__c', 
            'Total_Payments__c'), 
            new RollUpSummaryUtility.fieldDefinition('MAX', 'Amount_2__c', 
            'Total_Payments_2__c')
        };
        
        RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old, 
        'Payment__c', 'Opportunity__c', 'Opportunity', '');
        
    }
	
}

Pretty simple, right? All we’re doing is passing a few variables across to the utility and it handles the rest for you. Now, let’s talk about these variables. I’ve updated the demo page to use this utility, feel free to try out the Salesforce Roll-up Summary utility demo.

Salesforce Roll-up Summary Utility Variables and Considerations

The utility accepts the following variables:


RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, records, childObject, 
childParentLookupField, parentObject, queryFilter);

fieldDefinitions (list<RollUpSummaryUtility.fieldDefinition>) – The list of field definitions.  The fieldDefinition class is comprised of three variables:

  1. operation (String) – Can be either SUM, MAX, MIN, or COUNT. When using COUNT, the childField must ALWAYS be ‘ID’.
  2. childField (String) – The api name for the child object’s field that contains the values you wish to summarize. Remember, when using the COUNT operation, the value for this variable must ALWAYS be ‘ID’.
  3. parentField (String) – The api name for the parent object’s field that where the summarized value of the child object field will be stored.

Here’s an example of how you can create a fieldDefinitions list:


list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('SUM', 'Amount__c', 'Total_Payments__c'), 
new RollUpSummaryUtility.fieldDefinition('MAX', 'Amount_2__c', 'Total_Payments_2__c')
};

records (list<sObject>) – The list of records being passed to the utility. When using with a trigger this can either be trigger.new or trigger.old. Tip: Use trigger.old when executing during a trigger.isDelete operation.

childObject (String) – The API name for the child object. This object must have either a look-up or master-detail relationship to a parent object.

childParentLookupField (String) – The API name for the child object field that performs a look-up to a parent object where the summary results will be stored.

parentObject (String) – The API name for the parent object related to the child object. The childParentLookupField variable must be related to the object supplied in this variable.

queryFilter (String) – An SOQL statement that will be used to filter the child object records. The string must always begin with ‘and’ if it’s being used. If no filter is needed simply supply an empty text value of ”, never supply a NULL value. Let’s say that based on the above example we only want to get a sum of payments made by check, our code would look like this:


RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old, 'Payment__c', 
'Opportunity__c', 'Opportunity', 'and Type__c = \'Check\'');

Code for Salesforce Roll-up Summary Utility

Finally, the star of the show. Here’s the code used by the utility.  I have not written a test class for it but you should be able to get started by looking at the original trigger I published 9 months ago. If I get some time I’ll put together the unit tests and publish them here but they will be specific to the example above. If you use the utility with, let’s say, an Account trigger, you will have to write your test class around the Account object.

public class RollUpSummaryUtility {
	
	//the following class will be used to house the field names
    //and desired operations
    public class fieldDefinition {
        public String operation {get;set;}
        public String childField {get;set;}
        public String parentField {get;set;}
        
        public fieldDefinition (String o, String c, String p) {
            operation = o;
            childField = c;
            parentField = p;
        }
    }
    
    public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
    list<sObject> records, String childObject, String childParentLookupField, 
    String parentObject, String queryFilter) {
        
        //Limit the size of list by using Sets which do not contain duplicate
        //elements prevents hitting governor limits
        set<Id> parentIds = new set<Id>();
        
        for(sObject s : records) {
            parentIds.add((Id)s.get(childParentLookupField));
        }
        
        //populate query text strings to be used in child aggregrator and 
        //parent value assignment
        String fieldsToAggregate = '';
        String parentFields = '';
        
        for(fieldDefinition d : fieldDefinitions) {
            fieldsToAggregate += d.operation + '(' + d.childField + ') ' + 
            ', ';
            parentFields += d.parentField + ', ';
        }
        
        //Using dynamic SOQL with aggergate results to populate parentValueMap
        String aggregateQuery = 'Select ' + fieldsToAggregate + 
        childParentLookupField + ' from ' + childObject + ' where  ' + 
        childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
        ' group by ' + childParentLookupField;
        
        //Map will contain one parent record Id per one aggregate object
        map<Id, AggregateResult> parentValueMap = 
        new map <Id, AggregateResult>();
        
        for(AggregateResult q : Database.query(aggregateQuery)){
            parentValueMap.put((Id)q.get(childParentLookupField), q);
        }
        
        //list of parent object records to update
        list<sObject> parentsToUpdate = new list<sObject>();
        
        String parentQuery = 'select ' + parentFields + ' Id ' +
         ' from ' + parentObject + ' where Id IN :parentIds';
        
        //for each affected parent object, retrieve aggregate results and 
        //for each field definition add aggregate value to parent field
        for(sObject s : Database.query(parentQuery)) {
            
            Integer row = 0; //row counter reset for every parent record
            for(fieldDefinition d : fieldDefinitions) {
            	String field = 'expr' + row.format();
                AggregateResult r = parentValueMap.get(s.Id);
                //r will be null if no records exist 
                //(e.g. last record deleted)
                if(r != null) { 
                    Decimal value = ((Decimal)r.get(field) == null ) ? 0 : 
                        (Decimal)r.get(field);
                    s.put(d.parentField, value);
                } else {
                    s.put(d.parentField, 0);
                }
                row += 1; //plus 1 for every field definition after first
            }
            parentsToUpdate.add(s);
        }
        
        //if parent records exist, perform update of all parent records 
        //with a single DML statement
        if(parentsToUpdate.Size() > 0) {
            update parentsToUpdate;
        }
        
    }

}

Please feel free to slice and dice this code all you want and please I encourage you to share your feed back (good or bad). I’m going to try to devote more time to this website so if you have any questions or need assistance please leave a comment here and I’ll get to you as soon as I can.


I have been working with the Salesforce.com platform since 2003 in both administrative and development roles. I also have experience in web design working with HTML, CSS, Javascript, and PHP. I hold certification for the Salesforce.com Developer program. I am also an employee of Model Metrics, a Salesforce.com company.

  • Michael Burton

    Would love for this to work. It would solve so many problems for point and click/code dabbler admins like myself. However, I copied your code at the top of the page for the apex class and copied your code for the trigger, substituting in the fields I’m trying to summarize. I then ran reports to compare the numbers and I’m not getting the numbers to balance. The count is off, which explains why my sum is off. I didn’t change the code for the apex class at all and below is the code for the trigger. Is there something else I need to be doing to get this to work?

    trigger OpportunityRollUpCCs on Consumer_Contract__c(after delete, after insert,
    after update, after undelete) {

    if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){

    list fieldDefinitions =
    new list {
    new RollUpSummaryUtility.fieldDefinition(‘SUM’, ‘Principal_Balance__c’,
    ‘Total_Servicing_Contract_Princ_Bal__c’),
    new RollUpSummaryUtility.fieldDefinition(‘COUNT’, ‘ID’,
    ‘of_Contracts__c’)

    };

    RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
    ‘Consumer_Contract__c’, ‘Client_Setup_Servicing__c’, ‘Opportunity’, ”);

    }

    if(trigger.isDelete){

    list fieldDefinitions =
    new list {
    new RollUpSummaryUtility.fieldDefinition(‘SUM’, ‘Principal_Balance__c’,
    ‘Total_Servicing_Contract_Princ_Bal__c’),
    new RollUpSummaryUtility.fieldDefinition(‘COUNT’, ‘ID’,
    ‘of_Contracts__c’)

    };

    RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
    ‘Consumer_Contract__c’, ‘Client_Setup_Servicing__c’, ‘Opportunity’, ”);

    }

  • Jose

    Anthony,

    First of all, thanks so much for this. This is a great utility and works for me so far but there is a minor issue, I am hoping you can shed some light. We use this for a Count operation for certain type of cases and store the number of those cases in a field in Accounts. The problem is that when we do any mass operation on cases that meet the criteria we obtain a non select query error.

    if((trigger.isUpdate&&trigger.isAfter&&!util1.inCaseTrigger_updatedDMCA )|| trigger.isUnDelete){

    list fieldDefinitions =

    new list {

    new RollUpSummaryUtility.fieldDefinition(‘COUNT’, ‘Id’,

    ‘DMCA_Offenses__c’)

    };

    RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,

    ‘Case’, ‘AccountId’, ‘Account’, ‘AND Status=’Closed’ AND RecordType.DeveloperName=’DMCA’ AND Response_Status__c!=’Valid Counterclaim’ AND Response_Status__c!=’Invalid Claim”);

    util1.inCaseTrigger_updatedDMCA=true;

    }

    Thanks

  • Michael Burton

    This has been a great help but I’m still having problems using it for a child record with over 100k records to get the right numbers. Any ideas on how to adjust the code?

  • Katina

    Thank so much Anthony for this sample!
    I have a really simple question. After getting the trigger and class into my sandbox, I keep getting this error message:

    “Error: Invalid Data.
    Review all error messages below to correct your data.
    Apex
    trigger CustomeParentAccountRollUpOpenOpps caused an unexpected
    exception, contact your administrator:
    CustomeParentAccountRollUpOpenOpps: execution of AfterUpdate

    caused by: System.QueryException: Invalid bind expression type of Id for
    column of type Decimal: Class.RollUpSummaryUtility.rollUpTrigger: line
    62, column 1″

    I’m can’t get to the bottom of it and I’m sure it’s as simple as changing the field data type. Are all the custom field listed in your trigger set as “Currency” data type?

  • Gerry Marletta

    Hi Anthony,

    Thanks for putting in the time to create this – it’s become invaluable for me and my org. I’ve had success using the trigger across a number of different scenarios. The current one I’m trying to set up is a roll up on # of Opps to a User. It works fine, but we would like to create a filter so that it only looks at Opps that were created this year. I’m still fairly new to Apex, and can’t quite figure out how to add that filter to the trigger. Any help would be greatly appreciated!

    -Gerry

  • http://www.anthonyvictorio.com/ Anthony Victorio

    Hi Gerry, I’m so sorry for not getting back to you sooner, for some reason Disqus was not notifying me of comments. Do you mind sending me an email to anthony.victorio@gmail.com and I’ll try to help you out.

  • http://www.anthonyvictorio.com/ Anthony Victorio

    Hi Katina, sorry for not responding sooner, Disqus has not been notifying me of new comments for some reason. The trigger is setup to handle decimal and integer values which should work fine with currency fields. Do you mind emailing me at anthony.victorio@gmail.com if you’re still having issues? I’ll see if I can help you out.

  • http://www.anthonyvictorio.com/ Anthony Victorio

    Hi Jose, sorry for not getting back to you sooner, I have not been getting my comment notifications. Do you mind emailing me at anthony.victorio@gmail.com if you still need assistance?

  • asharnettffdc

    Any way of setting the trigger to be before insert and before update? The code I have to say is fantastic. And most important its free. Fantastic, now if it could only write the test script itself. :)

  • Jacob

    I’m working on my own set of rollup methods and came upon this page. I had a question regarding changing parents of a child and if your code covers that scenario? From what I can see with my quick-look, it appears that you only pass in the ‘new’ child records when updating; this would lead me to believe that changing a parent on a child record would not update the old parent’s summary:
    Child.Parent__c = ABC
    ABC.SumOfChild = 100
    Update Child.Parent__c to XYZ
    XYZ.SumOfChild = 100
    ABC.SumOfChild does not get updated, as there is no reference in the trigger.New to the old parent relationship.

    Am I just missing something or am I incorrect in my thinking and understanding?

  • http://www.facebook.com/people/Bill-Thril/100001329495561 Bill Thril

    Nice clean code! Wow, I am so impressed at what came up in the logs – with the amount of records you put in and to get such a low SOQL query count… I am not worthy! I am not worthy! Beautifully done!

  • http://www.facebook.com/people/Bill-Thril/100001329495561 Bill Thril

    Had the same problem, you just need to make a small change to the trigger code:

    Change “if(trigger.isDelete)” to “if(trigger.isDelete || trigger.isUpdate)”

    This fires the trigger on delete OR update, and will update the old parent record if you simply re-home the child or delete the parent reference.

  • Stephanie Arce

    THANK YOU! As a newbie to Apex this was very easy to understand.

  • Lloyd

    Any chance getting a copy of the test class. I’ve been trying to come up with but but am having problems. Thanks.

  • adriana

    Anthony,
    This works great! However, when I run reports for Opportunities, my SUM fields don’t update. One of the fields in the other object I am summing is a formula and that one is the one that doesn’t show me the real SUM once I want to use it back in the Opportunity record. Any ideas?

  • Kory Howard

    This is ridiculously useful, thank you!

  • Lynne

    Thanks so much for this very helpful trigger and class! I really can use this.

  • Stephanie Arce

    I’m wondering if I could get help: for the queryFilter, is it possible to use a relative date like THIS_MONTH? I’m not getting any syntax errors in the trigger itself, but it’s not updating my parent record. If I take out the filter it does work.

  • Amanda

    Hi Anthony, Thank you so much for putting this together. I’ve used it so many times in my org. I have a question about division, though. If I use a trigger to sum two values would it be possible to divide those two values to get the weighted average before populating the field on the object? I’m trying not to use additional fields if I don’t have to.

    Record 1 10 hours sold at $100/hr
    Record 2 3 hours sold at $150/hr

    I’d like the output of the trigger on the parent record to equal $111.54. (10*100 + 3*150)/13

  • deepthi

    I am using the above code for account and opportunity

    That is i am updating the account when a new opportunity is created but i am unable to save the record its showing the below error

    Error: Invalid Data.
    Review all error messages below to correct your data.
    Apex
    trigger RollUpSummary caused an unexpected exception, contact your
    administrator: RollUpSummary: execution of AfterInsert

    caused by: System.SObjectException: Invalid field Account for
    Opportunity: Class.RollUpSummaryUtility.rollUpTrigger: line 26, column 1

Read previous post:
Salesforce Roll-Up Summary Trigger
Salesforce Roll-Up Summary Trigger Revised

Why on earth would anybody want a Salesforce trigger to perform a roll-up summary type function when Salesforce already provides...

Close