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?