Numbering in a code field in calssic and sql

iqbalmad

Hi everyne,

In navision classic (local database), if you put a number in the code field, it looks like this:

1, 2, 3, 4, 5, 6 , 7 , 8, 9, 10, 11,......

In navision classic (sql database), if you put a number in the code field, it looks like this:

1, 11, 2, 3, 4, 5, 6 , 7 , 8, 9, 10, ......

why is that?

Slawek_Guzek

This is the difference between SQL Server sorting and classic NAV database sorting.

NAV sorts text/code fields having a numbers only in them like a numbers. SQL sorts them as text.

iqbalmad

is there a way to change this sql server sorting?

NavNab

Usually we use codes like 001, 002, 003, 004..., 010, 011... to have the correct sorting in SQL.

Slawek_Guzek

There is, but there are also negative consequences of doing that

To change the sorting order go to field's property, find SQL Data Type property, and change it to an integer. However, you will only be able to do so if all current data stored in the column can be converted to an integer. Also once done you will not be able to store in the field anything but an integer - that's the first, immediately visible drawback.

Another potential impact may be the performance, this may not surface straight away.