A big pitfall in Salesforce field type: Multi-select Picklist

Multi-select picklist is another thing I would avoid besides Person Account (see my previous post on person account). This is also something I couldn’t understand why Salesforce makes this feature, but make it so un-user-friendly.

• It can only take 150 values and each of the value cannot take more than 40 characters.
• It supports only very limited functions in formulas and workflows. VERY LIMITED (and they are useless ones too)! Don’t even try to pull the value of the multi-select picklist onto a formula or a text box via workflow, it WON’T WORK.
• Reporting is a disaster. You cannot report on the picklist values separately. You cannot sort the multi-select picklist by grouping by each picklist value. That is because Salesforce treat values in multi-select picklists as a combined text value (e.g. Salesforce treats A & B & C as a text value “A; B; C” not A, and then B, and then C)

• Although you can use “contain” in the filters to refer to the records which contain a certain value in the multi-select picklist, if you have very similar values in the picklist (e.g. Microsoft, Microsoft 2010), that would be an issue if you only want to see records in “Microsoft”.
• If you need it as a merge field on a document it is even more of a disaster. I tried putting it on a Conga document template and I have to spend a day figuring out how to replace the “;” in the text thread by “&”. I nearly cried when I was doing it.
• I am not a developer, but I imagine it would be a disaster to coding and triggers too.

I will only use this for my client when the data stored in multi-select picklists is very unimportant to the client’s business, so they don’t need to search or report on, and they don’t need to touch it at all. However I doubt why you would want extremely unimportant data on a system, that’s why I rarely use this.

One alternative is to use list of checkboxes. This makes reporting, formulas and workflows easier. Personally I think data would looks clearer and tidier on the record too. However it only works when your value list is short and sort of static. If you have a long list of value or if your values change frequently, you will end up having thousands of checkboxes on the detail page. For example, you can use this to store contact preference of a Contact (phone, email, fax) and I don’t think you will have 1000 contact methods in the coming 10 years.

The solution that I use most is to create a custom object and create records for all the values that you are supposed to use in a multi-select picklist. Then build a junction object to link the custom object with the picklist records to the object that you want to link to. You can also link this custom object to other objects by building more junction objects so you can make more use of the picklist records in the custom object. I had a client who wants to be able to select multiple countries for an Account, and this solution works really well (not perfect, but at least better than multi-select picklists).

This works particularly well when you have loads of values to select from and this gives you the flexibility to add or delete values comparing to the checkbox option.  You can also store some information related to the picklist values. Using the above example, they are able to store tax rates and tax system against each of the country record. This option makes reporting easier than having multi-select picklists, but the report still doesn’t look perfect. You won’t be able to create a report with a line for your Account record, and then all the countries attached to the Account record on the same line. However I think this can be solved by grouping the fields. Also, this can be confusing to the users to add countries from a related list, therefore you will need to emphasis on the user training.