Ask Search:
Jackie TraviesoJackie Travieso 

Formula Field (Text): Multi-select Picklist

What would the formula look like if I wanted to 'copy & paste' a value from a multiple select picklist from one object to another?

I tried this, but I get an error message.
TEXT( Opportunity__r.CPC__c )

Error msg:
CPC is a multi-select picklist field. Multi-select picklist fields are only supported in certain functions.
Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
Thanks @JackieT 

you could try something like this: 

IF(INCLUDES( Multi_Picklist_1__c , "A"), "A", NULL)  + BR() + 
IF(INCLUDES( Multi_Picklist_1__c , "B"), "B", NULL)  + BR() + 
IF(INCLUDES( Multi_Picklist_1__c , "C"), "C", NULL)

All Answers

System AdministratorSystem Administrator
Jackie, Multiselect picklist do not support 'Text' in formula field, zThe only way which I feel this might be possible is through trigger.

 

Steve MolisSteve Molis
ouch!  Multi-Picklists are evil...  I think you might have to create a series of IF statements, kinda like this...  but it's gonna be ugly.
 
IF(INCLUDES( Multi_Picklist_1__c , "A"), "A ;",  
IF(INCLUDES( Multi_Picklist_1__c , "B"), "B ;",
IF(INCLUDES( Multi_Picklist_1__c , "C"), "C ", NULL)))
Nebojsa ZgonjaninNebojsa Zgonjanin
Here you go:

How do I create a formula that shows the value(s) of a multi-select picklist?

We need to create a formula field on a child object that displays all of the selected values from a multi-select picklist on the parent object.


To achieve this result, you can create a formula using syntax like the example below.

IF ( INCLUDES ( parent.multipicklist__c , "Value 1" ), "Value 1; ",null ) &
IF ( INCLUDES ( parent.multipicklist__c , "Value 2" ), "Value 2; ",null ) &
IF ( INCLUDES ( parent.multipicklist__c , "Value 3" ), "Value 3; ",null ) &
IF ( INCLUDES ( parent.multipicklist__c , "Value 4" ), "Value 4; ",null ) &
IF ( INCLUDES ( parent.multipicklist__c , "Value 5" ), "Value 5; ",null )

 

 

 



Jackie TraviesoJackie Travieso
Hi Steve,
Great sessions at DF11!

Your recommendation worked perfectly if one value is selected. Do you know of a way to alter it so that multiple values can be displayed, if selected on the Oppty?

Thanks again!
jt
Steve MolisSteve Molis
Thanks @JackieT 

you could try something like this: 

IF(INCLUDES( Multi_Picklist_1__c , "A"), "A", NULL)  + BR() + 
IF(INCLUDES( Multi_Picklist_1__c , "B"), "B", NULL)  + BR() + 
IF(INCLUDES( Multi_Picklist_1__c , "C"), "C", NULL)

This was selected as the best answer
Steve MolisSteve Molis
actually this one might be a little better looking (not much) 

IF(INCLUDES( Multi_Picklist_1__c , "A"), "A" + BR(), NULL)   + 
IF(INCLUDES( Multi_Picklist_1__c , "B"), "B" + BR() , NULL)  + 
IF(INCLUDES( Multi_Picklist_1__c , "C"), "C", NULL)
Jackie TraviesoJackie Travieso
SteveMo & NZgon,
Thank you both for providing exactly what I needed! Having the format options is helpful and I learned something!

Have a great weekend!
jt
Jackie TraviesoJackie Travieso
I implemented SteveMo's recommendation and it works... but is there a way to eliminate the extra line spaces. For example, there are blank lines for D through G

A
B
C




H
I

Thank you!
jt
Jackie TraviesoJackie Travieso
You can disregard, I got it to work! Thank you!!!!
Steve MolisSteve Molis
Here you go JackieT

