This topic explains how items, such as documents, are numbered in Microsoft Dynamics NAV. The information is helpful if you use, or are planning to use Microsoft Dynamics NAV with Microsoft SQL Server. This topic is also helpful if you use Classic Database Server and want Microsoft Dynamics NAV to sort numbers correctly when you view data with external programs.

Number Sorting

Code fields in can contain both numerical values and text strings. This ensures that numbers kept in code fields on Classic Database Server are sorted in the correct numerical order. However, this does not necessarily happen when you use external programs to access the same data. External programs may view and sort these numbers as text. This means that when data is sorted, comparisons are made character by character, and not by comparing the numeric content of the strings.

Numbers that you keep in code fields on SQL Server using the Varchar SQL Data Type Property are not sorted in the correct numerical order. Microsoft Dynamics NAV with Microsoft SQL Server sorts the numbers as if they were text strings. The following table illustrates the differences that occur.

Numerical sorting	Text sorting
1	1
2	10
3	100
4	2
10	3
100	4

To avoid this problem, we recommend that you use a numerical series that has a fixed length. You can do this in three ways:

Define a numerical series that consists of a predefined number of digits that start with a digit other than zero, for example, 100-399 (300 numbers). If this numerical series is too short for your requirements, you can start a new numerical series, for example, 40,000-69,999 (30,000 numbers). If this numerical series is too short, you can start a new one such as 7,000,000-9,999,999 (3,000,000 numbers). Users will quickly get used to entering numbers that have a fixed length, and the numbers will be sorted correctly.

1001

1002

1003

…

9999

This solution enables you to define the SQL data type as being either Varchar or Integer and the sorting will still be correct.
Define a numerical series that consists of a predefined number of digits and that starts with a letter, such as A001-A999. This series will be sorted correctly. When the series is complete, you can define a new series by starting with a different letter. The users will quickly get used to entering numbers that have a fixed length, and the numbers will be sorted correctly.

A001

A002

A003

…

A999
Define a numerical series that consists of a predefined number of digits that start with zeros, for example, 001-999. We do not recommended this solution because users tend to ignore the zeros and to refer to the first number as 1. Users may, therefore, omit the zeros when entering numbers. Also, the numerical series feature in does not permit numbers to start with zero. Furthermore, Microsoft Dynamics NAV with Microsoft SQL Server will not allow you to save numbers that are defined according to this system as the Integer SQL data type.

Important
As a general rule, data types used in fields that are related to each other must be compatible. Therefore, when you use a SQL data type in a field, you will usually have to change the SQL data type settings of related fields in other tables. For example, in the General Ledger application area, if you change the SQL data type of the No. field in the G/L Account table from Varchar to Integer (or if you change the data type from Code to Text), you must change the data type of the G/L Account No. fields in the G/L Entry and G/L Budget Entry tables to the corresponding data type. Failure to do so results in the display of incorrect totals, based on these tables, in the chart of accounts and elsewhere.

Important

As a general rule, data types used in fields that are related to each other must be compatible. Therefore, when you use a SQL data type in a field, you will usually have to change the SQL data type settings of related fields in other tables. For example, in the General Ledger application area, if you change the SQL data type of the No. field in the G/L Account table from Varchar to Integer (or if you change the data type from Code to Text), you must change the data type of the G/L Account No. fields in the G/L Entry and G/L Budget Entry tables to the corresponding data type. Failure to do so results in the display of incorrect totals, based on these tables, in the chart of accounts and elsewhere.

Numbering Principles

To ensure that numbers kept in code or text fields are sorted correctly, irrespective of which database server you are using, you must use the following principles:

Always use a numerical series that has a fixed length, for example, 100-399.
Never use a numerical series such as 1-999 in code or text fields.
Never use a numerical series such as 001-999 in code or text fields.

Filters

If you do not follow the numbering principles, problems will arise when you apply filters that involve numbers.

An example of this is if you have not used a numerical series that has a fixed length, when you apply a filter, for example, 10...20, the result will be 10,100...20.

When you follow the numbering principles, you must remember to use these for filters that you apply. Here are two examples:

If you do not follow the numbering principles when you apply a filter, for example, 2...10, the result will contain no records. This is because 2, comes after 10.
You have followed the numbering principles and are using three-digit numbers. If you forget to follow the same principles when you apply a filter, for example, 10...20, the result will be 100,101,102...199.