I’m constantly amazed at the number of varied use cases folks in the Chatter Group are applying to the Declarative Lookup Rollup Summary tool these days. This short blog highlights a particular use case that seems to be on the increase. To resolve it i reached out for additional help in the solution from Process Builder, this is the story…

Background: What causes a Rollup to recalculate?

The default behaviour of the rollup tool is to look for changes to the field your rolling up on, the one specified in the Field to Aggregate field. In addition you can list other fields for it to look at via the Relationship Criteria Fields field, which as the name suggests is also important information if you’ve used rollup criteria. However its also important if the field your rolling up on is a Formula field. In the case of formula field rollups the platform doesn’t inform the tools Apex Trigger of changes to these. So it cannot directly monitor such fields and to resolve this must instead be told explicitly about the fields that are referenced by the formula expression. So far so good…

Challenge: Rollups over Cross Object Formulas?

A challenge however arises if you’re wanting to do a realtime rollup based on a formula field that references fields from a related record, a cross object formula! In this case how does the rollup tool know when changes are made to related records?

One solution to this is to switch to schedule mode by clicking the Schedule Calculate button on the rollup. For realtime rollups, its potentially feasible to enhance the tool to deploy triggers to related objects and bubble up knowledge of field changes to the cause a recalculate of the rollup on the child object… However before we resort to more code (even in the tool) lets see what we can do with the declarative tools we have already today…

Example Use Case

The following schema diagram shows a simplified mockup of a such a challenge i helped a community member out with in the tools Chatter Group.

Here is the scenario assumptions and break down…

• For whatever existing reasons the above is NOT a Master Detail relationship
• Rollup needed is to Sum the Quote Line Item > Amount into the Quote > Total field.
• The Quote Line Item > Amount field is a Formula field, which is a cross object formula pointing to the related Widget > Total field.
• The Widget > Total field is itself a Formula field, in this simplified case adding up the values of Widget > A + Widget > B + Widget > C.
• Whenever changes to the Widget > A, Widget > B or Widget > C fields are made we want the Quote > Total field to be recalculated.

Here’s the rollup summary definition between Quote and Quote Line Item…

While the above works if you use Calculate (one off full recalculate) or Schedule Calculate (daily full recalculate) buttons. Our issue arises in the general use of the Realtime mode. Since the tools triggers see nothing of the changes users make to the Widget fields above, realtime changes are not reflected on the Quote > Total rollup. This is due to the aforementioned reason, since we are using a cross object formula.

NOTE: The Calculate Mode pick list also has a Schedule option, this is a more focused background recalculate, only recalculating effected records since the last run, rather than Schedule Calculate button which is a full recalculate every night. So be aware if your using this mode that the problem and solution being described here also applies to Calculate Mode when set to Schedule as well. As it uses the same field monitoring approach to queue records up for scheduled recalculation.

If your fine without realtime rollups go ahead and use the Schedule Calculate button and at 2am each morning the Quote > Total amount will catchup with any changes made to the Widget fields that effected it, job done!

Solution: Shadow Fields and Process Builder

So when considering the solution, i did consider another rollup between the Widget and Quote Line Item to start to resolve this, thinking i could then put the result in field that the Quote Line Item > Quote  rollup would see change. However this was quickly proved a poor consideration as the relationship between Widget and Quote Line Item in this use case is the wrong way round, as Quote Line Item is the child in this case, doh! In other use cases, i have had success in using nested rollups to get more complex use cases to fly!

Shadow Field?

Either way i knew i had to have some kind of physical field other than a Formula field on the Quote Line Item object to notify the rollup tool of a change that would trigger a recalculate of the Quote > Total. I called this field Amount (Shadow) in this case, i also left it off my layout.

NOTE: I’ve made the assumption here that for whatever reason the existing cross object Formula field has to stay for other reasons, if thats not a problem for you, simply recreate Quote Line Item > Amount as a physical field and as you read the rest of this blog consider this your shadow field.

I then changed my rollup definition to reference the Amount Shadow field instead.

NOTE: If you managed to switch the field type of your Amount field from a Formula to a physical Number field as noted above you don’t need to do this of course.

Process Builder to update your Field to Aggregate

Next i turned to Process Builder to see if i could get it populate the above Amount (Shadow) field on Quote Line Item, as users made changes to Widget fields. Leveraging the child parent relationship between Quote Line Item and Widget. Here is the setup i used to complete the solution!

Summary

Its worth noting that if the relationship between Quote Line Item and Quote was Master Detail, you can effectively now of course use standard platform Rollup Summary Fields without needing the rollup tool at all. You may think me bias here, but not at all, i’d much rather see a fully native solution any day!

Regardless if this use cases fits yours or not, hopefully this blog has given you some other useful inspiration for further rollup and Process Builder combo deals! Enjoy!