Sorting Special Characters

headley27

It was just brought to my attention that some of the records in our Item table aren't sorted 'correctly'. It seems that hyphens (and perhaps other special characters )are being 'ignored'??

For example, I would expect a sort like this:
AX-SERV
AX-SUPER
AXSJ-123

But Navision is sorting like this:
AX-SERV
AXSJ-123
AX-SUPER

My guess is that Navision sees:
AXSE...
AXSJ...
AXSU...

Does anyone know why this happens and/or know of a workaround (other than renaming our item numbers)?

Thanks for you help.

nunomaia

Navision doesn’t use ASCII order, uses different an alphabetical order.
And somes chars are placed at end of order. That is your problem.

headley27

Thanks nunomaia.

I could accept and understand this sort order - hyphen before alphanumeric:
AX-SERV
AX-SUPER
AXSJ-123

I could accept and understand this sort order - hyphen after alphanumeric:
AXSJ-123
AX-SERV
AX-SUPER

What confuses me though is that Navision seems to be 'ignoring' the hyphen (although it is recognized when filtering or using find):
AX-SERV
AXSJ-123
AX-SUPER

Navision goes to AX-S, then to ASXJ and then back to AX-S.

Is there any documentation that explains this logic further?

Thanks again.

ta5

Classic or SQL-DB?

nunomaia

headley27 wrote:

Thanks nunomaia.

I could accept and understand this sort order - hyphen before alphanumeric:
AX-SERV
AX-SUPER
AXSJ-123

I could accept and understand this sort order - hyphen after alphanumeric:
AXSJ-123
AX-SERV
AX-SUPER

What confuses me though is that Navision seems to be 'ignoring' the hyphen (although it is recognized when filtering or using find):
AX-SERV
AXSJ-123
AX-SUPER

Navision goes to AX-S, then to ASXJ and then back to AX-S.

Is there any documentation that explains this logic further?

Thanks again.

I remember some time ago reading that indo. I try to find out, and then I will inform you if I found sorting order.

headley27

If you do find it....great, if you don't....no worries.

Don't look too hard.

It obviously isn't a big problem, just an annoyance.

Thanks again.

David_Singleton

ta5 wrote:

Classic or SQL-DB?

Headly, unless you answer ta5's question, the rest is meaningless.

If SQL, then sorting is determines by the code page you used when you created the SQL database. So if you are on SQL, then we need to know which Code page you chose when you created the database.

If you are on Native, then sorting is determines when you create the Navision database according to the STX file you use. So if on Native you need to know which STX file you were using when you created the database. This will be the file fin.stx that was copied from the language folder (probably form ENC in your case.)
In the STX file look for SORT variable, 00043-00110 to find out the order for special characters. If you used Canadian English, then "-" is considered as sorted after "Z" so its not "ignored" its just further down in priority.

Mbad

Excellent David.

headley27

Thanks David and ta5.

I missed the 'Classic or SQL-DB?' post.
I'm using SQL.

This now makes sense to me.
I need to change the collation of the database in question.

Thanks again.

themave

we are in the automotive industry and our parts numbers have dashes, dots, forward slashes, backward slashes and spaces - . / \ " " ect

to get a consistance sort, we use the no2. field in Navision that contains a squashed number as we call it. so we strip out all the special charaters. I made a key for the no2 field and we use that for most reports ect. we are on the native database. it is an easy work around.

We also get the result you state above

But Navision is sorting like this:
AX-SERV
AXSJ-123
AX-SUPER

the explanation above doesn't explain that sort. the AX- numbers should be together