Collation is a problem in sorting when you have characters like "ÅÄÖ". Those are sorted incorrectly.
Platform thinks that those ÅÄ = A and Ö = O, but that is wrong. Those should be after Z..
..TUVWXYZÅÄÖ
Idea how to fix this:
- possibility to set collation in lifetime on platform level (this sets platform default level collation)
- possibility to set collation in application (module properties > overwrites platform level collation)
When user sets collation either ways, platform adds automatically database searches (SQLs + aggregates) COLLATE clause after TEXT attributes.
Maybe add possibility to remove use of it in aggregates (checkbox: use collation [x]).
SELECT {Sorting}.*
FROM {Sorting}
WHERE
{Sorting}.[Sortedcolumn] LIKE '%'+ @SearchString + '%' COLLATE Finnish_Swedish_CI_AS
ORDER BY
@TableSort COLLATE Finnish_Swedish_CI_AS
Another possibility is to set collation on server or directly database, but this is probably not possible in AWS?
See community discussion and current workaround:
https://www.outsystems.com/forums/discussion/63776/incorrect-sorting-of-scandinavian-characters/#