Friday, February 10, 2012

Salesforce: Simple Roll-Up Summary Calculation Using Apex Trigger

Roll-up summary is a very useful function in Salesforce. We can calculate Sum, Max, Min and Average of numbers and currencies of child records in a Master-detail relationship. But there are few limitations.

  1. Roll-up summaries can be calculated only in Master-Detail relationships. You cannot calculate roll-up summary in Look-Up Relationships.
  2. We can have only 10 Roll-up summary fields.
  3. There are few standard objects on which we need Roll-Up summary but it's not available. E.g. Roll-up Asset information in Account. Need to know how many Assets are installed for a particular account.
  4. You can't use date functions in Roll-up summary calculations.
To overcome these problems we can write a trigger with SOQL select Aggregate functions.

Scenario: I have two objects here. Milestone and effort. In efforts I'm capturing number of hours spent for a particular task. I wish to have a sum of all these efforts related Milestone record.

Here is the sample code:

1:  trigger Sum_Effort_Hours_in_Milestone on Effort__c (after delete, after insert, after update, after undelete) {  
2:  if (Trigger.isDelete)  
3:  {  
4:  for (effort__c E : Trigger.old)  
5:    {  
6:      Milestone__c M = [select name, Milestone_Hours_2__c from Milestone__c where id =:E.Milestone__c];  
7:      List groupedResults = [select sum(Hours__c)aver from Effort__c where Milestone__c =: M.id];  
8:      Decimal decimalRevenue = 0;  
9:      if(groupedResults.size() > 0)
10:      {  
11:        String str = '' + groupedResults[0].get('aver') ;  
12:        decimalRevenue = Decimal.ValueOf(str) ;  
13:        System.debug('decimalRevenue ::::: ' + decimalRevenue) ;  
14:      }  
15:      M.Milestone_Hours_2__c = decimalRevenue;  
16:      update M;  
17:    }  
18:  }  
19:  else  
20:  {  
21:  for (effort__c E : Trigger.new)  
22:    {  
23:      Milestone__c M = [select name, Milestone_Hours_2__c from Milestone__c where id =:E.Milestone__c];  
24:      List groupedResults = [select sum(Hours__c)aver from Effort__c where Milestone__c =: M.id];  
25:      Decimal decimalRevenue = 0;  
26:      if(groupedResults.size() > 0)  
27:      {  
28:        String str = '' + groupedResults[0].get('aver') ;  
29:        decimalRevenue = Decimal.ValueOf(str) ;  
30:        System.debug('decimalRevenue ::::: ' + decimalRevenue) ;  
31:      }  
32:      M.Milestone_Hours_2__c = decimalRevenue;  
33:      update M;  
34:    }  
35:  }  
36:  }  

Hope this will help you in writing some complex triggers. Cheers...!!

Help Articles:
Aggregate Functions
Jeff Douglas - SOQL