Four Js Development Tools Forum

I've just been experimenting with using column sorting in our INPUT ARRAY, and I'm very surprised at the behaviour! It seems to just sort the table at the client end, but does nothing to the actual program array! I was expecting the rows of the array to be physically moved around, not just the visual rendition of the data.

Before it's suggested, I've seen the reference to sortColumn and sortType in the OM tree when using the tree debug window, and I've seen reference to it in topic:

https://4js.com/fjs_forum/index.php?topic=456.0 (https://4js.com/fjs_forum/index.php?topic=456.0)

However this only leads to more questions:

*) How can the rows be written easily to the database in the new sorted order? A lot of our programs have code that automagically renumbers a field of the array rows. ie traditionally, row 1 has 1 written to it, row 2 has 2 written to it, etc etc. Now, the physical order will not be reflected in this numbered column. Other users will not see the sorted sequence.

*) To be able to write the rows to the database in the order implied by sortColumn and sortType, would require writing code to physically order or step through the detail in that order. Even with the assistance of a code generator, it's not a reliable solution because:

*) after a user sorts by a column, they can then edit the value in the column and effectively make that row out-of-order compared to the sort order described by the sortColumn and sortType attributes. In other words, sortColumn and sortType do not make it possible to KNOW exactly which row is in which physical order.

*) the previous fault could be overcome by jumping the edited row to it's proper position within the sortColumn order, but that would make the display jumpy - not something I'd like to see.

*) Attempting to put the user on row(N) is very unintuitive.

*) Users expectation is that the rows would be physically ordered. But an AFTER INPUT check of the array for sequential ordering or non-overlap of some columns would issue logically correct errors that don't often make sense to the user.

I was expecting to see the rows physically sorted. I was experimenting to see what BEFORE/AFTER events get triggered before and after the sort. I was expecting to see one of two possibilities:

1/ no events need triggering because the user is moved to the same logical row. Therefore they're in the same field and row, so any AFTER FIELD and AFTER ROW would behave naturally

or

2/ The following events trigger

    AFTER FIELD
    [AFTER ROW]
    [AFTER INSERT]
    sort takes place
    user remains on the same physical screen row which is not necessarily the same logical row
    [BEFORE ROW]
    BEFORE FIELD

and if the AFTER events choose to issue NEXT FIELD then the sort is not performed.

Is column sorting feature only half-complete? Is there a setting I haven't found that requests physical sorting?

Andrew,

I confirm that the built-in sort is in fact a "visual sort": The rows in the program array are not touched, it's the rows data copied in the AUI tree which is sorted (note that only a page is sent). So for the programmer and for the dialog engine, the fact that rows are sorted or not sorted is transparent.

Of course sorting rows (visually) during an INPUT ARRAY makes only sense if the program array rows are independent from each other. If the order of the rows in the program array matters, sort should be disabled, and this can be done with the UNSORTABLECOLUMNS attribute of the TABLE container.

However you have raised important and interesting questions regarding program array sorting, we have this in mind.

Note there are other requests related to sort, such as:

