1

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!

Improve this question

1 Answer 1

Reset to default
2

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")
)
Improve this answer
5
  • 1
    Also, when you see something like IF(<condition>, true, false), the IF() is unnecessary. The <condition> part already results in a boolean value. Similarly, IF(<condition>, false, true) can be reduced to NOT(<condition>).
    – Derek F
    Dec 3, 2021 at 0:15
  • Thanks @DerekF : Deep down I knew that, but it is late and I was trying to answer this in the midst of other work, so I just modified the original formula and didn't think it all the way through. I appreciate you pointing that out, so I will edit my answer and add that.
    – Moonpie
    Dec 3, 2021 at 0:17
  • It worked! You guys rock! THANK YOU, THANK YOU, THANK YOU, SO MUCH! @DerekF
    – NoviceAdmin80
    Dec 3, 2021 at 3:28
  • It worked! You guys rock! THANK YOU, THANK YOU, THANK YOU, SO MUCH! @Moonpie
    – NoviceAdmin80
    Dec 3, 2021 at 3:28
  • 1
    @ChristianBurnett Be sure to accept the answer that helped you the most. Upvoting is also encouraged, but you'll need 15 reputation score to do that. Accepting an answer gives you a bit of reputation, gives the person whose answer you accept a bit of reputation, and signals to the larger community that your problem is solved (and doesn't require more attention).
    – Derek F
    Dec 3, 2021 at 3:31

You must log in to answer this question.

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