Sunday, May 31, 2015

Why Roll-Up Summary Requires Master-Detail

There's an idea that is 7 years old on the Success Community titled, "Eliminate Need for Master-Detail Relationship for Roll-ups", and users have voted the idea up to over 25,000 points.

I won't lie. I am most likely one of the 2,500 users who voted in favor of the idea, who thought I can't believe a no-brainer like this is still an idea and not a GA feature!

But today, while watching Who Sees What: Record Access Via Sharing Rules I suddenly realized that the reason for the delay could actually be ridiculously simple: Implementing this feature would violate the security design of the Salesforce1 Platform. How? By exposing information that would otherwise be hidden when OWD is set to "Private".

Scenario: VIP bank accounts

Let's say you're a system administrator for a fictitious financial institution called SaaS Bank. At SaaS Bank, there are everyday customers, and then there are VIP customers. VIP customers at SaaS Bank are high-profile individuals of great importance or great wealth, and a few notable VIPs include Barack Obama, Warren Buffett and Marc Benioff.

Understandably, VIPs get the white glove treatment. Their relationships are discretely managed by handful of bankers in the Private Bank department within SaaS Bank. These bankers are known as Private Bankers, and their number one priority is protecting their client's sensitive data, namely the clients' bank accounts and balances.

Data on customers' bank accounts are stored in a custom object labeled Bank Account, and all bank accounts serviced by SaaS Bank are tracked in this object.

The security requirement: Everyone at SaaS Bank should be able to see that a VIP is indeed a customer, but only Private Bankers (and their trusted colleagues) should be able to see the bank accounts held by a VIP.

The simple solution would be to make the Bank Account custom object private using OWD. And to relate Bank Account records back to a customer (i.e., a standard Account record), the object has a Lookup(Account) field, not a Master-Detail(Account) field.

The tricky requirement: All users want to see aggregate balance data for their customers.

So, if you could create a Roll-Up Summary field on the Account object that sums all balances for a customer, you would violate the private sharing model for the Bank Account object. A Roll-Up Summary field holds a value that is calculated based on all pertinent records.

In a private sharing model, how would... how could the Roll-Up Summary field hold a value that simultaneously shows 0 to a regular banker, the total balance to a Private Banker and something in between to other bankers with whom a Private Banker has manually shared records? The answer is it can't.

Objects with Master-Detail fields inherit the record access controls on parent objects. In this case you can present aggregate information on parent records via Roll-Up Summary fields, because if you have access to the parent record you also have access to all child records. But when you use a Lookup field instead because you need different record access for child records, well...

Okay, I get it, and I still need a workaround

I think there are legitimate reasons why an organization would want aggregate data to be automatically calculated and displayed through a Lookup relationship. There are two ways to work around this:
  • Ignore the built-in security constraint and leverage Apex running in system context to perform the roll-up. You can even use an off-the-shelf solution the free Declarative Lookup Rollup Summaries app or the paid Rollup Helper app.
  • Use a custom visual element (e.g., Visualforce page) to display a contextual roll-up, taking into account the current user's access to child records. This would leverage the with sharing keyword to accurately display different values to different users.

In the end, it would be nice of either of the above options were made into native features. And I'm guessing that the first option, the convenient and intentional deviation from an established security model, is what the 2,500 supporters of that 7-year-old idea want.


  1. Hey Marty -- Turns out since you took the screenshot of that idea, it's been placed in "Considered for Future Roadmap," aka not on Salesforce's roadmap.

    Was playing through your requirement though and came up with "What if a field could display something different based on the logged in user?", which is essentially your option #2! Was your thought that this custom element would just run a SOQL query "with sharing"?

    Looked into it a bit more and turns out that the Declarative Lookup Rollup Summary tool actually has a setting where you can select "Calculation Sharing Mode" for each rollup!

    tl;dr: Awesome post!!

    1. Hey Julien, thanks for the update on the idea! Regarding option #2, I wasn't thinking of a field, actually. IMO it would have to be a (Lightning?) component that queries the system whenever it's rendered to display the appropriate number to the user. The problem with a field is that it stores a value, and any value can be wrong depending on the security model and the viewing user's role.

      I think the Calculation Sharing Mode with the DLRS would have the same problem. Let's say there are x records in an object, and you're supposed to see y records and I'm supposed to see z records. With "User" mode when I update a child record the roll-up field will show z instead of x, but that's still wrong for you because you're supposed to see y.