Tech Tip: Sorting time formatted string fields in an entity listbox

PRODUCT: 4D | VERSION: 17 | PLATFORM: Mac & Win

Published On: February 14, 2019

When using time types in entites or objects, the time will automatically get converted to seconds. Since the time column contains the real number type, this column can easily be sorted asc or desc when clicking on the header by default.

However, what happens if you want to convert the numeric time field into a formatted string (E.G. HH:MM:SS) that can still be sorted from the header in the listbox? If the column expression was simply changed from This.created_at to Time string(Num(This.created_at)), the column apperance is correct but cannot be sorted when clicking on the header due to the colon characters.

To solve this issue:

1. Detect whether the time column header was clicked

2. Toggle boolean value whether to sort by ascending or descending

3. Reassign listbox entity selection using entitySelection.orderBy()

you must first detect whether the column with the time has been clicked (in this case, column 2) and toggle whether to sort the entity selection by asc or desc using a boolean value. To sort an entity selection, you can use the entitySelection.orderBy() to select the object property and sort type (asc or desc) will be sorted.

Here is the code snippet for the form and listbox methods.

// ----------------------------------------------------
// Method: Form1
// ----------------------------------------------------

Case of
   : (Form event=On Load)
   C_OBJECT($person)
   C_BOOLEAN(toggleSort)
   Form.LB:=ds.People.all()

End case

// ----------------------------------------------------
// Method: Form1.List Box
// ----------------------------------------------------

Case of
   : (Form event=On Header Click)
   ARRAY TEXT($colNames;0)
   ARRAY TEXT($headerNames;0)
   ARRAY POINTER($colPtrs;0)
   ARRAY POINTER($headerPtrs;0)
   ARRAY BOOLEAN($colVisible;0)
   ARRAY POINTER($stylePtrs;0)
   C_LONGINT($headerNum)
   LISTBOX GET ARRAYS(*;"List Box";$colNames;$headerNames;$colPtrs;$headerPtrs;$colVisible;$stylePtrs)
   $headerNum:=Find in array($headerPtrs;Self)

   If ($headerNum=2) // if "created_at" header was clicked
      C_TEXT($sortType)
      $sortType:=Choose(toggleSort;"desc";"asc")
      Form.LB:=Form.LB.orderBy("created_at "+$sortType)
      toggleSort:=Not(toggleSort)
   End if

End case

Now when the time column header is clicked, the entity selection be sorted by time and alternate between ascending and descending.