Ask Search:
Jolene WhiteJolene White 

Formula field to translate & concatenate multi-pick list values

Hello,
I am creating a formula field to concatenate a series of values based on values chosen in a multi-pick list. 

Multi-select picklist: Languages
Formula field where the 'code' corresponding to each language would appear: Language Code

Here is the current formula.  Currently it only wants to convert 1 value from the field 'Languages' into the value seen in the field 'Language Code'.

IF(INCLUDES(Languages__c,"Arabic"),"ARABIC;","")+ 
IF(INCLUDES(Languages__c,"Cambodian"),"CAMBO;","") + 
IF(INCLUDES(Languages__c,"Chinese"),"CHINESE;","")+
IF(INCLUDES(Languages__c,"Czech"),"CZECH;","")+
IF(INCLUDES(Languages__c,"Dutch"),"DUTCH;","")+ 
IF(INCLUDES(Languages__c,"Farsi"),"FARSI;", NULL)+ 
IF(INCLUDES(Languages__c,"Finnish"),"FIN;", NULL)+
IF(INCLUDES(Languages__c,"French"),"FRENCH;", NULL) + 
IF(INCLUDES(Languages__c,"German"),"GERMAN;", NULL)+
IF(INCLUDES(Languages__c,"Greek"),"GREEK;", NULL)+ 
IF(INCLUDES(Languages__c,"Hindi"),"HINDI;", NULL)+ 
IF(INCLUDES(Languages__c,"Italian"),"ITALIAN;","")+ 
IF(INCLUDES(Languages__c,"Japanese"),"JAPANESE;","") + 
IF(INCLUDES(Languages__c,"Nepalese"),"NEPALI;","")+ 
IF(INCLUDES(Languages__c,"Polish"),"POLISH;","")+
IF(INCLUDES(Languages__c,"Portuguese"),"PORT;","")+ 
IF(INCLUDES(Languages__c,"Russian"),"RUS;", NULL)+
IF(INCLUDES(Languages__c,"Sign Language"),"SIGNL;", NULL)+
IF(INCLUDES(Languages__c,"Swedish"),"SWEDISH;", NULL) + 
IF(INCLUDES(Languages__c,"Tagalog"),"TAGALOG;", NULL)+
IF(INCLUDES(Languages__c,"Korean"),"KOREAN;", NULL)+
IF(INCLUDES(Languages__c,"Malay"),"MALAY;", NULL)+
IF(INCLUDES(Languages__c,"Mandarin"),"MANDARIN;", NULL)+
IF(INCLUDES(Languages__c,"Urdu"),"URDU;", NULL)+
IF(INCLUDES(Languages__c,"Gujarati"),"GUJAR;", NULL)+
IF(INCLUDES(Languages__c,"Hmong"),"HMONG;", NULL)+
IF(INCLUDES(Languages__c,"Spanish"),"SPAN;", NULL)
Best Answer chosen by Jolene White
Mayank SrivastavaMayank Srivastava
Ah so that is happening because you haven't included the Amharic and Akan values in the formula like this:
​IF(INCLUDES(Languages__c,"Amharic"),"AMHARIC;","")+ 
​IF(INCLUDES(Languages__c,"Akan "),"AKAN;","")+ 
IF(INCLUDES(Languages__c,"Arabic"),"ARABIC;","")+ 
IF(INCLUDES(Languages__c,"Cambodian"),"CAMBO;","") + 
IF(INCLUDES(Languages__c,"Chinese"),"CHINESE;","")+
IF(INCLUDES(Languages__c,"Czech"),"CZECH;","")+
IF(INCLUDES(Languages__c,"Dutch"),"DUTCH;","")+ 
IF(INCLUDES(Languages__c,"Farsi"),"FARSI;", NULL)+ 
IF(INCLUDES(Languages__c,"Finnish"),"FIN;", NULL)+
IF(INCLUDES(Languages__c,"French"),"FRENCH;", NULL) + 
IF(INCLUDES(Languages__c,"German"),"GERMAN;", NULL)+
IF(INCLUDES(Languages__c,"Greek"),"GREEK;", NULL)+ 
IF(INCLUDES(Languages__c,"Hindi"),"HINDI;", NULL)+ 
IF(INCLUDES(Languages__c,"Italian"),"ITALIAN;","")+ 
IF(INCLUDES(Languages__c,"Japanese"),"JAPANESE;","") + 
IF(INCLUDES(Languages__c,"Nepalese"),"NEPALI;","")+ 
IF(INCLUDES(Languages__c,"Polish"),"POLISH;","")+
IF(INCLUDES(Languages__c,"Portuguese"),"PORT;","")+ 
IF(INCLUDES(Languages__c,"Russian"),"RUS;", NULL)+
IF(INCLUDES(Languages__c,"Sign Language"),"SIGNL;", NULL)+
IF(INCLUDES(Languages__c,"Swedish"),"SWEDISH;", NULL) + 
IF(INCLUDES(Languages__c,"Tagalog"),"TAGALOG;", NULL)+
IF(INCLUDES(Languages__c,"Korean"),"KOREAN;", NULL)+
IF(INCLUDES(Languages__c,"Malay"),"MALAY;", NULL)+
IF(INCLUDES(Languages__c,"Mandarin"),"MANDARIN;", NULL)+
IF(INCLUDES(Languages__c,"Urdu"),"URDU;", NULL)+
IF(INCLUDES(Languages__c,"Gujarati"),"GUJAR;", NULL)+
IF(INCLUDES(Languages__c,"Hmong"),"HMONG;", NULL)+
IF(INCLUDES(Languages__c,"Spanish"),"SPAN;", NULL)
Ensure all MSP values are accounted for in the formula.
 

All Answers

Patrick SullivanPatrick Sullivan
This looks like you are copying the values in an identical format to the underlying MultiSelect Picklist. What is the business case here?
Jolene WhiteJolene White
Not all of them are an exact copy.  For example, for Portuguese the value is PORT & for Russian it is RUS.
This field is being used for an integration that must contain these legacy value in all uppercase.
We would like to not use those shortened values like 'PORT' or 'RUS' because not all users may figure out what the value is.
Mayank SrivastavaMayank Srivastava
Jolene, what's the end goal here? Are you seeing an issue with the formula? Do you want it to show something else?
Jolene WhiteJolene White
The end goal is, if the values in the multi-select pick list are: Finnish and Russian, then the field 'Language Code' should contain: FIN; RUS;
Patrick SullivanPatrick Sullivan
Oh, that makes sense. 

Can you show a screen shot of it misfiring with the Languages__c value and the Language Code value?
Mayank SrivastavaMayank Srivastava
So that formula you posted will do exactly that. Is it not doing that?
Jolene WhiteJolene White
Here is a screen shot of the result:

Example
Steve MolisSteve Molis
I dunno wht you're mixing "" and NULL in your Formula. 

Can you create a Report of a List View that shows some records with the Multi-Picklist field, the values selected, and your Formula results side-by-side?

Try this
 
