With the sorting functionality in Microsoft Dynamics NAV 2016, you can decide the order in which information is displayed. For example, if you have many customer ledger entries, you can choose to order them by one of the following methods:
- Entry number only
- Customer number first, then by posting date
- Document type first, then by document number
- Document number first, then by customer number
The sorting options are predefined, and there are many for each table in Microsoft Dynamics NAV 2016. Each sorting option (or combination of sorting options) is called a key. You can see all the keys for a page through the Customize This Page functionality or by clicking the drop-down arrow next to the Sorting field in the page header.
By choosing a different sorting option, you can view the lines in a different order. In the following examples, the first image shows Sales Invoices sorted in Ascending order, and the second image show Sales Invoices sorted in Descending order.
Sorting Order
As soon as you have chosen a sorting key, Microsoft Dynamics NAV 2016 sorts the field contents of the selected columns according to the following order:
- Numbers only without blank spaces or special characters.
- Letters only.
- Numbers and letters combined.
When numbers and letters are combined, the field contents are checked character by character from left to right. The sorting priority is as follows:
- Blank spaces come before characters.
- Letters come before numbers.
- Numbers come before special characters.
The sorting priority results in the sorted list shown in the following table:
| Order | Reason |
| 1 | Numbers without blank spaces or special characters come before letters or combinations of numbers and letters, and Microsoft Dynamics NAV 2016 counts in ascending order. |
| 11 | |
| 12 | |
| 111 | |
| A 1 | This is a combination of letters and numbers. It comes before other such combinations because the blank space between A and 1 comes before anything else. |
| ABC | Letters come after blank spaces (so ABC comes after A 1 above). |
| AB1 | Letters come before numbers in letter/number combinations (so ABC comes before AB1). |
| A1 | Numbers are sorted after letters (so A1 comes after AB). |
| D S B | D comes after A, and blank spaces come before a character. |
| D1 | Numbers are sorted after letters (so D1 comes after D S B). |
| D.S.B. | Special characters come after numbers. |
| 1A | A number at the start of a combination of letters comes next to last in the sorting order. |
| +A1 | Special characters come last in a sorting. |
You must be familiar with sorting rules so that filters can be entered correctly. For example, the filter A100..110 may not result in any data being found because the interval cannot exist. The result of an incorrectly used filter can be incorrect or even no information.
To make sure that numbers are sorted correctly, follow these principles:
- Always use a numeric series of a fixed length (100 to 999).
- Never use a numeric series such as 1 to 999 in code or text fields.
- Never use a numeric series such as 001 to 999 in code or text fields.
The SQL Server Option for Microsoft Dynamics NAV 2016 sorts numbers as if they were text, as illustrated in the following table:
| Numeric Sorting | Text Sorting |
| 1 | 1 |
| 2 | 10 |
| 3 | 100 |
| 4 | 2 |
| 10 | 3 |
| 100 | 4 |
To avoid the mentioned issues of different sorting logic in SQL, follow these guidelines:
- Use a numeric series that has a fixed length by specifying a numeric series that has an either a predefined number of digits or starts with a number other than zero.
- Manually follow the numbering principles and use a fixed number of digits.