Numbering in a code field in calssic and sql

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?

Answers

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • iqbalmadiqbalmad Member Posts: 179
    is there a way to change this sql server sorting?
  • NavNabNavNab Member Posts: 181
    Usually we use codes like 001, 002, 003, 004..., 010, 011... to have the correct sorting in SQL.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.