function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Andrea SloanAndrea Sloan 

Roll Up Summary Field using Formula field as a filter

I'm would like to use a formula field which filters my end result based on the user who is curently logged in, in the creation of a roll up summary field on account object. Basically, I'm trying to get a count of all my opportunities for a given account but only when a field called "A&K Reporting Company" reads a certain country name based on user login specs. Each of our Sales Reps are responsible for different countries. So if the rep who is responsible for Argentina wants to know every time a certain account makes it's first confirmed opportunity to Argentina (Field A& K Reporting Company = Argentina) I've created a formula boolean type field which compares a field at the user level where the name of the country of the rep is inserted with this given country if it appears inside the given opportunity field record called A&K Reporting Company. If Argentina (in my example) shows up in both cases then my formula fields returns a True for the Argentina rep. However, if the rep is one from Canada then for the formula field to return true when this rep is logged in, the opportunity field data must read Canada. The name of this boolean formula field is my Localcountry and this is the field that I want to include in the roll up summary field of accounts to tag the count of the account's first opportunity but specifically to the country belonging to the specific rep logged in. Since formula fields are not accepted in RollUp Sumaries, I was wodering if someone can help me do this with code? I'm not familiar with coding so I don't know how easy or hard this would be. These reps don't own accounts or opportunities based on this field so unfortunately I can't tag ownership based on the name that is really indicated for the owner of the opportunity or account.
 
Michael WelburnMichael Welburn
Have you taken a look at the Declarative Roll Up Summary Tool (https://developer.salesforce.com/page/Declarative_Rollup_Summary_Tool_for_Force.com_Lookup_Relationships) as an option that doesn't require code (on your behalf)? Essentially what it does is allow you define rollup criteria that requires the creation of a trigger, and programmatically will create said trigger in your sandbox environment. Then you can test it and (upon verification of it working as intended) you can deploy it to production.

It was built by the community to fill this gap until Salesforce supports it officially - see the open Idea here: https://success.salesforce.com/ideaview?id=08730000000BrUAAA0


 
Andrea SloanAndrea Sloan
Hi Michael: I’ve read the whole link you sent me. Can you tell me where is the link to download this app? I can’t seem to find it anywhere? From what I’ve read, I don’t see it saying anywhere that you could include a formula field as part of the Roll Up Summary filtering process. That is what I need. Does it accept formula field as filters? What exactly did you mean by “defining creation of a trigger”? I’m not a developer so I’m a bit lost with that part??? Thank you, Andrea
Michael WelburnMichael Welburn
This is the link to the documentation: https://github.com/afawcett/declarative-lookup-rollup-summaries. About halfway down the page is the Packaged Release History section, which has a link for installation.

From a Salesforce perspective, there are a handful of configurable ways to update data on objects - workflow rules, roll up summary fields, formula fields, process builder, etc. If you can't do it via a configuration, the best way to do it (through code) is writing an Apex Trigger that executes based on a record being created/updated. What the Delcarative Rollup Summary tool does is create wizard for you to programmatically define the business logic involved in this Apex Trigger, but then it writes the code for you (within reason). Then you can simply test your data in a sandbox and verify it works, without writing code yourself, and deploy it upon successful verification to production.

The one caveat about formula fields is that since they are calculated realtime (they can be impacted by the user who is looking at the value), it appears you need to schedule these calculations to happen at a scheduled time instead of being able to do realtime calculations.

Another alternative, depending on the criteria in your formula field, is to use a workflow rule to copy the value in the formula field into a standard number field and then use that field for the Roll Up Summary. This would also not work if your formula field changed based on the user accessing it.