Andy in the Cloud

From BBC Basic to and beyond…

Rollups and Cross Object Formula Fields


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?

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.


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!





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!

26 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)

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

    * *

    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!

  10. Hi Andy

    Let me tell you that this tool is amazing and helped us allot in our org. recently I have seen that suddenly one of Rollup Summary that we have was not updating any more the filed. I have checked all others Rollups, updated to the latest version deploy the apex classes again. All of them now are working fine with the exception of one. Please see below the info:

    Parent Object: Contact
    Child Object: VCSSA__VCSEmail__c
    Relationship Field: VCSSA__Contact__c
    Relationship Criteria: none
    Relationship Criteria Fields: none
    Field to Aggregate: VCSSA__Date__c (date/time filed)
    Field to Order By: none
    Aggregate Operation: Last
    Aggregate Result Field: Last_email_date__c . (not a formula filed just a normal date/time filed)
    Aggregate All Rows: none
    Row Limit: none
    Calculation Mode: Realtime
    Calculation Sharing Mode: User

    It was working fine but suddenly it stop updating. On the apex jobs when I run the calculation everything is fine no error but nothing is updated.

    Do you see any issue with this Rollup? I would highly appreciate your help with this.

    Kind regards

    • This is a real-time rollup, so does not use Apex Jobs. Basically when you edit VCSSA__VCSEmail__c.VCSSA__Date__c and hit the Save button in the record edit it will fire a recalc in real-time. Providing you have the DLRS triggers deployed for object VCSSA__VCSEmail__c and they are Active. Review the Object definition under the Object Manager under Setup.

      • Hi Andy

        The Apex Trigger and Apex Class for this child object are deployed and active. The weird thing is that it was working perfectly like this and then suddenly around nov period it stop updating. I will keep trying to see if I can make it to work again.

        All the best

  11. Hi Andy,

    I have a concatenated rollup from OpportunityLineItem to Opportunity to concatenate an OpportunityLineItem field, say MAG__c for each Opportunity. This field (MAG__c) is a formula field referencing a field in Product2 object. Whenever the field value changes in Product2, I want the corresponding Opportunity rollup field to be recalculated. But there is no child relationship name in OpportunityLineItem for Product2 field.

    Do you have any suggestion?



    • Yeah you need to bubble a change up somehow. There is a blog post listed on the readme that did something like this a while back on some different objects. But the principe might translate.

  12. Hi Andy,

    I have a use case where I have a recursive lookup on the opportunity. (lookup on the opportunity to the opportunity)
    One of the opportunities is the child to the other.
    On the child opportunity I’ll have a quote and quote line items. On the quote, I am using rollup summary fields (standard) to bucket certain types amount fields based on the quote line items.

    I was then using cross object formula fields to bring over the rollup summary standard fields from the quote to the child opportunity.

    Finally, I was attempting to use this tool (the declaritive lookup summary tool) to sum the child opp formula amounts onto the parent opportunity – but ran into the challenges presented in this blog post.

    My question: Can I have the declaritive lookup summary tool reference direct the rollup summary standard fields on the quote? (in the “field to aggregate” config, i’d have to traverse down through the primary quote reference and then reference the quote record’s rollup summary field direct)

    • This is not something you can do in the tool, but you can create a formula field. Downside with that approach is the tool may not detect changes unless you bubble them up some how Per the discussion in this blog.

  13. I’m new to this tool so my apologies if I’m asking something that you’ve already explained! I have a client that has four lookups with the Account as the Parent and Opportunity as the Child. The Relationship Criteria is StageName = ‘Closed Won’ AND CloseDate = LAST_MONTH (and THIS_MONTH, LAST_YEAR,and THIS_YEAR- on the other rollups). They are summing the Opportunity Amount into an Account field (Last Month’s Sales). The Calculation Mode is in Realtime and there seems to be a scheduled job at 2 am. This all seems to be working, however there’s Accounts showing old Last Month data where it has the most recent Opportunity in 2018 (11/18, for example) instead of $0 (since there hasn’t been an Opp since 11/18). Other Accounts that have opps in the current month and year are getting updated just fine. How do I go about getting those Opportunities not currently fitting the lookup criteria (Opportunity Won Last Month) updated to show $0 (or whatever is current in month, year, last year)?

    • Yes you have a mix of rollup modes here. Real-time and Scheduled and possibly examples of the two variants of scheduled, full and incremental. This is fine but adds complexity, especially if you don’t have any docs from whomever set them up. First review this if what I just said sounds a bit unclear. Next check none of the rollups are overwriting each other, the field on the rollup definition that points to the parent field to receive the rollup should be different or at least if it’s the same with another rollup the criteria should split them apart records wise. Next if this looks good. It maybe just that this field is updated by the real-time rollup and these records predate it’s activation. If you thing that’s the case their is a Calculate button on the rollup definition that will kick off a full recall for applicable records. Hope this helps. Andy

      • Thanks Andrew! I deleted the scheduled rollups and left the real-time rollup summary as-is and everything is working as should now. Thanks again!

  14. Hi Andrew,
    It was an excellent tool and I was using this between two custom objects. But it sums up for only particular records and for the rest it’s not summing up. Do you know why?

    • It will only update on record insert or delete and when for updates only when fields listed in the “Relationship Criteria Fields” field are changed.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s