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

Varun NagpalVarun Nagpal
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
David BirnieDavid Birnie
Thanks for the tips in this post. I have elaborated this solution a little bit to incorporate the user's language in the calculated field. In my org the English (system) and Dutch language are supported.
IF(INCLUDES(Account_Type__c, "Value 1"),IF($User.User_Language__c="nl_NL","Waarde 1","Value 1") + BR() , "")+ 
IF(INCLUDES(Account_Type__c, "Value 2"),IF($User.User_Language__c="nl_NL","Waarde 2","Value 2"),"")
The custom field User_Language__c gets populated by process builder since you can access the "actual" user language field in process builder. This custom field can be accessed in formulas.
 
Steve MolisSteve Molis
How do you say "Multi-Select Picklist Fields Suck" in Dutch?
David BirnieDavid Birnie
I think multi-picklist are challenging when it comes to formula's but don't we like a challenge?
Samantha LattaSamantha Latta
Hello all! 
This string of answers seems like the most helpful for what I am trying to do! I have a custom field for "Days Outstanding" that us currently being calculated by: TODAY() - Requested_Date__c

However, our sales team does not want to see items that have a completed status with a large # of days outstanding. Unfortunately our status is a multi-picklist for the various status stages of the activity we are tracking. 
I need a way to calculate the # of days a request is outstanding but return blank (null) when the multi-picklist Status_c is for "Sent to Cardinal" list option. 
Anyone up for the challenege? 
Thank you! 
Steve MolisSteve Molis
Hi Sam
Can you post the complete list of Fields, Objects, the Datatypes and Values your Formula is referencing?

Sounds like you just need:
IF(
INCLUDES(Status__c, "Sent to Cardinal", 
NULL,
TODAY() - Requested_Date__c)

 
Liam DarmodyLiam Darmody

I am trying to do a similar thing but am not an admin (we don't have one and I'm just putting on the hat). 

Trying to do the following: 
We have a picklist on the account object that has 8 options to choose from. 

I'd like to create a workflow rule that automatically updates a field on the contact object: "Contact Account Type" and pulls in whatever is in that field on the account record if it's not blank. 

I setup a workflow rule to update Contact_Account_Type_Match_Update field with Account.Account_Type__c and got the below error message: 

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

Anyone know how I can accomplish this? I'm sure it's easy and I'm just missing it because novice. 

Steve MolisSteve Molis
Hi Liam,

All the error message says: "Error: Field Account_Type__c is a multi-select picklist field. Multi-select picklist fields are only supported in certain functions. 

You're really limited with what you can do with them.  You can't update a Multi-Picklist Fields with a Workflow Rule, you'll need to use either the Process Builder or get rid of the Multi-Picklist field and replace it with Checkboxes or something else.  

Multi-Select Picklist fields are good at 3 things:
1. Allowing a User to select one or more things from a list of options.
2. SUCKING 
I forget the third one... 
Jackie PohlJackie Pohl
Thanks for all of the helpful info!  What should this look like if you would like commas in between the values but not after the last value?  

Thank you!
Steve MolisSteve Molis
Like this?
 
IF(ISBLANK( Multi_Picklist__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES(Multi_Picklist__c, "Multi"), "Multi, ", NULL) + 
IF(INCLUDES(Multi_Picklist__c, "Picklist"), "Picklist, ", NULL) + 
IF(INCLUDES(Multi_Picklist__c, "Fields"), "Fields, ", NULL) + 
IF(INCLUDES(Multi_Picklist__c, "Suck"), "Suck, ", NULL) + ".", ", .", NULL))

 
SMB HelpdeskSMB Helpdesk
I used this Formula  from Nebojsa Zgonjanin above:
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 )

I put it into a workflow rule, so that it would update a text field that I could then use to filter lookup fields on.
I was wondering what modification would be needed, to have values removed from the text field when values are removed by a rep from the multi-select Piclist.

Thanks for any help,
Zachary
Steve MolisSteve Molis
Can you post screenshots of your WFR and Action? 

Keep in mind that you're using a Cross-Object reference, so your WFR won't trigger if the Multi-Picklist Field on the other Object is edited.
Roger MillerRoger Miller
Much appreciation to everyone on this thread. I'm trying to pull a the contents of a picklist from the account record to the contact record. Thought that this should work: 
IF ( INCLUDES ( Account.Funding_Likelihood__c , "1 = current funder" ), "1 = current funder; ",null ) + BR() +
IF ( INCLUDES ( Account.Funding_Likelihood__c , "2 = past/lapsed" ), "2 = past/lapsed; ",null ) + BR() +
IF ( INCLUDES ( Account.Funding_Likelihood__c , "3 = hot prospect" ), "3 = hot prospect; ",null ) + BR() +
IF ( INCLUDES ( Account.Funding_Likelihood__c , "4 = potential" ), "4 = potential; ",null ) + BR() +
IF ( INCLUDES ( Account.Funding_Likelihood__c , "5 = longshot" ), "5 = longshot; ",null ) + BR() +
IF ( INCLUDES ( Account.Funding_Likelihood__c , "6 = not now" ), "6 = not now; ",null ) + BR() +
IF ( INCLUDES ( Account.Funding_Likelihood__c , "7 = not ever" ), "7 = not ever; ",null )

But I keep getting this error: Error: Field Funding_Likelihood__c is a picklist field. Picklist fields are only supported in certain functions. I tried some other variations and got the syntax to work, but never got any results.

Help please! Thanks!
Lydia PearlyLydia Pearly
What if the return type has to be numbers like if it includes 'value1' include "1" and if 'value2' include "2" and so on..
Steve MolisSteve Molis
Hi Roger,
As the error message said "Error: Field Funding_Likelihood__c is a picklist field. Picklist fields are only supported in certain functions.

You can't use an INCLUDES function on a Picklist Field, they only work on Multi-Select Picklist Fields.  If the field is just a Picklist then you would use either an ISPICKVAL, CASE, or TEXT function instead 

Personally I would just use
TEXT(Account.Funding_Likelihood__c)

 
Steve MolisSteve Molis
Lydia Pearly

Can you provide more detailed information about what you're trying to do?  Like the Field Names, Datatypes, the Values, and the Results you're trying to get? I don't have any access to your SFDC org, so you need to provide as much detailed information as possible for us to help you. Otherwise it's like playing "Pin the Tail on the Donkey".  
Lydia PearlyLydia Pearly
I have a multi picklist which has 70 different values and our client needs a report for each of the value. I tried the method suggested by you that is

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)
 
with the return type being text. I still couldn't use them in reports. i tried bucketing them but they come as 'ABC' not sepertely. So I tried doing the same with the rerurn type as number so I could assign each number to a value of the Multi picklist and use them in reports. Please Advise. I hope I am clear witht the requirement.
Rodolfo POPOCATL PAULRodolfo POPOCATL PAUL
Hello,
This has been a useful entry and the solution with IF ( INCLUDES ..) works fine, but has the limit of 5000 bytes. What have you done when you exceed the number of characters in the formula? Any tips?
Thanks in advance,
 
Steve MolisSteve Molis
Rodoflo,
Can you post your Formula and provide more information?   I don't have any access to your SFDC org, so you need to provide as much detailed information as possible for us to help you. 
Rodolfo POPOCATL PAULRodolfo POPOCATL PAUL
Hello Steve Molis,
Thank you for answering. I have just adapted the formula you have all been using:
F ( INCLUDES ( Account.Client_Manager__c , "A Chay" ), "A Chay ; ",null ) & 
IF ( INCLUDES ( Account.Client_Manager__c , "A Decult" ), "A Decult ; ",null ) & 
….

IF ( INCLUDES ( Account.Client_Manager__c , "M Lachk" ), "M Lachk ; ",null )
 
