Writing a Checkbox Formula using 2 Picklist Fields

formula-field

I am having an issue writing a checkbox formula field on the Opportunity object.

I am trying to use 2 picklist fields – Type and Stage. I need the checkbox value to be true if

Type = NULL, New Business, Paywall, or Reactivation AND Stage – Closed Won

Here is my formula:

IF
( ISPICKVAL(Type,"")|| 
ISPICKVAL(Type,"New Business")|| 
ISPICKVAL(Type,"Paywall")|| 
ISPICKVAL(Type,"Reactivation")&& 
ISPICKVAL(StageName,"Closed Won"),TRUE, FALSE)

The syntax check is showing no errors. My issue is that the checkbox is being marked TRUE for any StageName value instead of only Closed Won.

Does anyone know what I am doing wrong?

Thank you so much!

Best Answer

I can't find a link to give the order of operations in a formula field, but I think AND has precedence over OR, so your formula is evaluating to:

Type = NULL OR New Business OR Paywall OR (Reactivation AND Stage = Closed Won)

That means it will be TRUE when Type = "Reactivation" AND Stage = "Closed Won"; and it will also be TRUE when Type = NULL OR "New Business" OR "Paywall" regardless of what the Stage value is.

Surround your Type selections with parentheses to isolate them and separate them from the StageName...

IF
( (ISPICKVAL(Type,"") || 
   ISPICKVAL(Type,"New Business") || 
   ISPICKVAL(Type,"Paywall") || 
   ISPICKVAL(Type,"Reactivation") ) && 
   ISPICKVAL(StageName,"Closed Won")
,TRUE
,FALSE)

That should make your formula evaluate to:

(Type = NULL OR New Business OR Paywall OR Reactivation) AND Stage = Closed Won

Update

To paraphrase what @DerekF said in his comment, the IF() is overkill, so you could use this:

( ISPICKVAL(Type,"") || 
  ISPICKVAL(Type,"New Business") || 
  ISPICKVAL(Type,"Paywall") || 
  ISPICKVAL(Type,"Reactivation") ) && 
ISPICKVAL(StageName,"Closed Won")

or, as I prefer to write it:

AND(
  OR(
    ISPICKVAL(Type,""),
    ISPICKVAL(Type,"New Business"),
    ISPICKVAL(Type,"Paywall"),
    ISPICKVAL(Type,"Reactivation")
  ),
  ISPICKVAL(StageName,"Closed Won")
)
Related Topic