Learning Objectives

Introduction to Using Picklists in Formulas

A picklist field lets you choose a value from a pre-populated list. While you can’t write a
formula that returns a picklist as a result, you likely have to reference picklists in your
formula fields.

Say you need a validation rule that requires a user to fill out an explanation if they select
"Other" as an Account’s Type. This validation rule formula
requires that a user fill out the text field Other Type if they set the
account’s Type as "Other."

ISPICKVAL(Type, "Other") &&
ISBLANK(Other_Type__c)

If a user leaves Other_Type__c blank when Type is set to "Other," the validation rule fires,
and the user can’t save the form.

Common Picklist Functions and Operators

Three functions take picklist values as arguments in all formula fields: ISPICKVAL(), CASE(), and
TEXT().

ISPICKVAL(picklist_field, text_value) returns true if
the value of picklist_field matches text_value, and false otherwise. You can combine ISPICKVAL() with PRIORVALUE(). You can use this function in assignment rules, validation rules,
field updates, and workflow rules to find the previous value of a field.

For example, this validation rule prevents a user from changing a case’s
Type from a previously selected value back to blank.

NOT(ISPICKVAL(PRIORVALUE(Type), "")) &&
ISPICKVAL(Type, "")

The validation rule fires if the prior value of Type is not blank and
the current value is.

CASE() is useful for writing formulas that have
different results based on the value of a picklist. This formula with the Number return type
assigns a case a priority based on its type.

The formula compares Type to each case, assigning a priority when it
finds a match. Electrical cases are given a priority of 1, Electronic cases 2, and so on.

TEXT() converts a picklist value to a Text value in the
master language of your organization, not the language of the current user. After a picklist
value has been converted to a Text value, you can use Text functions, such as BEGINS() and CONTAINS(),
on it.

This formula, for example, displays a case’s Status as a sentence.

"This case is " & TEXT(Status)

Note

You cannot use TEXT() on multi-select picklists.
Multi-select picklists are not recommended in formula fields.

Use Picklist Fields in Formulas

Create a Validation Rule Based on a Picklist

A picklist value often determines which other fields on a record are required. ISPICKVAL() and CASE()
are useful for creating validation rules that check whether a certain picklist value is
selected. For example, say you want users to enter a reason when they change a case’s
Status picklist value to Escalated.

First, create a custom text field Reason for Escalating on the Case
object.

In Setup, use the quick find box to find the Object
Manager.

Click Case | Fields & Relationships and click New.

Select Text Area and click Next.

In Field Label, enter Reason for
Escalating. Field Name populates automatically.

Click Next.

Click Next again and then click Save.

Now use the Status picklist field to set up a validation rule on
Reason for Escalating.

In Error Message, enter Please enter a reason for
changing the case status to Escalated.

Click Save.

The validation rule ensures that if the Status is set to Escalated,
Reason for Escalating is not blank. Test your formula by updating the
Status of a case to Escalated and saving the record without entering
a Reason for Escalating. The form displays your error message under the
Reason for Escalating field.

Assign a Contact’s Priority

What if you want to assign a priority to contacts based on their associated account rating,
a picklist field? For this formula, we use a cross-object reference for the contact’s
account rating, and the Is Executive checkbox formula field we created
in Using Basic Logic in Checkbox Formulas. Is Executive is checked if a
contact’s Title includes the words "Executive," "President," or
"Chief."

Because there are three possible account ratings—Hot, Warm, or Cold—and two options for
Is Executive—checked or unchecked—there are six total cases. We’ll
use CASE() to assign each possibility a priority based
on the following conditions.

Account.Rating

Is_Executive_c

Priority

Hot

Yes

1

Hot

No

1

Warm

Yes

1

Warm

No

2

Cold

Yes

2

Cold

No

3

Create a formula field on the contact object with the name Priority and the type
Number.

In this formula, we used IF() statements inside the
larger CASE() statement to more efficiently check all
six cases.

Picklist Examples

This formula returns the number of days since an account was activated based on the custom
picklist field Contract Status and the custom Date field
Contract Activated Date. If Contract Status is
not Activated, the field is
blank.

This formula uses the custom date field Payment Due Date and the
custom picklist field Payment Status with the options Paid and Unpaid
on the Contract object. If Payment Status is Unpaid and it’s past the
payment due date, the formula field displays Payment
overdue! Otherwise, the field is
blank.

If
users change a lead’s Status from closed to open, they see an error
when they try to save the record.

Common Errors with Picklists

The only functions that can take picklist fields as parameters in all formula fields are
ISPICKVAL(), CASE(), and TEXT(). Using picklist values
in any other function results in an error. This Checkbox formula field, for example, is
meant to display a checkbox that indicates whether the Lead Source is
"Partner Referral." The equals operator (=), however,
does not support picklist fields, and this formula causes an error.

LeadSource = "Partner Referral"

Instead, use ISPICKVAL() to check a picklist field’s
value, or use TEXT() to convert a picklist value to
Text before using the equals operator.