but I have 61 options for my multiple picklist, that makes aprox 4500 characters.
I have been searching, but I don’t think I can optimize the formula. Any suggestions?
Thanks in advance
Steve MolisSteve Molis
Hi Rodolfo, 
You're probably gonna have to use something other than a Formula Field, probably something like the Process Builder or a Trigger, or something like that.  
Or use another datatype other than a Multi-Select Picklst field (they're horrible anyway)
 
Rodolfo POPOCATL PAULRodolfo POPOCATL PAUL
thanks Steve! 
 
Kendra Von AchenKendra Von Achen
You can now do this seamlessly with Process Builder.  For example, I was trying to create a formula on Opportunity to source a field from Accounts.  Because the Account field was multi-select, I couldn't use formula field without going through each picklist value like the examples above.  Instead, I created a Process Builder that starts at the Account, and upon a change to the field I was looking to source, I then update all Opportunities associated to the Account and referenced the Account field as the value for my Opportunity field update.
James OwingsJames Owings
This has been an awesome string. Thanks to everyone for posting... hopefully you can give me some guidance too.

'm trying to make some efficiency improvements and have two objects (accounts, and a custom profile object) that have similar but not identical Multi-Select picklists. On the Custom object I want to find out all the brands a customer sells, and on the account I track brands that are active with my product and associated with the customer. I'm hoping to build a process builder that will note when one of my active brands has been chosen in the Custom Object list, and append it to the Multi-Select Picklist on the Account Object for further/future use.

I know how to set the process builder trigger to activate only when the specified brand(s) is/are chosen = done.

I don't want to do a complete replacement (there are times when the account list should have more than the custom profile list, or vice-versa)
I know how to pull the "PriorValue" for the beginning part of the append process, but my formula:

    Priorvalue([Custom_Profile__c].account__c.brands__c ) &[Custom_Profile__c].Brands_on_site__c 

says it can't be used in this way:

    Error: The formula expression is invalid: Field Custom Profile is a multi-select picklist field. Multi-select picklist     fields are only supported in certain functions.

I'm only using functions that are functional from the researd I can find : https://help.salesforce.com/apex/HTViewHelpDoc?id=tips_for_using_picklist_formula_fields.htm&language=en_US 

I'm guessing part of my trouble is that my lists don't match identically, so I'll need to use a formula like the above
          IF(ISBLANK( Multi_Picklist__c ), NULL,
               SUBSTITUTE( IF(INCLUDES(Multi_Picklist__c, "Multi"), "Multi, ", NULL) +
                    IF(INCLUDES(Multi_Picklist__c, "Picklist"), "Picklist, ", NULL) +
                    IF(INCLUDES(Multi_Picklist__c, "Fields"), "Fields, ", NULL) +
                    IF(INCLUDES(Multi_Picklist__c, "Suck"), "Suck, ", NULL) +
               ".", ", .", NULL))

Also, I've always used the "&" sign to concatenate values, I notice in the above formula it looks like the "+" symbol is used... are these interchangeable, or is this the trick to appending?

In any case, will I have to build a formula to pull the account list, combine it with the custom list, then update the whole field? or can I just append the new information directly?

I'll keep working at it, but any advice would be appreciated.
James OwingsJames Owings
OK... so I've found a "workable" solution... (I'm totally open for suggestions on a more eloquent solution), but in case someone else needs this, here is how I solved this:

  IF(OR(INCLUDES([Custom_Profile__c].account__c.brands__c, "Brand1" ),
            INCLUDES( [Custom_Profile__c].Brands_on_site__c,"Brand1"))
       ,"Brand1; "
       ,NULL) +
...
 IF(OR(INCLUDES([Custom_Profile__c].account__c.brands__c, "Brand15" ),
            INCLUDES( [Custom_Profile__c].Brands_on_site__c,"Brand15"))
       ,"Brand15; "
       ,NULL)

(Effectively I had to compare each item of value agianst each field, and rebuild the list to update instead of append)

My original formula included 36 line items, but SFDC wouldn't allow the 7k+ characters in the process builder (limited to 3900) so I had to pair it down to the most relevant care-abouts to meet the limit. It works for now, but isn't very scalable.

the other drawback, is that if a value on the account object is selected before the Process Builder runs, and not included in my 15 items above... it is lost.

My only thought to overcome this is to build a Process Builder with more than a single trigger that looks at each value and is set to loop multiple times... (that doesn't sound promising), or to build a flow in Flow Designer where I can build the formula in smaller peices to run at a time, and use that loop function build my full list, then update the field.

Hoping someone else has and idea or suggestion?
Bill PowellBill Powell
I worked with Steve Mo's original formula and cleaned up the return values a bit. I needed to implement this for my org and wasn't liking how it called every line break outside the if statement, so brought the line break into the statement to only return if it found that "included" value. Now the values will stack on top of one another. 

Hope this helps! 


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)
Nicolle BennettNicolle Bennett
Hi all - this a great thread, thank you. I've tried several of the options above, but the formula only shows my first picklist value. For example, using Bill's above:

IF(INCLUDES( FGM_Base__Request__r.Objectives__c , "Rewrite the narrative"), "Rewrite the narrative" + BR(), NULL)   &  
IF(INCLUDES( FGM_Base__Request__r.Objectives__c , "Connect women and girls with skills, information, capital, and networks"), "Connect women and girls with skills, information, capital, and networks" + BR(), NULL)  &
IF(INCLUDES( FGM_Base__Request__r.Objectives__c , "Dismantle barriers to technology access and usage"), "Dismantle barriers to technology access and usage" + BR(), NULL)  &
IF(INCLUDES( FGM_Base__Request__r.Objectives__c , "Improve data, evidence, and transparency"), "Improve data, evidence, and transparency" + BR(), NULL)  &
IF(INCLUDES( FGM_Base__Request__r.Objectives__c , "Advance private sector leadership on gender diversity in tech"), "Advance private sector leadership on gender diversity in tech" + BR(), NULL)

Multi field referenced is FGM_Base__Request__r.Objectives__c
Value are pretty lengthy, is that to blame?

Rewrite the narrative
Connect women and girls with skills, information, capital, and networks
Dismantle barriers to technology access and usage
Improve data, evidence, and transparency
Advance private sector leadership on gender diversity in tech

Also, Objectives multi-select picklist is a dependent picklist, on Big_Bet__c if that has any bearing.

Thanks for your help!
 
Nicolle BennettNicolle Bennett
I should add that the example I gave above showed no values, and one of Steve's examples above that just returned "Rewrite the narrative" (even though multiple were selected)
Steve MolisSteve Molis

Nicolle Bennett 

Can you post a screenshot of the record that you are testing this with?
Bill PowellBill Powell
Multi Select Picklists (MSP's) are the devil, but are necessary on occasion. To make a long explanation short, with a standard picklist each value is its own individual value. With a MSP(multi select picklist), the whole list is a value, so to speak. So if you want to tell if that list includes a specific value, the formula is a little different to extract a value. Its a little more difficult to work with. Once you post a screenshot, we can get a better idea of how to help. Though, Batman...errr...Steve Mo is on the scene. So you're definitely in good hands :-)
Nicolle BennettNicolle Bennett
Hi all,

Thank you for your responses! Attached are some screenshots. Here is the scenario - on the Opportunity object, I have a picklist field called Big Bet, which has 3 dependent multi-select picklists: Focus Areas, Pillars, and Objectives.
On a child object of the opportunity, called Grantee Report, I want to display whatever choices someone makes in the above mentioned fields, using formulas.
The Big Bet formula works fine, as it's just a standard picklist. The scenario I've been trying is to replicate the chosen values in Objectives (but I will also have to repeat this process for Pillars and Focus Areas, I was just starting with Objectives). As you can see, the formula I'm using is only displaying one value for Objectives, even though multiple are chosen. Let me know if this is making sense, or if I just confused things more! If you need additional info, just let me know. Thank you so much again for your help.

Opportunity picklist fields
Opportunity pick list fields

Edit view
User-added image

Child object (with formula outputs for two of the fields, Big Bet and Objectves):
User-added image

Formula used for Objectives:
User-added image
Nicolle BennettNicolle Bennett
I got it! This works!

IF(ISBLANK(FGM_Base__Request__r.Objectives__c), NULL, 
SUBSTITUTE( 
IF(INCLUDES(FGM_Base__Request__r.Objectives__c, "Rewrite the narrative"), "Rewrite the narrative; ", NULL) + 
IF(INCLUDES(FGM_Base__Request__r.Objectives__c, "Connect with skills, information, capital, and networks"), "Connect with skills, information, capital, and networks; ", NULL) + 
IF(INCLUDES(FGM_Base__Request__r.Objectives__c, "Dismantle barriers to technology access and usage"), "Dismantle barriers to technology access and usage; ", NULL) + 
IF(INCLUDES(FGM_Base__Request__r.Objectives__c, "Improve data, evidence, and transparency"), "Improve data, evidence, and transparency; ", NULL) + 
IF(INCLUDES(FGM_Base__Request__r.Objectives__c, "Advance private sector leadership on gender diversity in tech"), "Advance private sector leadership on gender diversity in tech; ", NULL) + ".", "; .", NULL))

(displays any values selected for Objectives)

I can't thank you all enough for looking in to this! Hope this can help someone too...
Cassie RatliffCassie Ratliff
I am using this syntax:

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 )

Is it possible to tweak the formula to only include semi-colons if there are multiple values and not after the last value?
Steve MolisSteve Molis
Hi Cassie, I posted a formula to do that in the thread 
IF(ISBLANK( Multi_Picklist__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES(Multi_Picklist__c, "Multi"), "Multi, ", NULL) + 
IF(INCLUDES(Multi_Picklist__c, "Picklist"), "Picklist, ", NULL) + 
IF(INCLUDES(Multi_Picklist__c, "Fields"), "Fields, ", NULL) + 
IF(INCLUDES(Multi_Picklist__c, "Suck"), "Suck, ", NULL) + ".", ", .", NULL))
Tip of the Hat to Deepak Anand for hipping me to the SUBSTITUTE function

 
Cassie RatliffCassie Ratliff
Thanks for quick response, Steve! However, that just adds a period to the end of the list. With my original formula, if no value exists, the field is blank. If there is one value, the field reads "Multi;". If there are mutliple values, the field reads "Multi; Picklist; Fields;". With your formula, it now reads "Multi; Picklist; Fields;." 

I want it to read "Multi". Or "Multi; Picklist; Fields". No semi-colon after the last item.
Steve MolisSteve Molis
Hi Cassie,
I've used that exact Formula and it wors fine.  Can you copy&paste the Formula you're using and a screenshot that shows the Multi-Picklist Field and your Formula field results side-by-side?  
Emily PetersenEmily Petersen
I read through this thread and thought I'd come up with what I needed. I'm adding a multi-picklist field from the Acct to the Task Objects. I chose a Formula/Text field and then used this code...but I get "Syntax Error" with no additional info. Help?

