Sort Order on SQL server and 3.70

guidorobben

Hi,

I have a question. If I create a table that has a code filed as index, it will be nicely sorted. But when I use the same table on SQL server I have a problem with the sorting if I also use numbers. This is due to the fact that SQL server sees the field as a Text field. So it order it like this.

1
10
100
2
20

I can change the SQL data Type to variant. This will solve the problem partly. It will first show text and then number.. Example:

a
b
c
1
2
3

Another problem with this is, Everytime I create a field I have to set this Property. Further more. I cannot install a fob over the old database because the fields have to be empty for this.

Is there a simple solution to my problem??

Regards,

Guido

Luc_VanDyck

A simple solution:
Make sure that the length is always the same, like this:

001
002
010
020
100

guidorobben

We came up with the same thing. But we already have a lot of stuff, it will take a lot of time to convert. With all the hidden errors it might produce.

Thanx anyway...

ZackMacKracken

changed

Zoltan_Karpati

Hi All,

We have similar problems, I suppose. We changed our native Navision 3.60 to SQL version a month ago. (SQL collation, Hungarian dictionary sort order)
We'have encountered several problems originating from the difference in sorting:
- ACNO1..ACNO2 in account schedules behaves differently, as a result we get bad figures or circular reference
- we have payment terms, like CASH, CASH-DE, CASH-EN: we can change CASH-DE to CASH-EN, but we can't change CASH-EN to CASH because the system overwrites CASH with CASH-DE, etc. Generally speaking we have troubles with codes where one code starts with another code.

We tried Windows collation, too. In that case we had problem with sorting our double-letter tongues, like 'sz','gy'.

I don't know universal solution. And you?

Regards,
Zoltan

cernst

Add an extra field to the table and put some code in the trigger OnValidate in the original field that fills the new field with fixed length. For example if you have.
10
100
20
the new field could look like
0010
0020
0100
You also need to add the new field to the keys and use this key in the forms and reports you want to sort.
This way you don't have to "mess up" the original data.
The way SQL sorts fields in you can't change so you have to do something in Navision.

RobertMo

We have tried many things, but the best to avoid all this problems is to rename records. (even better to start at begining)
Use codes that begin with a letter and are all the same length. E.g. C0001 for customers, I001, M3001 for items (materials), SR04-0001 for documents, etc.
Avoid "-" and other chars in code or use the same format for all codes (e.g. all CASH-XX) Also avoid country specific chars.
It is the best approach if you ever need to change Native<->SQL or to change a collation in SQL...