Cumulative Sorting

by David Swain

copyright 2002 David Swain

On December 18, 2002, Ray Briggs II posted an interesting request to the Omnis Underground list as follows:

I have the headers on my headed list box enabled and I sort on the column
clicked. I want the headed list box to act like any other spreadsheet when I
sort on one column then another. I want the first column sort to be held in
tact when the second column sort begins.
For Example if I sort the list on number I get this
17320 | Dec 5
17376 | Dec 4
17377 | Dec 4
But then when I sort on date it forgets that I just sorted on numbers.
17377 | Dec 4
17376 | Dec 4
17320 | Dec 5
I want
17376 | Dec 4
17377 | Dec 4
17320 | Dec 5
I know I can List.$sort($ref.C2,0,$ref.C1,0) but what is a good way to
retain that I clicked on C1 first.
I want it to act like Excel and retain the old sort choice(s). How do you
guys/gals handle it? I have a few ideas but nothing very elegant. I don't
just want to retain the last column sort either, I want it to remember all
of them (like Excel)

In my original response, I suggested one possible solution using Omnis Studio Notation (which I knew Ray prefers over normal Omnis Studio commands), but since it uses Notation, it is necessarily “un-elegant”. When sorting lists using Notation, all the sort criteria must be specified each time a sort is performed — and Omnis doesn’t retain the previous sort order within a new sort. So we have to find some way of “rolling over” the previous sort information into the current sort requirements — and in such a way that we don’t violate the limit of 9 sort levels Omnis Studio imposes.

About 20 minutes later, I had a working example of a much more “elegant” technique, which I privately sent to Ray even though it relies on native Omnis Studio commands rather than Notation. I have since pared down the code further to a total of only 14 lines of code (including the line On evHeaderClick). 7 of these lines are in the “$event” method of the headed list field, 4 are in a private method of the headed list field named “shiftsorts” and 3 in another (public) method of the headed list field named “$update” (which is actually used to re-seek the current line after the lines are resorted, so it is not directly part of the basic solution to Ray’s request).

This technique relies on the fact that there is a set of “Sort variables” in the set of system states in Omnis Studio. While we can’t directly access them at the system level using Omnis Studio Notation, we can influence them through a Report class and the setting of a “current report”. Each time the Set current report command is executed, the sort variables defined within that Report class are imposed on the “system set” of sort variables, replacing them. We do have notaional access to the set of sort variables within a Report class.

But there is good news and bad news about this. Most notational groups (like $sorts) contain a method named “$addbefore” that can be used to place a new member of the group at a specific location within that group. The $sorts group for a Report class contains this method, but it doesn’t work precisely the way one would expect. My thought was to use a dummy report class for this purpose, simply inserting each new sort criterion to the top of the list of sort variables (as sort variable number 1) and then re-establish the set of sort variables used by the Sort list command by issuing a Set report name command. this did not, in fact, work as expected, so I had to write a small routine (4 lines of code) to cycle through the 9 sort variable slots, move the dataname value for each one down a notch and then replace the dataname in sort variable 1 with the name of the new variable. (This is why the solution took 20 minutes!)

So here are the players:

A Window class (named “headedListSortTester”) containing a Headed List Box field (named “headedList”) used to display the contents of a list variable (instance variable) named “list”. This list variable is defined and populated in the $construct method of the window using a File class and records from a native Omnis datafile. The window also contains a $destruct method that calls a private class method named “clearsorts”.

A Report class (named “sortDummy”) that is completely devoid of all content.

The “headedList” field has no calculation, so it simply displays the contents of each column in the order it is defined in the “list” variable itself. All columns are derived from a File class named “Member file”, so the names are qualified using this File class name. The data-bound variables from the File class used to define “list” are: “lastName”, “stateCode”, “zipCode” and “seqNumber”. The fourth column is not displayed in “headedList”.

The “headedList” field contains three methods: “$event”, “shiftsorts” and “$update”. Here is the code:

$event (contains local variable “currSeq”)

On evHeaderClick
 Calculate currSeq as lst(Member file.seqNumber)
 Do method shiftsorts (pick(pColumnNumber-1,nam(Member file.lastName),nam(Member file.stateCode),nam(Member file.zipCode)))
 Set report name sortDummy
 Sort list
 Do $cfield.$update(currSeq)

shiftsorts (contains local variables “sortlevel” and “sortRef” and parameter “varname”)

For sortlevel from 9 to 2 step -1
 Calculate sortRef.[sortlevel].$dataname as sortRef.[sortlevel-1].$dataname
End For
Calculate sortRef.1.$dataname as varname

$update (contains parameter variable “currSeqNo”)

Set search as calculation {Member file.seqNumber=currSeqNo}
Search list (From start,Do Not Load Line)
Clear search class

I included pushbuttons on the window so the user can examine the current set of sort variables or clear them as necessary. I didn’t bother (in this first version — I may revise it at a later date) with the descending or uppercase properties of these sort variables. Also, this technique is limited to use with the native Omnis datafile or global variables for the definition of the list involved (and therefore the sort variables).

But in answer to the question “Can we do this in Omnis Studio?”, the answer is “Yes!” (again!) and the proof is in the demo library that accompanies this explanation.