TABLE custom sorting

[Huy H.]

Hi,

We have a TABLE that's being displayed using DISPLAY ARRAY (in unbuffered, non-paging mode).  One of the column is of alpha numeric type, where values are a combination of number and letters.  E.g. 1A, 1B, 1C, 2A, 2B .., 10, 11, 12A, etc...  When we let the frontend handle he sorting, it's sorting alphabetically: 1A, 1B, 1C, 10, 11, 12A,...2A, 2B...

We have been able to work around this by creating a PHANTOM field in the array to pad the values with "0"s to get the values to sort "naturally". 

The problem is that in UNBUFFERED and non-paging mode, we do not have any control over what the program should use to sort this column.  Our current work around is converting this DISPLAY ARRAY to paging mode (ON FILL BUFFER), so that ON SORT method, we resort the array using the PHANTOM field.  We think this is a bit of a hack.
Is there a feature/attribute where we can set the sorting column for a given table column -- similar to the IMAGECOLUMN attribute?

[Sebastien F.]

Hello Huy,

Thanks for this suggestion.

Little precision: It's not the front-end which sort rows: the front-end asks the fglrun to sort rows.

I believe it should not be difficult to implement a kind of "SORTCOLUMN" attribute to associate another column for sorting.

However, this opens the door to any type of ordering and the end user might be confused if the visible values are not sorted in a natural way.

I see following options for now:

1) Add another (visible) column with a plain ordinal integer, that can be used to sort rows.

2) In the existing column, prefix the value with an ordinal number (length of prefix matters as this is lexicographic sorting!):

    (101) 1A
    (102) 1B
    (103) 1C
    (104) 10
    (105) 11
    (106) 12A

3) Compute the max length and prefix values with zeros to make all values have the same length (in SQL producing the result set, if possible):

However, in this case you get pure numeric values before alphanumeric values (0010 before 001A)

    0010
    0011
    001A
    001B
    001C
    012A
    100A

4) If this ordering is the most common used, save end-user from the mouse click and prepare the result set before the DISPLAY ARRAY, with SQL ORDER BY <your-computed-column>.

...

Seb

[Huy H.]

Thank you Sebastien for the clarification and the suggestions.  We've implemented similar patterns in our code to create these phantom "sortcolumn" fields to avoid using alphabetical in situations where the field is mixed with numbers and letters.  Interestingly enough, we often get complaints from users that the sorting was not as the expected due to the fields being alpha numeric.  We've been getting away with it by saying that because it's trying to sort it alphabetically, but it's a hard sell.

While I was reviewing a related issue on our system, we found another work-around for this issue without using the ON FILL BUFFER mode.  If we put our DISPLAY ARRAY in a WHILE dialog, when the user sorts this particular field, we sort the array using the "sortcolumn" field, then EXIT DISPLAY then come back in again to re-synch the array with screen index:

Code (genero) Select Expand


WHILE choice = K_CONTINUE
  DISPLAY ARRAY m_arr TO s_arr.*
    ON SORT
      ...
      CALL m_arr.sort("sortfield", FALSE)
      LET choice = K_CONTINUE
      EXIT DISPLAY
  END DISPLAY
END WHILE 


[Huy H.]

Having said all that, I still think there is a value to having a SORTCOLUMN attribute or something similar to control how sorting should be done by the DVM.  We have many instances in our application where the sorting is not always alphabetical.

[Bryce S.]

Hi, I thought I'd add our 2c worth to this discussion, even if a little late...

We would like to be able to use sortable columns in a number of places but don't, currently we have to set UNSORTABLECOLUMNS as order on the displayed column is not right.

For instance, we display a horses handicapping 'rating'. This is like 'R40, R41, R42, ..., R99, R100, R101,...
So a rating of R100 is higher than R99 but it won't come out like that if user sorts on this column. Ideally a phantom field with the sort order associated to the displayed column would be ideal I think  (phantom: R099, R100, etc. for sorting).

We have the same thing with horse names, our database has a column for sort order for these names so they come out correctly, it would be nice to assocociate the sort_name with the display_name column in the form for ordering.
e.g.   'Le Barbier' comes after 'Leaway' because the space is not used in the sort order, and people find horses in lists where they expect them. A natural character sort would put Le Barbier before Leaway.

(I think Melanie has a request in the system for this sorting issue still).

Regards,
  Bryce Stenberg.
  Harness Racing New Zealand Inc.