IF(ISBLANK(Account.Other_Platforms_Used__c),NULL,
SUBSTITUTE(
IF(INCLUDES(Account.Other_Platforms_Used__c,”AdWords/Search Ads”),”AdWords/Search Ads,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Facebook/Social Ads”),”Facebook/Social Ads,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Google Display Network”),”Google Display Network,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”DoubleClick Bid Manager”),”DoubleClick Bid Manager,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Simplfi”),”Simplfi,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Centro/SiteScout”),”Centro/SiteScout,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”DataZu”),”DataZu,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”RocketFuel”),”RocketFuel,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”AppNexus”),”AppNexus,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Media Math”),”Media Math,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Turn”),”Turn,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”Other”),”Other,”,NULL)+
IF(INCLUDES(Account.Other_Platforms_Used__c,”The Trade Desk”),”The Trade Desk,”,NULL)+ ".", ", .", NULL))
Steve MolisSteve Molis
Hi Emily,

How are you creating the Formula Field?  Are you typing in the formula manulay?  Or are you using the Insert Field and Insert Function Buttons?  
Can you post a complete screenshot of the Formula Field and Formula in the Formula Editor? 

 
Kel HillKel Hill
I have spent my day trying all the suggestions in this thread, but I get no data back.
I don't get errors, but no data is being found.
Steve MolisSteve Molis
Kel,
Can you post the exact Formula that you are using and also a screenshot of a Report or a List View that shows all of the Fields that are being referenced in the Formula, the values slected in those fields, and your Formula result side-by-side.  Just saying "my Formula ain't working" is kinda like walking into a garage and saying "My car won't start, what's wrong with it?" (without bringing your car) 

Tips & Tricks: Measure twice and cut once with Formula QC List Views and Reports 
https://success.salesforce.com/answers?id=9063000000046BfAAI
 
