• Tweet

• Tweet
• 

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.