IF(INCLUDES( Multi_Picklist_1__c , "A"), "A" + BR() , NULL) +
IF(INCLUDES( Multi_Picklist_1__c , "B"), "B" + BR() , NULL) +
IF(INCLUDES( Multi_Picklist_1__c , "C"), "C" + BR() , NULL) +
IF(INCLUDES( Multi_Picklist_1__c , "D"), "D" + BR() , NULL) +
IF(INCLUDES( Multi_Picklist_1__c , "E"), "E", NULL)
Jackie TraviesoJackie Travieso
Today, I have a new need (surprise, surprise)!
I need to create a formula field that will copy & paste a single picklist value from one field to another. I've tried for over an hour to manipulate the solution you provided for Multi-select picklists, but have failed miserably!

What should my formula look like?

Thank you!
Steve MolisSteve Molis
Hi Jackie,
Are you trying to map a picklist to another picklist using a WFR?  or just display the selected picklist value as text?
Jackie TraviesoJackie Travieso
Hi Steve,
I just want to display the picklist value as text on another object.

jt
Steve MolisSteve Molis
Okay @Jackie T  the moment of truth...  what are the objects, how are they related, and what is the field name?
Jackie TraviesoJackie Travieso

The truth is a scarey thing!

I've got 3 needs:
1. Account & Opportunity. Field Name State
2. Account & Opportunity: Field Name Stage
3. Account & Opportunity: Field Name Type

I'm using these in a Junction Object to display the data in the appropriate related lists.

Jackie TraviesoJackie Travieso
I've got the solution... just in case anyone else needs it! This example uses States!

if(ISPICKVAL( Opportunity__r.Project_State__c , "AK") , "AK",
if(ISPICKVAL( Opportunity__r.Project_State__c , "AL") , "AL",
if(ISPICKVAL( Opportunity__r.Project_State__c , "AR") , "AR",
if(ISPICKVAL( Opportunity__r.Project_State__c , "AZ") , "AZ",
if(ISPICKVAL( Opportunity__r.Project_State__c , "CA") , "CA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "CO") , "CO",
if(ISPICKVAL( Opportunity__r.Project_State__c , "CT") , "CT",
if(ISPICKVAL( Opportunity__r.Project_State__c , "DE") , "DE",
if(ISPICKVAL( Opportunity__r.Project_State__c , "FL") , "FL",
if(ISPICKVAL( Opportunity__r.Project_State__c , "GA") , "GA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "HI") , "HI",
if(ISPICKVAL( Opportunity__r.Project_State__c , "IA") , "IA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "ID") , "ID",
if(ISPICKVAL( Opportunity__r.Project_State__c , "IL") , "IL",
if(ISPICKVAL( Opportunity__r.Project_State__c , "IN") , "IN",
if(ISPICKVAL( Opportunity__r.Project_State__c , "KS") , "KS",
if(ISPICKVAL( Opportunity__r.Project_State__c , "KY") , "KY",
if(ISPICKVAL( Opportunity__r.Project_State__c , "LA") , "LA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MA") , "MA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MD") , "MD",
if(ISPICKVAL( Opportunity__r.Project_State__c , "ME") , "ME",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MI") , "MI",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MN") , "MN",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MO") , "MO",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MS") , "MS",
if(ISPICKVAL( Opportunity__r.Project_State__c , "MT") , "MT",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NC") , "NC",
if(ISPICKVAL( Opportunity__r.Project_State__c , "ND") , "ND",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NE") , "NE",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NH") , "NH",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NJ") , "NJ",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NM") , "NM",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NV") , "NV",
if(ISPICKVAL( Opportunity__r.Project_State__c , "NY") , "NY",
if(ISPICKVAL( Opportunity__r.Project_State__c , "OH") , "OH",
if(ISPICKVAL( Opportunity__r.Project_State__c , "OK") , "OK",
if(ISPICKVAL( Opportunity__r.Project_State__c , "OR") , "OR",
if(ISPICKVAL( Opportunity__r.Project_State__c , "PA") , "PA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "RI") , "RI",
if(ISPICKVAL( Opportunity__r.Project_State__c , "SC") , "SC",
if(ISPICKVAL( Opportunity__r.Project_State__c , "SD") , "SD",
if(ISPICKVAL( Opportunity__r.Project_State__c , "TN") , "TN",
if(ISPICKVAL( Opportunity__r.Project_State__c , "TX") , "TX",
if(ISPICKVAL( Opportunity__r.Project_State__c , "UT") , "UT",
if(ISPICKVAL( Opportunity__r.Project_State__c , "VA") , "VA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "VT") , "VT",
if(ISPICKVAL( Opportunity__r.Project_State__c , "WA") , "WA",
if(ISPICKVAL( Opportunity__r.Project_State__c , "WV") , "WV",
if(ISPICKVAL( Opportunity__r.Project_State__c , "WI") , "WI",
if(ISPICKVAL( Opportunity__r.Project_State__c , "WY") , "WY",
Null))))))))))))))))))))))))))))))))))))))))))))))))))

