0

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: enter image description here

Wondering what the appropriate syntax is for a blank picklist value

2
  • 4
    When trying to debug formulas, the first step should always be to format the formula like it were code (single logical statement per line, indent an extra level each time you encounter an open parenthesis). It's just too easy for our brains to get lost in the sea of parenthesis otherwise. I've done the formatting for you this time but encourage you to get used to doing it yourself.
    – Derek F
    Jan 10 at 16:08
  • 3
    Hint: missing ')' could also mean: too many '('... Jan 10 at 16:10

1 Answer 1

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.

5
  • Thanks Derek F, your assistance with the structure was helpful but now I'm getting an error for null picklist value as you thought might happen. I posted an updated screenshot of the new formula in my original post (couldn't figure out how to do it in a comment). Any thoughts on how to write that line correctly?
    – user7417
    Jan 10 at 17:41
  • @user7417 ah, I see I missed another syntax issue. I'll edit my answer. Also note that I said you should use null, not "null". null is a special empty/blank/there's nothing here value. It's a literal value (like a number or a boolean true/false) and should not be enclosed in quotes.
    – Derek F
    Jan 10 at 17:46
  • And I'm sorry as well, I should have added to my latest post that I did try null first withOUT the parenthesis and recieved the same error so I just tried adding the parenthesis and it still didn't like it.
    – user7417
    Jan 10 at 18:17
  • Thanks for all your help Derek. I appreciate how well explained everything so that I could not only fix it, but also UNDERSTAND it. I clearly need to brush up on my formulas. My traffic light is now working as expected. I would post the final formula but I can't seem to do that anywhere but my original post/question. Thanks again!
    – user7417
    Jan 10 at 18:24
  • @user7417 You're welcome. Also, comments aren't meant to hold lots of information. They're mostly for getting clarification (and are temporary, at least in theory), so their length is limited (to something like 500 characters) and you can't add pictures or do much formatting.
    – Derek F
    Jan 10 at 18:27

You must log in to answer this question.

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