Roll up summary for Lookup field in Salesforce?

There are two main types of relationship fields in Salesforce.

1) Master-Detail
Creates a special type of parent-child relationship between this object (the child, or “detail”) and another object (the parent, or “master”) where:
- The relationship field is required on all detail records.
- The ownership and sharing of a detail record are determined by the master record.
- When a user deletes the master record, all detail records are deleted.
- You can create rollup summary fields on the master record to summarize the detail records.
The relationship field allows users to click on a lookup icon to select a value from a popup list. The master object is the source of the values in the list.

2) Lookup
Creates a relationship that links this object to another object. The relationship field allows users to click on a lookup icon to select a value from a popup list. The other object is the source of the values in the list.

So let’s say that you know you are going to want to do a count on the number of child records. The first thing we should try to do it to create a Master-Detail relationship between the parent and child object so that we can use the ‘out-of-the-box’ roll-up summary field to do the count calculation.

But sometimes a Master-Detail relationship is not allowed. Why would that be?
1) An object may only have two Master-Detail relationships.
2) A standard object cannot be the child/detail in a Master-Detail relationship. An example is that you cannot create a Master-Detail relationship from the Asset object because the Asset cannot be the child in a Master-Detail relationship.

Let’s have a specific example. So let’s say you have a classroom object and a desk object. Normally this would be setup as a Master-Detail relationship so that we could easily create a roll-up summary field to count the number of desks in the classroom. But maybe the desk object already has two Master-Detail fields on it because it is in a relationship with the School and District objects. Of course you can beg and plead with those who already used up those two Master-Detail relationships, but let’s assume that conversation had a quick ‘No’ as the answer. So now you are stuck with using the lookup object.

How are you going to get a count of the number of desks in a classroom? It is going to take some trigger work and a helper class, but it can be done. Here are your steps..

1) Create a trigger on the Desk object. Since we are just doing a count, we do not need to worry about updates to the desk object. If we had a ‘damaged’ or ‘not usable’ type flag on the Desk__c object then we may need to watch for updates also if we wanted to keep track of the desks that are usable in a given classroom. It is important that all of these are ‘after’ triggers as we will be doing a query so we want all the data to be saved in the database already.

trigger deskMaster on Desk__c(after delete, after insert, after undelete) {
set < string > classroomIDs = new set < string > ();
if (trigger.isInsert) {
for (Desk__c deskItem: Trigger.new) {
classroomIDs.add(deskItem.Classroom__c);
}
} else if (trigger.isDelete) {
for (Desk__c deskItem: Trigger.old) {
classroomIDs.add(deskItem.Classroom__c);
}
} else if (trigger.isUnDelete) {
for (Desk__c deskItem: Trigger.new) {
classroomIDs.add(deskItem.Classroom__c);
}
}
if (classroomIDs.size() > 0) {
ClassroomHelper crh = new ClassroomHelper();
crh.updateDeskCount(classroomIDs);
}
}

2) Create a class that can do the count of the Desks per classroom.

public with sharing class ClassroomHelper {
public void updateDeskCount(set < string > deskIDs) {
if (deskIDs == null) {
return;
}
if (deskIDs.size() == 0) {
return;
}
list < Desk__c > deskList = new list < Desk__c > ();
deskList = [Select ID From Desk__c Where Classroom__c =: deskIDs];
integer totalCount;
map < string, integer > classroomIDToDeskCountMap = new map < string, integer > ();
for (Desk__c deskItem: deskList) {
if (classroomIDToDeskCountMap.containsKey(deskItem.Classroom__c)) {
totalCount = classroomIDToDeskCountMap.get(deskItem.Classroom__c);
} else {
totalCount = 0;
}
totalCount += 1;
classroomIDToDeskCountMap.put(deskItem.Classroom__c, totalCount);
}
list < Desk__c > deskUpdateList = new list < Classroom__c > ();
Desk__c deskTemp;
for (string deskID: classroomIDToDeskCountMap.keySet()) {
deskTemp = new Desk__c();
deskTemp.ID = deskID;
deskTemp.Desk_Count__c = quoteLineItemIDToAdditionalCostMap.get(deskID);
deskUpdateList.add(deskTemp);
}
if (deskUpdateList.size() > 0) {
update deskUpdateList;
}
}
}

3) Write your unit test and away you go!

I would like to write the above routine so that it will work against any object. We would need to pass it an object name, the list of IDs and the parent column to compare. Then setting up this type of work for any object would be a snap.

If you wanted to do a sum, min, max or string concatenation the above structure will also work, but you will need to do those calculations instead of just doing a count. Again, this could be made into a generic routine that would just work on sObjects.