Oh my god. It's full of code!

Easy Rollups using aggregate queries

So one thing I know that almost every salesforce user has wanted to do at some point is do rollups between objects that don’t have master detail relationships. I know this topic has been covered before by building custom counter code, but usually it’s kind of bulky and may have governor limit problems. What I have here is a simple an effective way to do your rollups using the under appreciated aggregate query function.

Essentially the idea is, your function takes a list of records. Iterate through the list, and find the Id’s of any related parent object. So if you were trying to calculate a rollup field on accounts that sum some value on contacts, your trigger would be on the contacts, and add every account id encountered in any of those contacts to a set.

Then using that set, run an aggregate query that does your summing or whatever for all those accounts. Iterate over the returned data set, extract the values, add them to the account object, then update the accounts. It might look something like this (provided you have a field on your contact object called age__c that is numeric and a numeric field on your account called totalAge__c).

The first two lines about creating contacts is just so you can test this in execute anonymous mode. I wanted to create some sample data. Like I said, normally the data you’d be iterating over to build the list of accountIds would be the contacts in the trigger context. When you go to use this, you can of course remove those lines.

//just get some sample contacts to play with. Normally the contacts would be in the trigger.new scope
list<contact> contacts = new list<contact>();
contacts = [select accountid from contact limit 500];


set<Id> accountIds = new set<Id>();
list<account> updateAccounts = new list<account>();

//add all accounts that are going to need their counters updated to the set
for(contact c : contacts)
{
    accountIds.add(c.accountid);
}

//run the query to sum the data
AggregateResult[] totalAges= [select  SUM(age__c)totalAge,accountId                     
                                                                  From Contact
                                                                  group by ROLLUP (accountId)];

//loop over the result set. One row per account with a totalAge and accountId column
for(AggregateResult ar: totalAges)
{
                Id thisAccountId = string.valueOf(ar.get('accountId'));
                Account thisAccount = new Account(Id=thisAccountId);    
               thisAccount.totalAge__c = double.valueOf(ar.get('totalAge'));
                updateAccounts.add(thisAccount);
}
update updateAccounts;

This works really well as an after trigger. Call it as an after trigger for insert, update, delete, and undelete. For insert update, and undelete pass it the trigger.new context of contacts, and for delete pass it the trigger.old context.

Anyway, I’ve used this approach several times and it works very nicely. It’s bulk safe, fairly fast, and easy to maintain and add many more counters to. You can just keep creating aggregations and fields to assign them to on the parent object. Hope this helps someone.

4 responses

  1. Anonymous

    Thank you for sharing your knowledge! It helped us!

    June 22, 2011 at 8:36 pm

    • I’m glad it helped you guys out! Thanks for the comment. Knowing that I am actually doing some good makes me keep blogging 😛

      June 22, 2011 at 8:39 pm

  2. Kothai

    Thanks for this great piece of code! I am struggling learning how to get google interactive charts to draw data off a soql query…and at the same time running out of roll up fields…so this looks perfect, once I work out how to adapt it! Cheers!

    August 16, 2011 at 11:25 am

  3. Kothai

    Hey Kenji, could you show what the code would look like if ALL the objects and fields were custom objects and custom fields? When I try to convert this code for use I can’t get it to work. The problem seems to lie in both how I express the custom object and custom fields in the query bit and also how I express the custom fields in the result syntax.

    In addition I want to AVG instead of SUM – but only counting the fields which are not equal to 0. Could you give me some pointers on how to adapt your code for that?

    August 19, 2011 at 1:20 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s