Presenting data to your users in an order that makes sense to them is essential. Both Jet and SQL Server support sorting one or more fields in a table alphabetically, numerically, and chronologically. That’s great, but sometimes you need more options. Rebecca Riordan shows you how to handle user-defined sort orders.

There are a number of situations in which the built-in sorting capability of your database engine just won’t do the job. A common case is data that’s truly alpha and numeric. The sorting rules are different when you combine numbers and text in the same field, and you’ll get anomalous results, as shown in Figure 1. The digit 3 might come before the digits 10 numerically, but when it’s alphabetized, 3 comes after 10 because the letter “3” comes after the letter “1”–probably not what you or your user would want.

Figure 1

And sometimes, more often than you might expect, your data has an intrinsic order that simply can’t be derived by the database engine. Historical “eras” are one example, as shown in Figure 2. In this case, you might think that assigning dates to the eras would solve your problem. But these historical classifications are largely defined by technological advances that occurred at widely different times in different geographic locations, so adding dates would impose unnecessary constraints on the data model.

Figure 2

Ranking records

Whenever you’re faced with a situation like this, the solution is to add an extra field to the table that reflects the intrinsic order of the “real” field but, unlike the primary data, this field can be sorted by the database engine. The most efficient data type to use in this situation is an Integer, but don’t try to use an Autonumber field. Even allowing for probable gaps, Autonumbers won’t allow the user to add or reorder the rows once they’re entered, which is an important part of this functionality.

The actual structure of my sample table is shown in Figure 3. Although the Rank field that’s used to order the records contains unique values, it isn’t the primary key. The Rank values will likely change if, for instance, the user adds records to the table, and it’s considered good practice, whenever possible, to avoid using any field whose value changes as a primary key. Also, as you’ll see in a moment, reordering the records requires temporarily duplicating values, which violates the uniqueness requirement for primary keys. For that reason, the index on the Rank field (and it should be indexed) allows duplicates.

Figure 3

Once you’ve created the field and established the values, displaying the records in the correct order becomes simply a matter of sorting by Rank. There’s nothing the least bit tricky in that, but “establishing the values” in the first place requires some consideration. You really don’t want to describe the purpose of the Rank field to your clients, and even if you’re building a system for your own use, adding and updating these values manually is tedious and error-prone.

Managing the rank

Fortunately, it’s fairly simple to both hide the Rank field from users and maintain the integrity of the field. All you need to do, as always, is restrict data entry to a form that manages the process. Figure 4 shows an example that updates the sample table. The up and down arrow buttons rearrange the order of existing items, while the controls in the group box allow the user to add new items at a specific position in the list.

Figure 4

Moving up and down

Moving items within the list is conceptually quite simple; you simply swap the values in the Rank field of two records, which is what the code behind the up arrow button shown here does. The first few lines create a reference to a DAO recordset and set its current record to the one selected in the ListBox (named “SortedList” in the example):

Private Sub btnMoveUp_Click()

   Dim rs As DAO.Recordset

   Dim strCriteria As String

   Set rs = Me.SortedList.Recordset

   strCriteria = "ItemValue = '" & Me.SortedList & "'"

   rs.FindFirst (strCriteria)

   With rs

      .Edit

      !Rank = !Rank - 1

      .Update

      .MovePrevious

      .Edit

      !Rank = !Rank + 1

      .Update

   End With

   Me.SortedList.Requery

End Sub

The actual work of reordering the records is done inside the With…End With block. The first three lines edit the current record. Since this function moves the current record up in the order, the value of its Rank field is decremented by one.

At this point there are two records in the table with the same value in their Rank field. To fix this anomaly, I must increment the previous record. This is precisely what the final four lines in the block do. Since the recordset variable is set to the Recordset property of the ListBox, the variable inherits the order of records, so a simple MovePrevious method call will move the current record correctly. Then it’s simply a matter of incrementing the value of the Rank field and updating the table. The final statement in the procedure requeries the ListBox to display the records in their new position.

The code to move a record down in the list is almost identical, requiring only that the first record be incremented and the second decremented (and, of course, you need to use MoveNext rather than MovePrevious to reposition the current record).

Adding records

