Beware the pitfalls of multi-select picklists (MSP) when designing or optimizing your salesforce.com database. The temptation is great to use MSPs, in so many places, I recommend fighting that temptation as much as is reasonable. Product Interest descriptions and products purchased by a client account are both fairly common uses for MSPs, in some cases these could be considered acceptable uses for this field type, however the caveats are great; the system only allows using MSPs in some formula operators (INCLUDES, ISBLANK, ISNULL, [& ISCHANGED, PRIORVALUE in non formula field formulas) and only some filter criterion work. This strictly limits our ability to leverage the data in these fields for reporting, data validation and work flow rules. Let’s discuss these issues in detail.
The limited capacity for these data types makes the data in these fields much less usable. MSPs can be used in field dependency work, as a dependent field, however NOT as a controlling field.
With the dependent picklist feature we are also limited to using MSPs only as the dependent field, not as the controlling field. This behavior restricts us from being able to make a picklist field with additional values based on the MSP. An example of how this would be useful in the software vertical is defining version(s) of software purchased, based on the software title purchased. There are work arounds for this, they are generally ugly and in many cases the Assets feature should be considered the best practice for this type of data collection.
In reporting there are several challenges with MSPs. One of the biggest issues being that summary and matrix reports are fairly unusable in their current state with MSPs. Say you want to use a MSP to summarize your reportâ€™s result set. Well the values that are put into the MSP on record save, look like “Value 1, Value 2″ therefore on summary one would see only identically detailed value sets in the summary. It would be very useful if we could see each of these values separately in the summaries; so each value would have its own summary line. With the current behavior we may only summarize identically matching value sets; this could be problematic if marketing wants to know which clients have each of the company’s products. Today, this would take a different report for each product, one would use the INCLUDES filter operator with a single value to find the records with that value defined.
When using an MSP field in a criterion in reporting, assignment rules or views; we are also limited. First of all we can only use the Equals, Not Equals, Includes or Excludes. Equals and not equals require that we have a specific string to match, say “Value 1″ or “Value 1, Value 2″ would have to be matched exactly or else the record would not be included in the result set. Includes and excludes are more lenient, so saying “Value 1, Value 2″ would mean either of the values or both of the values could be matched. These are useful when creating reports, views and work flow rules. Secondly, a drawback here is that the system does not allow us to pick the MSP values from a lookup, like we can on a standard picklists, we are forced to manually type the values, if we typoed, we do not know until an attempted save, then we get an error message.
So, you say WHAT AM I TO DO?
Sadly there is not a pat answer for this. If you have only a few values in the MSP, it could make sense to use Checkboxes. Please use Checkboxes sparingly as there are drawbacks here as well. Generally speaking I recommend using a custom object with a Master-Detail relationship to capture this data. Again, drawbacks exist here, as reporting can be challenging.
The best course of action available to us today, is voting on the Idea Exchange for a change in these system behaviors. Below, I have a list of applicable Ideas that I am requesting that you vote on.
Please vote for salesforce.com to roll out more advanced abilities around the MSPs here;
Added Functionality for Formulas — DELIVERED!!!
Added Functionality for Reporting
Allow MSPs to be the Controlling field in dependencies
Please drop us a comment, letting us know that you voted!
To contact FbD for consultation on this and other salesforce.com features, contact us.