Steve MolisSteve Molis
Hi Jackie,
I think you might be able to do it using either a TEXT or CASE function instead of all those nested IF ISPICKVAL functions.

Steve MolisSteve Molis
did you try using? 

TEXT(Opportunity__r.Project_State__c)  

or 

CASE(Opportunity__r.Project_State__c ,
"AK" , "AK",
  "AL" , "AL",
  "AR" , "AR",
  "AZ" , "AZ",
  "CA" , "CA",
  "CO" , "CO",
  "CT" , "CT",
  "DE" , "DE",
  "FL" , "FL",
  "GA" , "GA",
  "HI" , "HI",
  "IA" , "IA",
  "ID" , "ID",
  "IL" , "IL",
  "IN" , "IN",
  "KS" , "KS",
  "KY" , "KY",
  "LA" , "LA",
  "MA" , "MA",
  "MD" , "MD",
  "ME" , "ME",
  "MI" , "MI",
  "MN" , "MN",
  "MO" , "MO",
  "MS" , "MS",
  "MT" , "MT",
  "NC" , "NC",
  "ND" , "ND",
  "NE" , "NE", 
  "NH" , "NH",
  "NJ" , "NJ",
  "NM" , "NM",
  "NV" , "NV",
  "NY" , "NY",
  "OH" , "OH",
  "OK" , "OK",
  "OR" , "OR",
  "PA" , "PA",
  "RI" , "RI",
  "SC" , "SC",
  "SD" , "SD",
  "TN" , "TN",
  "TX" , "TX",
  "UT" , "UT",
  "VA" , "VA",
  "VT" , "VT",
  "WA" , "WA",
  "WV" , "WV",
  "WI" , "WI",
  "WY" , "WY",
Null)
Stacey SmithStacey Smith
AMAZING THANK YOU FOR THIS SUPPORT! WORKED FOR ME!
Concept Services MgmtConcept Services Mgmt
THIS.IS.FANTASTIC.STEVEMO!
Brian LewisBrian Lewis
Is there a way to make this dynamic?  So as your options for the multi picklist changes you don't have to update the formula?
swati pandeyswati pandey
I am using this formula for multipicklist 

TEXT( Customer__r.Customer_Dissatisfaction_Reason__c )

but getting this error

Error: Field Customer_Dissatisfaction_Reason__c is a multi-select picklist field. Multi-select picklist fields are only supported in certain functions

Thierry LaffinThierry Laffin
Hello Steve, 
Thanks, your formula : 

IF(INCLUDES( Multi_Picklist_1__c , "A"), "A", NULL)  + BR() +...

is smart ; and you should put the "BR()" commande into the brackets to avoid multiple line jumps

like that : 

IF(INCLUDES( Multi_Picklist_1__c , "A"+ BR()), "A", NULL)   +...

Thierry Laffin
Ryan MorganRyan Morgan
If you want to format it exactly like a standard Multi-Select Picklist you can add spaces, Trim the whole thing to get rid of the space at the end, then use Substitute to replace the spaces with space-semicolon.

SUBSTITUTE(TRIM(
IF(INCLUDES(Multi_Picklist_1__c,"A"),"A  ",NULL)&
IF(INCLUDES(Multi_Picklist_1__c,"B"),"B  ",NULL)&
IF(INCLUDES(Multi_Picklist_1__c,"C"),"C  ",NULL)
)," ","; ")

