Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Sort Swedish Alphabet ...
Finance | Project Operations, Human Resources, ...
Answered

Sort Swedish Alphabet and English Alphabet in X++ code

(3) ShareShare
ReportReport
Posted on by D365FO Avatar 396
HI Folks,
 
I need to sort the items name which contains English & Swedish Alphabets Å, Ä and Ö.
 
I have put order by asc in my select statement then below output is coming which is incorrect as all the Swedish alphabets should come in the end.
 
Statement-
select Description, * from ABCItem
order by ABCItem.DESCRIPTION asc
Output-
Å
Ä
A
Ö
O
T
U
 
 
Excepted output-
A
O
T
U
Å
Ä
Ö
 
 
So how can I get this output, is it possible to do this in D365 FO through code. If yes then how?
 
Thanks!!
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    In X++, the only two ways I see are direct SQL and sorting in the application layer. If you don't have to do it in X++ and you can use, say, a report, you have some extra options.

    Regarding direct SQL, a better alternative to do the whole query there may be creating view with a computed column and using ROW_NUMBER() OVER.
  • D365FO Avatar Profile Picture
    D365FO Avatar 396 on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    Yes Thanks Martin, its worked in SQL Server, but as you suggested it's not possible with D365 FO code yet.
     
    So as of now, by simply there is no such feasibility in D365 FO without intervention of Microsoft.
     
    Right?
  • Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    The correct syntax is something like ORDER BY MyField COLLATE Finnish_Swedish_CI_AS.
  • D365FO Avatar Profile Picture
    D365FO Avatar 396 on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    Hi,
     
    I got your point but I'm still not cleared whether I can differentiate the Swedish and English character or not.
     
    As I gone through below Microsoft Doc for Collation and Unicode support. Here there is nothing mentioned how I can collate Swedish characters.
     
     
    Below SQL statement is also not working, If I try to run it in SSMS.

    select Description, * from ABCItem
    order by Swedish COLLATE 
     
    How can I check this in my SQL server?
  • Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    Which of the four statements you'd like me to elaborate?
  • D365FO Avatar Profile Picture
    D365FO Avatar 396 on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    Hi Martin,
     
    Thanks a much for your response.
     
    Could you please explain it more what you mean by this statement-
     
    It's also possible to use a different collation for a query (ORDER BY COLLATE), but there is no support for it (AFAIK) in F&O, therefore you'd need a direct SQL statement. You can also request such a feature.
     
  • Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    Sort Swedish Alphabet and English Alphabet in X++ code
    Sorting rules are defined by the collation used by the database server.
     
    One option is just accepting the situation, even if you don't like it.
     
    Another would be changing the DB collation, if Microsoft allows it (you can't do it in production on your own). But it would still be a problem if you have users that don't use Swedish. And even if you don't have today, your company can expand tomorrow.
     
    It's also possible to use a different collation for a query (ORDER BY COLLATE), but there is no support for it (AFAIK) in F&O, therefore you'd need a direct SQL statement. You can also request such a feature.
     
    The last option is fetching all values from database and then sorting them in the application layer, where you can do whatever you want.
     
    Maybe someone has more ideas...

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,791 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,488 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans