2

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.

Improve this question
4
  • Please post the definition of CASL Implied Consent Expiry date including any formula fields it references. If CASL Implied Consent Expiry date or any of its dependent formula fields use a cross object formula or have a reference to NOW() or TODAY(), then CASL Implied Consent Expiry date won't be available. The workaround involves using workflows to create a shadow copy
    – cropredy
    Jan 19, 2015 at 22:28
  • Its posted in the original. Here it is again: IF( ISPICKVAL(StageName, "Closed Won"), CloseDate + 730, IF( ISPICKVAL(StageName, "Closed Lost"), DATEVALUE(CreatedDate)+ 182, DATE( 1900, 1, 1 )) ) I changed the "null" value to a solid date. For reference: StageName is a picklist, CloseDate is a Date, and CreatedDate is the system generated Date/Time. These are all opportunity fields. None of them are formula driven.
    – Amanda
    Jan 20, 2015 at 13:49
  • The formula seems to result in the correct/expected dates at the opportunity level. But it will not allow me to select the opportunity CASL expiry date when setting up the rollup field at the account level. I'll also add that I've tested the IF statements independently and then it allows me to use the date field in the rollup setup... I do think that the formula is somehow throwing an error as @crmprogdev stated. COuld it be a data type issue? I'm trying to convert everything to the DATE data type.
    – Amanda
    Jan 20, 2015 at 13:56
  • this would be pretty impressive/astonishing if SFDC did a semantic analysis of the formula field and realized one possible value was NULL and hence made CASL expiry field unavailable as a criteria for the Account RSF field - but looking at your last comment to crmprogdev, it seems as if even after dealing with null, the CASL field still cant be selected
    – cropredy
    Jan 20, 2015 at 20:32

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • Thanks. Can I ask for clarification...? I thought my formula resulted in a null/blank if the stage is not Closed Won or Closed Lost... So, If closed Won, else if Closed Lost, else Null
    – Amanda
    Jan 19, 2015 at 18:45
  • I think you'll find that anytime its not closed-won or closed-lost, the formula results in an error, not null/blank because only half of it can be evaluated. That's the essence of what I've quoted above. It can't be closed-won and closed-lost at the same time. The rest of the time it's #error. Otherwise it's either #error + value or value + #error.
    – crmprogdev
    Jan 19, 2015 at 19:49
  • I didn't state that very well. A Null value is the same as a #error. It would generate a null pointer exception during the roll-up calculations. The way it's currently set up, you need to specify values for when it's something other than closed-won or closed-lost. It can't be left at null.
    – crmprogdev
    Jan 19, 2015 at 20:12
  • Thank-you! That does help. I didn't realize the null value would generate that error. I have since replaced it with a "dummy" date of Jan 1 1900 (easily searchable and won't be close to any actual values...) So now every OPP record has a date in that expiry field. However, the Account roll-up field is still not showing the formula date field in the roll-up setup... I'll keep trying :)
    – Amanda
    Jan 19, 2015 at 20:22
  • It is working for me, no problems in selecting the formula field on Opp to roll-up on the Account. @Amanda: can you please confirm if it is resolved for you as well?
    – user24148
    Aug 24, 2015 at 14:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .