Picklists and lookups are both very powerful. Sometimes it is easy to decide which to use in a certain situation, and other times it is not.
Picklists or Lookups?
Picklists, or drop-down lists, allow the user to choose one (or more in the case of multi-select picklists) selection from a list of fixed values. It is quick, easy and intuitive.
There are drawbacks, however. Prior to the recent introduction of global picklists (and, yes, this form was created before they were available), I was faced with having to reproduce the same list for every field on the receiving form.
This was a deal breaker. The thought of remembering to maintain up to five identical lists was a nightmare. Instead, I chose to use lookups – my form of a global picklist.
This was not the only situation where I needed to reuse a picklist:
Of course, this came at a cost. Not only does it require more clicks and keystrokes, it also requires a lookup object. In fact, it requires a separate lookup object for program and donation.
Filtered Lookups
Using lookups presented another serious problem. I could not afford to “waste” two of the ten custom objects on glorified picklists. Furthermore, there likely could be more of these. This was also a deal breaker.
I reached back into my “old programming days” to engineer a solution. Prior to the cloud, software companies had to ship installation disks and it was a big deal to upgrade software. Database changes were the most difficult. In addition to updating the software on every computer, a special conversion application was written and executed against the database to modify the structure.
As an aside, that is why I really appreciate Salesforce. As a developer in the 90’s, I often elected to postpone or not implement a feature because the work involved to upgrade the database outweighed the benefits. The flexibility of the Salesforce environment exponentially increases my productivity and allows me to concentrate completely on fulfilling the needs of the user.
I dealt with multiple picklists by creating a single table.
The Picklist table held all the values for every picklist. To present only flowers in a drop-down I simply filtered on the “Flower” picklist type.
I employed exactly the same technique in Salesforce:
I named the object Category. The name is equivalent to the value and the type is actually a picklist.
My next challenge was filtering the values on the receiving form. Lookups in Salesforce can have a filter that is applied so that only those records that match the filter are visible. There is also a setting that requires the user to select only from those values, even if he attempts to search for a different one.
This worked well until I reached the limit of five filtered lookups per object. Bummer.
With the recent inclusion of global picklists, the use of an extra object for really no reason, and, most importantly, a poor user experience, I will replace all of this with global picklists.
When to Use Lookups?
This question is directed specifically at lookups which return a limited set of choices. Here are my top four reasons:
- There are considerably more than 10 values: in some cases, an actual lookup dialog may make it easier for the user to find a value in a long list.
- New values are routinely added: only system administrators can modify picklists, and this may be inconvenient.
- Other data is associated with each item: some items may no longer be selectable but need to be retained for historical purposes. You may wish to use a filtered lookup that restricts the selection of certain values based upon criteria.
- The name of the “value” may be changed: the value is stored as a string and there is no mapping back to the list of values. If an item name is changed, it will not update the existing records.