0

There is a Show__c object lookup on Opportunity.

On Account, I want to create a roll-up summary field to get the sum of all the Opportunities for the current Show__c. 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__c object) in the roll-up summary field? Any other approach to calculate sum?

  • 1
    Welcome to SFSE! I've edited your question to hopefully make it a bit clearer to everyone (I assume that "Show" is a custom object). I'm not sure that there's enough information here yet for other people to really understand your data model (which they'll need to understand to be able to offer accurate advice). Can you please edit your question to go into more depth about how your three objects are related, and where the date that you're trying to use as a cutoff is stored? – Derek F Aug 24 '18 at 13:46
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.