Kel HillKel Hill
IF(ISBLANK(Account.PMS__c),NULL,
SUBSTITUTE(
IF(INCLUDES(Account.PMS__c,"none(Stand-Alone)"),"none(Stand-Alone)",NULL)+
IF(INCLUDES(Account.PMS__c,"5Star(PMS)"),"5Star(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"AgilysysDatamagine(Storage)"),"AgilysysDatamagine(Storage)",NULL)+
IF(INCLUDES(Account.PMS__c,"AgilysysLMS(PMS)"),"AgilysysLMS(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"AgilysysVisualOne(PMS)"),"AgilysysVisualOne(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Avvio(UKEngineIF)"),"Avvio(UKEngineIF)",NULL)+
IF(INCLUDES(Account.PMS__c,"Brilliant(PMS)"),"Brilliant(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"ClubEssentials(ClubPMS)"),"ClubEssentials(ClubPMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Duetto(RevMgmt)"),"Duetto(RevMgmt)",NULL)+
IF(INCLUDES(Account.PMS__c,"Expedia(OTA)"),"Expedia(OTA)",NULL)+
IF(INCLUDES(Account.PMS__c,"HotelConcepts(PMS)"),"HotelConcepts(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"IGT(GamingPMS)"),"IGT(GamingPMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"inforEpitome(PMS)"),"inforEpitome(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"inforHMS(PMS)formerlyMedallion"),"inforHMS(PMS)formerlyMedallion",NULL)+
IF(INCLUDES(Account.PMS__c,"inforMedallion(PMS)"),"inforMedallion(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Jonas(ClubPMS)"),"Jonas(ClubPMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"NorthStar(ClubPMS)"),"NorthStar(ClubPMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Opera(PMS)"),"Opera(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Passkey(GroupsMgmt)"),"Passkey(GroupsMgmt)",NULL)+
IF(INCLUDES(Account.PMS__c,"RainMaker(RevMgmt)"),"RainMaker(RevMgmt)",NULL)+
IF(INCLUDES(Account.PMS__c,"roomMaster(PMS)"),"roomMaster(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"SiteMinder(ChannelMgmtIF)"),"SiteMinder(ChannelMgmtIF)",NULL)+
IF(INCLUDES(Account.PMS__c,"Springer-Miller(PMS)"),"Springer-Miller(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Triptease(OTApop-up)"),"Triptease(OTApop-up)",NULL)+
IF(INCLUDES(Account.PMS__c,"VisualMatrix(PMS)"),"VisualMatrix(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Zeus(PMS)"),"Zeus(PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"other"),"other",NULL)+
IF(INCLUDES(Account.PMS__c,"unknown"),"unknown",NULL)+".",",.",NULL))


I get only a single period as the result.
Steve MolisSteve Molis
How about a screenshot of a Report that shows a few records with the Account.PMS__c field and your Formula Field side-by-side? 
Kel HillKel Hill
Account.PMSEnvironment Field that the formula is intended to populate
Kel HillKel Hill
Does the original field that contains the multi-picklist that the formula is querying have to be configured as a Global Picklist, or can it be created from scratch?
Steve MolisSteve Molis
No it does not need to be a Global Field

Could you please create a Report that shows the multi-picklist (PMS & Interfaces) field and the formula field (Environment) side by side so that I can see what you're putting into the Formula and what you're getting? 
Steve MolisSteve Molis
Like this
User-added image
Kel HillKel Hill
I will try to do that tomorrow. Just heading out for the day. Thanks for your help, Steve Molis.
Steve MolisSteve Molis
Try a small test Formula like this
IF(ISBLANK(Account.PMS__c),NULL,
SUBSTITUTE(
IF(INCLUDES(Account.PMS__c,"none(Stand-Alone)"),"none(Stand-Alone),",NULL)+
IF(INCLUDES(Account.PMS__c,"5 Star (PMS)"),"5 Star (PMS),",NULL)+
IF(INCLUDES(Account.PMS__c,"AgilysysLMS(PMS)"),"AgilysysLMS(PMS),",NULL)+
IF(INCLUDES(Account.PMS__c,"other"),"other,",NULL)+
IF(INCLUDES(Account.PMS__c,"unknown"),"unknown,",NULL)+".",",.",NULL))
Also double-check the values that are in the Menu Options, the Field, and compare them to the ones in your Formula, they need to be a 100% EXACT match, even blank spaces.

For example in your screenshot it looks like the Mulri-Picklist field has "5 Star (PMS)" but in your Formula you're saying:
IF(INCLUDES(Account.PMS__c,"5Star(PMS)"),"5Star(PMS)",NULL)+

"5 Star (PMS)"  and "5Star(PMS)" are not the same values

 
Kel HillKel Hill
Thanks, Steve Molis
Fixed the missing spaces.

Tried a shorter list in the formula and got same result as am currently getting. - only "unknown" seems to work.

IF(ISBLANK(Account.PMS__c),NULL,
SUBSTITUTE(
IF(INCLUDES(Account.PMS__c,"none (Stand-Alone)"),"none (Stand-Alone)",NULL)+
IF(INCLUDES(Account.PMS__c,"5 Star (PMS)"),"5 Star (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Agilysys Datamagine (Storage)"),"Agilysys Datamagine (Storage)",NULL)+
IF(INCLUDES(Account.PMS__c,"Agilysys LMS (PMS)"),"Agilysys LMS (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Agilysys Visual One (PMS)"),"Agilysys Visual One (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Avvio (UK Engine IF)"),"Avvio (UK Engine IF)",NULL)+
IF(INCLUDES(Account.PMS__c,"Brilliant (PMS)"),"Brilliant (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Club Essentials (Club PMS)"),"Club Essentials (Club PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Duetto (Rev Mgmt)"),"Duetto (Rev Mgmt)",NULL)+
IF(INCLUDES(Account.PMS__c,"Expedia (OTA)"),"Expedia (OTA)",NULL)+
IF(INCLUDES(Account.PMS__c,"Hotel Concepts (PMS)"),"Hotel Concepts (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"IGT (Gaming PMS)"),"IGT (Gaming PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"infor Epitome (PMS)"),"infor Epitome (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"infor HMS (PMS) formerly Medallion"),"infor HMS (PMS) formerly Medallion",NULL)+
IF(INCLUDES(Account.PMS__c,"infor Medallion (PMS)"),"infor Medallion (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Jonas (Club PMS)"),"Jonas (Club PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"NorthStar (Club PMS)"),"NorthStar (Club PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Opera (PMS)"),"Opera (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Passkey (Groups Mgmt)"),"Passkey (Groups Mgmt)",NULL)+
IF(INCLUDES(Account.PMS__c,"RainMaker (Rev Mgmt)"),"RainMaker (Rev Mgmt)",NULL)+
IF(INCLUDES(Account.PMS__c,"roomMaster (PMS)"),"roomMaster (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"SiteMinder (Channel Mgmt IF)"),"SiteMinder (Channel Mgmt IF)",NULL)+
IF(INCLUDES(Account.PMS__c,"Springer-Miller (PMS)"),"Springer-Miller (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Triptease (OTA pop-up)"),"Triptease (OTA pop-up)",NULL)+
IF(INCLUDES(Account.PMS__c,"Visual Matrix (PMS)"),"Visual Matrix (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"Zeus (PMS)"),"Zeus (PMS)",NULL)+
IF(INCLUDES(Account.PMS__c,"other"),"other",NULL)+
IF(INCLUDES(Account.PMS__c,"unknown"),"unknown",NULL)+".",",.",NULL))

User-added image
Steve MolisSteve Molis
What about checking the values that are in the Multi-Picklist Menu, and the values in the Field, and the ones in your Formula?  As I said in my previous post "they need to be a 100% EXACT match, even blank spaces

Looking at your Formula and your screenshot it looks like that field has the value "5 Star (PMS)"  but you Foormula is looking for the value "5Star(PMS)" and those do not match.
Steve MolisSteve Molis
Just an fyi - If you don't get a Syntax Error but you get an incorrect result it usually means that there is either a mismatch between the values in the Field and the values in the Formula, or your Formula is calling in the wrong field.
Steve MolisSteve Molis
Is this being done in a Formula(Text) field?  Or are you doing this in a Field Update Action that is triggered by a Workflow Rule or Process Builder?  

Are you copy & pasting the values from the list of values in the Mulit-Picklist field setup page?  
Is the Multi-Picklist field only edited manually?  
Or has any of the data been inported via an upload or integration with another source? 
Kel HillKel Hill
Steve Molis

I am revising my formula in Notepad and copying and pasting it into the Formula(Text) field.

I corrected the space problem by copying and pasting the picklist values into the formula from each line of the Printable View available from the the Mutli Picklist field view.
Kel HillKel Hill
Steve Molis

Because "unknown" seems to be working, I am beginning to wonder if the others are not working because they contain brackets in the data content of the selections themselves.
Steve MolisSteve Molis
Check to see if there are any "hidden" blank spaces before or after the text in the Picklist Field. 

Again, I would work with a limited number of picklist values to start
Kel HillKel Hill
Steve Molis

There are no hidden spaces. I even tried to add hidden spaces to the start and end of a selection and the system seems to know enough to strip such spaces away on its own, as they disappear when saving and are not to be found when re-editing the field.
Steve MolisSteve Molis
I've got it!  

You need to use the API Name in the Formula
User-added image
Kel HillKel Hill
Steve Molis

Do you mean the first quotes in each line of the formula should be the API Name instead of the Label?
Presumably the second quotes in each line that spec what to display in the field can be different if desired.
Will give it a shot!
Kel HillKel Hill
Steve Molis

That worked!
Have a drink!
Thanks so much.
Steve MolisSteve Molis
No problem Kel, thanks for hanging in there with me.

Cheers,

SteveMo
Madhuri JampaniMadhuri Jampani
Thank you Steve Molis. It worked for me.
Jennifer McInnesJennifer McInnes
Wow this is a long thread. I am trying to do the same thing. I'm trying to update a new field with the value of a picklist "IF" it matches. This is what I have in my formula:

IF(INCLUDES( Partner__c , "Cognizant"), "Cognizant ;",  
IF(INCLUDES( Partner__c , "Accenture"), "Accenture ;",
IF(INCLUDES( Partner__c , "Tata (TCS)"), "TCS ;",
IF(INCLUDES( Partner__c , "", "n/a",
"Other"
)))))

When I check Syntax this is what I get:  Error: Incorrect number of parameters for function 'INCLUDES()'. Expected 2, received 4

If I remove one of the IF statements, then it works fine but as soon as I add that fourth one it errors. Is there a limit that I'm unaware of?

Thanks
 
Steve MolisSteve Molis
Hi Jenn,

A Nested IF won't work for this, you need a Closed IF Array, like this:
 
IF(ISBLANK( Multi_Picklist_1__c ), NULL,
SUBSTITUTE( 
IF(INCLUDES(Partner__c, "Cognizant"), "Cognizant, ", NULL) + 
IF(INCLUDES(Partner__c, "Accenture"), "Accenture, ", NULL) + 
IF(INCLUDES(Partner__c, "Tata (TCS)"), "Tata (TCS), ", NULL) + 
"Other, " + ".", ", .", NULL))

 
Jennifer McInnesJennifer McInnes
Thanks Steve, you're always coming through.
Steve MolisSteve Molis
No problem Jen, you owe me a beer! ;-D
Jennifer McInnesJennifer McInnes
Yay, I'm part of the club. OK, I need to be able to have a blank pick list value = "n/a" and anything other than the 3 value fields Cognizant, Accenture, Tata (TCS) = "Other".  When I add the extra IF, I get an error.

IF(ISBLANK( Partner__c ), NULL,
SUBSTITUTE( 
IF(INCLUDES(Partner__c, "Cognizant"), "Cognizant, ", NULL) + 
IF(INCLUDES(Partner__c, "Accenture"), "Accenture, ", NULL) + 
IF(INCLUDES(Partner__c, "Tata (TCS)"), "Tata (TCS), ", NULL) +
IF(INCLUDES(Partner__c, ""), "n/a, ", NULL) 
"Other, " + ".", ", .", NULL)))
Steve MolisSteve Molis
Here, I tested this on my Dev SFDC org and it works
IF(ISBLANK( Multi_Picklist_1__c ), "N/A", 
SUBSTITUTE( 
IF(INCLUDES(Multi_Picklist_1__c, "Multi"), "Multi, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Picklist"), "Picklist, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Fields"), "Fields, ", NULL) + 
"Other, " + ".", ", .", NULL))
User-added image
 
Jennifer McInnesJennifer McInnes
Hi Steve,

This is interesting: I have a record where the multi picklist value is just "Accenture", but this new field is pulling it as "Accenture, Other". 
Steve MolisSteve Molis
Hi Jen,
Can you create a Report or a List View that displays all of the Fields in your Formula and the Formula results side-by-side (like I did)?  We can't pop the hood and see inside your SFDC org, so it's gonna make troubleshooting the Formula a LOT easier if we can see the Input and the Output side-by-side.  

Tips & Tricks: Measure twice and cut once with Formula QC List Views and Reports
https://success.salesforce.com/answers?id=9063000000046BfAAI
Jennifer McInnesJennifer McInnes
Hi Steve, here you go. You can see that the name in 'partner' field only has one value, but the 'partner name for reporting' is pulling 2 values.

User-added image
Steve MolisSteve Molis
Can you post the exact Formula that you used?
Jennifer McInnesJennifer McInnes
Sure, here you go. Thanks!
IF(ISBLANK( Partner__c ), "n/a", 
SUBSTITUTE( 
IF(INCLUDES(Partner__c, "Cognizant"), "Cognizant, ", NULL) + 
IF(INCLUDES(Partner__c, "Accenture"), "Accenture, ", NULL) + 
IF(INCLUDES(Partner__c, "Tata (TCS)"), "Tata (TCS), ", NULL) + 
"Other, " + ".", ", .", NULL))
Jennifer McInnesJennifer McInnes
Hi Steve, were you able to look at this for me? I appreciate your help.
Mahesh RajaMahesh Raja
Hi, i have a scenario where i have a button where i am doing URL hacking and populating the values from one object to another object on click of  a button.

The problem here is the button is called from two places. There is a field 'Product_Type__c ' which is a single picklist field and populates the value to the field-'product' on the destination. but on second place where we are calling the button the field is multi-select picklist field - 'Product_Categories__c ' and the destination field is same field - 'product'. when i am calling this it won't take the value from both the places.

so i thought of creating a formula field to populate the values and then use this formula field in URL hack of the button. but i am getting an error when trying to do it. the formula i am using:

IF( ISPICKVAL(Type, 'Multi-Product')  , TEXT(Product_Categories__c ) ,  Product_Type__c )

as multiselect can't use text 

 can anyone help me
Steve MolisSteve Molis
Sorry man, I don't do URL Hacks 

Can you post a complete screenshot of the Formula and the Error?
Mahesh RajaMahesh Raja
User-added image
Steve MolisSteve Molis
Yeah that ain't gonna work, you're gonna have to do something like this
 
IF( ISPICKVAL(Type, 'Multi-Product')  , 
IF(ISBLANK( Multi_Picklist_1__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES(Multi_Picklist_1__c, "Multi"), "Multi, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Picklist"), "Picklist, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Fields"), "Fields, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Suck"), "Suck, ", NULL) + ".", ", .", NULL)) ,  
Product_Type__c )

 
Mahesh RajaMahesh Raja
Thanks Steve it is working
 
