0

The client has a field in the Account, which is a roll up summary (sum) of a formula column in Opportunity. Let's call the formula field in Opportunity oppFormulaField.

The formula field is not visible in roll up, so the customer created another column in Opportunity (let's call it oppField2) and with a workflow try to duplicate the value of oppFormulaField.

Since the original Opportunity field formula (oppFormulaField) has time based formula, the value changes over time, but the changes don't get reflected in the workflow process and oppField2 doesn't have get updated over time.

They are suggesting to write an apex to daily copy the value of oppFormulaField to oppField2. But the whole thing seems to me so much broken. It's more like putting band aids all over the place.

Is what they are suggesting a good way to deal with roll up summary and formula fields or there are better reliable solutions for aggregation over a time based formula field in another object?

Here is the formula field:

IF((End_Date__c >= TODAY() && CloseDate <= TODAY()),"Yes", "No")

2

I don't think there is a general-purpose solution because it really depends on what the time-dependent formula represents. There could potentially be a "cut the middleman" solution whereby scheduled Apex or time-dependent workflows simply replace whatever it is the formula is seeking to achieve, but it's impossible to know without reviewing the whole solution.

Rather than writing scheduled Apex to copy a formula field so that a native roll-up summary will fire, I would consider applying Declarative Lookup Rollup Summaries to replace the roll-up summary. The extra field oppField2 could be removed, and a DLRS rollup running nightly in Scheduled mode can perform the entire process of rolling up oppFormulaField to the Account. Then, no code needs to be written at all.

In this case, I think you could also replace the formula field with some workflow or process builder automation. Given the original formula:

IF((End_Date__c >= TODAY() && CloseDate <= TODAY()),"Yes", "No")

there's basically two days on which the field might need to change: the close date, and the end date. You could add two Checkbox fields:

  • Is_End_Date_Passed__c
  • Is_Close_Date_Passed__c

Construct a Time-Based Workflow Rule (or Process) set to fire 0 days after the Close Date to set Is_Close_Date_Passed__c to true. Then, add another one to fire 0 days after End_Date__c and set Is_End_Date_Passed__c.

Your native roll-up summary's criteria can then look for Is_End_Date_Passed__c to be false and Is_Close_Date_Passed__c to be true. The workflow rules will provide the triggerable events needed to get the rollup updated.

I think that'll do the trick, if I understood your description correctly. I'm assuming the roll-up currently looks for "Yes".

  • Hi David, added the formula field to the end of the question for better clarification. – apadana Dec 4 '18 at 19:14
  • @apadana Thanks. I think there is a simple solution for this case. – David Reed Dec 4 '18 at 19:22
  • that was brilliant. Thank you. It makes total sense. – apadana Dec 4 '18 at 19:30

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.