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