All Sorts (cont)

<< Page 1


It is easy enough to sort records in a sub-form. Simply open the QBM dialog, find the sub-form, select the field to sort on, click sort by, and then ascending.

The problem is that this is a permanent setting; you cannot change it dynamically. We can neither swap the column on which we are sorting, nor the direction.

With my solution, we do both.

Clicking on a label button alerts our form to the fact that the user wants to sort on a given field. The sort state (which column, which direction), is stored in memory using the setarray function (Ill refer to this as simply the array value).

Two virtual fields, vSortAsc and vSortDesc, are derived from the array value. Depending on the sort order, the value in the first field is either the value from the appropriate column, or a constant (in my example, a string of Zs). vSortDesc is simply derives the value of the selected column.

In the QBM, the sort order is set on these two fields, first in ascending order on vSortAsc, then in descending order on vSortDesc.

When the sort order is ascending, vSortAsc will contain the value of the desired column, and the data will simply sort in ascending order. The second sort in descending order does not affect the order on screen.

When the sort order is descending, vSortAsc will contain the same value for each record. The sorting on vSortDesc will now be effective, and so the records will appear in reverse order.

Heres a quick illustration of this:

Sort in Ascending Order

vSortAsc vSortDesc
Adrian Adrian
Barry Barry
Charles Charles

Sort in Descending Order

vSortAsc vSortDesc
ZZZZZ Charles
ZZZZZ Adrian

Since the virtuals are text fields, we need to work a bit harder with date values, as they will otherwise sort alphabetically on the text values of the date (02/02/2005 will appear before 01/01/2004). To get round this, I reverse the dates so that they are formatted YYYYMMDD. DataEase has no easy functions to help with this, so that part of the derivation looks a bit involved.


A key concept is that of toggling values, where a value is switched between two or more states. If this were a light switch, pseudo-code for toggling the switch would be:

Lightswitch := if ( Lightswitch = off , on , off ) . 

The light has two states on and off and this code toggles between them. (Actually, it has three: on, off and blank. If the state is other than on or off for example, it hasnt yet been given a state then the default with this code will be to set it to off. Hence the second condition in vNameSorts OML.)

Variations of this concept are seen in the derivations of all the virtuals, and in the OML behind the column headings.

In Version 7 we can change the text on a button. This also means that we can navigate to the button via the keyboard. For the moment, Im using a button-styled field.

Clearselectionfilter not only removes any user-entered selection criteria, it also refreshes the display, returning to the first main record in the set. My main record is simply the first one in the Looper table.

And that, more or less, is it. Run the form in user view. While #2 is blank, you should see that the Name button has the forward arrow after it, and that the names are in alphabetical order. Click the name button, and its label changes to show the backward arrow, with the names in reverse order.

Click date, and the forward arrow now appears after the word Date; the records are in chronological date order, with the oldest first. Click the date button again, and the records are in reverse date order.

DataEase will struggle if you try to sort all 50 million records in your table, since the sort is on a virtual. But then again, Explorer will take its time if you try to do the same with a ridiculous number of files. You should make sure that you have filtered the set to a manageable amount first. Even so, you can probably easily get away with a couple of thousand at a time!

If you need to change the derivation in vSortAsc or vSortDesc, go to the Query By Model dialog, find the fields, highlight them and click display (or hit the spacebar), okay this and the modify layout option dialog, and they will reappear in the sub-form. Make your changes, then go back to the Query By Model dialog to remove them from the display.

Figure 1: Top: the same list sorted first in ascending Name order, then descending by Name. Below: in date of birth in order, then in reverse order. Note the little arrow after the button label to indicate which field is being used to sort, and in which direction.