Andy in the Cloud

From BBC Basic to Force.com and beyond…

Rollups and Cross Object Formula Fields

14 Comments

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.

FormulaRollupUseCase.png

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

ForumlaRollupDLRS.png

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?

AmountShadowFieldEither 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.

ChangesToRollup

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!

FormulaRollupProessBuilder1.png

FormulaRollupProessBuilder2.png

FormulaRollupProessBuilder3.png

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!

14 thoughts on “Rollups and Cross Object Formula Fields

  1. Looking forward to digesting this. I am using DLRS and process builder extensively to streamline multiple processes (without code) for a number of non profits.

    What the two combined can achieve is amazing

    Regards Greg Devine

    +64 274 338 463 (+64 274 DEVINE) greg@manukauroad.com

    *Manukau Road Consulting* *Using our Past and our Present to Build our Future*

    *www.manukauroad.com *

    On 14 February 2016 at 01:23, Andy in the Cloud wrote:

    > Andrew Fawcett posted: “I’m constantly amazed at the number of varied use > cases folks 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 help fr” >

    • Excellent, look forward to your thoughts. Have you blogged about your solutions at all, I would be happy to promote and share if so. Thanks!

  2. Pingback: Rollups and Cross Object Formula Fields | SutoCom Solutions

  3. Before you deploy this solution, you should make sure that whoever is responsible for managing Widget records knows that Process Builder Flows aren’t bulkified yet (as of Spring ’16), so if they were to do a bulk update to Widgets, you’ll hit the SOQL query governor limit as the DLRS trigger gets fired for each Quote Line Item record.

  4. Certainly when playing with process builder late last year I was seeing major issues with any bulk operations, which given that was key for me forced me to give up on it.

  5. Andy – we are getting occasional errors. An error occurred at element Save_Case (FlowRecordUpdate).
    UPDATE — UPDATE FAILED — ERRORS : (CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY) dlrs_CaseTrigger: execution of AfterUpdate,

    • Can you please post a further description of the issue and screenshots of your flow to the chatter group (see readme file for link).

  6. Hi, thanks for creating this awesome tool! I scheduled a job to run at 2 am every weekday but I don’t see any records underneath the Schedule Items, Calculate Jobs, or Logs tabs. Should I be seeing something there?

  7. Hi Andy,

    It’s a awesome tool and solve most of the my problem. The only concern left with Events. I tried to count total events and open events on opportunity. I created count__c formula field return type number and default value 1 on activity object. It gives me 1 Event 1 count but i have facing issue with invitees. Let say an opportunity have 1 event with 1 Assignee & 1 invitees so total 2 event count showing but actually it is only 1 event with two attendees. so total event show as 1 not 2.

    Please help me to figure out it.

  8. Good work. I agree a native solution would be nice but I doubt they will ever implement it. Whatever ORM Salesforce servers internally use, they will need to issue Outer Joins to the physical DB to pull in Lookup relationships as opposed to Inner joins for Master Detail relationships. With the number of subtleties and performance implications of dealing with Outer joins, this will probably never be implemented as native functionality.

  9. You are a lifesaver. This was just what we needed!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s