Multiselect Picklists with many values

Question

Note: Though this is still of use in extreme cases, the limit on multi-select picklist values has been raised to 500 values in Winter '16.

Also, the Global Picklists feature, which is currently (Summer '16) in Beta, also helps with the problem as stated (though it also has the 500-value limit).

I have several fields in my org that require picklists with many values. Examples of values include countries, langauges, NAICS codes and the like.

Multiselect picklists are limited to 150 choices, but the universe of these lists is significantly higher (over 200 countries, many many languages and NAICS codes).

I want picklists because they establish the standardized values which makes searching and aggregate reporting easier. I specifically want multiselect picklists, because accounts can use many languages or have operations in many countries.

One common feature of all of these fields (aside from the massive number of options) is that the choices are universal. That is, whenever we refer to countries, we can have the same countries. Whenever we refer to languages, we can use the same set of languages.

Disclaimer: For the moment, please disregard the fact that States + Countries is a feature we can use. Although that gives us a list of countries, it does not let us easily use those values in custom fields. Also, I want a solution that will work for languages and other non-country fields, too.

My ideal answer:

1. Uses standard Salesforce visual idioms.
2. Doesn't require anyone to learn anything outside of the ordinary for this field.
3. Allows power admins as much "normal" Salesforce power as possible (permissions, layouts, option maintenance, workflow rules, etc.).
4. Allows for reporting similar to reporting on traditional multiselects.
5. Is easy to maintain.

What is the best way to solve this problem?

Answer 1

Currently there will be no solution without tradeoffs. In most cases I tend to pick approaches which provides good UX and best convenience for the users.

To get something user friendly multiselect picklists doesn't come close. They give me a feeling of the year 2003, more than a few choices end up in scrolling tapestries and selecting+deselecting feels like crap. Fiddeling through 200+ fields will be a task for which the users are not likely to fall in love with you.

From the perspective of the datamodel standard (multiselect) picklists lead often to not normalized structures. Take the countries or languages for instance: it's likely you'll need them on multiple entities and you end up declaring them redundantly again and again with the challenge keeping them in sync. Dataquality r.i.p.

Actually fromthe UI perspective one of the better examples for a nice multiple picklist is the selection of tags here at SFSE:

• supports typing and auto complete
• perfect usage of screen real estate
• comfortable handling
• quite common and intuitive

To get this on a SF layout you need some kind of javascript. There are many to choose from. If you like jquery have a look on this

• http://www.jqueryscript.net/demo/jQuery-jQuery-UI-Plugin-For-Simle-Tokenized-Autocomplete-Autocomplete-Multiselect/
• http://harvesthq.github.io/chosen/ (different approach which checkboxes)

To keep the datamodel clean and normalized I strongly agree with @DanielHoechst to use custom objects. Expose an editable list page e.g. for language-choices only for admins. Reuse them everywhere. Add and maintain languages on a single place. Alternatively you can use Custom Settings instead. The overall implementation pattern will be roughly the same.

To fetch the choices you could use JS remoting

https://www.salesforce.com/us/developer/docs/pages/Content/pages_js_remoting_example.htm

To store the selected values on the object I would use a text(255) if possible (easier reporting but need for overrun protection plus limits) or you go for a longtext(128000) if necessary. Since the JavaScript is doing the aggregation, there is no need for a trigger.

Now how to get this most conveniently on the layout? And how on edit? This in the end is at the moment the hardest part. To get the custom JS integrated the solution to embed a VF page rules out: a separate save button is clearly not nice.

If you want to be a good citizen you end up in overriding the entire view and edit with vf-pages. apex:detail helps a bit to reuse layouts. But even that's quite radical, it's still best practice. From the admin perspective unfortunately not perfect.

If you're not afraid to get your hands dirty have a look at this

End of javascript sidebar workarounds? - eventually this would provide a solution to wire it nicely into the standard view and edit without VF override - but it likely won't last forever.

For the future all my hope is on Aura Components to get it done right.

Finally reporting is still an issue. But also is with standard multiple picklists. So having a text field with delimiters is not too bad either.

Answer 2

Multi-picklists are notoriously difficult to work with and I avoid them at all costs. There is no way around the 150 item limit. Summary reporting is completely impossible with multi-select as it summarizes for each possible combination.

I would suggest using a child object that stores what countries/languages the account operates in. You most likely have other properties that you want to track such as date when started, etc. This approach gives you much more flexibility.

Answer 3

Since we already have some approaches, I shall try for a new one and bit more simplified.

1. Create 2 Multiselect picklist fields.

   
   Field1 with values : A to M
   Field2 with values : N to Z

2. Let the user select values for both or any one of field.

3. Write an apex trigger to concatenate the values selected in these 2 fields and store it in a hidden field(from layout) that can be used for reporting.

4. In Reports, you can use this hidden field to group your data and generate dashboards.

Cheers,

Answer 4

The easiest route to go is make your case to support to have this limit raised.

I don't know what your absolute max number of values needed will be, but there are many limits that can be modified if there is a reasonable implementation case. It is always worth making the request.

Answer 5

One Solution

For countries, I did the following solution that I don't deem near perfect:

1. Create a Custom Setting with a list of all countries.
2. Create the relevant custom field, and prepopulate it with the most common 150 answers. That allows for easier reporting and admin uses.
3. Using the Multiselect Picklist component, make a VF page that is simply a form wrapping that component and save/cancel buttons. (See screenshot below)
4. Only display that form if the field is updateable. Otherwise, if it's accessible but not updateable, just display an outputField for that field.
5. Place the VF page into the standard view layout. This allows admins to edit everything else on the layout, and placement of that field itself.

Problems

1. Admins will likely drop the wrong field onto a layout.
2. Because the page is VF, it will not show on the Edit layout, which means that editing must be done, weirdly, on the View page.
3. Someone will forget to press Save. This is especially problematic if the user is making a number of inline changes to the record.
4. Error messages will be disregarded or missed.
5. If the user only has view permissions, the height of the VF iframe is ludicrously high.
6. Translations don't work well, as far as I understand.

Answer 6

@Keith C and @Charles Koppelman, there is a new app called Universal Picklists that might help solve some problems. You can manage picklists across multiple objects from one location. Hope this helps.

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

Answer 7

if i understand you correctly with the country list you can try the following:

1. Create a multi-picklist field that will split the countries into regions. The more regions the better because you will have less values in the other field and you can select more values in both cases. Example: American countries, Asian, European, African, South American etc...
2. Create another field that can be a multi-picklist and add the countries i suggest that you limit your list per the first field up to 30 per previous value. This way you can select up to 5 regions from previous field. It will be impossible for you do select more because of the limit of 150.
3. Make a field dependency with both fields and connect them.

Hope I've answered what you've been looking for. Cheers, D.