Picklists

We’ve spent a bunch of time solving the problem of vocabulary lists for a database application. A vocabulary list is one of those tables that have a key (typically an integer or a GUID) and some text. The “key” (primary key in DBMS-speak) is referenced by a column in a more important table. Most of the time, the end-user selects this key by choosing an item via a drop down list box that has some human readable text and the application stores the item’s “key” into the database. We call the object that solves this problem a Picklist.

Normally, you solve the problem by creating a 2 column table with a key and some descriptive text. For example, say you wanted to have a status table:

Table: status

You might get a stab of reusability and conclude that a good idea is to store these sorts of things in a table that has a type and therefore keep yourself from having to create 300 of these tables in your database. So, the table might look like this:

Reusable design: picklist

Now, all you have to do is change the SELECT statement and you can have one table to handle most everything. Next, you bump into a requirement where you have to store something besides and integer as the key (say, its data that you didn’t own and the key is some cryptic string). No problem, you just add another column to this list with an alternate ID (or another table, I suppose) and indicate that somehow.

2^nd try: picklist

Next, you decide that some of the items in this list are required to be shown, but you don’t want the user to select them. So you add another column to the table and indicate which items are disabled.

3^rd try: picklist

At this point you’ve thought of several more interesting requirements so your table design ends up with several more columns to support these:

• Localization. If you plan to support a multi-lingual application you are very likely to need to have your vocabulary displayed in the user’s culture.
• Readable string as a way to lookup an ID. The common case for this is when you want either to set a default value in a field but do not want to hard code some opaque primary key values in your code or you want to take some action based on the value that a user chooses and you don’t want to hardcode the primary key value.
• Caching (or not) the items in the list. If you are going to model this, you’ve likely decided that you need to metadata tables – Picklist (for the name of the list and some attributes like “cache”), and PicklistItems (for the actual items).
• Discrimination. Often you’ll have a requirement where the value in one column filters (or discriminates) the potential values in another. For example, you may have a list of vehicle make types in one list and based on the selection, you need to show the available models in another. The typical way to solve this is to take the value from the first control, post the results to the server, dynamically determine the new SELECT statement, and return the page with the second control filtered.
• Two-way lookup. Often you have the value of the item’s key only and you need to find the text, but we have found that you just a frequently need to do the reverse (e.g. you have the text and you want the key). We use the reverse lookup, for example, when searching or filtering in a list – the user enters “Open” and we search for “1.”

The next issue you solve is how to fill the controls with the contents of these lists. The most straightforward method is to write code to open a DataReader with a statement like (SELECT id, description FROM picklist WHERE list_type = ‘status’) and add these items to a DropDownList control like this:

while (dr.Read())
{
    listControl.Items.Add(new ListItem (dr.GetString(0), dr.GetInt32(1)));
}

Or, a better way is to bind using the DataBinding features of the DropDownList– something along these lines:

DataTable dt = CreateTableFromPickListSQL();
dropList.DataSource = dt;
dropList.DataTextField = “id”;
dropList.DataValueField = “description”;
dropList.DataBind();

In both cases, you are responsible for creating either the DataReader or DataTable and properly disposing of it. Most folks know how to do this, or at least they know how to cut and paste some code somewhere that does it reasonably well. It’s still a pain and generally isn’t written in such a way to promote even moderate reusability.

Another consideration is that you may need to use one of these lists for something other than as a DataSource for a DropDownList control. For example, if you are displaying a table of data that has several columns which are foreign keys to your picklist table you either must create some sort of view to resolve these or do a lookup as you fill the list (this is almost never an option for performance reasons). The trouble with the view is that unless you strictly enforce the integrity of your data you are likely to lose some rows because the values in your main table have foreign key values that are invalid.

There are a bunch of other factors, of course, which is why there is a dedicated object to solve this problem. Again, a Picklist represents a domain specific list of values that can be used to translate the key of an item (typically a foreign key in a database table) to a description.

In the NetQuarry Platform, Picklists are of one of three distinct types:

• A “standard” Picklist. This type of list is loaded from the metadata and supports the descriptive text in the current user’s culture (language). We use a pattern similar to the one just described to model this. (Picklist, PicklistItems (with a foreign key to a “Text” table to support localization))
• A “SQL” Picklist. This type of list is loaded from one of the databases (can be the “main” operational database or the repository) and can contain 1 to 3 columns of data.
• An “Enumeration” Picklist. This list is a 2 column Picklist loaded from a string type name that represents an Enumeration as its source. (The NetQuarry Studio makes good use of this.)
• For the most part, Picklists tend to have the LimitToList attribute set. This attribute says that the item selected by the user MUST be one of the items in this list. There are several other attributes of interest as well, listed here and briefly explained:

 

Member Name	Description
Cache	This list should be cached.
HasDiscrim	This list uses a discriminator to break items into sets.
LimitToList	This list has at least 2 columns and the values must come from the list.
HideUnknownItems	The list should hide unknown items.
MarkUnknownItems	The list should mark unknown items.
StoreAltText	The list stores the item”s Alternate Key Text column in the DB and uses it as it”s ID.
StoreAltInt	The list stores the item”s Alternate Key Int column in the DB and uses it as it”s ID.
StoreItemName	The list stores the item”s Name column in the DB and uses it as it”s ID.
NoNullEntry	The list does not need to provide a null entry.
SortByText	List should be sorted by the display text.
SortDesc	List should be sorted in DESC order. (Standard Picklists only)
SortByKey	List should be sorted by the stored key.
AllowUserAdd	Users can add new items to this list (providing they have Configuration permission).
AllowUserDelete	Users can remove items from this list (providing they have Configuration permission).
AllowUserUpdate	Users can update items in this list (providing they have Configuration permission).
Disabled	The picklist is disabled.
IgnoreWhitespace	Ignore leading and trailing whitespace when matching values.