Conceptually, in order to insert an item at a specific position, you must shift all the records after that position down one to make room. In practical terms, that means that you must increment the value of the Rank field of each record by one. Whereas to reorder the records I needed to change only two values, in this case I’m potentially changing the value of the Rank field for every record in the table. It’s far more efficient to do this using a query than to process the whole recordset in code.

Here’s the Jet SQL statement that increments the appropriate records (the principle would be the same in other dialects of SQL, but the syntax would differ somewhat):

PARAMETERS InsertRank Short;

UPDATE CustomSort SET CustomSort.Rank = [Rank]+1

 WHERE (((CustomSort.Rank)>=[InsertRank]));

The query accepts a single parameter, InsertRank, and increments the Rank field of every record where the original value is equal to or greater than this value. The code that uses this query is shown here and is called from the button labeled “Insert Above” on my sample form:

Private Sub btnAbove_Click()

    Dim theRank As Integer

    Dim qdef As DAO.QueryDef

    Dim rs As DAO.Recordset

    theRank = Me.SortedList.Column(1)

    Set qdef = CurrentDb.QueryDefs("InsertRank")

    qdef.Parameters!InsertRank = theRank

    qdef.Execute

    Set rs = Me.SortedList.Recordset

    With rs

        .AddNew

        !Rank = theRank

        !ItemValue = Me!newValue

        .Update

    End With

    Me.SortedList.Requery

End Sub

In this procedure, the value of the Integer variable theRank is set to the second column of the item that’s currently selected in the ListBox. In my sample form, that column contains the Rank field. You can’t see the rank column because I’ve set the width of the column to zero. The Rank value is passed to the query I just showed you, with the result that all of the records with a Rank of that value or greater are incremented. This leaves a “hole” in the list–the value specified in the theRank variable is no longer present in the table.

The value stored in theRank is used again in the With…End With block at the end of the procedure that adds the new record. This effectively fills the hole in the table, leaving the values in the Rank field in a complete, consecutive state.

Again, the code for the button labeled “Insert Below” is almost identical, with the single exception that the value of the theRank variable is set to the value of the currently selected record plus 1:

theRank = Me.SortedList.Column(1) + 1

Maintaining an artificial ranking field is useful whenever you’re dealing with a data set that has an arbitrary intrinsic order, and, as you’ve seen, it’s fairly simple to implement. But for the sake of clarity, the example I’ve used is missing some critical functionality. Most importantly, there’s no error-handling in the code samples.

Real-world problems

All of the sample procedures assume that an item has been selected in the ListBox, but by default, no items are selected in a ListBox when the form is first opened. In a production application, you must either check for a selection in the procedures or, a better solution, disable the buttons until a selection is made (being a belt-and-suspenders kind of girl, I’d do both).

The procedures also assume that the MultiSelect property of the ListBox is set to None. If you allow either Simple or Extended MultiSelect, you would have to loop though all of the items in the ItemsSelected collection of the ListBox in the reorder procedures.

I wouldn’t recommend allowing multiple selections in this situation, however, since the benefit to the user is likely to be outweighed by the possible confusion. What should happen, for example, when the user has selected the items shown in Figure 4? Should each item be individually moved up one position, or should they all be grouped above the “Classical” item? In some situations the appropriate behavior may be immediately clear, but most of the time it won’t be.

In addition, a production system would almost certainly require additional functionality. The example doesn’t provide a means for editing existing values, for instance. In a real application, you’d probably be working with more than a single “real” data field, and it might make sense to allow all of them to be edited and ordered on a single form.

At the very least, you’d probably want to add “Insert Top” and “Insert Bottom” buttons. This functionality can be achieved with the example configuration, but why make the user think about it? Both of these require only simple variations on the sample code. “Insert Top” would set the value of theRank to 1, while “Insert Bottom” doesn’t need to shuffle existing records at all–simply set theRank to the value of the last record plus 1.

Application design often requires that you make unpleasant choices between accommodating the user’s model of the world and what can be implemented within time and budget constraints using the inherent functionality of our tools. Fortunately, supporting custom sort orders gives us a break from trade-offs. It’s easy (and cheap) to implement, and it matches the user’s model precisely.