Help with a formula field to display traffic light image

Question

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

Answer 1

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()

AND(
    (
        ISPICKVAL ( Restriction_New__c <> " "), 
        (
            ISPICKVAL( Account_Status_New__c , "Active")
        )
),

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:

• To contain parameters for a function
• Organizing things
• Overriding operator precedence (just like in math)

Adding things without a purpose tends to not end well. The following is all you need for this AND() section of your formula

AND(
    ISPICKVAL( Restriction_New__c <> " "), 
    ISPICKVAL( Account_Status_New__c , "Active")
),

It'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 is null. I imagine you should replace " " with null 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 a NOT().

NOT(ISPICKVAL(Restriction_New__c, null))

NOT(<thing 1 == thing 2>) is the same as <thing 1 != thing 2>. You need to use NOT() here because ISPICKVAL() can itself only check for equality. TEXT(Restriction_New__c) != null may be an alternative.