I am trying to build the formula for a traffic light on an Account Field.
The criteria is:
If "Member Status" (this is a picklist field) = Active AND "Restriction" (this is a picklist field) Does NOT = Null, then display Yellow Light
If "Member Status" (picklist) = Inactive, then display Red Light
If "Member Status" (picklist) = Active AND "Restriction" (picklist) = Null, then display Green light.
This is what I have currently after changing multiple times according to syntex errors:
IF(
ISPICKVAL( Account_Status_New__c , "Inactive"),
IMAGE("/img/samples/light_red.gif", "red"),
IF(
AND(
(
ISPICKVAL ( Restriction_New__c <> " "),
(
ISPICKVAL( Account_Status_New__c , "Active")
)
),
IMAGE("/img/samples/light_yellow.gif", "yellow"),
IF(
ISPICKVAL( Account_Status_New__c , "Active"),
IMAGE("/img/samples/light_green.gif", "green" ),
null
)
)
)
I'm currently getting this syntax error:
Error: Syntax error. Missing ')'
but I'm not sure where to put as I've already added some and I think I might be making it worse.
Any suggestions are appreciated.
UPDATE: New formula and error:
Wondering what the appropriate syntax is
for a blank picklist value
Best Answer
As I laid out in my comment, properly formatting your formula is the first step here. It has a tendendy to make it extremely clear where the most common mistakes happen.
In your case, the issue is in the
AND()
As you fear, you've added extra parenthesis and made things worse. To be syntactically valid, you need to get rid of the extra ones that don't do anything (you could add one more close paren in the proper place, but there's really no reason for those extra parens).
Parenthesis are used for a few reasons:
Adding things without a purpose tends to not end well. The following is all you need for this
AND()
section of your formulaIt's also important to note that just because something is syntactically valid doesn't mean that it's semantically correct (i.e. that it behaves the way you intended it to). The
ISPICKVAL( Restriction_New__c <> " ")
bit worries me.Salesforce generally doesn't store blank strings or strings with only spaces. Unless you specifically added
" "
, a single whitespace, as a value to that picklist, this part of your formula will ensure that you never hit the "yellow" status. The "no value is selected" value for picklists isnull
. I imagine you should replace" "
withnull
here.+edit:
I missed another syntax error.
ISPICKVAL( Restriction_New__c <> " ")
is not valid. As you'll see in Formula Operators and Functions,ISPICKVAL()
takes 2 parameters. The first one is the picklist field api name, the second is the value to test against.If you want to check that a picklist does not have a particular value selected, you need to wrap the
ISPICKVAL()
inside of aNOT()
.NOT(<thing 1 == thing 2>) is the same as <thing 1 != thing 2>. You need to use
NOT()
here becauseISPICKVAL()
can itself only check for equality.TEXT(Restriction_New__c) != null
may be an alternative.