What I want to happen:
- Each opportunity has a custom formula field, with a return type Date, called "CASL Implied Consent Expiry date".
If the OPP is Stage=Closed LOST, the Expiry date will be the OPP Created date +182 days.
If the Stage=Closed WON, the expiry date will equal Close Date +730 days.
If the Opp is still open the Expiry date will be empty/null.
- The parent account, will/should use a rollup summary field to find the MAX date of all OPP expiry dates. (The date that is the most recent)
I've created a formula(date) field on the OPPORTUNITY object – here's the formula (it's producing the correct/expected results):
IF( ISPICKVAL(StageName, "Closed Won"),
CloseDate + 730,
IF( ISPICKVAL(StageName, "Closed Lost"),
DATEVALUE(CreatedDate)+ 182,
null)
)
My trouble is that I can't select the CASL Expiry date field in the rollup summary field setup.
It's my understanding that I should be able to use the MAX/Min on date fields – so long as the formula field "does not contain cross-object field references or functions that automatically derive values on the fly, such as NOW or TODAY."
(reference https://help.salesforce.com/HTViewHelpDoc?id=fields_about_roll_up_summary_fields.htm)
I might be misunderstanding the meaning of "automatically derive values on the fly, such as NOW or TODAY" but I don't think my date formula is doing that…
If it is, and the rollup summary is out of the question, what would be the next recommendation?
Thanks in advance.
Best Answer
From the reference you cited:
Your formula will produce an error any time
IF(ISPICKVAL(StageName, "Closed Won")
orIF( ISPICKVAL(StageName, "Closed Lost")
evaluates to false. That's why SF won't allow you to use your formula field.