Arbitrary sort of selection options

_Problem_

Very often, options that are displayed in a selection control (drop-down list or simple list) need to be sorted in a logical order that is relevant to the business domain rather than in alpha-numerical order.

Let us take the lookup values for the status of a sale as an example. Usually, they have to be sorted in the order in which the status changes, as shown below:

# Preparation
# Marketing
# Negotiation
# Closed

A _simplistic_ database table that is used to store these lookup values typically consists of only two fields: ID and Description. Suppose such a table, *StatusLookup*, has the following structure:

The *ID* field is of type _CHAR_ and is a foreign key (FK) in referencing records. The choice of datatype and size for this field is driven by the desire to have meaningful values in it. So, _PREP_ stands for _Preparation_ status, _MKTG_ for _Marketing_, NEGO for _Negotiation_ and _CLOS_ for _Closed_.

The *Description* field holds the caption for each option that will be displayed in the selection control.

As can be seen, sorting by either the id or the description does not yield the result we expect, that is, a logical order. One solution would be to replace the meaningful ids with values that would produce the order we wish to achieve when sorted by the *ID* field. Doing so, however, negates our effort to have values that can be easily identified.

_Solution_

The better solution is to add a new field to the table; it contains values that are arbiitrarily defined to produce the sort order we desire. So, our new table structure becomes:

We fill in the *Sequence* field with integer values; when sorted by this field, the logical order should be the one we want to achieve. A typical table will be as follows:

|*ID*|*Description*|*Sequence*|
|CLOS|Closed|4|
|MKTG|Marketing|2|
|NEGO|Negotiation|3|
|PREP|Preparation|1|

The SQL statement or stored-procedure that we use to get the lookup values is as follows:

The recordset that contains the results returned from this query can be iterated through sequentially; the fields of each row are used to generate each option in the selection control. Thus, our goal to logically sort the lookup values is achieved.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: