By Paige Van Riper

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.

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.

Paige Van Riper

Paige Van Riper

11 responses to “The Evils of Multi-Select Picklists in Salesforce

  1. Right on target Tal. Great for controlling entry, but awful for report and painful for views. And can’t use them as controlling fields for dependent picklists. After all these years SF has not seen fit to rearchitect this field type to make it truly useful. Since I don’t see a great clamor for this change perhaps we’ve become numb to the issues.

  2. Paige, you are right on the money. I have had to explain all of this to salesforce customers time and time again. Maybe next time instead of wasting my breath I will point them to this post!

    If you are at all interested we created an app that helps get around some of the report issues and allows you to get frequency counts on individual picklist values. Check it out here:

    https://appexchange.salesforce.com/listingDetail?listingId=a0N3000000B53brEAB

    Its completely free and 100% salesforce native.

  3. I really really wish I had read this a few months ago!
    ” In my rookie Salesforce administrator days, I thought MSPs were awesome. Then my nightmares began”- this is me right now!

    Thanks for the tips

    V

  4. Thanks! You clarified some concerns I had about MSP’s. Clearly they break Cobb’s rules as well 🙂

  5. Hmmm. This is a tough nut to crack. I’m working on an organization’s web-to-lead form and they need the ability to select all kinds of options like Arts, Science, etc.

    I’m sure that as soon as I create this mass of 15 checkboxes, that another 5 new ones will emerge…Any advice?

    1. Jim, there are some situations where multi-select picklists are the best option available. I think my question about the web to lead form is are they going to want to report on how many people have science and how many have art for example? If they don’t need to report and analyze that an MSP might be your best option. If they do want to report on it then I’d stick with checkboxes – messy but does the job.

Leave a Reply

Your email address will not be published. Required fields are marked *