Admin Tools vs Coding: Take your Rollup Summaries to the Max
Admin Tools vs Coding: Take your Rollup Summaries to the Max

Admin Tools vs Coding: Take your Rollup Summaries to the Max

05/19/2020 by Mario Di Genio
In this post we’ll take a look at how an admin can implement rollup summary fields in scenarios where it would not be possible directly without additional development.

A roll-up summary field calculates a value in a master object from detail records related to it via a master-detail relationship. It is a very helpful feature to have on a single record basis, but there are certain limitations that prevent admins from using it to the fullest. In this post we’ll take a look at the two most common scenarios where we need rollup summaries that cannot be directly created in the master object and how to work around them.

The Admin Way

Scenario 1: Standard Objects as the Detail Object

Some standard objects do not have a master-detail relationship, but still allow you to create rollup summary fields in the master. For example: you can create a rollup summary in the Account from the related Opportunities, even though the Opportunity object has a standard lookup to Account (not a master-detail relationship).

But this is a special setting that Salesforce provides only for certain standard objects. In some cases it is not possible to create a rollup summary in the master or even create the master-detail relationship in the detail standard object. For example: if you want to know how many Assets or the average price of all Assets per Product Family related to a certain Account, the Asset object has a lookup to Account but Salesforce does not allow you to create the rollup summary or create a master-detail relationship to the Account in the Asset object.

The way we can work around this is:

  1. Create a mirror object of the detail object.
  2. Create in the mirror object the master-detail relationship to the master object.
  3. Create in the mirror object the master-detail relationship to the original detail object.
  4. Create mirror fields in the mirror object to store the information we want to roll up.
  5. Create the rollup summaries in the master object from the mirror records.
  6. Create a lookup relationship to the mirror object in the detail object.
  7. Create the processes that create and update the mirror records for every creation or update of the original detail records.
    1. Process to link the detail record to the mirror record when the mirror record is created via the lookup from step 6.
    2. Process to create and update the mirror record every time a detail record is created or updated respectively.

In the case of the Assets where we want to roll up to the Account the Asset count and the Asset average price we:

  1. Create a mirror object called “Asset Mirror” of the Asset detail object.
  2. Create in the Asset Mirror object the master-detail relationship to the Account master object.
  3. Create in the Asset Mirror object the master-detail relationship to the original Asset detail object.
  4. Create mirror fields in the Asset Mirror object to store the information we want to roll up. In this case: Price, Quantity and Product Family.
  5. Create the rollup summaries in the Account master object from the Asset Mirror records.
  6. Create in the Asset detail object a lookup relationship to the Asset Mirror object.
  7. Created the processes that create and update the Asset Mirror records for every creation or update of the original Asset detail records.
    1. Process to link the Asset detail record to the Asset Mirror record when the Asset Mirror record is created via the lookup from step 6.
    2. Process to create and update the Asset Mirror record every time an Asset detail record is created or updated respectively.

So with this implementation, we have a rollup summary from objects that cannot have a direct master-detail relationship completely done with declarative tools.

Some Considerations:

The processes could be limited if the detail records are going to be mass-created or updated at any point by the hundreds, as Process Builders could consume a lot of operations that could hit Salesforce limits.

Remember that in Salesforce everything piles up.

  • Any new operation to an object adds to what you may already have running on it: Process Builders, Flows, Apex Triggers, etc. whether they are implemented by an admin of your organization or from an installed package.
  • When you have several Process Builders, Flows, or Apex Triggers on an object you cannot control the sequence of execution. Maybe the Process Builder to copy the value of a field from the detail object to the mirror object is executed first and then another Process Builder or Trigger overwrites the value in the detail object as it does some additional operations, leaving you with a mirror record that does not contain the actual field value that should be rolled up, leading to inaccurate summary values.

Why is it necessary to copy the information to roll up from the original detail record into the mirror record if you can build formulas in the mirror object to retrieve the values with the lookup to the original detail object? You cannot create rollup summary fields based on formula fields, which is why we need to copy the information field by field.

Scenario 2: Rollup Summary Field Limits

Salesforce has limits as to how many rollup summary fields can be created in the master object. Even if the detail objects are different for each rollup, there’s a limit by default of up to 25 rollup summary fields that can be created in the master. The workaround is to create a multi-level master-detail hierarchy to allow the rollup summaries needed at each level so that all the rollup summaries are in an intermediate object, without consuming the limit in the master object at the very top of the hierarchy.

