How To Use Picklist Value In Formula Field In Salesforce

Learning Objectives

After completing this unit, you lot'll be able to:

Draw 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 yous can't write a formula that returns a picklist as a result, y'all likely accept to reference picklists in your formula fields.

Say you need a validation dominion that requires a user to fill out an explanation if they select "Other" as an Account'south Type. This validation rule formula requires that a user fill up out the text field Other Blazon if they gear up the account'south Type as "Other."

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

If a user leaves Other_Type__c blank when Type is gear up to "Other," the validation rule fires, and the user can't save the course.

Mutual Picklist Functions and Operators

Three functions have 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 simulated otherwise. You tin 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 case, this validation rule prevents a user from changing a case'due south Type from a previously selected value back to blank.

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

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

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

Example(Type,   "Electrical", ane,   "Electronic", 2,   "Mechanical", 3,   "Structural", 4,   "Other", 5, 5)

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

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

This formula, for example, displays a case's Status every bit a sentence.

"This case is " & TEXT(Condition)

Employ Picklist Fields in Formulas

Create a Validation Rule Based on a Picklist

A picklist value oftentimes determines which other fields on a tape are required. ISPICKVAL() and Case() are useful for creating validation rules that check whether a certain picklist value is selected. For instance, say y'all want users to enter a reason when they alter a case'due south Status picklist value to Escalated.

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

In Setup, use the quick find box to find the Object Director.
Click Case | Fields & Relationships and click New.
Select Text Surface area and click Adjacent.
In Field Label, enter Reason for Escalating. Field Name populates automatically.
Click Next.
Click Next once more so click Salve.

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

Click the newly created Reason for Escalating field.
Under Validation Rules, click New.
In Rule Name, enter Reason_Required.

In Error Condition Formula, enter the following validation rule:

AND(   ISPICKVAL(Status, "Escalated"),   ISBLANK(Reason_for_Escalating__c) )

In Mistake Message, enter Please enter a reason for changing the instance status to Escalated.
Click Save.

The validation dominion ensures that if the Status is set to Escalated, Reason for Escalating is not bare. 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 fault message under the Reason for Escalating field.

Users who do not set a Reason for Escalating will see this error message. "Error: Please enter a reason for changing the case status to escalated."

What if you desire to assign a priority to contacts based on their associated account rating, a picklist field? For this formula, we utilise a cross-object reference for the contact'south business relationship rating, and the Is Executive checkbox formula field we created in Using Basic Logic in Checkbox Formulas. Is Executive is checked if a contact'southward Title includes the words "Executive," "President," or "Main."

Because there are 3 possible account ratings—Hot, Warm, or Common cold—and two options for Is Executive —checked or unchecked—there are 6 full cases. We'll apply Instance() to assign each possibility a priority based on the post-obit conditions.

Account.Rating	Is_Executive_c	Priority
Hot	Yes	1
Hot	No	1
Warm	Yes	ane
Warm	No	2
Common cold	Yes	two
Cold	No	3

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

In Setup, apply the quick observe box to find the Object Director.
Click Contact | Fields & Relationships and click New.
Select Formula and click Next.
In Field Characterization, enter Priority. Field Name populates automatically.
Select Number, and change Decimal Places to 0.
Click Next.

Enter the following formula:

Instance(Account.Rating,   "Hot", one,   "Warm", IF(Is_Executive__c, 1, 2),   "Cold", IF(Is_Executive__c, 2, three), 3)

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 Appointment. If Contract Condition is non Activated, the field is blank.
```
IF(ISPICKVAL(Contract_Status__c, "Activated"),   TODAY() - Contract_Activated_Date__c, zip)
```
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 Condition 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)
```
This validation rule uses ISPICKVAL() and PRIORVALUE() to display an error if someone tries to change a atomic number 82'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 Condition from closed to open, they see an error when they try to save the tape.

Mutual Errors with Picklists

The but functions that can take picklist fields as parameters in all formula fields are ISPICKVAL(), CASE(), and TEXT(). Using picklist values in any other office results in an error. This Checkbox formula field, for instance, 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, employ ISPICKVAL() to check a picklist field's value, or employ TEXT() to convert a picklist value to Text before using the equals operator.
```
ISPICKVAL(LeadSource, "Partner Referral")
```
```
TEXT(LeadSource) = "Partner Referral"
```