Salesforce Roll-Up Summary Trigger
Why on earth would anybody want a Salesforce trigger to perform a roll-up summary type function when Salesforce already provides you with Roll-Up Summary fields?
The answer may not be apparent until you begin to be inhibited by the limitations of Roll-Up Summary fields. So what are these limitations?
- Objects have a limit of 10 maximum roll-up summary fields
- Objects on the master side of a master-detail relationship can have roll-up summary fields
- Lookup relationships do not support roll-up summary fields
- Long text area, multi-select picklist, and system fields are not supported as filters or bases for roll-up summary fields
- Campaign roll-up summary fields do not recalculate values when Lead or Contacts are deleted
Colin Loretz and Jeff Douglas have both developed very similar solutions for combating these challenges through the use of Salesforce apex triggers:
- Roll-Up Summary Fields with Lookup Relationships Part 1 – Jeff Douglas
- Salesforce Rollup Summary Fields using Apex Code – Colin Loretz
However, both approaches still face some challenges:
- Governor Limits – If their list<> contains more than the allowed amount (currently 10,000) then an exception will be thrown
- Invalid Query Locator – Colin’s solutions relies on sub-query (related record) calls, which tend to break when processing thousands of records.
To address these challenges I put together an apex trigger that reduces the size of my list by taking advantage of sets, the SOQL SUM and Group By functions and the AggregateResult sObject. I also used maps to retrieve my sum totals which eliminates the invalid query locator errors.
The following trigger sums a number field on a custom object and then updates an Opportunity number field with the total. The structure is as follows:
- Opportunity
- Payments (Lookup Relationship on Opportunity__c)
Salesforce Roll-Up Summary Trigger
trigger OpportunityRollUpPayments on Payment__c (after delete, after insert, after update) {
//Limit the size of list by using Sets which do not contain duplicate elements
set OpportunityIds = new set();
//When adding new payments or updating existing payments
if(trigger.isInsert || trigger.isUpdate){
for(Payment__c p : trigger.new){
OpportunityIds.add(p.Opportunity__c);
}
}
//When deleting payments
if(trigger.isDelete){
for(Payment__c p : trigger.old){
OpportunityIds.add(p.Opportunity__c);
}
}
//Map will contain one Opportunity Id to one sum value
map OpportunityMap = new map ();
//Produce a sum of Payments__c and add them to the map
//use group by to have a single Opportunity Id with a single sum value
for(AggregateResult q : [select Opportunity__c,sum(Amount__c)
from Payment__c where Opportunity__c IN :OpportunityIds group by Opportunity__c]){
OpportunityMap.put((Id)q.get('Opportunity__c'),(Double)q.get('expr0'));
}
List OpportunitiesToUpdate = new List();
//Run the for loop on Opportunity using the non-duplicate set of Opportunities Ids
//Get the sum value from the map and create a list of Opportunities to update
for(Opportunity o : [Select Id, Total_Payments__c from Opportunity where Id IN :OpportunityIds]){
Double PaymentSum = OpportunityMap.get(o.Id);
o.Total_Payments__c = PaymentSum;
OpportunitiesToUpdate.add(o);
}
update OpportunitiesToUpdate;
}
I have tested this method and have successfully updated over 10,000 Opportunity records with over 35,000 payment records through the bulk-api, so I feel good about it. When I get around to it I’ll write up the test class and publish it here.
Update: Here’s the test class.
@isTest
private class TestOpportunityRollUpPayments {
static testMethod void myUnitTest() {
Profile pf = [Select Id from Profile where Name = 'System Administrator'];
User u = new User();
u.FirstName = 'Test';
u.LastName = 'User';
u.Email = 'testuser@test123456789.com';
u.CompanyName = 'test.com';
u.Title = 'Test User';
u.Username = 'testuser@test123456789.com';
u.Alias = 'testuser';
u.CommunityNickname = 'Test User';
u.TimeZoneSidKey = 'America/Mexico_City';
u.LocaleSidKey = 'en_US';
u.EmailEncodingKey = 'ISO-8859-1';
u.ProfileId = pf.Id;
u.LanguageLocaleKey = 'en_US';
insert u;
system.runAs(u){
Opportunity o = new Opportunity();
o.Name = 'Test Opportunity';
o.StageName = 'Closed Won';
o.CloseDate = system.today();
insert o;
system.assertEquals(o.Total_Payments__c, null);
//Test payments on insert
Payment__c p1 = new Payment__c();
p1.Opportunity__c = o.Id;
p1.Amount__c = 100;
insert p1;
Opportunity ou1 = [select Total_Payments__c from Opportunity where Id = :o.Id];
system.assertEquals(ou1.Total_Payments__c,p1.Amount__c);
//Test payments on update
Payment__c p1u = [select Amount__c from Payment__c where Id = :p1.Id];
p1u.Amount__c = 200;
update p1u;
Opportunity ou2 = [select Total_Payments__c from Opportunity where Id = :o.Id];
system.assertEquals(ou2.Total_Payments__c,p1u.Amount__c);
//Test payments on second insert
Payment__c p2 = new Payment__c();
p2.Opportunity__c = o.Id;
p2.Amount__c = 800;
insert p2;
AggregateResult ag1 = [select sum(Amount__c) from Payment__c where Opportunity__c = :o.Id];
Opportunity ou3 = [select Total_Payments__c from Opportunity where Id = :o.Id];
system.assertEquals(ou3.Total_Payments__c,ag1.get('expr0'));
//Test payment on delete
delete p2;
AggregateResult ag2 = [select sum(Amount__c) from Payment__c where Opportunity__c = :o.Id];
Opportunity ou4 = [select Total_Payments__c from Opportunity where Id = :o.Id];
system.assertEquals(ou4.Total_Payments__c,ag2.get('expr0'));
}
}
}
To see this trigger in action jump over to the roll-up summary trigger demo.
Do you see any holes in my code? Please point them out in the comment section below :)
Update: I’ve re-purposed this trigger in order to create a generic utility for performing roll-up summaries. Please see: Salesforce Roll-Up Summary Utility For Lookup Fields With Filter.
Pingback: Salesforce Roll-up Summary Utility for Lookup Fields with Filter