For example: We have projects with tasks that have subtasks. The tasks and subtasks will allocate information about time spent on each one. The time of the subtasks has to be rolled up to its corresponding tasks. Each task is of a particular type, and the project must provide rolled up time information per task type. Here we have a sample with numbers:

Now this seems like a direct implementation with rollup summary fields, but: 

What if there are 20 task types? That would mean 20 different rollup summary fields in the Project, which would consume pretty much all the rollup summaries available for a master object.

What if in the future you need to create more rollup summaries and you don’t have enough?

What if the project already has 7 rollup summaries? You already wouldn’t have enough room for all the new rollup summaries without hitting the limit.

So whether you want to save the rollup summaries for future development or if you already don’t have enough room, you can consider having some of the rollup summaries (particularly the ones within the same category–which in this case would be “time per task type”) in an additional intermediate object that can allocate them.

For the example above, we create the “Project Summary” object which will have a master-detail relationship to the Project and that the tasks can have a master-detail relationship to. All the rollup summaries are created in the Project Summary object and the rest is to provide a way to create the Tasks from the Project so that it is a seamless process for the users (similar to the processes created in scenario 1).

There would be only one Project Summary for each Project, since the Project Summary is just a way to separate a bunch of fields from the Project without having to create additional steps for users to manage their tasks. 

Some Considerations:

You can request an increase to the limit of 25 rollup summary fields from Salesforce if you present your business case for it. The limit can only be increased up to 40 but increasing the limit per master object could also affect the system performance if you have a lot of detail records (in the line of thousands for each master for example).

You may want to always leave some room for future development and not consume all your possible rollup summaries. So keep that in mind and try to create an additional level to your object hierarchy for a large number of rollup summaries with a similar purpose (as in the scenario presented above).

You can only create a depth of master-detail relationships of up to 4 levels (which means up to 3 levels of rollups based on rollups). Salesforce will prevent you from creating a master-detail relationship that would generate a fifth level of depth.

So in the scenario described above we are within the limit, but that’s how far you can go with that solution, as another object level to create a different type of rollup would not be allowed via declarative tools.

The Advanced Admin/Coding Way

Process Builder and Flows

You can create fields in the master object (of any type: Number, Currency, Date, etc. — not rollup summary) and then Process Builders and Flows which retrieve the detail records, do the calculations and save the results in those fields. This would not involve master-detail relationships or rollup summary fields at all, but just as with scenario 1, these processes could be limited if the detail records are going to be mass created or updated at any point by the hundreds (or even as low as a few dozens) at a time. Remember

Apex Triggers

Coding allows you to do the same as described with Process Builders and Flows, but it may be more efficient when it comes to the way records are processed to minimize the operations and therefore allow a larger amount of detail records to be created or updated at one time. Still, some limitations may be hit when there are a lot of rollups from the same detail record as well. Remember

DLRS package

DLRS (Declarative Lookup Rollup Summary) is a powerful package that allows you to create roll-up summary fields from lookup relationship fields. This is helpful if you have the package already installed and ready to be used. But if you have to do it in a production environment without having to install an entire package for this, we need to look for another solution.

DLRS provides an easy way to configure rollup summary fields from lookup relationships, but in the backend DLRS works with Triggers, so Salesforce limits could be hit with a large amount of records. There are several options to do the calculations live or scheduled, but if you have several DLRS based on the same detail object, since the calculations are done one DLRS definition at a time, it may consume a lot of operations that could also hit Salesforce limits when mass creating or updating detail records. Remember

The Takeaway

There are ways to stay within declarative tools to do rollup summary fields, but it’s all in the limits and order of execution. A constant throughout this article has been “Remember that in Salesforce everything piles up.” As a developer, we always take this into account, but as an administrator you will have to do what the platform allows you to do. If you don’t know how things work in the backend, you may unexpectedly run into the limits or fall into the cases where the order of execution creates inconsistent results. Besides thinking of your particular solution, look at the big picture and identify the chain executions that your solution could add to and whether that would affect your overall processes or not.

Resources

Here are some resources to learn more:

What do you think of the workarounds to create rollup summaries? Which one(s) have worked out best for you? Any other workarounds you know of? Tell me all about it in the comments below, in the Salesforce Trailblazer Community, or tweet directly at me @mdigenioarkus.

More by
Mario Di Genio