chart of accounts

nishandesai

HI friends. I wanted to know one thing, in charts of accounts, if we enter any line after 9999 then suppose if i enter 10000 then it comes right after line 1000 that is the first line and not after 9999 where actually i need it so is there ne method where we cn lpace it after 9999, n if no i wanted to know one thing.. suppose i have a group from 1020 to 1050.. toal is from 1020 to 1050. The entry 10020 is placed right after 1020 so will that interfere in my total or it will not.


Regards
Nishan

Timo_Lässer

Navision Online Help wrote:

Sorting of Numerical Values in Code Fields

The following information is helpful if you use or are planning to use the SQL Server Option for Navision. We also recommend that you read this information if you use Navision Database Server and want Navision to sort numbers correctly when you view data with external programs.

How Number Sorting Works
Code fields in Navision can contain both numerical values and text strings. Navision ensures that numbers kept in code fields on Navision 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 Navision sorts the data, comparisons are made character by character and not by comparing the numerical content of the strings.

Numbers that you keep in code fields on SQL Server using the Varchar SQL data type are not sorted in the correct numerical order. The SQL Server Option for Navision sorts the numbers as if they were text strings. The following table illustrates the differences that occur:

Numerical   Text
Sorting     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 as consisting 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 is the solution that we recommend because you can now 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. Users will quickly get used to entering numbers that have a fixed length, and the numbers will be sorted correctly.

A001
A002
A003
...
A999

You cannot apply this solution if you are using the numerical series feature in Navision. Further, even if you are not using the numerical series feature, this solution makes it very difficult to convert your data to the Integer SQL data type.

Define a numerical series as consisting of a definite number of digits that start with zeros, for example, 001-999.

We do not recommend this solution because there are several inherent drawbacks. Firstly, the user tends to ignore the zeros and to refer to the first number as 1. Users may, therefore, omit the zeros when entering numbers. Secondly, the numerical series feature in Navision does not permit numbers that start with zero. Furthermore, the SQL Server Option for Navision 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 normally 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 follow the principles described below:

• 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 in Navision. Here is an example:

• 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.

nishandesai

HI Timmo, Thanks fpr the document. I got the things cleared
Cheers
nishan