Alternatives to a Multi-Select Picklist

Multi-select picklists can be useful for user interface (UI) and data standardization. However, there are some gotchas to using multi-select picklists when you want to work with that data later on.

In this blog post, we’ll look at a real-world scenario of how one Salesforce Admin implemented a UI that enables them to collect multiple values and easily use that data in reports and automation, such as flows.

I sat down (virtually) with Andrew Russo, a seasoned Salesforce Architect at BACA Systems, to talk about multi-select picklists and what he did to implement a UI that meets his business users’ needs and improves the ability to use that data later. 

Multi-select picklist limitations

First, a little about multi-select picklists. They’re useful because they give you a way to let users select multiple items from a list.

However, multi-select picklists have implications when working with that picklist data in other areas of Salesforce. Let’s look at some examples of a Distributor object with a multi-select picklist field called Regions.

• Data—Data is stored in the object as a semicolon-separated list of values. For example, if you have a Regions multi-select picklist, the values appear like this in the UI:

Midwest; Southwest;Northwest;West

The way the selected values are stored can pose challenges when you want to update data via the API or import data. For example, let’s say there are some distributors that now all handle the Northeast region, and you want to update those via data import. If the data you’re importing has just the Northeast region, then all those records will be updated to just that region. Instead, your import data needs to include the regions that the distributor supports in addition to the new region. 

• Automation and Integration—If you want to use multi-select picklist values in an Apex trigger, you need to write code to parse the semicolon-delimited string. When it comes to flows, you can’t update a multi-select picklist using the field update element. However, you can use a multi-select picklist as part of a flow, but similar to Apex triggers, you must parse the values from the semicolon-delimited string of values.
• Formulas—Only certain formula fields support multi-select picklists, but there are workarounds. For example, if you want to copy a multi-select picklist field value from one record to another, you need to use the INCLUDES function as detailed in this Trailblazer Community post. For more information, see Tips for Working with Picklist and Multi-Select Picklist Formula Fields.
• Reports—Reports are another area to consider if you want to include multi-select picklist field data in your report or if you want to group or sort by that data. When you include a multi-select picklist field in a report, the data appears as a semicolon-delimited string like it does in the UI. Using picklist values to group data in a report also has some limitations. For example, let’s say you want to group by region and list all the distributors in each region. If you group on the Region field, the groups would look something like this:

Midwest;Southwest;Northwest;West Northeast;Mid-Atlantic West

A real-world example

Back to my chat with Andrew Russo. BACA Systems is a company that builds robotic machinery for the countertop industry. When he started working at BACA, it had an existing Salesforce implementation that used multi-select picklists.

For each customer, BACA’s requirement was to capture all of the equipment that customer has—whether it’s BACA equipment or from a competitor. The org he inherited had two multi-select picklists: one to capture a customer’s current equipment and one to capture the current equipment manufacturer.

As Andrew told me, the biggest issue was that you couldn’t match the equipment to the manufacturer because the picklists weren’t related in any way. For example, BACA might sell a bridge saw and multiple competitors might sell the same thing. The multi-select picklists are unrelated, so you can’t match up the specific equipment with the manufacturer. This caused problems when he wanted to create reports later on.

To capture accurate data and make it easy to use later, he implemented a custom object called Customer Equipment. The Customer Equipment object is related to the Customer object and has three dependent picklists. 

• Machine Type
• Current Equipment Manufacturer
• Machine Model

When you select the machine type, it narrows down the manufacturers. When you choose a manufacturer, it further narrows down the models. Each piece of customer equipment is a record in the Customer Equipment object with a foreign key of CustomerId.

Capturing the data this way means that it’s easy to add new machine types, manufacturers, and models. But Andrew says the biggest benefit comes later when you want to start reporting on the data. You can generate a report of equipment and customers that have that equipment.

He further automates data capture by implementing a flow. When one of their sales people sells equipment, it triggers a flow that adds a record to the Customer Equipment object. Equipment they sell to a customer is automatically added to this object which removes the manual step of the sales person manually adding that data.

Andrew noted that if you already have a multi-select picklist and implement an alternative solution, you need to think about how to handle the transfer of data for existing records. In his case, because the multi-select picklists weren’t related, moving the existing data was a challenge. If you have a simple use case with clean data, you can use data export and import to move the multi-select picklist data to the related custom object.

Scope it down

Andrew’s first piece of advice to admins is that when users come to you with a business requirement for a multi-select picklist, dig into what they need. He cites the example of a requirement he received for a multi-select picklist that contains the names of companies that clients are referred to. It has about 25 values, and a new value is added about once a month. Their goal was to report on these values, so a multi-select picklist wasn’t the best option.

When you drill down and ask questions, you might find that two picklists fulfill the requirement. In his case, a Primary Referral picklist field and a Secondary Referral field  matched the need. Andrew’s pro tip: Use a global value set to standardize the two picklists. 

If you go with two picklists, Andrew says you can also keep the UI uncluttered by using a dynamic form. With a dynamic form, you can display the primary picklist, and if the user selects a value, then display the secondary picklist.

He mentioned that if there’s an edge case where a third referral needs to be tracked, that can be recorded somewhere else in the customer record. “As Salesforce Admins and org owners, we don’t want to build for the 1%… Build to meet 85% of the needs.”

Consider other options

Another alternative solution that Andrew implemented in a recent scenario is a junction object. His users had a requirement to capture all the professional associations for each contact. Those associations were of different types: fellowships and industry groups the contact belongs to in addition to schools they attended. 

One of his considerations was that a multi-select picklist has a max of 500 values, which they would likely exceed. So, he recommended:

• A Professional Group object that contains the association type and the name of the association
• A Group Association junction object that has a required lookup to the Contact objects and a required lookup to the Professional Group object

A junction object has a master-detail relationship to two objects; in this case, Customer and Professional Group. The advantage to a junction object is that it gives you more flexibility in reports. You can report on which contacts are related to a professional group. Alternatively, for each contact, you can see which associations they have.

If you have multi-select picklists in your org or your users request them, keep in mind how you want to use that data. As Andrew showed us, you have options: Picklists, dependent picklists in a separate object, or a junction object can give you the UI you need and set you up to use that data later.

Resources

• Salesforce Help: Tips for Working with Picklist and Multi-Select Picklist Formula Fields