Roll-Up Summary Fields with Lookup Relationships – Part 1 | Jeff Douglas

Roll-Up Summary Fields with Lookup Relationships – Part 1

July 30th, 2009

Roll-Up summary fields are a great way to perform calculations on a set of detail records in a master-detail relationship. For instance, if you have a sales order you can create a roll-up summary field to display the sum total of all sales order items (related detail records) for the sales order. The one drawback regarding roll-up summary fields is that they only work for master-details relationships. If you have a lookup relationship to your detail records from your sales order, then roll-up summary fields are not available.

So how do you perform this same type of functionality if you only have a lookup relationship? I ran across this same problem while doing some non-profit work for Medisend International and the solution (with a caveat) is to write a trigger to perform the roll-up.

Medisend ships medical products overseas to developing countries. Medisend has a Shipment custom object that has a related list of Inventory Items which are the actual products on the shipment. In their scenario, the Inventory Items cannot be a master-detail relationship since these items can live on their own until they are assigned to a shipment. So to display the total number of items on the shipment to the case managers, I created a numeric filed on the shipment and wrote a trigger to sum the total number of items assigned to the shipment each time an Inventory Item is added or removed from the shipment.

However, there is one caveat due to governor limits. If the shipment contains more than 1000 items, then the trigger will throw an error at line 19 below. This is due to governor limits specifying that a List can only contain 1000 records. There is an Idea regarding this functionality so please vote for it. However if you are confident that your collection will never contain more than 1000 records, this solution should work for you.

This solution didn’t work for Medisend due to the number of possible items on a shipment, so I had to structure the solution a little differently. I’ll outline my final approach in my next post.

Trigger InventoryItemRollup.trigger

 
trigger InventoryItemRollup on Inventory_Item__c (after delete, after insert, after update) {
 
	Set<id> shipmentIds = new Set<id>();
	List<shipment__c> shipmentsToUpdate = new List<shipment__c>();
 
	for (Inventory_Item__c item : Trigger.new)
		shipmentIds.add(item.Shipment__c);
 
	if (Trigger.isUpdate || Trigger.isDelete) {
		for (Inventory_Item__c item : Trigger.old)
			shipmentIds.add(item.Shipment__c);
	}
 
	// get a map of the shipments with the number of items
	Map<id,Shipment__c> shipmentMap = new Map<id,Shipment__c>([select id, items__c from Shipment__c where id IN :shipmentIds]);
 
	// query the shipments and the related inventory items and add the size of the inventory items to the shipment's items__c
	for (Shipment__c ship : [select Id, Name, items__c,(select id from Inventory_Items__r) from Shipment__c where Id IN :shipmentIds]) {
		shipmentMap.get(ship.Id).items__c = ship.Inventory_Items__r.size();
		// add the value/shipment in the map to a list so we can update it
		shipmentsToUpdate.add(shipmentMap.get(ship.Id));
	}
 
	update shipmentsToUpdate;
 
}

Unit Test – Test_InventoryItemRollup.cls

@isTest
private class Test_InventoryItemRollup {
 
	static Shipment__c createShipment(String name, ID caseId) {
 
    	Shipment__c s = new Shipment__c();
    	s.Name = name;
    	s.Aid_Case__c = caseId;
    	insert s;
 
		return s;
 
	}
 
    static testMethod void addItems() {
 
    	Set<id> shipmentIds = new Set<id>();
    	Integer shipment1ItemCount = 10;
    	Integer shipment2ItemCount = 7;
 
 		Aid_Case__c case1 = new Aid_Case__c();
 		case1.Name = 'ZZZ0101';
 		insert case1;
 
    	// create test shipments
    	Shipment__c shipment1 = createShipment('Shipment 1',case1.id);
    	Shipment__c shipment2 = createShipment('Shipment 2',case1.id);
 
    	List<inventory_Item__c> items = new List<inventory_Item__c>();
 
    	for (Integer i=0;i<shipment1ItemCount;i++) {
 
			Inventory_Item__c item = new Inventory_Item__c();
			item.Name__c = 'LP'+i;
			item.Shipment__c = shipment1.Id;
 
			items.add(item);
 
    	}
 
    	for (Integer i=0;i<shipment2ItemCount;i++) {
 
			Inventory_Item__c item = new Inventory_Item__c();
			item.Name__c = 'LP10'+i;
			item.Shipment__c = shipment2.Id;
 
			items.add(item);
 
    	}
 
    	insert items;
 
    	// fetch the shipments
    	shipmentIds.add(shipment1.Id);
    	shipmentIds.add(shipment2.Id);
 
    	// query for the shipments
    	Map<id,Shipment__c> shipmentMap = new Map<id,Shipment__c>([select Id, Name, items__c from Shipment__c where Id IN :shipmentIds]);
 
    	System.assertEquals(shipment1ItemCount,shipmentMap.get(shipment1.Id).Items__c);
    	System.assertEquals(shipment2ItemCount,shipmentMap.get(shipment2.Id).Items__c);
 
    	// now update an inventory item
    	items.get(0).Shipment__c = null;
    	update items.get(0);
 
    	// query the shipment to find out the total items now
    	Shipment__c shipment3 = [select items__c from Shipment__c where Id = :shipment1.id];
 
    	// assert that the shipment is one less than the original
    	System.assertEquals(shipment1ItemCount-1,shipment3.Items__c);
 
    }
 
}

Categories: Apex, Code Sample, Salesforce

23 Comments

