68
Views
12
Comments
Incorrect sorting of Scandinavian characters
Question

Hi there,

I am the new guy who has all those weird problems..  

We have this sorting problem that characters like "ÅÄÖ" are sorted incorrectly like you see in this screenshot. Correct order is shown on Order-column and default cloud platform setup handles these characters incorrectly.

Platform thinks that those ÅÄ = A and Ö = O, but that is wrong. Those should be after Z..

..TUVWXYZÅÄÖ 


Added Sort.xlsx file, if someone wants to try to fix it.

I think the database locale should be changed somehow to use UTF8, but no idea how. 

Does anybody knows how to get it right? 


Cheers,

-JP

Sort.xlsx

mvp_badge
MVP
Rank: #17

This seems to be a workaround, if you cannot change collation to SQLServer, but it would be nice to get instruction from Outsystems how to get it right to DB level (e.g. Outsystems public cloud environment).

mvp_badge
MVP
Rank: #17

Hi Jukka,

I will drop your question at OutSyatems.

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Jukka,

I will drop your question at OutSyatems.

Regards,

Daniel

Hi Daniel,

It seems that it is not possible to change COLLATION to Outsystems Public Cloud. You have to get those own servers and self managed environment that you can customize server collation and DB collation.

This was pretty sad news to us and all Scandinavian companies, because we get partially broken platform from public cloud setup. Only way is to do that sorting manually.

Hope they add option to set up collation to server on those build up scripts some day.

BR,

-JP

 

mvp_badge
MVP
Rank: #47

Hi Jukka,

I´ve never try it, but I found some tips to use this kind of SQL replacing your collate. Could you please try it in your queries to see if this works?

SELECT {Sorting}.* FROM {Sorting} ORDER BY @TableSort COLLATE YOURCOLLATENAME_CI_AS

Fábio Fantato wrote:

Hi Jukka,

I´ve never try it, but I found some tips to use this kind of SQL replacing your collate. Could you please try it in your queries to see if this works?

SELECT {Sorting}.* FROM {Sorting} ORDER BY @TableSort COLLATE YOURCOLLATENAME_CI_AS

True - that is the "workaround" I am using, but I cannot use e.g. aggregates at all, if I have to search or sort by any text, which includes those Ä,Ö,Å,... characters.

I did it like this..

SELECT {Sorting}.*
FROM {Sorting}
WHERE {Sorting}.[Sortedcolumn] LIKE '%'+ @SearchString + '%'  COLLATE Finnish_Swedish_CI_AS
ORDER BY @TableSort

 That @TableSort includes that same collation clause that WHERE has (see that screenshot from List_SortColumn).


.. but it still makes it that a little bit harder to all who has those special characters.


Cheers,

-JP

mvp_badge
MVP
Rank: #17

Hi Jukka,

Question, where you able to solve your problem with the help of OutSystems support?

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Jukka,

Question, where you able to solve your problem with the help of OutSystems support? 

Regards,

Daniel

Hi Daniel,


Unfortunately they told me that it is not possible to change collation to Outsystems Public Cloud. 

Only way to handle this is to do it in SQL (advanced query).


They recommended to create development idea for them and get support for it. Haven't do it yet.


BR,

-JP

Rank: #14441

Hi,

Unfortunately similar problem occurs when you filter on ÅÄÖ in an aggregate. You will also get words containing A and O...

// Rolf

Hi Rolf

That is true. That was my original case how I noticed it. 

You have that workaround by using SQL(advanced query) and e.g. "COLLATE Finnish_Swedish_CI_AS" in WHERE and ORDER BY clauses after text attributes.

-JP

mvp_badge
MVP
Rank: #17

Hi,

Thanks for your feedback.

Ok I mark your answer as a solution, not that it is a solution, but people will be better able to see that it is not possible, and maybe later you share the link of the idea in this discussion, so people can vote on it.

You will have my vote, as I can imagine for Scandinavian customers using the OutSystems Cloud, this is limiting the usability of OutSystems.

Regards,

Daniel

Hi @Daniël Kuhlmann,


Took longer than I thought to get the development idea done, but now it is there. I thought that my solution might be easier to implement than DB or Server level configuration change.

https://www.outsystems.com/ideas/10187/possibility-to-add-db-collation-on-application-and-platform-level

Maybe you could drop a like there.


BR,

-JP