Sorting rows in a list

List controllers implement a built-in sort. This feature can be disabled if not required.

When a DISPLAY ARRAY or INPUT ARRAY block is combined with a TABLE container, the row sorting feature is implicitly available. Row sorting is supported on TREE containers with DISPLAY ARRAY dialogs only.

To sort rows in a list, the user must select a column header of the table, using a mouse click on desktop. Multiple sort columns can be selected, by using alt-click.

The program dialog code can also define primary sort columns (also known as "group by" columns), with the setGroupBy() and addGroupBy() dialog methods. Primary sort columns are part of the sorting columns list. Primary sort column force rows to be always grouped by the values of the given columns. To specify the initial sort order of a primary column, you can use the setGroupByDesc() method.

When a given column is already selected for ascending sort, the next [alt-]click will select the column for descending order, and when already selected for descending order, a next [alt-]click will de-select that sort column, except if the column is defined as primary sort column by program. In this case, the primary sort column must remain and the end user can only switch from ascending to descending order.

Selecting a table column header triggers a GUI event, that instructs the runtime system to reorder the rows displayed in the list container.

In fact, the rows are only sorted from a visual point of view; the data rows in the program array (the model) are left untouched. Therefore, when sorting applies, the visual position of the current row might be different from the current row index in the program array. To convert a visual index to/from the program array index, use the visualToArrayIndex()/arrayToVisualIndex() dialog methods.

When the program array is modified interactively, or by program with DIALOG methods, the runtime does not perform row sorting, to keep new created rows visible. However, the runtime will perform row sorting, when the length of program array changes, after using DYNAMIC ARRAY methods like appendElement(): Since the exact array operation is not known by the runtime when manipulating directly the program array, the runtime must re-build the entire internal list of visual indexes. Note that if you delete and then append/insert a new row with array methods, the number of rows does not change and not automatic new sort will apply. Consequently, it is recommended using only DIALOG methods or DISPLAY ARRAY modification triggers, to manipulate the array rows by program during the dialog execution.

To sort rows on a character string column, the runtime system uses the standard collation order of the system, following the current locale settings. As a result, the rows might be ordered a bit differently than when using the database server sort (with an ORDER BY clause of the SELECT statement), since database servers can define their own collation sequences to sort character data.

The data used to sort rows is the raw data in the program array. When using a COMBOBOX with ITEMS defining key/label pairs, the runtime system uses the key values to sort the table rows.

The built-in sort is enabled by default. To prevent sorting in TABLE or TREE containers, define the UNSORTABLECOLUNMS attribute at the list container level, or set the UNSORTABLE attribute at the column/field level. As rows can be created and modified during an INPUT ARRAY instruction, you may want to use the UNSORTABLECOLUMNS attribute for tables controlled by INPUT ARRAY.

To execute code after a sort was performed, use the ON SORT interaction block in the dialog, for example to display the current row position with DIALOG.arrayToVisualIndex(). With a page-mode DISPLAY ARRAY using ON FILL BUFFER, the build-in sort is disabled. Use the ON SORT trigger to re-sort the result providing rows in ON FILL BUFFER. The dialog methods getSortKeyAt() and isSortReverseAt() methods must be used to get the sort columns and sort order. For more details, see Paged mode with sorting feature.

When an application window is closed, the selected sort column and order is saved. The sort will be automatically re-applied the next time the window is created. This way, the rows will appear sorted when the program restarts. The saved sort column and order is specific to each list container.

In order to cleanup the sort columns (primary sort columns and user-defined sort columns), call the resetSort() dialog method. This method can be used in BEFORE DISPLAY or BEFORE DIALOG blocks, and during the dialog execution, in an ON ACTION block.

Custom sorting rules can be implemented with a comparison function, that must be associated to a column field name with the ui.Dialog.setColumnComparisonFunction() method. Do not implement complex code in such function: It will have an impact on performances with a large set or rows, compared to the build-in sorting algorithm.