[SalesForce] Having trouble with a roll-up summary field that is trying to use a formula(date) child field

What I want to happen:

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

  1. 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:

Make sure that the filter for your roll-up summary does not encounter a formula field that results in “#Error!”. If your filter criteria uses a formula field that results in an error, no matches are returned for that filter criterion. For example, if your roll-up summary filter is “Formula Field equals 10” and two records contain errors while one contains the value “10” in that field, your summary only includes the record with the value “10.”

Your formula will produce an error any time IF(ISPICKVAL(StageName, "Closed Won") or IF( ISPICKVAL(StageName, "Closed Lost") evaluates to false. That's why SF won't allow you to use your formula field.

Related Topic