Jake July 30th, 2009
Fantastic post! Thank you so much. I really wish there were more real-world solutions, especially around triggers, complete with unit tests out there. I know I’ll be able to use this in several places in our org. You rock!
Will July 30th, 2009
Great post – we ran into exactly the same issue with needing to create rollup summaries for lookups last week (in our case we were using count() to do the rollup and exceeded the SOQL limit). We reached a similar solution.
Gregory Grinberg July 30th, 2009
Hi, maybe I am missing something but couldn’t you increase the number of records you can process by batching the SOQL loop and putting the DML call inside of it. You can also avoid the extra SOQL call for the map this way.

I.E.
for(List ships : [SOQL]){
list ToUpdate = new list
for(Shipment__c s : ships){
s.items__c = s.Inventory_Items__r.size()
ToUpdate.add(s);
}
update ToUpdate;
}
jeffdonthemic July 31st, 2009
Gregory, the problem is in this line:

s.items__c = s.Inventory_Items__r.size();

The SOQL will return more than 1000 records and will throw an exception.
jeffdonthemic July 31st, 2009
Awesome Will. Thanks for the feedback. Glad to know that I am not crazy.
u October 2nd, 2009
Thanks for sample code. do you have suggestions on how I am implement status count?

I have a requirements to add total_items and returned_items count in shipment table.
I added a status in Inventory_Item__c if checked, it is returned item. Any suggestions can be great help
u October 5th, 2009
Thanks for the posting.
I have two status fields in inventory_items
and in the shipment object I have to add the count for each status
for example fields:

Inventory_Items.shipped__c (boolean)
Inventory_Items.returned__c (boolean)
shipment__c.Total_items
Shipment__c.Total_shipped__c
shipment__c.Total_returned__c

Please give me suggestions how I am implement. I added the status and I am getting duplicate entries error while updating.
Thanks for your reply
jeffdonthemic October 5th, 2009
That’s alot of info to update for each record. A couple of ideas might be to add a couple of formula fields to do the calculations, call some future methods to do the calculations depending on the number of licenses you have and perhaps even using async Apex.
Matt February 15th, 2010
Love your work Jeff!
You have saved my behind on more than one occasion.

How would you go about modifying this to roll-up values from Account record to Parent Account record?
Jeff Douglas February 23rd, 2010
Matt, I haven’t thought about it much but if you want to rollup this info to the parent account, I might look into scheduling a batch apex job to run at night. I’m not sure how much data you have or how many accounts but this might be the most scalable. Good luck!
Onur April 19th, 2010
Hi Jeff,
I am volunteering for a non-profit and I need to calculate the sum of values of items__c, instead number of sum of records. Is there any easy way to do it?
Thank you very much in advance for your help, this is a great blog.
Jeff Douglas April 20th, 2010
Onur, you can now use aggregate functions in SQOL to sum() the number of records for a certain criteria. Check out my blog post
SOQL – How I Query With Thee, Let Me Count the Ways for more info for more info on aggregate functions.
Onur April 20th, 2010
Thank you for the quick response, since I am not a developer I will spend some time to understand the aggregate queries. i assume the new query should replace the
shipmentMap.get(ship.Id).items__c = ship.Inventory_Items__r.size();
part.

Thanks again
Onur April 20th, 2010
Hi Jeff,
I am having some problems with aggregate function, it does not seem to sum items__c. If you do not mind would you post a little demonstration for roll up with aggregate functions.
I understand if you say I ask too much.
Thanks again and sorry for the bother.
Roll-Up Summary Fields With Lookup Relationships – Part 2 | Jeff Douglas - Technology, Coding and Bears... OH MY! July 13th, 2010
[...] the first part of this post I outlined the issues involved with creating a trigger to do roll-up summaries with a lookup [...]
Manoj January 26th, 2011
Hi Jeff ,

I have a similar requirement and was writing a test class for it .I was wondering what does the ” Aid_Case__c ” field refer to in your test class?

Thanks and Regards
Jeff Douglas January 26th, 2011
@Manoj, each shipment belongs to an “aid case” so the Aid_Case__c field is a relationship lookup to the Aid_Case__c object. HTH
Dave February 9th, 2011
Hi Jeff, i have a very simple application of what you are speaking of but i am not a coder.

I currently use SF Objects to track commissions for my Sales Reps and Managers.

When 1 or more mangers has earned an override, I select the manager(s) through a Lookup Relationship. Since this is not always done at the time i create the commission, it is not possible for me to make this a Master/Detail Relationship.

So basically I am wanting to simply have a field on the Manager’s Object that will calculate the SUM of a Currency Field from any override commissions that were attached.
Jeff Douglas February 14th, 2011
@Dave, I think you will need to have a trigger for this to calculate the totals at runtime.
Salesforce Roll-Up Summary Trigger Revised June 7th, 2011
[...] Roll-Up Summary Fields with Lookup Relationships Part 1 – Jeff Douglas [...]
Abe September 20th, 2011
Hi Jeff, I am fairly new to Salesforce and have the following issue. My client has been using Salesforce for over 2 years and has over 100,000 account records. Now, I need to create a roll-up/count of the opportunity records attached to the account. Will this roll-up field be dynamically populated for my existing records?
Any information in this regard will be greatly appreciated. Thanks!
Jeff Douglas September 24th, 2011
Abe, you can simply create a rollup summary field that counts the number of opportunities for an account. It’s pretty standard functionality. Check the help docs for more info.
Sachin Agarwal October 12th, 2011
HI @Jeff ! I see both blog on “Roll-Up Summary” (part1 & Part 2).

But i think when we create a trigger to maintain value as like roll-up summary field,
then trigger on “UNDELETE” is also required
otherwise it update the wrong value when we undelete the child record.