Cathy SlovekCathy Slovek
Hello, I have been trying to use this post to create a formula field on my Opportunity to include multi select pick values from an Account record. I have tried to match the example formulas in this post. If I only have one ")" after the final NULL in the formula I get a Syntax error that I am missing a ")". When I add a ")" after the final NULL I get the Error:Incorrect number of parameters for function 'IF()'. Expected 3, received 4. This is a picture of my formula. Any guidance would be appreciated! cass
User-added image
Steve MolisSteve Molis
Cathy Slovek 
Can you copy&paste the actual Formula as Text?  (it's hard to debug an image file)
Cathy SlovekCathy Slovek
Sure! Thanks for reviewing.

IF(ISBLANK( Account.SPS_Cross_Sell_Target__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Productivity Products"), "Productivity Products", "", null)+
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Workflow Solutions"), "Workflow Solutions" , "", null)+
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Sensing & IoT"), "Sensing & IoT", "", null)+
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Industrial Safety"), "Industrial Safety", "", null)+ 
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Retail"), "Retail", "", null)+ 
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Intelligrated"), "Intelligrated", "", null)+ ".", ", .", NULL))
Steve MolisSteve Molis
Hi Cathy. try this
IF(ISBLANK( Account.SPS_Cross_Sell_Target__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Productivity Products"), "Productivity Products, ", NULL)+
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Workflow Solutions"), "Workflow Solutions, " , NULL)+
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Sensing & IoT"), "Sensing & IoT, ", NULL)+
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Industrial Safety"), "Industrial Safety, ", NULL)+ 
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Retail"), "Retail, ", NULL)+ 
IF(INCLUDES( Account.SPS_Cross_Sell_Target__c , "Intelligrated"), "Intelligrated, ", NULL)+ ".", ", .", NULL))

 
Cathy SlovekCathy Slovek
Steve this worked! You are a rock star! I need to compare the two to see what I was doing wrong. Thank you so much for your help, especially late on Friday evening! I am going to test now. Made my day! cass
Cathy SlovekCathy Slovek
Steve, my test was 100% success! Thank you again for taking the time to help me today! cass
Steve MolisSteve Molis
No problem Cathy, glad you got it working

SteveMo
Jaime BlalockJaime Blalock
So I need to copy over a value from my account detail page to the opportunity detail page...and the value is from a multi picklist :(   I've attached a snip of my error, any suggestions would be appreciated. Thanks!User-added image
Steve MolisSteve Molis
Hi Jamie,
That formula won't work, you can't use a TEXT Function on a Multi-Picklist Field.  You'll need to use an INCLUDES Function, like in the other Formulas that are posted within this thread 

Like this one
IF(ISBLANK( Account__r.Multi_Picklist_1__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES(Account__r.Multi_Picklist_1__c, "Multi"), "Multi; ", NULL) + 
IF(INCLUDES(Account__r.Multi_Picklist_1__c, "Picklist"), "Picklist; ", NULL) + 
IF(INCLUDES(Account__r.Multi_Picklist_1__c, "Fields"), "Fields; ", NULL) + 
IF(INCLUDES(Account__r.Multi_Picklist_1__c, "Suck"), "Suck; ", NULL) + ".", "; .", NULL))

 
Jaime BlalockJaime Blalock
Oh wow! I have so many options is this pick list.....no way I can do this. Oh well, guess I will think of another solution. Thank you!
 
Steve MolisSteve Molis
Best Option:  Get rid of the Multi-Picklist field
Jaime BlalockJaime Blalock
I really want to switch it to a single picklist field because it does not need to be a multi-list...but if I do I will lose all the eisting data in that field. Yes I know I can pull out the data  and push it back in ....but ain't nobody got time for that. Why is nothing ever EASY??? Thanks again!
 
Jaime BlalockJaime Blalock
Ok so I need this to happen and there is no other way I can think to make it happen...so I have spent quite a bit of time putting this together. Will you please review it?

IF(ISBLANK( Account__r. Backgrounds_Competitors__c), NULL,
SUBSTITUTE(
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Abso - Sterling Infosystems"), " Abso - Sterling Infosystems; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Accurate Background"), " Accurate Background; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " ADP"), " ADP; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " ADP CrimLink"), " ADP CrimLink; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " American Background - Sterling"), " American Background - Sterling; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Applicant Insight"), " Applicant Insight; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Asurint"), " Asurint; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Aurico"), " Aurico; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Background Investigation Bureau"), " Background Investigation Bureau; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " BackTrack"), " BackTrack; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " BIG - Vertical Screen"), " BIG - Vertical Screen; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " CARCO"), " CARCO; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Castlebranch"), " Castlebranch; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Certiphi - Vertical Screen"), " Certiphi - Vertical Screen; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Checkr"), " Checkr; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Clearstar"), " Clearstar; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Corporate Screening Services"), " Corporate Screening Services; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Creative Services Inc"), " Creative Services Inc; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Criminal 411"), " Criminal 411; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " DataFacts"), " DataFacts; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " EBI"), " EBI; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Edge Information Services"), " Edge Information Services; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " EmployeeScreenIQ-Sterling"), " EmployeeScreenIQ-Sterling; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " ESR"), " ESR; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " First Advantage"), " First Advantage; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " FirstPoint"), " FirstPoint; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Global HR Research"), " Global HR Research; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Hireright"), " Hireright; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " HR Plus"), " HR Plus; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " InfoMart"), " InfoMart; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Intellicorp"), " Intellicorp; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Interstate Backgrounds"), " Interstate Backgrounds; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Justifacts"), " Justifacts; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Lexis Nexis / Choice Point"), " Lexis Nexis / Choice Point; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Nat'l Background Data*"), " Nat'l Background Data*; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " No More Forms/Applicant Insight"), " No More Forms/Applicant Insight; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " None"), " None; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " One Source"), " One Source; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Orange Tree"), " Orange Tree; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " PreCheck"), " PreCheck; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " PreciseHire"), " PreciseHire; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Pre-employ.com"), " Pre-employ.com; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Publicdata.com"), " Publicdata.com; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Rapid Court"), " Rapid Court; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Recordscheck.net"), " Recordscheck.net; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " SecurTest"), " SecurTest; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Selection.com"), " Selection.com; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Sterling"), " Sterling; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Talentwise-Sterling"), " Talentwise-Sterling; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Tandem Select - Sterling"), " Tandem Select - Sterling; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Transunion"), " Transunion; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " TruScreen - Vertical Screen"), " TruScreen - Vertical Screen; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Universal"), " Universal; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " USA-Fact"), " USA-Fact; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Verifications Inc- FADV"), " Verifications Inc- FADV; ", NULL) +
IF(INCLUDES(Account__r. Backgrounds_Competitors__c, " Verified Person"), " Verified Person; ", NULL) +
".", "; .", NULL))
 
Steve MolisSteve Molis
Scheisse...  Never mind the 5K Compile Size Limit, that thing is over 5,000 Characters!  (there's a 3,900 Character Limit)
Kelsey LinnellKelsey Linnell
FINALLY got my formula field to at least populate something (oy!), but am still getting a semicolon and period at the end... any advice?

IF(ISBLANK(pse__Opportunity__r.Current_Product__c ), NULL,
SUBSTITUTE(
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "ACA"), "ACA; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c  , "Dayforce"), "Dayforce; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c  , "Enterprise"), "Enterprise; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "eTime"), "eTime; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Globalview"), "Globalview; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c ,"HPL"), "HPL; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "HPW"), "HPW; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "HRB"), "HRB; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "JD Edwards"), "JD Edwards; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Kronos"),"Kronos; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Lawson"), "Lawson; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c ,"pay@work (ca)"), "pay@work (ca); ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Paychex"), "Paychex; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Pay Expert"), "Pay Expert; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c ,"PayForce"), "PayForce; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "PCPW"), "PCPW; ", NULL) +
IF(INCLUDES(pse__Opportunity__r.Current_Product__c , "Peoplesoft"), "Peoplesoft; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "ProBusiness"),"ProBusiness; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "RUN"), "RUN; ", NULL) +
IF(INCLUDES(pse__Opportunity__r.Current_Product__c , "SAP"), "SAP; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Source Series"), "Source Series; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Streamline"), "Streamline; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c ,"TeamPay (ca)"), "TeamPay (ca); ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Tempworks"), "Tempworks; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Ultipro - Enterprise"), "Ultipro - Enterprise; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Ultipro - Unknown"), "Ultipro - Unknown; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "Ultipro - Workplace"), "Ultipro - Workplace; ", NULL) +
IF(INCLUDES(pse__Opportunity__r.Current_Product__c , "Vantage"), "Vantage; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c , "WFN"), "WFN; ", NULL) +
IF( INCLUDES(pse__Opportunity__r.Current_Product__c ,"Other"), "Other; ", NULL) + ".", ", .", NULL))

User-added image
Steve MolisSteve Molis
Hi Kesly it lookis like are you using 
, .
at the end og your Formula

Try changing it to
,.


 
Janine SpiresJanine Spires
Hi all. My multi picklist formulas solution as described here is working perfectly. Thanks for all the contributions to this thread. But now the word has come down from on high (good grief!) to increase the number of entries required in the multi picklist. Same problem now as Jaime Blalock (above) - the fully written list is too large in characters (and probably compile size too). We are not in a position to re-work the design to remove multi picklist values approach (that we inherited!)

Is there a consensus preferred way to tackle this problem? I've seen a bit about triggers and workflow rules above, and process builder, but don't want to start down the wrong rabbit hole! Here's my scenario:

Opportunity object has multi picklist field called "Scope"
Project object has formula field called "Scope" to match picklist choices:

