Rollup Summary Fields on any field/object using APEX Code
Level: Intermediate
Rollup Summary Fields
The Rollup Summary fields provided by Salesforce offer a great way to summarize relationship data. If you have two objects, “Timesheet” and “Time Entry”, a rollup summary field can total the number of hours assigned to any given timesheet.
What problem does this address?
Standard rollup summary fields require a master-detail relationship. Currently, you can not setup a master-detail-detail+ relationship so what happens when you have more than 2 objects that you want to summarize? You won’t be able to.
The following Apex code helps solve this situation by allowing you to summarize information on objects that are related by either a Master-Detail relationship or a Lookup relationship field. You can even summarize information in a cascading fashion, by linking more than 2 objects together. I’ve been working on a project management application on the Force.com platform and we have Projects->Tasks->Time Entries. The time entries are summarized by hours and dollars at both the Task and Project level.
In tackling this problem, there are five use cases we must consider:
1. Creating a new record that rolls up to the parent record
2. Deleting an existing record that currently rolls up to the parent record
3. Updating a record with a new value and remains under the same parent record
4. Updating a record with a new value and changing the parent record that it rolls up to
5. The child value remains the same and we change the parent record that is being rolled up to
Disclaimer & Warning:
There are other ways to accomplish this type of functionality so please feel free to critique the code in the comments. The code has been written for processing bulk records but has not been extensively tested for excessively large record sets. IE: A parent object that summarizes 100+ child records that summarizes another 100+ child records below that. The more objects you have cascading under one another, the greater the chances of running into the Salesforce governor limits. Please feel free to provide feedback and recommendations on this as you encounter them.
GETTING STARTED
Step 1: Create the Parent and Child objects
1. Create a parent object:
- Object Label: Weekly Timesheet
- Object Name: Weekly_Timesheet
- Save
2. In the Custom Fields & Relationships area of the new object, click New.
3. Create a new field to store the summarized data:
- Field Type: Number
- Field Label: Total Hours
- Field Name: Total_Hours
- Length: 16
- Decimals: 2
- Save
4. Create a child object:
- Object Label: Time Entry
- Object Name: Time_Entry
- Save
5. In the Custom Fields & Relationships area of the new object, click New.
6. Create two fields on the child object:
- Field Type: Number
- Field Label: Hours
- Field Name: Hours
- Length: 16
- Decimals: 2
- Follow prompts and Save
- Field Type: Lookup Relationship
- Field Label: Timesheet
- Field Name: Timesheet
- Follow prompts and Save
7. Create a trigger (Download formatted code here)
trigger rollupHoursToTimesheet on Time_Entry__c (after insert, after update, after delete) {
double sumTotalHours = 0.0;
Weekly_Timesheet__c [] sheetsToUpdate = new Weekly_Timesheet__c[]{};
//***********************************************
//Code for updating existing records and new records
//***********************************************
if(Trigger.isInsert)
{
Time_Entry__c [] teNew = trigger.new;
for(Time_Entry__c te : teNew)
{
for (Weekly_Timesheet__c timesheet : [select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :te.Timesheet__c])
{
//Sum all the timesheet entries
for (Time_Entry__c timeEntries: [select Id, Hours__c from Time_Entry__c where Timesheet__c = :timesheet.id])
{
sumTotalHours += timeEntries.Hours__c;
}
timesheet.Total_Hours__c = sumTotalHours;
//add timesheet to list to be updated outside of the loop
sheetsToUpdate.add(timesheet);
}
}
//commit the changes to Salesforce
update sheetsToUpdate;
}
//***********************************************
//Code for updating when a record is updated
//***********************************************
else if(Trigger.isUpdate)
{
//sum total both old and new
Time_Entry__c [] oldTime = Trigger.old;
Time_Entry__c [] newTime = Trigger.new;
Double newSum = 0.0;
Double oldSum = 0.0;
for(Time_Entry__c newTe: newTime)
{
for(Time_Entry__c oldTe : oldTime)
{
Weekly_Timesheet__c oldTimesheet = [Select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :oldTe.Timesheet__c];
Weekly_Timesheet__c newTimesheet = [Select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :newTe.Timesheet__c];
Time_Entry__c [] newSumHours = [Select Id, Name, Hours__c from Time_Entry__c where Timesheet__c = :newTimesheet.Id];
Time_Entry__c [] oldSumHours = [Select Id, Name, Hours__c from Time_Entry__c where Timesheet__c = :oldTimesheet.Id];
//sum premiums from child objects
for(Time_Entry__c oldSumHour : oldSumHours)
{
oldSum += oldSumHour.Hours__c;
}
for(Time_Entry__c newSumHour : newSumHours)
{
newSum += newSumHour.Hours__c;
}
newTimesheet.Total_Hours__c = newSum;
oldTimesheet.Total_Hours__c = oldSum;
sheetsToUpdate.add(newTimesheet);
sheetsToUpdate.add(oldTimesheet);
}
}
update sheetsToUpdate;
}
//***********************************************
//Code for updating when a record is deleted
//***********************************************
else if(Trigger.isDelete)
{
Time_Entry__c [] teOld = trigger.old;
for(Time_Entry__c te: teOld)
{
for (Weekly_Timesheet__c timesheet: [select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :te.Timesheet__c])
{
for (Time_Entry__c timeEntries: [select Id, Hours__c from Time_Entry__c where Timesheet__c = :timesheet.id])
{
sumTotalHours += timeEntries.Hours__c;
}
timesheet.Total_Hours__c = sumTotalHours;
sheetsToUpdate.add(timesheet);
}
}
update sheetsToUpdate;
}
}
trigger rollupHoursToTimesheet on Time_Entry__c (after insert, after update, after delete) {
double sumTotalHours = 0.0;
Weekly_Timesheet__c [] sheetsToUpdate = new Weekly_Timesheet__c[]{};
//***********************************************
//Code for updating existing records and new records
//***********************************************
if(Trigger.isInsert)
{
Time_Entry__c [] teNew = trigger.new;
for(Time_Entry__c te : teNew)
{
for (Weekly_Timesheet__c timesheet : [select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :te.Timesheet__c])
{
//Sum all the timesheet entries
for (Time_Entry__c timeEntries: [select Id, Hours__c from Time_Entry__c where Timesheet__c = :timesheet.id])
{
sumTotalHours += timeEntries.Hours__c;
}
timesheet.Total_Hours__c = sumTotalHours;
//add timesheet to list to be updated outside of the loop
sheetsToUpdate.add(timesheet);
}
}
//commit the changes to Salesforce
update sheetsToUpdate;
}
//***********************************************
//Code for updating when a record is updated
//***********************************************
else if(Trigger.isUpdate)
{
//sum total both old and new
Time_Entry__c [] oldTime = Trigger.old;
Time_Entry__c [] newTime = Trigger.new;
Double newSum = 0.0;
Double oldSum = 0.0;
for(Time_Entry__c newTe: newTime)
{
for(Time_Entry__c oldTe : oldTime)
{
Weekly_Timesheet__c oldTimesheet = [Select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :oldTe.Timesheet__c];
Weekly_Timesheet__c newTimesheet = [Select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :newTe.Timesheet__c];
Time_Entry__c [] newSumHours = [Select Id, Name, Hours__c from Time_Entry__c where Timesheet__c = :newTimesheet.Id];
Time_Entry__c [] oldSumHours = [Select Id, Name, Hours__c from Time_Entry__c where Timesheet__c = :oldTimesheet.Id];
//sum premiums from child objects
for(Time_Entry__c oldSumHour : oldSumHours)
{
oldSum += oldSumHour.Hours__c;
}
for(Time_Entry__c newSumHour : newSumHours)
{
newSum += newSumHour.Hours__c;
}
newTimesheet.Total_Hours__c = newSum;
oldTimesheet.Total_Hours__c = oldSum;
sheetsToUpdate.add(newTimesheet);
sheetsToUpdate.add(oldTimesheet);
}
}
update sheetsToUpdate;
}
//***********************************************
//Code for updating when a record is deleted
//***********************************************
else if(Trigger.isDelete)
{
Time_Entry__c [] teOld = trigger.old;
for(Time_Entry__c te: teOld)
{
for (Weekly_Timesheet__c timesheet: [select Id, Name, Total_Hours__c from Weekly_Timesheet__c where Id = :te.Timesheet__c])
{
for (Time_Entry__c timeEntries: [select Id, Hours__c from Time_Entry__c where Timesheet__c = :timesheet.id])
{
sumTotalHours += timeEntries.Hours__c;
}
timesheet.Total_Hours__c = sumTotalHours;
sheetsToUpdate.add(timesheet);
}
}
update sheetsToUpdate;
}
}
8. Create the unit test for the trigger in an Apex class
[COMING SOON]