You'll have to tweak it if any of your Multi-Select Picklist values have a space in them.
Steve MolisSteve Molis
Here's an updated version (thanks to @DeepakA) I found that Report and Dashboard Charts gack on the BR() tags, so I replaced them with the SFDC semi-colon delimiter.  

SUBSTITUTE( 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Multi"), "Multi ;", NULL) + 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Picklist"), "Picklist ;", NULL) + 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Fields"), "Fields ;", NULL) + 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Suck"), "Suck ;", NULL) + ".", ";.", NULL)


Kamalavathi MedidiKamalavathi Medidi
I am using formula as below shown

IF(INCLUDES( Opportunity__r.Country_Inclusion__c, "United States"), "United States; ", null) &
IF(INCLUDES( Opportunity__r.Country_Inclusion__c, "Canada"), "Canada; ", null) 

In 'Opportunity',  Country_Inclusion__c field contains nearly 150 picklist values
but formula field on 'Case' allowing only upto 45 values, how can i achieve all 150 Opportunity values into formula field
Steve MolisSteve Molis
 Kamalavathi Medidi  

Unfortunately with that many Multi-Picklist values you're nearly guaranteed to exceed the 5K Compile Size limitation of a Formula field.  

Can you provide more information about the Fields, Objects, values involved and what you're trying to do?  Also which edition of SFDC are you using? 
Catriona HoganCatriona Hogan
Hi - I'm looking for a similar solution but have tried all of these and they don't seem to work.I have a Multi-select Picklist (Characteristics__c) that I am using to list a group of characteristics of the product and need to create a separate field to show the chosen characterics in text format in another language. I've tried using the INCLUDES function which should work but it just comes up with the following: Error: Field Characteristics__c may not be used in this type of formula. Can anyone help here?

Thanks  
David PabstDavid Pabst
I made a slight modification to the @SteveMo code a few posts up. This way, the value looks the same as the source. 
@SteveMo example: Multi ;Picklist ;Fields ;Suck
My example: Multi; Picklist; Fields; Suck
SUBSTITUTE( 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Multi"), "Multi; ", NULL) + 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Picklist"), "Picklist; ", NULL) + 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Fields"), "Fields; ", NULL) + 
IF(INCLUDES(Account__r.F-ingMulti_Picklist__c, "Suck"), "Suck; ", NULL) + ".", "; .", NULL)
Steve, any reason you didn't do it like this?
Steve MolisSteve Molis
Catriona Hogan 

Can you post a screenshot of the Formula you're using in the Formula editor and the error message?

Are you doing this in Formula(TEXT) dtatype field and using the Advanced Formula Tab and Insert Field and Insert Function buttons? 
Steve MolisSteve Molis
@David Pabst 

Not really, you can use any delimeter or spacing you want.  
Dennis OnyangoDennis Onyango
@Steve Molis

What if I need to update a multi-select field onto a TEXT in order to make it searchable? What logic would I use? Searched all day on this.
 
Steve MolisSteve Molis
Dennis, 
What are you trying to do?  Update a Text datatype field instead of a Formula(Text) field?  For that you'd use a Workflow Rule or the Process Builder 
Anne CarloAnne Carlo
hi all.   Has this solution changed at all since originally posting in 2011? ....it isnt working for me :(  Also, is the BR() necessary?

IF(INCLUDES( Multi_Picklist_1__c , "A"), "A", NULL)  + BR() + 
IF(INCLUDES( Multi_Picklist_1__c , "B"), "B", NULL)  + BR() + 
IF(INCLUDES( Multi_Picklist_1__c , "C"), "C", NULL)
Steve MolisSteve Molis
Anne, can you post the Formula you're using and elaborate on what problems you're having?  For example: Are you getting syntax errors?  an invalid result? does you're neighbor's laptop burst into flames?  Is one of your socks missing?
Anne CarloAnne Carlo
hi Steve - i realized that i didnt actually have the objects related.   and yes, my socks are missing.  ;)
Steve MolisSteve Molis
Well, better that than your neighbors laptop bursting into flames ;-D