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

3 comments:

Anonymous said...

Your trigger will break if there are multiple records are inserted/updated/deleted. That's because of the SOQL is written inside for loop. You can refer other optimized triggers written by many in force.com discussion boards.

Unknown said...

Yes. I'm aware... This trigger can be modified to perform more complex operations.. This is just a start for beginners..

Anonymous said...

Please don't put up non-production ready code. Beginners will think that this is allowed. They don't know better because you don't state explicitly that DML statements must not be in loops.
NEVER do a DML in a loop without some serious workarounds... in other words, NEVER do it.