None of us like to limit our choices if we don’t have to, so on its surface Salesforce’s multi-select picklist (MSP) seems like a great field type. Users can select multiple options while you can still enjoy the standardization that picklists bring, right? In my rookie Salesforce administrator days, I thought MSPs were awesome. Then my nightmares began.
First of all, multi-select picklists create reporting headaches. The first pitfall is if you want to pull a report or filter using a multi-select picklist value you need to know the difference between using the operator “equals” which will pull up records with only that single MSP value or using “contains” or “includes” as the operator which will pull all records which contain the value regardless if there are additional values also included in the field.
Another reporting challenge is by their very nature MSPs don’t lend themselves to grouping your data by values. If you’ve selected A, B and C values in one record, B and D in another, and C in a third you’d get 3 different groupings; one for ABC, one for BD and the other for C. It is difficult to tell how many records hae B selected for example. This also means you can’t use most kinds of dashboards graphs to represent the data. It is a complicated mess to get real meaning out of the data.
Want to be able to use that MSP field in a formula field? Forget it. You’re very limited in what kinds of formulas you can use MSPs in. Actually the brilliant Steve Molis outlines how you can do it but it sure isn’t pretty or fun.
Featured Content
When it comes to migrating your organization from one Salesforce instance to another, there are 5 key points of preparation to keep in mind before
In an effort to keep up with the ever-changing landscape of technology, nearly every nonprofit organization and higher education institution has undertaken or will need
You also cannot use MSPs as controlling fields for dependent picklist fields. They can only be used as the dependent field.
Were you thinking of updating a value in your MSP using a workflow field update? Can’t be done.
Multi-select picklists also pose challenges when merging records in Salesforce. If you merge records with MSP fields using the standard Salesforce merge tool, the MSP values in the master record are the only ones retained unless you’re using a specialized data merge tool such as DemandTools where you can specify that the values from the non-master records should be merged with those from the master.
Importing data to update records with MSPs also is tricky. If you update records with MSPs with a standard data tool it will simply overwrite all existing values in the MSP with the new value(s) being brought in. With a tool like DemandTools you can specify you’d like to merge the values being imported with those already existing in the system.
Do you want to merge your data into a document? You’re going to have to do a lot of reformatting to get it to look right because the values will come out separated by a “;” which isn’t grammatically correct.
You’re probably asking at this point, “What’s the alternative?? Checkbox fields can be a good alternative if you don’t have more than a handful of choices. Checkbox fields unfortunately have drawbacks as well. If you have a lot of choices your users will be scrolling and scrolling to get through them. Also checkboxes have limitations for reporting since they don’t allow you to group all choices into one category to report on..
Another possible alternative is to create a custom object and create records for each of the MSP values. Then you would create a junction object between the custom object and the object you need to use the MSP values on. So if, for example you want to be able to capture what a constituent is interested in you’d create records for all of the potential interests in the custom object such as arts, education, environment, etc. and on John Smith’s record in the Interests related list you’d add education and arts. This isn’t a perfect solution either and has its limitations for reporting.But particularly if you have a long and/or changing list of options, this may be the best way to go.
Admittedly I may have been a bit overly dramatic with the Darth Vader image and evil tag but it is important to know the shortcomings of MSPs before you create them all over your Salesforce instance and then have to deal with the fallout when it comes time to report on the values. I do use MSPs at times for clients, either because they’re the best option available or because the client insists on them but I make sure that my clients are aware of how to set up filters properly and their limitations in reporting and other functions in Salesforce.
