Alphanumeric sort of CODE fields

Is there a way to get Navision to sort a code field “correct”? From my understanding the term “IBM001” should before “IBMA01” and after “IBS000” for example. Navision seems to sort all numbers (regardless of length) first, then all alphanumeric startint with a letter and at last all alphanumeric starting with a number. so an entry like IBMGEN gets before IBM001 … do you know a way to change this behavior ? Best Regards, Arno Pucher example:

Navision  "Correct Order"
-------------------------
1	  1
11        1
1         11
A         1A
AA        1B
A00       A 
A1A       A00
A1B       A11
A11       A1A
B         A1B
C         AA
1A        B
1B        C

The online help contains a chapter on this topic, you can find it under “C/SIDE reference guide” → “Information” → “Sorting of Numerical Values in Code Fields”. I’m afraid you will have to live with it, though. Code fields, after all, contain text and are treated as such. [:(]

This is not a Navision problem but SQL Server problem. If you use a native Navision database the sortorder will be correct. Regards Janus

quote:


Originally posted by rossing
This is not a Navision problem but SQL Server problem. If you use a native Navision database the sortorder will be correct. Regards Janus


[B)] Unfortunatly this IS native Navision (version 2.60A) with SQL Server I could change the sort order …

This is nothing to do with SQL Server, and by the way, for the Code field there is no real ‘correct’ just expected or unexpected ordering depending on your opinion. The Code field is mainly intended to separate pure numeric values from non pure numeric values (those that contain anything other than a digit). The pure numerics are sorted as numbers, grouped together and placed first out of these two categories, by Navision server. Conversly the similar data type in SQL Server - the sql_variant - will place the pure numerics in a group (they call a family) after the character data. These are just conventions since there is no ‘correct’ standard for grouping different types of data in this kind of complex data type. In terms of ordering the non-numerics, this is where it comes down to collations (or, if you like, code pages and sort orders). In Navision server this can be modified by changing the sorttable entry of the stx file, which is fine for single characters (in SQL Server, as an example, this is governed by the chosen collation for your chosen locale, and includes double characters ordering as single characters and vice-versa - therefore for more flexibility). But most of use are used to digits sorting before letters because of the good old ascii table, which most collations are based on.