Allow column sort by order in lookup table

Related products: Software Factory

Current situation



We have quite a few tables with sizes such as XS, S, M, L, XL etc. We now use a hidden sort column to sort these from smallest to largest size. When we want to apply this sort order in a new table we add the same hidden sort column by means of an expression so we can apply the same trick as in the lookup table.



Issue



This is somewhat cumbersome however and it also makes it impossible for users to do this manually since clicking on the header of the size column sorts these sizes alphabetically instead.



Proposed solution



I would like to see an option that when a user clicks on the column header the sizes are sorted according to the default sort option of the lookup table. This is quite similar to the default sort in a combo drop-down but instead applies to the column in a grid. I can imagine a drop-down in lookup options that allows to switch the grid sorting between "alphabetical" and "lookup table".
I would also appreciate some kind of custom sort mechanism, also on user content.



We often have part numbers containing alphanumeric characters. The sorting on this value is is 'string' based, so i.e. the sorting is then as following:




  • P1
  • P10
  • P2
  • P3

Where as a user would expect some alphanumeric sorting:


  • P1
  • P2
  • P3
  • P10

We currently work-around on this by having an extra hidden sorting column filling the numbers (P0000001) and then apply string sorting. But when the user clicks on another column(s), this sorting goes away (and you don't want to have this column visible, only use it for sorting).



Maybe a suggestion for this problem would be to assign a 'sorting column' to columns if necessary. This column must be a hidden column.



I.e. the column 'name' should be sorted based on the value of the column 'name_sort', which must be / or can be hidden.

The solution of using a hidden column is also necessary when wanting a different sorting of NULL-values. When sorting ascending the NULLs are on top, while sorting descending the NULLs are at the bottom.

This can be “remedied” by including an extra hidden column, but the preferred option (something that Oracle supports) is something like NULLS FIRST/LAST.


Hi Roland, would it be possible to move these lookup tables into the model as domain elements instead? Because of this hidden sorting column it appears to me that changes to such tables are rarely made at runtime, if at all. In the latest version of the Software Factory (2020.1) you can specify that columns with domain elements must be sorted by element order number, which kind of works like your hidden sorting column.

If for example you need to store extra information for each size, you can still use a lookup table, and use the column with domain elements as primary key, and sorting still works as expected.

Hope this helps!


 Hi Henk, this will only work if the sorting of elements is solved (see also  https://community.thinkwisesoftware.com/ideas/422).

 


Hi André, that idea was indeed implemented, in Software Factory 2020.1. See also: https://docs.thinkwisesoftware.com/blog/2020/01/01/2020_1.html#breaking-change-domain-element-sorting


This idea has been implemented like Henk said. Lookups can be sorted alphabetically and by order no.

 

Updated idea status OpenCompleted