IF(INCLUDES(Languages__c,"Arabic"),"ARABIC;",NULL)+ 
IF(INCLUDES(Languages__c,"Cambodian"),"CAMBO;",NULL) + 
IF(INCLUDES(Languages__c,"Chinese"),"CHINESE;",NULL)+
IF(INCLUDES(Languages__c,"Czech"),"CZECH;",NULL)+
IF(INCLUDES(Languages__c,"Dutch"),"DUTCH;",NULL)+ 
IF(INCLUDES(Languages__c,"Farsi"),"FARSI;", NULL)+ 
IF(INCLUDES(Languages__c,"Finnish"),"FIN;", NULL)+
IF(INCLUDES(Languages__c,"French"),"FRENCH;", NULL) + 
IF(INCLUDES(Languages__c,"German"),"GERMAN;", NULL)+
IF(INCLUDES(Languages__c,"Greek"),"GREEK;", NULL)+ 
IF(INCLUDES(Languages__c,"Hindi"),"HINDI;", NULL)+ 
IF(INCLUDES(Languages__c,"Italian"),"ITALIAN;",NULL)+ 
IF(INCLUDES(Languages__c,"Japanese"),"JAPANESE;",NULL) + 
IF(INCLUDES(Languages__c,"Nepalese"),"NEPALI;",NULL)+ 
IF(INCLUDES(Languages__c,"Polish"),"POLISH;",NULL)+
IF(INCLUDES(Languages__c,"Portuguese"),"PORT;",NULL)+ 
IF(INCLUDES(Languages__c,"Russian"),"RUS;", NULL)+
IF(INCLUDES(Languages__c,"Sign Language"),"SIGNL;", NULL)+
IF(INCLUDES(Languages__c,"Swedish"),"SWEDISH;", NULL) + 
IF(INCLUDES(Languages__c,"Tagalog"),"TAGALOG;", NULL)+
IF(INCLUDES(Languages__c,"Korean"),"KOREAN;", NULL)+
IF(INCLUDES(Languages__c,"Malay"),"MALAY;", NULL)+
IF(INCLUDES(Languages__c,"Mandarin"),"MANDARIN;", NULL)+
IF(INCLUDES(Languages__c,"Urdu"),"URDU;", NULL)+
IF(INCLUDES(Languages__c,"Gujarati"),"GUJAR;", NULL)+
IF(INCLUDES(Languages__c,"Hmong"),"HMONG;", NULL)+
IF(INCLUDES(Languages__c,"Spanish"),"SPAN;", NULL)

 
Mayank SrivastavaMayank Srivastava
Ah so that is happening because you haven't included the Amharic and Akan values in the formula like this:
​IF(INCLUDES(Languages__c,"Amharic"),"AMHARIC;","")+ 
​IF(INCLUDES(Languages__c,"Akan "),"AKAN;","")+ 
IF(INCLUDES(Languages__c,"Arabic"),"ARABIC;","")+ 
IF(INCLUDES(Languages__c,"Cambodian"),"CAMBO;","") + 
IF(INCLUDES(Languages__c,"Chinese"),"CHINESE;","")+
IF(INCLUDES(Languages__c,"Czech"),"CZECH;","")+
IF(INCLUDES(Languages__c,"Dutch"),"DUTCH;","")+ 
IF(INCLUDES(Languages__c,"Farsi"),"FARSI;", NULL)+ 
IF(INCLUDES(Languages__c,"Finnish"),"FIN;", NULL)+
IF(INCLUDES(Languages__c,"French"),"FRENCH;", NULL) + 
IF(INCLUDES(Languages__c,"German"),"GERMAN;", NULL)+
IF(INCLUDES(Languages__c,"Greek"),"GREEK;", NULL)+ 
IF(INCLUDES(Languages__c,"Hindi"),"HINDI;", NULL)+ 
IF(INCLUDES(Languages__c,"Italian"),"ITALIAN;","")+ 
IF(INCLUDES(Languages__c,"Japanese"),"JAPANESE;","") + 
IF(INCLUDES(Languages__c,"Nepalese"),"NEPALI;","")+ 
IF(INCLUDES(Languages__c,"Polish"),"POLISH;","")+
IF(INCLUDES(Languages__c,"Portuguese"),"PORT;","")+ 
IF(INCLUDES(Languages__c,"Russian"),"RUS;", NULL)+
IF(INCLUDES(Languages__c,"Sign Language"),"SIGNL;", NULL)+
IF(INCLUDES(Languages__c,"Swedish"),"SWEDISH;", NULL) + 
IF(INCLUDES(Languages__c,"Tagalog"),"TAGALOG;", NULL)+
IF(INCLUDES(Languages__c,"Korean"),"KOREAN;", NULL)+
IF(INCLUDES(Languages__c,"Malay"),"MALAY;", NULL)+
IF(INCLUDES(Languages__c,"Mandarin"),"MANDARIN;", NULL)+
IF(INCLUDES(Languages__c,"Urdu"),"URDU;", NULL)+
IF(INCLUDES(Languages__c,"Gujarati"),"GUJAR;", NULL)+
IF(INCLUDES(Languages__c,"Hmong"),"HMONG;", NULL)+
IF(INCLUDES(Languages__c,"Spanish"),"SPAN;", NULL)
Ensure all MSP values are accounted for in the formula.
 
This was selected as the best answer
Jolene WhiteJolene White
Thank you!  There were so many values I totally missed it.
Steve MolisSteve Molis
I'm just gonna leave this here...
https://success.salesforce.com/0693A000006IbUM