+ Start a Discussion
Steve ChadbournSteve Chadbourn 

How to get picklist value in formula

I'm trying to construct a formula field by concatenating a persons title (picklist), first name and last name. The first name and last name are OK but when I try and add the title to the formula I get:
 
Error: Field Claimant_Title__c is a picklist field. Use it with an ISPICKVAL() or CASE() function instead.
 
Here is the formula:
 
Claimant_Title__c & " " & Claimant_First_Name__c & " " & Claimant_Last_Name__c

So how do I get the current value of the picklist field?
Best Answer chosen by Admin (Salesforce Developers) 
SubMensaSubMensa
You are running into one of the major pitfalls of Formulas in SFDC. For some reason they do not allow us to reference Picklist values as the text strings that they are.

In regards to your formula the best option for you would be to use a CASE() function to first insert the Title, then append the remaining text concatenation after.

So, depending on the values you have within the Claimant_Title__c field, your formula would look something like the following:

CASE ( Claimant_Title__c ,
  "Mr." , "Mr." ,
"Ms." , "Ms." ,
 "Mrs." , "Mrs." ,
"Dr." , "Dr." ,
"Prof." , "Prof." ,
"" )
& " " & Claimant_First_Name__c & " " & Claimant_Last_Name__c

All Answers

SubMensaSubMensa
You are running into one of the major pitfalls of Formulas in SFDC. For some reason they do not allow us to reference Picklist values as the text strings that they are.

In regards to your formula the best option for you would be to use a CASE() function to first insert the Title, then append the remaining text concatenation after.

So, depending on the values you have within the Claimant_Title__c field, your formula would look something like the following:

CASE ( Claimant_Title__c ,
  "Mr." , "Mr." ,
"Ms." , "Ms." ,
 "Mrs." , "Mrs." ,
"Dr." , "Dr." ,
"Prof." , "Prof." ,
"" )
& " " & Claimant_First_Name__c & " " & Claimant_Last_Name__c

This was selected as the best answer
Steve ChadbournSteve Chadbourn

Thanks. I hoped this was not the case but was semi resigned to it.

It would be nice if a function like TEXT() could be used to get the picklist value. I'll search ideas and raise it if not there.

 

Steve ChadbournSteve Chadbourn

Its already registered as an idea:

http://ideas.salesforce.com/article/show/43022

Please promote if you read this thread and agree.

 

 

ethanoneethanone

So are you saying that if I have a picklist for the list States in the US that an opportunity might be in, for me to utilize the value of one of those states, i need a case statement with 50 items in it? And to make matters worse, if I want to use the value of a State and County combination, I'd have a case statement with 3000 options in it? That is nuts. And BTW, there is a limit on formulas of only 3900 characters, so it is impossible. Is there any way to accomplish this?

Mukesh Kumar 107Mukesh Kumar 107
Is there any workaround for Multi-Select Picklist? Above solution works for Single-Select picklist only.