Ask Reuben – June 14, 2023

Sort, Keep At Top

When sorting in a TABLE, how can I keep rows at the top?

The ui.Dialog.setGroupBy method was introduced in FGL=4.01.03.  This provides a way to define a grouping column that the rows in your Table will be grouped by.  Rows with the same group value will be kept together.

Any sorting of the table will still keep these rows together and the users sort will be a secondary sort.  It is the SQL equivalent of ORDER BY grouping-column [,sort-column].

Typically the values in the grouping column will not be unique, and will share a lot of common values.  For example city/state/country, or as in my example later a binary value to indicate if selected or not selected.

By selecting an appropriate column, it is possible to ensure that certain rows are always kept at the top of the Table. In the screenshot below, the user has sorted by a Quantity field but the rows that are “Selected” are kept at the top of the table. When a different sort is selected, the “Selected”rows are kept at the top of the table.

The implementation is very simple, simply at the beginning of the dialog add a call to ui.Dialog.setGroupBy with the desired grouping column as the parameter.

The example code for the screenshots is at the end of the article.

Like anything released mid-release, it has not had an Early Access Program to fine tune the concept.  One thing that has been pointed out is that the sort order is equivalent of ORDER BY grouping-column [,sort-column] where sort-column is the table-column chosen by the end-user to sort rows.  How do you implement equivalent of  ORDER BY grouping-column DESC [,sort-column]  ?, that is have the grouping column sorted descending.  The answer is to populate the grouping-column appropriately, so in my case it was 0=selected, 1=unselected, as opposed to the more intuitive 0=unselected, 1=selected.  Another approach you could have used is to add a column comparison function that reverses the sort collation …

CALL DIALOG.setColumnComparisonFunction(grouping-column, FUNCTION reverse_sort)
FUNCTION reverse_sort(s1 STRING, s2 STRING) RETURNS INT
    RETURN util.Strings.collate(s1, s2) * -1
END FUNCTION

… but that is a little more tricky with a PHANTOM column as in my example.

So if you have FGL=4.01.03 experiment with this new ui.Dialog.setGroupBy method, and report any improvements for future releases.

#! example 185.4gIMPORT util

CONSTANT IMAGE_SELECTED = "fa-check-circle"
CONSTANT IMAGE_UNSELECTED = "fa-circle-o"

MAIN
    DEFINE arr DYNAMIC ARRAY OF RECORD
        available BOOLEAN,
        selected_img STRING,
        id INTEGER,
        desc STRING,
        qty1 DECIMAL(11, 2),
        qty2 DECIMAL(11, 2),
        qty3 DECIMAL(11, 2),
        qty4 DECIMAL(11, 2)
    END RECORD
    DEFINE i INTEGER
    DEFINE last_row INTEGER

    WHENEVER ANY ERROR STOP
    DEFER INTERRUPT
    DEFER QUIT
    OPTIONS FIELD ORDER FORM
    OPTIONS INPUT WRAP

    CALL ui.Interface.loadStyles("askreuben185.4st")
    CLOSE WINDOW SCREEN

    FOR i = 1 TO 26
        LET arr[i].id = i
        LET arr[i].desc = ASCII (64 + i), ASCII (96 + i), ASCII (96 + i)
        LET arr[i].qty1 = util.Math.rand(100000) / 100
        LET arr[i].qty2 = util.Math.rand(100000) / 100
        LET arr[i].qty3 = util.Math.rand(100000) / 100
        LET arr[i].qty4 = util.Math.rand(100000) / 100
        LET arr[i].available = 1 -- all rows unselected to stary
        LET arr[i].selected_img = IMAGE_UNSELECTED
    END FOR

    OPEN WINDOW w WITH FORM "askreuben185" ATTRIBUTES(TEXT = "Ask Reuben 185")
    WHILE TRUE
        DISPLAY ARRAY arr TO scr.* ATTRIBUTES(UNBUFFERED)
            BEFORE DISPLAY
                CALL DIALOG.setGroupBy("scr.available") --group arrays on the value of this column
                
                IF last_row > 0 AND last_row <= arr.getLength() THEN
                    CALL DIALOG.setCurrentRow("scr", last_row) -- put cursor back on previously selected row
                END IF

            ON ACTION toggle -- User clicks on this image to change between available/unselected and unavailable/selected
                LET arr[arr_curr()].available = NOT arr[arr_curr()].available
                LET arr[arr_curr()].selected_img = IIF(arr[arr_curr()].available==1, IMAGE_UNSELECTED, IMAGE_SELECTED)
                LET last_row = DIALOG.getCurrentRow("scr")
                EXIT DISPLAY -- exit array to resort
        END DISPLAY
    END WHILE
END MAIN
#! example185.per
LAYOUT
TABLE
{
[f00     ][f01][f02      ][f03      ][f04       ][f05       ][f06     ]
}
END
END
ATTRIBUTES
PHANTOM formonly.available;
IMAGE f00 = formonly.selected_img, TITLE="Selected", ACTION=toggle;
EDIT f01 = formonly.id, TITLE="Id";
EDIT f02 = formonly.desc, TITLE="Description", STRETCH=X;
EDIT f03 = formonly.qty1, TITLE="Quantity 1";
EDIT f04 = formonly.qty2, TITLE="Quantity 2";
EDIT f05 = formonly.qty3, TITLE="Quantity 3";
EDIT f06 = formonly.qty4, TITLE="Quantity 4";

INSTRUCTIONS
SCREEN RECORD scr(available, selected_img, id, desc, qty1, qty2, qty3, qty4)
<!-- example185.4st -->
<?xml version="1.0" encoding="ANSI_X3.4-1968"?>
<StyleList>
  <Style name="Window">
     <StyleAttribute name="windowType" value="normal" />
  </Style>

   <Style name="Table">
     <StyleAttribute name="headerAlignment" value="auto" />
     <StyleAttribute name="alternateRows" value="yes" />
  </Style>
 
</StyleList>