In previous versions of OpenInsight, EditTable sorting has been implemented by the SORTEDCOL property, which simply performs a Quicksort on a single column and then only on the visible text contained in each cell. This latter trait is particularly sub-optimal because it pays no attention to the actual type of data represented in the cell itself; for example, if you wish to sort on a column containing dates, you usually have to write code to convert the data to a numeric format first, and then pass the results onto the V119 sort function, all of which gets tedious very quickly.
In OpenInsight 10 the SORTEDCOL property has been deprecated and has been replaced by the new SORT method detailed below:
The SORT method
This method allows you to perform a multi-column sort on an EditTable, along with the ability to convert the data to an appropriate format before the sort takes place.
Call Exec_Method( CtrlEntID, "SORT", SortCriteria, SortOptions )
The method takes two parameters. The first, SortCriteria, which is a dynamic array structured as follows:
<0,1> @svm'd list of column numbers to sort by <0,2> @svm'd list of sorting directions/justifications for each column passed in field <0,1>. Available values are: 0 - Descending Left 1 - Ascending Left 2 - Descending Right 3 - Ascending Right <0,3> @svm'd list of ICONV patterns used to convert the column data to its internal format before the sort takes place.
The default ICONV pattern used for sorting a column is taken from it’s VALID property. This means that you can flag a column as a date (e.g. “DE”) in the Form Designer, and have it sort properly in a numeric fashion without any extra coding needed.
The second parameter, SortOptions, is a dynamic array structured as follows:
<1> If TRUE$ then perform a trim operation before the sort takes place, or FALSE$ to prevent the trim. If this field is null then the SORTTRIM property is used to decide if a trim operation takes place.
(A trim operation is the removal of “blank” rows from the EditTable control. A description of trim functionality will appear in a future post).
Using the SORT method triggers a new event called SORTED:
The SORTED event
This event takes the same parameters as passed to the SORT method described above. This event is fired before any sorting takes place, thereby giving you the chance to modify the criteria or options, or even prevent it by using the Set_EventStatus() function. All event script and QuickEvent handlers are processed before sorting.
The COLHEADERSORTINGMODE property
This is another new property for EditTable controls and can be set to one of the following values:
- 0 (Disabled – this is the default value)
- 1 (Sort on single-click)
- 2 (Sort double-click)
When set to to 1 or 2, clicking or double-clicking on a column header will automatically sort the contents of the control by that column, in a similar manner to Popup entity sorting. The SORTED event is still raised in the manner described above however, so you may still intercept and modify the process if you wish.
The SORTTRIM property
When set to TRUE a sort operation automatically performs a trim operation before sorting. This property can be overridden by passing a flag in the SORT method SortOptions parameter described above.
We hope these improvements make sorting in EditTables a little less onerous.
[EDIT: 27 Sep 15, Updated for SortOptions argument and SORTTRIM property]
[EDIT: 17 Nov 15, Updated for COLHEADERSORTINGMODE property]
(Disclaimer: This article is based on preliminary information and may be subject to change in the final release version of OpenInsight 10).
Do you also cater for the blank rows at the end of the edit table to avoid having to perform a trim before a sort.
Hi Chris, Not at present. There is a new AUTOTRIM property that performs trimming operations on LOSTFOCUS, along with a “manual” TRIM method too.
We could add something like a SORTTRIM property to flag that the TRIM method gets executed before a sort. Of course, you can always perform a TRIM during the SORTED event – you can manipulate the contents there as you wish before the SORT gets executed.
Could you not add a fourth argument “Autotrim” set to True to the “SORT”. If there is no trim then the blank rows will appear at the top after the sort.
OK – I’ve added a flag to the SORT method to execute the TRIM method before the sort. There’s also a SORTTRIM property that you can set as well.
Carl, does the numeric sort use the extended precision comparisons? I just re-encountered the bug (in 7.2.2) where data with more than 5 decimals didn’t sort correctly when using SORTEDCOL.
The sorting is now handled back in Basic+ via v119() so we can use the Iconv processor – does v119 work for you?
I’ve never been much of a fan of v119 due to the time it usually takes to setup a sort compared to doing an insertion sort so I don’t know. I’ve never heard anyone complain about right justified V119 sorting with high number of decimals but I think the opinion pool is a bit shallow.
I’ve never had an issue with V119. Normally, if I know I’ll be working with it, I’ll build the arrays with @RM delimiters. Having said that, if I did need to sort something it’s usually just a few CONVERT calls. It used to be a pain, because you had to check for the 64k borders, and then merge and extract, but now it’s just set it and call it. Much quicker then LOCATE BY.
Is there an option to make sorting case insensitive? I know you’ve deprecated SORTEDCOL but if you could put a property in for insensitive you could make SORTEDCOL respond to it too 🙂
I’ll look at that but bear in mind you can manipulate the data any way you wish before it sorts – one of the arguments is an IConv process – so you can pass an ICONV proc that simply does an uppercase conversion.
While I’m thinking about column sorting we have instances where we sort on a hidden column but it doesn’t display the sort icon. Could there be a property for the column to which the icon will be displayed?
I assume the new sorting process will sort -ve numbers in their correct order.
I certanly hope so – do you have problems with the current V9 SORTEDCOL?