Return to site

Using a Picklist in a Validation Rule

Business Problem:

It's not uncommon for businesses to use picklists to ensure data quality and conformity. This often comes up when working with Opportunities. A good example of this is listing the Competitor that you lost a deal to in order to track who your biggest competitor is.

Formula Rule Solution:

AND(
ISPICKVAL(StageName,"Closed Lost"),
NOT(
ISPICKVAL( Competitor__c, "Competitor_Picklist_Value1")),
NOT(
ISPICKVAL( Competitor__c, "
Competitor_Picklist_Value2")),
NOT(
ISPICKVAL( Competitor__c, "
Competitor_Picklist_Value3")
)
)

Let's break this validation rule down. In this rule I am introducing the NOT() function which is a great function for determining is something is NOT true... or false if you are following the logic. However, the NOT() function only works with one parameter at a time. Let me explain.

THIS IS POOR SYNTAX:

NOT(
ISPICKVAL( Competitor__c, "Competitor_Picklist_Value1"),
ISPICKVAL( Competitor__c, "Competitor_Picklist_Value2"),
ISPICKVAL( Competitor__c, "Competitor_Picklist_Value3")
)

The NOT() function will only work with one parameter.

The odd thing here is that if your picklist is actually a multi-select picklist you don't need all this mumbo-jumbo. All you need is something that looks like this:

AND(
ISPICKVAL(StageName,"Closed Lost"),
ISBLANK(Competitor__c)
)