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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | @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
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!
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.
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;
}
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.
Awesome Will. Thanks for the feedback. Glad to know that I am not crazy.
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
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
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.
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?
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!
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.
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.
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
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.
[...] the first part of this post I outlined the issues involved with creating a trigger to do roll-up summaries with a lookup [...]