SUBSTITUTE( 
IF(INCLUDES(Opportunity__r.Scope__c, "multi"), "multi; ", NULL) + 
IF(INCLUDES(Opportunity__r.Scope__c, "pick list"), "pick list; ", NULL) + 
IF(INCLUDES(Opportunity__r.Scope__c, "scaling"), "scaling; ", NULL) + 
IF(INCLUDES(Opportunity__r.Scope__c, "OMG FML"), "OMG FML; ", NULL) + 
... many more ...
IF(INCLUDES(Opportunity__r.Scope__c, "final one and most of these are like so incredibly long because the bosses want what they want and this cannot be reduced in length"), "final one and most of these are like so incredibly long because the bosses want what they want and this cannot be reduced in length; ", NULL) + ".", "; .", NULL)

I can't see any way to re-order the logic, or to save characters, especially given the limited amount of functions available to multi picklists, but even if I did as described here:
https://resources.docs.salesforce.com/204/latest/en-us/sfdc/pdf/salesforce_formula_size_tipsheet.pdf
the scaling problem would come back eventually. Any suggestions greatly appreciated.
Steve MolisSteve Molis
Hi Janine Spires, 

What Formula Limit(s) are you up against? 
The 5K Comiple Size Limit? 
Or the 3,900 Character Limit? 

I'm just gonna leave this here...
User-added image
Janine SpiresJanine Spires
Hi Steve, such a quick reply! Thanks. I am running into the 3,900 character limit even with some entries already removed (but in reality we will need all of these and more!) In my attempt to save every possible character, I was getting this:

Error: Invalid Data. The size of your string is 4,240 bytes but may not exceed 4,000 bytes.

The compiled character size was showing as ok in green, 4,122 characters.
Steve MolisSteve Molis
Crap...  those are HARD Limits
Burhaan QuinnBurhaan Quinn
Thanks @Steve Molis. 
As you posted very recently, while on topic I wonder if you could help, Sensei Steve?

I'm trying to segment customers, as an example; personality traits, and the most viable option in terms of estate space on the object is multi-select picklists (MSPs) (to which I have already setup & implemented), and as everyone knows this creates a reporting nightmare.

I tried exploring various solutions like: text parsing the MSP and/or creating separate fields [for each picklist select option] with a binary 'includes' formula, but this is also is a pain to report on (due to sheer amount of columns contained in the report and is thus unable to be displayed in bar graphs).

I've heard through the grapevine that it may be best to created objects for the MSP field and have them listed as a record under the current object (as a related record)... Is this advisable and would this make reporting on the segmentation any easier (as I think checkboxes would be used if this solution was used but would the same problem occur on the reporting side (with columns)?)?  Is there any way around this at all / any other ways?
 
Steve MolisSteve Molis
Hi Burhaan Quinn 

Can you give some more detailed infornmation?  How many Muti-Picklist fields and Multi-Picklist Values are you talking about? 
Can you post a mock-up of what you want this "Dream Report" to look like (if you could build it)?
Fiona HunterFiona Hunter
@steve molis
I think the above would work for what i need but not understanding it fully can you help.

I need to create a chart that counts the actual values of leads received regardless of the current stage they are at 
 
CURRENT STATUSCurrent countActual Count
Leads Received15(15+10+20+25+22+10+5)
Amy Qualified10(10+20+25+22+10+5)
Meeting Booked20(20+25+22+10+5)
Meeting Completed25(25+22+10+5)
LOA IN22(22+10+5)
CETV10(10+5)
WRITTEN55

I initially thought I would be able to create the chart using bucket lists but that only allows value to be in one bucket, then thought a multi picklist but this creates the string rather than a count in each stage.

I note it mentions on a few threads to use a formula but cant grasp how that works if i need to create a chart as thought if i created multiple fields then that will cause issues trying to build a chart as too many options although would consider just a report if thats all that is possible.

Thanks





 
Steve MolisSteve Molis
@Fiona Hunter 

Ugh!  my head hurts just looking at that...  Current Status is a Multi-Select Picklist Field?  Can you post a screnshot of the Report you're currently using?
Fiona HunterFiona Hunter
@Steve molis

Sorry for delay never got a notification havent finalised any report yet as i always get so far then what i planned never worked.

Currently it is a single picklist which I then thought I could bucket but cant have item in more than one bucket so that was thrown out the window.

Ideally i would love a report that shows this but all combined!!  Seems impossible as really it doesnt work as it adds upto more than 100%!



User-added image

I have now added a tick box so if i run a summary report it counts correctly so we can at least show the information as a table instead but again I am struggling as it would appear I need to add a separate formula to each tick box and they all get listed below so doesnt make easy reading if anyway to even fix this.


User-added image

I am sure the above table is an easier thing and i am just not getting my head around.

My boss just keeps telling me it must be easy!!!

Any help greatly appreciated on this one
Steve MolisSteve Molis
Yeah, that's where I was headed...  create a set of Formula(Checkbox) fields 
Fiona HunterFiona Hunter
@steve molis

Am i right in thinking though that this still wont give me a visual pie chart though and only option is a table and can you help me have it looking visually better than above the % all along the bottom as really it would be perfect if it would look like thsi if possible?


User-added image
Steve MolisSteve Molis
So the want an "All in One" Pie Chart AND they want to count and % that each option was selected?
Fiona HunterFiona Hunter
@steve molis

Yes thats what i am ideally looking to achieve!

Thanks
Fiona
Sudheer ArutlaSudheer Arutla
Hi I used this formula to show the picklist values of another object, but the values are not swhoing up, I observed the If condiiton alwasy evaluating to False, anyone faced this problem?
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 )
Sudheer ArutlaSudheer Arutla
Hi I used below formula template to show the picklist values of another object, but the values are not swhoing up, I observed the If condiiton alwasy evaluating to False, anyone faced this problem?
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 )

This is my formula:

if(INCLUDES( KPI__r.Project_Champion__c , "Research and Education") , "Research and Education; ",null) & 
if(INCLUDES( KPI__r.Project_Champion__c , "Operations") , "Operations; ", null) & 
if(INCLUDES( KPI__r.Project_Champion__c , "Stakeholder Relations; ") , "Stakeholder Relations", null) & 
if(INCLUDES( KPI__r.Project_Champion__c , "Corporate") , "Corporate; ",null) & 
if(INCLUDES( KPI__r.Project_Champion__c , "Autism CRC Academy") , "Autism CRC Academy",null)

Is this formula template still working?
Steve MolisSteve Molis
Sudheer,
If you're getting a Bad Result instead of a Syntax Error, can you create a Report or a List View that displays all of the Fields in your Formula and the Formula results side-by-side and post a screenshot?  It's gonna make troubleshooting the Formula a LOT easier if we can see the Input and the Output side-by-side.  

Tips & Tricks: Measure twice and cut once with Formula QC List Views and Reports
https://success.salesforce.com/answers?id=9063000000046BfAAI
Burhaan QuinnBurhaan Quinn
@Steve Molis,

Apologies for the delayed response.

Details:

I'm trying to segment 3 MSPs; Motivations, Personality Traits and Objections - each MSP has at MSP values 7 options and max 15. Ideally, I would like one column for each MSP, so that I could report on the record counts and maybe combine it with other objects/fields for greater insight. Is there a way to do: IF(Motivations__c, CONTAINS("Make More Money", "Fund Personal Venture", "Reinvestment", "Etc."), 1, 2, 3, 4) ... ie., assign numbers to each MSP value and have them displayed in one single column??

Thanks 
Steve MolisSteve Molis
@Burhaan Quinn 

Sorry I'm having a hard time trying to visualize what you're describing.  Could you post screenshots?
Edward SantandreaEdward Santandrea
Had to to this for all 50 states.  Had to rename the field (to make it shorter) and got in 4994 characters!    "Thank Steve" that there are not more states!
Paul BlomerthPaul Blomerth
Hi All,

Wow, this thread is impressive. Quick question on this: I'm trying to move the 'churn reason' from our opportunity to the account when a churn opp is closed. Here's what I put together, but it's not working. Any ideas what I'm doing wrong? Thanks!

IF(INCLUDES( Churn_Reason__c , "Acquisition"), "Acquisition", NULL) + BR() + 
IF(INCLUDES( Churn_Reason__c , "Building In-House"), "Building In-House", NULL) + BR() + 
IF(INCLUDES( Churn_Reason__c , "Champion Left"), "Champion Left", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Community Plan"), "Community Plan", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Company Closed"), "Company Closed", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Missing Feature(s)"), "Missing Feature(s)", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Other"), "Other", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Poor Adoption"), "Poor Adoption", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Price"), "Price", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Regulation/Compliance"), "Regulation/Compliance", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Service Related"), "Service Related", NULL + BR() + 
IF(INCLUDES( Churn_Reason__c , "Went with Competitor"), "Went with Competitor", NULL ))))))))))
Joseph WestlakeJoseph Westlake
Hi All,
I am trying to auto-add values to a multi select picklist on one object when a picklist on an associated object is selected and saved. I am trying to achieve this using Process Builder as I am using PE of Salesforce.

An example scenario is:
Master Object is Account, which is a Company. The Custom Object is for Resellers. There could be multiple resellers for the Company in various countries. And resellers can sell for other companies as well. As we cannot have a Parent Child relationship in this scenario, we created a Lookup Relationship between these.

We have a multi select picklist for Country in Account, and a picklist in the Reseller Object. We want to keep adding the Country name (picklist field on Reseller Object) to the Country name field (multi select picklist) in the Account object whenever a reseller is associated to the Account.

