function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ajinkya Deshmukh 16Ajinkya Deshmukh 16 

Rollup summary field criteria

There is a show object lookup on the opportunity. On Account, I want to create a roll-up summary field to get the sum of all the opportunities for the current show. When the show for the current year changes(eg. 2018 to 2019), the sum for 2019 show opportunities should be displayed in the roll-up summary field. How to check the date for the current show (start date on Show object) in the roll-up summary field. Any other approach to calculate sum?
NagendraNagendra (Salesforce Developers) 
Hi Ajinkya,

Native roll-up summary fields can only be created based on, and only update based upon changes to, non-formula fields of specific types that are located on the object being rolled up. As a consequence, you can't build a rollup from Opportunity to Account based on field data on a third object, Show.

Additionally, the comparison criteria for a Roll-up Summary Field cannot be dynamic - e.g., you can't compare a field value against THIS_YEAR or some other field value. Criteria must be comparisons against a static value, like "2019".

There's (at least) three possible solutions, and this can be done declaratively, although the declarative solution without DLRS is a little bit complex.

One is to use Process Builder or Process Builder and Flow to update fields directly on the Opportunity when the associated Show changes, so you can use a native roll-up summary field to total the relevant Opportunities.
You'd probably need a Checkbox field like This Year's Show on the Opportunity to use in your filter, given the criteria limitations mentioned above, and your Process could populate this by taking into account all the criteria you need from the Show.

It sounds like there is a scheduled component to this too - in January, you want to make sure that all of the roll-up fields reflect the new year. This could be accomplished with Process Builder, again, using Time-Dependent Actions. You'd need to have a calculated Date formula field for "First Day of Following Year" on the Show, have your Process fire on creation, and execute a scheduled action 0 days after "First Day of Following Year". The action would then perform an update on all the related Opportunities to change the field that's being used to roll them up.

Another is to write a fair amount of code that does largely the same workflow as described above, including both triggers and Scheduled Apex to address the time-delayed workflow.

Probably the easiest solution is to use Declarative Lookup Rollup Summaries. You can write a rollup in DLRS, using Scheduled mode, that will refresh on a nightly basis, and you can roll up there on a criterion that includes a formula field like "Show__r.Year__c = THIS_YEAR".

Hope this helps.

Kindly mark this as solved if the reply was helpful.

Thanks,
Nagendra