function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
CRMsimpleCRMsimple 

Formula field to return values but only if a certain picklist value is selected

I'm trying to update this formula, which works fine as it is, but I want the formula to run only if the record has a certain value in a picklist field. The existing code is as follows:

Code:
IF( Days_since_last_review__c >= 120, "120 Days No Review",
IF(Days_since_last_review__c >= 90, "90 Days No Review",
IF(Days_since_last_review__c = 0, "Never Had Review", null
)
)
)

What I want it to do now is evaluate ISPICKVAL Contact_Status_c, "Client" to run the rest of the formula only if this is the value. I've tried several ways using AND(ISPICKVAL but I keep getting errors like missing ')' or expected 2 received 3 etc.

Any help would be greatly appreciated. (I'm guessing it's either not possible or I'm missing something extremely simple)

Thanks!

Gemini@WorkGemini@Work

You don't need to do anything funky with an AND () operator, just add another IF () statement check to the top of your list.  Aside from that, it's easier to spot missing parens if you indent your nested IF statements.

Code:
IF ( ISPICKVAL ( Contact_Status__c, "Client" ), 
    IF( Days_since_last_review__c >= 120, "120 Days No Review",
        IF(Days_since_last_review__c >= 90, "90 Days No Review",
            IF(Days_since_last_review__c = 0, "Never Had Review", null )
        )
    )
)


 



Message Edited by Gemini@Work on 08-03-2008 11:26 AM
CRMsimpleCRMsimple
Thanks - I tried that before and kept getting this error  - incorrect number of parameters for function IF(). Expected 3 received 2.
Any thoughts on how to correct it? This is what I've been trying to figure out.


Gemini@WorkGemini@Work

ahh, of course ... the syntax for an IF() function is:

IF (logical test, value_if_true, value_if_false).  Salesforce is complaining because there is no "value_if_false" for the first IF statement in this test.

You could just set the field name to it's current value.  There's probably a more elegant way of doing this, but this early in the morning, the following code should work:

Code:
IF ( ISPICKVAL ( Contact_Status__c, "Client" ), 
    IF( Days_since_last_review__c >= 120, "120 Days No Review",
        IF(Days_since_last_review__c >= 90, "90 Days No Review",
            IF(Days_since_last_review__c = 0, "Never Had Review", null )
        )
    ),
    formula_field_name__c
)


 
Here, formula_field_name__c should be changed to whatever the field is that you're doing this formula on.  The effect will be that if the "Contact_Status__c" is "Client", it will follow your nested IF statement rules -- but if "Contact_Status__c" is something else, it will just copy the existing field value back into the field -- effectively leaving it unchanged.

Someone should do a study on the corelation of nested IF statements and migraine headaches, ya think?

CRMsimpleCRMsimple
Thanks JP!

yeah, I'll definitely sign up for that study :smileywink: