All Sorts

A sub-form displays data in a tabular form. Above each column is a button acting as a label. The user clicks the first button label, and the data is sorted in ascending order. They click again for descending order. Each button labels repeats the same behaviour.

Ingredients

  • 1 x One Level Higher or Form Open Related to pin a subset of data to the form
  • 2 x form-only virtuals for sorting
  • 1 x editbox (or button, if using DataEase 7) for each value on which to sort
  • 1 x Get/setglobal (or get/setarray) custom functions to carry sort state
  • 1 x ClearSelectionFilter CDF to refresh screen
  • 1 x OML script per sort value to change sort state
  • 1 x document properties set to display first record 3-D styling to imitate a button

In this recipe, we will imitate the sortable columns familiar in Windows Explorer, where you a click on the column label will sort first in ascending, then in descending order.

It demonstrates a number of techniques, including using virtual fields that only appear in the data model.

Building the Sort Form

I start with the form I created in the article Filtering with One Level Higher. This consists of a form built over Looper, with two sub- forms, one of which is Customer. Customer displays two fields: vNameComboR, which is the full customer name combined into a single string, starting with their last name, and vDOB, a virtual showing their date of birth.

  1. Drag the Customer sub-form down a little and add to the Looper record object, a virtual called vNameSort. Make it text, 50, and derived:
if ( getarray ( 2 ) = blank , "Name >" , 
if ( getarray ( 2 ) = "Date*" , "Name" , getarray ( 2 ) ) )
  1. Add a second virtual called vDateSort. Make it text, 50, and derived:
if ( getarray ( 2 ) = "Name*" or getarray ( 2 ) = blank , 
"Date" , getarray ( 2 ) ) )
  1. Change the visual control for both these fields to 3D, and style them so they look like buttons.
  2. Add to the clicked event of vNameSort the following OML:
define "t" number . 
t := setarray ( 2 , if ( getarray (2) = "Name >" or
getarray (2) = blank , "Name <" , "Name >" ) ) .
t:= clearselectionfilter () .
  1. Add to the clicked event of vDateSort the following OML:
define "t" number . 
t := setarray ( 2 , if ( getarray ( 2 ) = "Date >" ,
"Date <" , "Date >" ) ) .
t:= clearselectionfilter() .
  1. Now we need to add the form only virtuals that we will be sorting on. Drag vNameComboR down to expand the Customer record object, and add to this area a virtual called vSortAsc, text, 100, derived:
if ( getarray ( 2 ) = "Name >" or getarray ( 2 ) = blank , vNameComboR , 
if ( getarray ( 2 ) = "Date >" ,
concat ( year ( DOB ) ,
lastc ( concat ( "0" , month ( DOB ) ) , 2 ) ,
lastc ( concat ( "0" , day ( DOB ) ) , 2 ) ) , "ZZZZ" ) )
  1. Add a second virtual called vSortDesc, text, 100, derived:
if ( getarray ( 2 ) = "Name <" , vNameComboR , 
concat ( year ( DOB ) , lastc ( concat ( "0" , month ( DOB ) ) , 2 ) ,
lastc ( concat ( "0" , day ( DOB ) ) , 2 ) ) , "ZZZZ" )

I'm using the greater than arrow > to indicate that the value appears in ascending order, and its reversed twin to show we are sorting in descending order. If you choose other symbols (my screenshots, you may notice, use the chevron characters), make sure that this is reflected in the derivation.

  1. Open the Query By Model dialog, move to the SeeCustLastName box, and scroll down to see these two virtuals at the bottom of the list. Click on vSortAsc, then click on the Sort By button. Make sure its sort order is 1, and that the default Ascending is checked.
  1. Move to vSortDesc, click on the Sort By button, make sure that the order is 2, and change the order to Descending.
    Now highlight both vSortAsc and vSortDesc, and uncheck the display option at the top right of the dialog (or hit the spacebar).
    Click OK. Accept the default of Modify on the New layout dialog.

>> Page 2