đź“Ł Attention Salesforce Certified Trailblazers! Link your Trailhead and Webassessor accounts and maintain your credentials by December 14th. Learn more.
close
trailhead

Use Picklists in Formulas

Learning Objectives

After completing this unit, you’ll be able to:
  • Describe use cases for formulas that reference picklist fields.
  • Use picklist functions in formula fields.
  • Create a formula referencing a picklist field.

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.

CASE(Type,
  "Electrical", 1,
  "Electronic", 2,
  "Mechanical", 3,
  "Structural", 4,
  "Other", 5,
5)

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

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.

  1. In Setup, use the quick find box to find the Object Manager.
  2. Click Case | Fields & Relationships and click New.
  3. Select Text Area and click Next.
  4. In Field Label, enter Reason for Escalating. Field Name populates automatically.
  5. Click Next.
  6. Click Next again and then click Save.

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

  1. Click the newly created Reason for Escalating field.
  2. Under Validation Rules, click New.
  3. In Rule Name, enter Reason_Required.
  4. In Error Condition Formula, enter the following validation rule:
    AND(
      ISPICKVAL(Status, "Escalated"),
      ISBLANK(Reason_for_Escalating__c)
    )
  5. In Error Message, enter Please enter a reason for changing the case status to Escalated.
  6. 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.

Users who do not set a Reason for Escalating will see this error message.

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.

  1. In Setup, use the quick find box to find the Object Manager.
  2. Click Contact | Fields & Relationships and click New.
  3. Select Formula and click Next.
  4. In Field Label, enter Priority. Field Name populates automatically.
  5. Select Number, and change Decimal Places to 0.
  6. Click Next.
  7. Enter the following formula:
    CASE(Account.Rating,
      "Hot", 1,
      "Warm", IF(Is_Executive__c, 1, 2),
      "Cold", IF(Is_Executive__c, 2, 3),
    3)

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

Picklist Examples

  1. 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.
    IF(ISPICKVAL(Contract_Status__c, "Activated"),
      TODAY() - Contract_Activated_Date__c, null)
  2. 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(AND(Payment_Due_Date__c < TODAY(), 
      ISPICKVAL(Payment_Status__c,
      "UNPAID")),
      "Payment overdue!", null)
  3. This validation rule uses ISPICKVAL() and PRIORVALUE() to display an error if someone tries to change a lead’s Status from "Closed - Converted" or "Closed - Not Converted" to "Open - Not Contacted."
    ISPICKVAL(Status, "Open - Not Contacted") &&
    BEGINS(TEXT(PRIORVALUE(Status)), "Closed")

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

    The error message a user sees when they violate the validation rule.

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.

    ISPICKVAL(LeadSource, "Partner Referral")
    TEXT(LeadSource) = "Partner Referral"
retargeting