What would be the best approach to achieve this? Any help here is appreciated. Thanks in advance.
Steve MolisSteve Molis
@Paul Blomerth 
What's not working?  What method are you using to copy the values from the Opportunity.Churn_Reason__c field to the field on the Account object?  

Also, what if there are more than 1 Opportunity?  Are you just writing over whatever is in the Account field? 
Steve MolisSteve Molis
@Joseph Westlake 

I would post this as a new question, as it's a bit off topic from the original thread of using a Formula field to copy values selected from the Multi-Picklist field
Paul BlomerthPaul Blomerth
@Steve Molis--it simply wasn't creating the text field when the picklist was populated. I am using a workflow rule to then push that text value from the opp to the account. I found another formula that is working, with the only downside being a ; at the end of each value. If there's a way to remedy this to remove the ; that would be ideal. The formula I went with is below:

IF(INCLUDES( Churn_Reason__c , "Acquisition"), "Acquisition; ","") 

IF(INCLUDES( Churn_Reason__c , "Building In-House"), "Building In-House; ","") 

IF(INCLUDES( Churn_Reason__c , "Champion Left"), "Champion Left; ","") 

IF(INCLUDES( Churn_Reason__c , "Community Plan"), "Community Plan; ","") 

IF(INCLUDES( Churn_Reason__c , "Company Closed"), "Company Closed; ","") 

IF(INCLUDES( Churn_Reason__c , "Missing Feature(s)"), "Missing Feature(s); ","") 

IF(INCLUDES( Churn_Reason__c , "Other"), "Other; ","") 

IF(INCLUDES( Churn_Reason__c , "Poor Adoption"), "Poor Adoption; ","") 

IF(INCLUDES( Churn_Reason__c , "Price"), "Price; ","") 

IF(INCLUDES( Churn_Reason__c , "Regulation/Compliance"), "Regulation/Compliance; ","") 

IF(INCLUDES( Churn_Reason__c , "Service Related"), "Service Related; ","") 

IF(INCLUDES( Churn_Reason__c , "Went with Competitor"), "Went with Competitor; ","")
Steve MolisSteve Molis
Try something like this:
IF(ISBLANK( Multi_Picklist_1__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES(Multi_Picklist_1__c, "Multi"), "Multi, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Picklist"), "Picklist, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Fields"), "Fields, ", NULL) + 
IF(INCLUDES(Multi_Picklist_1__c, "Suck"), "Suck, ", NULL) + ".", ", .", NULL))
that should get rid of the extra ; at the end
 
Paul BlomerthPaul Blomerth
Thanks, Steve. I put this together using the actual field names, and it's telling me I'm missing a ". Any ideas what is wrong?

Here's the whole formula: 

IF(ISBLANK( Churn_Reason__c ), NULL, 
SUBSTITUTE( 
IF(INCLUDES(Churn_Reason__c, "Acquisition"), "Acquisition, ", NULL) + 
IF(INCLUDES(Churn_Reason__c, "Building In-House"), "Building In-House", ", NULL) + 
IF(INCLUDES(Churn_Reason__c, "Champion Left"), "Champion Left, ", NULL) + 
IF(INCLUDES(Churn_Reason__c, "Community Plan"), "Community Plan, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Company Closed"), "Company Closed, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Missing Feature(s)"), "Missing Feature(s), ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Other"), "Other, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Poor Adoption"), "Poor Adoption, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Price"), "Price, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Regulation/Compliance"), "Regulation/Compliance, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Service Related"), "Service Related, ", NULL) +
IF(INCLUDES(Churn_Reason__c, "Went with Competitor"), "Went with Competitor, ", NULL) + ".", ", .", NULL))
Steve MolisSteve Molis
You've got commas inside of your quotes 

IF(INCLUDES(Churn_Reason__c, "Acquisition"), "Acquisition, ", NULL) +
Paul BlomerthPaul Blomerth
Ah, got it. That did it. Thanks, Steve!
Joseph WestlakeJoseph Westlake
Sure @Steve Molis. 
Dinah BagleyDinah Bagley
I am trying to create a formula based on selection of a field type.  For instance, if product type equals One Card then Anticipated Spend field needs to equal Total Spend file Volume *5%.  I keep getting a Syntax error that says I need another ).  When I add it it is still not accepting it.  Can anyone help?

If (ISPICKVAL(Product_Type__c, 'Visa One Card'), ((Anticipated_Spend__c = (Total_Spend_File_Volume__c *0.05)
Steve MolisSteve Molis
Dinah Bagley 

Can you provide some context?  Where are you using that Formula?  Are you trying to created a Validation Rule or a Formula Field?

What result should the Formula return if the Product_Type__c is NOT 'Visa One Card?'

What is the dadatype of each field in your Formula?  
Dinah BagleyDinah Bagley
I am using the field in opportunties.  I am creating a formula field.  I have several product types and I need the system to auto calculate Estimated Spend volume based on product type and a set percentage for each product type.  Does that make sense?  I will need to use the multi picklist within the formula. For instance:

If (ISPICKVAL(Product_Type__c, 'Visa One Card'), ((Anticipated_Spend__c = (Total_Spend_File_Volume__c *0.05)
If (ISPICKVAL(Product_Type__c, 'ACH'), ((Anticipated_Spend__c = (Total_Spend_File_Volume__c *0.20)
Steve MolisSteve Molis
Okay what is the complete list of Product_Type__c values and the desired outcome for each one?
Steve MolisSteve Molis
Dinah,
Are you just trying to do something like this?
Total_Spend_File_Volume__c * 
CASE(Product_Type__c, 
'Visa One Card', 0.05,
'ACH', 0.20,
0 )

 
Dinah BagleyDinah Bagley
See percentages and picklist values below:
Visa One Card - 5% of Total Spend Volume, which needs to populate in the Anticipated Spend field I have set up.
Virtual Card - 20%
CPX Direct - 5%
CPX ACH + - 5%
D/D - 5%
ACH - 25%
Check - 35%
Steve MolisSteve Molis
Here you go
Total_Spend_File_Volume__c * 
CASE(Product_Type__c, 
'Visa One Card', 0.05,
'Virtual Card', 0.20,
'CPX Direct', 0.05,
'CPX ACH', 0.05,
'D/D',0.05,
'ACH', 0.25,
'Check', 0.35,
0 )

 
Dinah BagleyDinah Bagley
Thank you so much!  That was exactly what I needed.  I was obviously going down the wrong path.  :-)
Steve MolisSteve Molis
No problem (you owe me a beer!)  ;-D
Jeff UrbanJeff Urban
Can this be done as an OR.  As in if the multi pick list has values a,b,c,d,e,f....it could show if it is a,b,or c it puts X?
Steve MolisSteve Molis
@Jeff Urban 
Can you provide some context?  What exactly are you trying to do?
Jeff UrbanJeff Urban
I have a multi select pick list of skill sets (about 200), i'm wanting to break into sectors (about 20) for easier lookup. 

So for example, let's assume the skill sets are A,B,C,D,E in a multi-select picklist. And say A,B, and C are for Surgery, and D and E are for Revenue Cycle.  

Here are the scenarios I'm wanting.  If A (And/OR) B (AND/OR) C are Selected it shows Surgery.  If D (And/OR) E are selected, it shows revenue Cycle.  If A,B, or C are Not checked, then nothing shows .  Also If A and D are selected, it would show Surgery AND Revenue Cycle

So only if A,B or C is selected, it shows Surgery
If D or E is selected it shows Revenue Cycle
If (say) A and E are selected, it shows Surgery, Revenue Cycle 

I'm thinking it might be creating 20 sector fields (that aren't visible) and having them fill in for the first part.  So if A, or B, or C is selected, it shows Surgery.  (still might have problem with the multi select) and would be updated when skill sets are updated.

Then combining those fields into the Sector piece.  But need to get around the "null field"

That's probably as clear as mud. 
Jeff UrbanJeff Urban
Here is where I got messed up.  The below only works on regulay pick list.  It's because of the multi select field.

IF( 
CASE(
Consultant_Main_Skill_Picklist__c, 
"Cerner Anesthesia", 1,
"Cerner CareAware", 1,
"Cerner BMDI", 1,
"Cerner SurgiNet", 1,
"Epic Anesthesia", 1,
"Epic OpTime", 1,
"McKesson HSM", 1,
"McKesson Orsos", 1,
"Meditech ORM", 1,
"Picis", 1,
"SIS", 1,
0
) == 1, 
"Surgery", 
NULL 
)
 
Steve MolisSteve Molis
200 Multi-Picklist Values?!?   (I just threw up in my mouth) 
User-added image
Jeff UrbanJeff Urban
HA.  So I'm learning.
Jeff UrbanJeff Urban
It's whjy i'm trying to break it down into sections.  Problem is, we have multiple EMR's (Epic, Cerner, etc.)  and each has multiple items.  But there is cross over.  So someone might have Epic Willow and Cerner PharmNet, etc, so can't really break by EMR. 
Simon LiberSimon Liber
Hi guys, 
thanks for all the answers, but what can i do if i have a very long list (world countries) in the multi picklist field? how can i adjust the answer:
 "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)" 

To my list? 

thanks
Steve MolisSteve Molis
Simon Liber
Can you post the complete list?  

PS.  If you're running up against the Formula Field Compile Size or Character Limits  you don't have too many options.  You'll probably have to ditch the Formula field and use Process Builder or a Workflow Rule instead
Simon LiberSimon Liber

User-added image

Here is just the beginning of the list, as i mentioned - all countries list.  
I guess it will have to be Process builder/workflow.

which is better for this kind of case? and how you suggest to implement it? 

thanks a lot!!

Simon LiberSimon Liber

@steveMolis 

btw, why can it be with some kind of loop code through advanced formula? 
 

Steve MolisSteve Molis
Hi Simon,
You're most definitely gonna hit the Formula Field Compile Size or Character Limits.   I'm not sure what you mean by "loop code", for that sorta thing you're getting into Apex and/or VisualFlow (not Formulas) 

The only thing that I know about Apex, is that I dunno anything about Apex 
Simon LiberSimon Liber
can it be done by Process builder/workflow??
Steve MolisSteve Molis
Possibly.  The Process Builder and Workflow have higher limits for Field Update Actions (I don't know off the top of my head what those limits are).

Essintially you would create a Process or Workflow that is tiggered whenever your Field is edited.  That would initiate an Immediate Action (a Field Update) which then updates a Text field (instead of a Formula field) using the same Formula Logic 
Judy OttoJudy Otto
Based on posts above, this is the formula I created. The field Areas_Served__c is the field on the opportunity and it's a multi-picklist. I want to create a new Formula field that is text and dump whats in the Areas_Served__c field into this new field. The list below is a list of counties. 
Any idea how to fix?

User-added image
Steve MolisSteve Molis
@Judy Otto 

Could you copy&paste your Formula using the code clipboard?  I can't really debug a picture of a Formula
Judy OttoJudy Otto
"IF(INCLUDES( Areas_Served__c , "All SWIF Counties"), "All SWIF Counties", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Other"), "Other", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Big Stone"), "Big Stone", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Chippewa"), "Chippewa", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Cottonwood"), "Cottonwood", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Jackson"), "Jackson", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Kandiyohi"), "Kandiyohi", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Lac qui Parle"), "Lac qui Parle", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Lincoln"), "Lincoln", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Lyon"), "Lyon", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "McLeod"), "McLeod", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Meeker"), "Meeker", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Murray"), "Murray", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Nobles"), "Nobles", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Pipestone"), "Pipestone", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Redwood"), "Redwood", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Renville"), "Renville", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Rock"), "Rock", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Swift"), "Swift", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Yellow Medicine"), "Yellow Medicine", NULL)"
Mark ThomasMark Thomas
This is a great thread. Was able to get the fix I needed right near the top. Thanks Steve Molis!!!
Steve MolisSteve Molis
Hi Judy 