- defining the default sort (#1499),
- being notified after sort (#16776).
- get the visual index of current row to display "current/total" info (#14740),
- multiple-column sort (#11736),
- program array sorting (#3491),
- take the control on sort events to write your own sort (using SQL for ex) (#7837),
- querying the sort info to order the rows in reports or fill the array with SQL (part of #7837)

Seb

Hello,
why we have the todays input or display array sort behavior? Sorting is a feature of the view. When sorting we never touch the model (the 4gl array). This has at least two good reasons:
- an array may be used in more than one display or input array statement. Each dialog may sort in a different way.
- a display array statement must not modify the the array - it's a display.

Yes, in some special cases it could be useful to sort the model. A new attribute like SORT ARRAY could easily solve your problem.
Example:
    INPUT myArray.* FROM myTable.* ATTRIBUTE(UNBUFFERED, SORT ARRAY)
    ...

The result would be: the array myArray is (physically) sorted as the view.

Rene

I see that all the questions about BEFORE/AFTER events evaporate with the current view-only sorting. Very simple! However, the pressure from our demo team (led by our master of customer-appeal) is to bring on universal column sorting. Switching it off in programs that could really benefit from a physical sort sounds like it would be unpopular and/or unacceptable. I'll have to meditate on people's expectations and have a chat with a few people to get a rough count of affected programs. The danger for me is, once word gets out that array-sorting is Very Close, the pressure will be enormous!


Thanks, I'll have a look at them for ideas. I have noticed that if I put a sequentially numbered noentry column (but it can't be a phantom) then the ordering of the rows is reflected partially in the //Table/TableColumn/ValueList for that column. But as you know, the 'partial' is because the value list only reflects what is visible to the user. Having the sort order for 10 out of 100 rows is not going to allow me to reorder them.

Instead of sorting by writing a temp table and reloading it (is that the idea in 7837?) it should be possible to grab the array with base.typeInfo() and then sort it using a generic routine. The only custom-code needed per array would then be something to extract the data back out of the sorted dom-tree and put it into the array again.

For me to make it automagical, would require hooking into every ON ACTION and AFTER event, rush off and do a physical sort, and then reset the sortColumn attribute. Would assigning a value to sortColumn and sortOrder cause immediate re-ordering of the visual data? Otherwise this idea would need a method call demanding that physical order is reinstated. This would probably have to be used like this:

    ON ACTION all_of_them
        call check_the_sorting()
        ......

function check_the_sorting()
    if reliably-detect-sorting then
        save sortColumn and sortOrder
        set sortColumn and sortOrder to null # back to natural order to match the physical about to be assigned
        ui.interface.refresh
        physically sort the rows according to the nominated column  # unbuffered will immediately display them
        set sortColumn and sortOrder back to the saved values    # even if it resorts, will match the new physical order
        ui.interface.refresh

or something like that. There's a fair number of what-ifs in there. I haven't even begun to look at the BEFORE/AFTER events. Honestly, I'm not sure this code could really fly. The best might be to offer a button to physically sort the data; I'm not sure that would be popular either.

Ahh well, more ideas welcome. I expect we're not the only people in the world who would have similar needs.

At the same time? Interesting; I wonder if I can think of any use for this...

True. DISPLAY ARRAY should not mess with the actual data. However maybe it needs to be an option? Otherwise, printing in the order the user has just sorted would not be possible. In such a situation, the programmer would have to know that the array must be thrown away or the unnatural data-change would leak out.

Yes, this could work, however it leaves you with the interesting challenge of deciding what to do about BEFORE/AFTER events. I still haven't decided which of my two original suggestions is the proper one. Perhaps leaving the user on the same logical row (ie moving them physically) is the only appropriate action, however technically, the current row HAS changed because it's sequence number is different. This may affect some programs that track and assign the sequence number. I haven't yet derived a "proof" that one approach or the other is flawed or preferred.

If the 2nd approach is used (fire the AFTER events, sort, fire the BEFORE events) then as far as the programmer and the runtime is concerned, the sort will take place when there is no "current" row, and it would be just like that moment when the user is between rows - ie between the last AFTER ROW and before the next BEFORE ROW. This sounds like a very safe state to be in when performing the sort.

All in all, a very interesting subject!

... to save you reinventing the wheel, have a look at my copy entire array to clipboard/excel routine http://sourcefourjs.googlecode.com/files/auitree_1_0.zip which does that, in particular the function init_row_map().  In that routine, if I paste the array into Excel, the order of the rows in Excel is exactly the order as they appear in the array.  (although I will conceed that I wrote the array before it was possible to sort an input array so the case where the user changes a value after the array has been sorted won't be catered for).

Also don't forget that stored settings (and the array sort order) are remembered between sessions.  So if the array is out of sort sequence when you write it, when you read it back into an array, it will be as per the tables sort sequence so it will be back in order.

One of the reasons for introducing the behaviour was that you may have a DISPLAY ARRAY and click a button to update (i.e INPUT ARRAY).  The sort functionality means that the order of the rows won't change as you do this.  Prior to this, the order of the rows in the INPUT would be different than in the DISPLAY.

Also with Excel if you sort on a column and then change a value, the order of the rows doesn't change.  You have to physically click on the sort button again to re-order the rows reflecting your change.  I'd argue that our behaviour is consistent with that.

Finally, a question for Pierre-Nicolas, if a column can't be sorted i.e is UNSORTABLE, there is no feedback to the user apart from nothing happening when they click on the column header.  Is there any visual clue we can provide to the user that this can't be done (much in the same way Drag'n'Drop alters the cursor when we can't drop)

Andrew,

My only advice for today: you should not (have to) implement tricky workarounds to get a nice sort feature in your lists. Don't start to dig in the AUI tree or try to fake the dialog to achieve what you want to do.

Until we provide you with the bricks to properly implement your sort, I would suggest that you keep INPUT ARRAYs without the default visual sort (use UNSORTABLECOLUMNS), and have a button / shortcut to open a secondary window to let the user select the columns to order the list. Then you can build the ORDER BY clause and re-execute the SELECT statement to sort the rows... Using SQL to sort rows has for me 3 major advantages: It uses the database collation rules, for large lists it is probably faster if indexes are on the table, and the user get fresh data that could have been modified by other users. In case of detail list (order lines), if a column defines the line position, you can also re-calculate the positions since you have the control.
You might not allow this SQL sort in some context: For example in INPUT ARRAY, when the user has started to modify the current row or has inserted a new row, the action to open the sort window should be disabled.
Further, as you have the total control over the sort information, you can save it and restore it for a next session.
Using a separate window to choose sort columns has another advantage to me: When it comes to multiple-column sort, the user can clearly see what columns have been already selected (with sort type asc/desc).

So yes, with the possibility that Genero BDL offers today, I would implement a "physical sort based on SQL".

Regarding #7837, the idea I raised was to allow programmers to overwrite the default (visual) sort, by taking the control with a new ON SORT trigger, which is fired when the user clicks on column headers. With 2 new methods the DIALOG could give you the clicked column and the order type (asc/desc). So you could implement the SQL sort (or any other sort method), with the standard user interface for sort (click on column headers)...

  ON SORT
       LET order_by_clause = " ORDER BY " || DIALOG.getSortColumnName() || " " || DIALOG.getSortOrder()
       -- Re-execute SQL to re-fill the array

Further, if you believe that SQL is not the right way, if we provide a sort method for dynamic arrays (#3491), you could directly order the rows in the model without executing SQL... we could also have a dialog option to get this automatically, as Rene suggested.

Seb

I like the idea of requesting the runtime perform the sort physically. I can't see the need to attempt to perform it myself, at least not for our style of code. Having a SORT ARRAY attribute available would be great. I would also like to have access to an event like AFTER SORT so that renumbering can be performed if necessary. Also it leaves it open for you to implement multi-column sorting (eg click col1, then shift-click col2) whenever it suits you; we would get access to it then.

I am convinced that before sorting, the runtime would have to perform the AFTER FIELD, AFTER ROW and AFTER INSERT as necessary so that the sort is performed in a "between-rows" state. Otherwise there would probably be issues coping with before/after logic if the physical row has moved to a different index. If the AFTER-events perform NEXT FIELD then it makes sense for the sorting to be cancelled/ignored.

This also suggests that a BEFORE SORT event could be useful; it might have the power to block the sort depending on conditions. However I'm not asking for it because it's probably too obscure for our needs.

Finally, please allow for physical sorting during DISPLAY ARRAY too. I know that DISPLAY ARRAY is not supposed to alter the data, but it's actually more subtle than that. For a start, it's not unusual to add ACTION's which effectively alter the data programmatically. Strictly speaking, DISPLAY ARRAY is NOT prevented from altering the data; only the end user is prevented from altering individual fields or adding/deleting rows without assistance from the 4GL code. Being able to physically sort a DISPLAY ARRAY is too powerful a concept to pretend it's not appropriate. Otherwise we'd be back to manual coding if it were needed. It makes sense to allow it if a SORT ARRAY attribute is used on DISPLAY ARRAY.

Do you have any kind of wild-guess time-scale for this to be added? Completely free of commitment of course! I'm just fishing for the possibilities for when it might become available.

Sorting the array (controlled by a new DISPLAY/INPUT ARRAY attribute) is not a big deal.
But: we may have different expectations when this should be done: immediately (my initial expectation)? Or in a "between-rows" state? The implementation in a "between-rows" state is much more difficult. Sure, a new trigger, called after the physical sorting has been done could be useful.

A remark about sequential sorting: this should be a built-in feature. My mail client is the apple mail app. If I'm sorting my mail box, sorting is always done sequentially: If i want to sort my inbox by from and date, I have first to sort by date, then by from. That's all.

BTW: when sorting the array (not the view) sorting would implicitly be done sequentially. You can not "unsort".