Microsoft Dynamics AX (Archived)

Sorting of String type which has Numeric values

(0) Share

Report

Posted on by Community Member

Hi All,

I have customers in ax which is a number sequence

when I pull the values, they are coming as

C0000109
C00001092
C00001093
C00001094
C00001095
C00001096
C00001097
C00001098
C00001099
C0000110
C00001100
C00001101

The sorting order is not happening properly...

Index is already on Customer ID

I want the output as

C0000109
C0000110
C0000111
C0000112
..

C00001090

C00001091
C00001092
C00001093
C00001094

and so on

Please help to get the proper output

Thank You,

G Praveen Kumar

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

dolee 11,279 on at

Like (0)

Report

RE: Sorting of String type which has Numeric values

Hi Praveen,

It's a string field and it will sort as a string. Usually the trick is to make sure the customer id length is always the same. So you should have C00000109 instead of C0000109.

Now what you can do instead is add a new column in the table for sorting and maintain proper values there.

e.g. the new column will always contain the numerical equivalent of the customer account. (C0000109 -> 109, C00001093 -> 1093)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Sorting of String type which has Numeric values

Thank you Dominic, that's a good Idea, I can create a new column as you said and sort on that to get the correct data.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Sorting of String type which has Numeric values

Hi Dominic Lee,

As i am also facing the same issue, 1 string field when trying to sort it's not working properly.

As per your suggestion i can't create 1 more Extra Column in my EBCleanorderBookedsheduled table, Is there any other option to achive it.?

Was this reply helpful? Yes No
Martin Dráb 236,570 Most Valuable Professional on at

Like (2)

Report

RE: Sorting of String type which has Numeric values

Please don't say "it's not working properly" - it sorts those text values exactly as it should.

If you can't fix your data, an alternative solution is creating a view with a computed column. There you can add an integer computed column, extract the numeric part from your string and sort the view by the computed column (as a number).

By the way, if referenced tables use the surrogate key to refer to this table (and you don't have any external references), changing your text values should be easy.

Was this reply helpful? Yes No