ON SORT block

Basics

The ON SORT interaction block can be used to detect when rows have to be sorted in a DISPLAY ARRAY or INPUT ARRAY dialog.

ON SORT is used in two different contexts:

  1. In a regular full-list DISPLAY ARRAY / INPUT ARRAY dialog, the ON SORT trigger can be used to detect that a list sort was performed.
  2. In a DISPLAY ARRAY using paged mode (ON FILL BUFFER), use ON SORT to detect a sort request from the user and re-fetch the rows from the database in the required order.

ON SORT in regular full-list DISPLAY ARRAY or INPUT ARRAY

In a regular DISPLAY ARRAY / INPUT ARRAY dialog not using paged mode, the ON SORT trigger can be used to detect that a list sort was performed.

When the ON SORT block executes in this context, the (visual) sort is already done by the runtime system and the ON SORT block is only used to execute post-sort tasks, such as displaying current row information.

To display the row position information, use the arrayToVisualIndex() dialog method to convert the current program row number to the visual row number:
DISPLAY ARRAY arr TO sr.* ...
   ...
   ON SORT
      MESSAGE SFMT( "Row: %1/%2",
         DIALOG.arrayToVisualIndex( "sr", DIALOG.getCurrentRow("sr") ),
         DIALOG.getArrayLength( "sr" )
      )
   ...
If needed, you can get the sort column and sort order with the getSortKey() and isSortReverse() dialog methods:
DISPLAY ARRAY arr TO sr.* ...
   ...
   ON SORT
      MESSAGE SFMT( "Sort on %1, %2 order",
           DIALOG.getSortKey("sr"),
           IIF( DIALOG.isSortReverse("sr"), "descending", "ascending" )
      )
   ...

ON SORT in DISPLAY ARRAY using the paged mode

In a DISPLAY ARRAY implementing paged mode with ON FILL BUFFER trigger, built-in row sorting is not available because data is provided by pages.

Use the ON SORT trigger, to detect a sort request and perform a new SQL query to re-order the rows. In this context, the sort column and sort order are available with the getSortKey() and isSortReverse() dialog methods:

DEFINE key STRING, rev BOOLEAN

DISPLAY ARRAY arr TO sr.* ...
   ...
   ON SORT
       -- Re-execute the SQL statement to fill the page of rows in ON FILL BUFFER
       -- Assuming that form field names match table column names
       LET key = DIALOG.getSortKey("sa")
       LET rev = DIALOG.isSortReverse("sa")
       IF key IS NULL THEN
          CALL execute_sql( NULL )
       ELSE
          CALL execute_sql( "ORDER BY " || key || IIF(rev," DESC"," ") )
       END IF

See Paged mode of DISPLAY ARRAY for more details about the paged mode in DISPLAY ARRAY and how to implement sort in this type of record list dialog.