I just noticed that you have "quotation marks" around your Formula
 
IF(INCLUDES( Areas_Served__c , "All SWIF Counties"), "All SWIF Counties", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Other"), "Other", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Big Stone"), "Big Stone", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Chippewa"), "Chippewa", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Cottonwood"), "Cottonwood", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Jackson"), "Jackson", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Kandiyohi"), "Kandiyohi", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Lac qui Parle"), "Lac qui Parle", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Lincoln"), "Lincoln", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Lyon"), "Lyon", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "McLeod"), "McLeod", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Meeker"), "Meeker", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Murray"), "Murray", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Nobles"), "Nobles", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Pipestone"), "Pipestone", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Redwood"), "Redwood", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Renville"), "Renville", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Rock"), "Rock", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Swift"), "Swift", NULL)  + BR() + 
IF(INCLUDES( Areas_Served__c , "Yellow Medicine"), "Yellow Medicine", NULL)

 
Steve MolisSteve Molis
You also might wanna move your BR 's inside your IF's 
IF(INCLUDES( Areas_Served__c , "All SWIF Counties"), "All SWIF Counties" + BR() , NULL) + 
IF(INCLUDES( Areas_Served__c , "Other"), "Other"+ BR(), NULL)  + 
IF(INCLUDES( Areas_Served__c , "Big Stone"), "Big Stone"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Chippewa"), "Chippewa"+ BR(), NULL)  + 
IF(INCLUDES( Areas_Served__c , "Cottonwood"), "Cottonwood"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Jackson"), "Jackson"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Kandiyohi"), "Kandiyohi"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Lac qui Parle"), "Lac qui Parle"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Lincoln"), "Lincoln"+ BR(), NULL)  + 
IF(INCLUDES( Areas_Served__c , "Lyon"), "Lyon"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "McLeod"), "McLeod"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Meeker"), "Meeker"+ BR(), NULL)  + 
IF(INCLUDES( Areas_Served__c , "Murray"), "Murray"+ BR(), NULL)  + 
IF(INCLUDES( Areas_Served__c , "Nobles"), "Nobles"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Pipestone"), "Pipestone"+ BR() NULL)   + 
IF(INCLUDES( Areas_Served__c , "Redwood"), "Redwood"+ BR(), NULL)   + 
IF(INCLUDES( Areas_Served__c , "Renville"), "Renville"+ BR() NULL)   + 
IF(INCLUDES( Areas_Served__c , "Rock"), "Rock"+ BR() NULL)   + 
IF(INCLUDES( Areas_Served__c , "Swift"), "Swift"+ BR(), NULL)  + 
IF(INCLUDES( Areas_Served__c , "Yellow Medicine"), "Yellow Medicine", NULL)

 
Steve MolisSteve Molis
Your WFR Trigger would just be something like
 
AND(
OR(
ISNEW(),
ISCHANGED(Volunteer_Interest__c)
)
NOT(ISBLANK(Volunteer_Interest__c))
)

 
Rosa MossRosa Moss
Hi All, 
I'm experiencing a similar issue like the ones posted above. I'm trying to filter out picklist values by creating a formula field (Status) under the child object that I am using with Declaritive Lookup Rollup Summaries. Out of the eight values I do not want to count the "Closed - Resolved" value. Is there a way to have all seven values counted and then exclude the "Closed - Resolved"? 

While creating the formula field this is the error that is coming up:
"Error: Field nPMAPP__Issue_Status__c is a picklist field. Picklist fields are only supported in certain functions." 

Please help! 
Steve MolisSteve Molis
Rosa, can you post the Formula that you used?  If the field is a Picklist (not a Multi-select Picklist) you can't use an INCLUDES function
Ethan EisenhardEthan Eisenhard
Hello, 

I am trying to grab the values from the multi select through the URL Request Paramter function from my Cloud Page and Insert into Sales Force Object using AMPScipt.

I have successfully set the variable to SET @amenities = RequestParameter("HSC_Design_Amenities__c") and it puts all the values selected into the correct DE using the 

UpsertDE('Design Studio Event RSVP',1,'SubscriberKey',@Subkey, 'Email',@email,'FirstName', @firstname,'LastName', @lastname,'Phone',@phone,'Number of Guests', @guests, 'HSC_Design_Amenities__c', @amenities, 'HSC_Desired_Price_Range__c', @pricerange, 'DateAdded',NOW())

Now when I try to do the same for the UpdateSingleSalesforceObject in the same fashion I get a Server 500 error.

Set @lead = UpdateSingleSalesforceObject('Lead', @Subkey,'Email',@email,'FirstName', @firstname,'Phone',@phone, 'HSC_Desired_Price_Range__c', @pricerange, 'HSC_Design_Amenities__c ',@amenities)

Now I tried to use the formula above, 

var @amenities =
IF(INCLUDES( HSC_Design_Amenities__c , "Dual (2) Staircases"), "Dual (2) Staircases" + BR() , NULL) +
IF(INCLUDES( HSC_Design_Amenities__c , "Dual Master Suites"), "Dual Master Suites", + BR(), NULL) +
IF(INCLUDES( HSC_Design_Amenities__c , "Fireplace"), "Fireplace" + BR() , NULL) +
IF(INCLUDES( HSC_Design_Amenities__c , "Formal Dining"), "Formal Dining" + BR() , NULL) +
IF(INCLUDES( HSC_Design_Amenities__c , "Formal Living"), "Formal Living" + BR() , NULL) +
IF(INCLUDES( HSC_Design_Amenities__c , "Game Room"), "Game Room" + BR() , NULL) +
IF(INCLUDES( HSC_Design_Amenities__c , "Working/Dirty Kitchen"), "Working/Dirty Kitchen", NULL)

And that does not work.

I am confused why SF wont accept the @amenties var into the CRM, while Marekting Cloud has no problem. 

Is there a solution or syntax that I am missing that would help with this problem? 

Thanks! 

EE

 
Steve MolisSteve Molis
@Ethan Eisenhard

Where are you trying to use that Formula?  
This original thread (started over 7 years ago) was in response to a question about creating a Formula Field on an opbject that displays the selected values from a Multi-Select picklist field on another object.   

If you're trying to do something else, you might be better off posting a new question, and provide detailed information about how and where your Formula is being used.
Ethan EisenhardEthan Eisenhard
@Steve Moilis

Thanks for the respoonse,

I am trying to post the formula in a Cloud Pages using AmpScipt. 

I have made a new question now as I think this thread may not be about AmpScript.

Thanks
Steve MolisSteve Molis
yes, this thread is about as far from AmpScript and Cloudpages as you can get