Introduction

Picklists are perfect if we want our User to choose a value for a field from a pre-set list.

For Example, Case records include the standard field, Priority, which can take on three values – High, Medium, and Low. So, instead of expecting the User to type in the value for the field, we can make things a whole lot easier and pre-populate the list. They can simply expand the list and choose the appropriate value!

NOTE:

Although we can reference picklists in formulas, we cannot create a formula that returns a picklist.

Common Picklist Functions and Operators

(for a better view, right-click on the image and click on “Open image in new tab”):

Use Picklist Fields in Formulas

A Validation Rule based on a picklist

Let us create a validation rule on the Case object that prevents Users from changing the value of the field, Status to Escalated unless the field, Priority, is High.

1. From Setup, open Object Manager, and then select Case.

2. From the sidebar, open Fields & Relationships.

3. Open the field, Status.

4. On Validation Rules, click New.

5. For Rule Name, enter Escalate_Rule.

6. Enter the following for the Error Condition Formula.

NOT(TEXT(Priority)="High") && ISPICKVAL(Status, "Escalated")

7. For Error Message, enter “Priority needs to be High for Case Escalation.”

8. Click Save.

The validation rule prevents the User from changing the Status of a Case record to Escalated unless the Priority is High. The error message pops up, as shown in the image below, when the User tries to change the Status to Escalated when the Priority is not High.

A Custom Formula Field based on a picklist.

Let us create a Formula field of type, Number on the Case Object that assigns a value to the custom field, Case Level, based on the picklist value, Case Priority.

1. From Setup, open Object Manager, and then select Case.

2. From the sidebar, open Fields & Relationships and then click New.

3. For Field Type, select Formula.

4. For Field Label, enter Case Level. For Formula Return Type, choose Number, and set Decimal Places to 0.

5. Click Next.

6. Enter the following formula:

CASE(Priority,
     "High", 1,
     "Medium",2,
     "Low",3,
    3)

7. Click Next, Next, and Save.

The value of the formula field, Case Level, will depend on the value of the picklist, Priority, as shown below.

Picklist Examples

1. Commission of Closed-Won Opportunities

Let us calculate the Commission of Opportunities with Stage equal to “Closed Won.”

IF(ISPICKVAL(StageName,"Closed Won"), Amount *0.03,0)

2. Checkbox Field for Private, Insurance Accounts

Let us create a checkbox field for the Account object that’s selected if the Account has the value Private for “Ownership” and “Not For Profit” for Industry.

TEXT( Ownership ) = "Private" && TEXT( Industry ) = "Not For Profit"

3. Validation Rule on the Lead object

Let us create a Validation rule on Lead objects that prevents Users from changing Lead Status to Open – Not Contacted from Closed – Converted or Closed – Not Converted.

ISPICKVAL( Status , "Open - Not Contacted") &&
BEGINS(TEXT(PRIORVALUE(Status)),"Closed")

Common Errors with Picklists

As of now, only three functions can take a picklist field as a parameter 🡪 ISPICKVAL(), CASE(), and TEXT(). Let us test this out. Try using a picklist field in any other function. So, what happened? I am sure an error message popped up!

Example:

We want the checkbox field selected when the Priority of a Case is High. The first formula that comes to our mind may look like this:

Priority = "High"

Click Check Syntax in the formula editor to see an error message! The equal to (=) operator does not accept picklist fields. There are two solutions to this issue :

• Let us use the ISPICKVAL() function to compare a picklist field value.

ISPICKVAL(Priority,"High")

• Let us first use the TEXT() function to convert the picklist field to a text value, after which we can use the equal to (=) operator to check the value.

TEXT(Priority) = "High"