Sorting a list alphabetically in microflow

0
I'm sorting a list of objects in a microflow alphabetically (ascending) on a string attribute. The result is strange: Antiquiteiten Goederen Inboedel Inventaris Lijfsieraden Opruimingskosten Verzamelingen computers elektronica huishoudelijk As you can see, somehow a distinction is made between values starting with a capital letter and those starting lowercase. This result occurs both on screen as well as in a pop-up which iterated over the sorted list. Sadly, it is not an option to make all values start with a capital letter or lowercase, since the distinction is important to the users. Is this a bug? Or am I missing something? (I've tested in Mx 5.6.2 and Mx 5.17) Thanks for any help.
asked
3 answers
2

I have seen this also, and I think it is due to how Java sorts. Java sorts on the underlying hexadecimal values of the characters. As the hex value for capital A is lower than the hex value for lowercase a, you will see string values sort based on this.

Solution would be to have another column in your entity in which you store the same strings, only in all lowercase or call uppercase. Then use this column for sorting, use the original column for display.

answered
2

The example that you are giving here makes sense, and there are databases that actually sort that way too. Where a capital letter is considered different from a lower case letter, and placed separate from the lower case version.

When looking at sorting in database retrieves, the Mendix platform currently has no influence over the sequence in which the values are returned. It does instruct to sort ASC or DESC, but the database determines if ASC means that we are sorting (empty)ABCDabcd or AaBbCcDd(empty), or any other combinations.

The database determines what the sort order is based on the locale and collation (this applies to both MS SQL and Postgres, I've never researched the other supported DB types). In MS SQL you could even change this, with Postgres you have little influence over what is happening.
Microflow sorting is done in Java/Scala, from some quick searches on google it seems that scala sorts case-sensitive. That would support your conclusion.
If this is a bug or not, I'm not sure. I haven't given it much thought yet, it is more about expectation and being sure of what happens. And would require some research and testing. I would definitely recommend entering a ticket request that all cases are researched and documented. Because I don't think anybody would be able to predict with absolute certainty what the sorting would be of a dataset in MF/DB on different environments (even OS and language could have impact on this behavior).


On a side note, keep in mind that a word such as "één" might not even be positioned between the 'e' and 'f'. But those scenario's aren't to likely (there aren't to many words that start with a special character and are use for sorting). To determine the exact behavior in microflow and on the different databases would require some testing.

answered
1

Create a sort attribute in your entity and make sure that it is filled entirely lowercase or uppercase. Either in a before commit event or when the user saves the object on a page. This allows proper sorting on grids and listviews too.

If your data does contain special characters like é, replace these with the corresponding regular character in your sort attribute.

answered