1

What are the different possible ways through which we could achieve Rollup summary fields? We could have it when we have Master-Detail relationship, what if we don't have one and how else could we achieve it?

1

A trigger is the way to do it when there's not a M-D relationship. There's a freebie App on the App Exchange called Roll-up Helper that will do it for you on a limited number of fields and objects. The code for it is available in a repository on Github for the Declarative Rollup Helper.

3
  • Or use Declarative Lookup Rollup Summary from Andrew Fawcett at github.com/afawcett/declarative-lookup-rollup-summaries – cropredy Sep 22 '15 at 19:37
  • @crop1645 What do you think the link above goes to where it says 'Declarative Rollup Helper'? – crmprogdev Sep 22 '15 at 20:54
  • 1
    Rollup Helper is an appexchange product (upgradeable to a paid version w more feats) - I thought the code for it was separate from DLRS (the link you provided - and free). – cropredy Sep 22 '15 at 22:43
1

As mentioned in crmprogdev's answer, you'll need to do this through a trigger.

Up to about 6 months ago, I was using a similar solution to what pnoytechie provided. Now, my preferred solution in this case appears to be the same method used by Roll-up Helper (as linked to in crmprogdev's answer). Let Salesforce (SOQL) do the heavy lifting by using an aggregate SOQL query.

Let's use the Account and Opportunity standard objects for an example. Say you want to roll-up the total amount of all Opportunities on an Account, and have a Decimal field TotalOppAmount__c on Account. A possible trigger could be:

Trigger AccountTotalOppAmount on Account(before update){
  Account tempAccount;
  for(AggregateResult rollup :[SELECT AccountId, SUM(Amount) TotalAmount FROM Opportunity WHERE AccountId IN :trigger.new]){
    tempAccount = trigger.newMap.get((Id)rollup.get('AccountId'));
    tempAccount.TotalOppAmount__c = (Decimal)rollup.get('TotalAmount');
}

Now, this is a rather trivial example. It is possible to create a rollup summary field on Account that summarizes a field on Opportunity, but I'll claim that this template will work across any two sObjects (or heck, even on the same sObject, if you really wanted to do that).

  • Do the work before update
  • Use an aggregate query to summarize however many fields you want
  • iterate over the resultSet
  • Grab the record that you want to roll-up to from trigger.newMap
  • Set the value(s) on your target sObject

The power of this pattern is that it not only works if you have a lookup relationship between two sObjects, it will work as long as you store data that can be used from one record, as a foreign key, on a different record

This is the method I am currently using to roll-up data to OpportunityLineItem from a pseudo-related object in my company's org called OpportunityLineItem_Detail__c.

A few points to be aware of before using this pattern:

  • Data retrieved from an aggregate query that uses an aggregate function (SUM, AVG, MAX, etc...) is of type Object, and needs to be cast before it can be meaningfully used
    • non-aggregated fields may not need to be cast. At the very least, using AccountId to grab a record from trigger.newMap like in my example works without complaint.
  • You should still check for null results. In my experience, SUM() doesn't complain if it runs into a few null values, but will give you null if all the values you are summing happen to be null
  • If you aren't using a lookup relationship, you'll need to explicitly manage your 'foreign key' with more code
  • It does use one query. Depending on the number and depth of child records, you can end up eating well into the 101 query governor limit.
0

You can achieve the same via Trigger.

For example, you have lookup City to Country lookup relationship, and you want to have total population for each country by summing up per city population:

Country (Country__c) Object Fields

  1. Name
  2. Population (Country_Population__c)

City (City__c) Object Fields

  1. Name
  2. Population (City_Population__c)
  3. Country (Country__c)

Supposed population in City object is number and is required, in your City trigger, you can have like this:

trigger cityTrigger on City__c (after insert, after update, after delete, after undelete) {
    Set<Id> cIds = new Set<id>();
    Map<Id, Country__c> ctryMap;
    for (City__c city : trigger.new) {
       if (!String.isBlank(city.Country__c)) cIds.add(city.Country__c);
    }

  ctryMap = new Map<id, Country__c>([select id, population__c from Country__c where ID in :cIds limit 1000]);

    //add city population when new city is added
    if (tigger.isInsert || trigger.isUndelete) { //new or recovered records so we need to add
       for (City__c city : trigger.new) {
          if (ctryMap.containsKey(city.Country__c) {
            ctryMap.get(city.Country__c).Country_Population__c += city.City_Population__c;
          }
       }
    }

    //adjust when city population changed
    if (trigger.isUpdate) {
       for (City__c city : trigger.new) {
          if (ctryMap.containsKey(city.Country__c) {
            ctryMap.get(city.Country__c).Country_Population__c -= trigger.oldMap.get(city.id).City_Population__c;
            ctryMap.get(city.Country__c).Country_Population__c += city.City_Population__c;
          }
       }    
    }

    //deduct population of delete cities
    if (trigger.isDelete) {
       for (City__c city : trigger.old) {
          if (ctryMap.containsKey(city.Country__c) {
            ctryMap.get(city.Country__c).Country_Population__c -= city.City_Population__c;
          }
       }    
    }
    update ctryMap;
}

NOTE: The code is just to show how it could be done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.