Navision Financials with SQL

infox · 2000-08-09

We have a pretty big problem and our Navision Solution Center was not able to help us yet. We have installed the Database on the SQL 7 Server. The installation was done according to the manual. I was able to reproduce this error on the Cronus Database. It is as follows:
If you go into the Customers and press F5 and select a filter on
Customer Number 30000 .. 39999 following selection will be made:
30000, 31505050, 31669966,31987987, 32124578, 32656565, 32789456, 34010100, 34010199 etc. i.e. actually all Numbers starting with 30000.
Does anyone have a good idea on how to solve this problem! This kills my project!
It would be great if you could help me.

Thank
Infox

Dave_Cox · 2000-08-10

I think this can be corrected by Navision A/S by them editing the stx file.

We had a Customer who did not like the way Navision sorted its list compared to his Access database the sort order was Different in Navision.

I am not sure about the SQL Version but it seems from your mail that the SQL Version used SQL Indexes which are sorted Different from the Navision Keys.

Have you compared the Customer Sort Order between Standard Cronus and SQL Cronus?

MindSource (UK) Limited
Navision Service Partner

david@mindsource.co.uk
info@mindsource.co.uk

[This message has been edited by Dave Cox (edited 09-08-2000).]

Dave_Cox · 2000-08-10

Infox

I copied the Cronus Customer List to the clipboard
Pasted it into Excel
Created a Access Database and Imported the Spreedsheet
When I ran the table the Sorting Order was as you described
In your first posting.

Native Navision keys sorting Data Different from MS Indexes.

The only option is to enter or change your Customer Numbers to group them as you require.

If Navision A/S agree to edit the stx file this will result in the Navision Keys being the same as the MS Indexes and no help to you.

[This message has been edited by Dave Cox (edited 09-08-2000).]

mfabian · 2000-08-10

The following is from Navision Denmark about this issue:

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

How Number Sorting Works
Code fields in Navision Financials can contain both numerical values and text strings. Navision Financials ensures that numbers kept in code fields on Navision 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 Financials 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 Microsoft SQL Server Option for Navision Financials sorts the numbers as if they were text strings. The following table illustrates the differences that occur:

NumericalSorting TextSorting
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 Financials. 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 Financials does not permit numbers that start with zero. Furthermore, the SQL Server Option for Navision Financials 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

mfabian · 2000-08-10

<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Dave Cox:
I think this can be corrected by Navision A/S by them editing the stx file.<HR></BLOCKQUOTE>

Dave,

Editing the stx file has two disadvantages:
1) It only works if you edit the STX-file *BEFORE* you create the database. Once the db has been created, any change in the STX has no effect.
2) Once I described this solution in an internal Swiss-navision Forum. Result: The swiss chief-navision-programmer almost killed me for having done so <img border="0" title="" alt="" src="images/smiles/icon_smile.gif" />

Marcus

Marcus Fabian
m.fabian@thenet.ch
+41 79 439 78 72

Dave_Cox · 2000-08-10

The Problem is still Navisions

If you have a Telesales company that has been using Native Navision for several years they will expect that if they upgrade to SQL that the sort Order of thier Customers, Items and Prospects will be the same in the SQL version as the Native Version.
"Not the reality is it?"

They will be used to the sort order filters etc: used for list and reports for financial analysis, that they are already using, but the Information will be sorted differently, you cannot ask a Customer to renumber 10,000 Customer and or Item Records.

The Operatives will not be able to find what they need for the quick entry od data!

The criteria set out by Navision in Fabians post is fine for new Instalations, however look at Navision's Sample data in Cronus which does not meet their guide lines, if Cronus wanted to migrate to SQL they would have this problem and look how long they have been a Customer of Navisions, "Update the sample data and Lead by Example!".

Maybe Navision A/S should include two stx files one which sorts the Navision way and one which sorts the Microsoft way, this would enable new Instalations to use the second stx file so if they migrate to SQL at a later date the sorting will not be a problem!

MindSource (UK) Limited
Navision Service Partner

david@mindsource.co.uk
info@mindsource.co.uk

[This message has been edited by Dave Cox (edited 10-08-2000).]

jp · 2000-08-10

Navision CODE fields have always had a split personality with respect to sorting. Their sort order is CONTENT dependent. As a result, numeric codes with optional suffixes (101, 101-A, 101-1) sort differently in standard Navision than in any other program.

I'm not suprised that this non-standard sort had to be dropped in SQL.

-jp

BarryW · 2000-08-11

Is there any way to tell Navision to convert certain VARCHAR fields to INTEGER before doing the restore into an NFSQL database? I know that there's a couple of CODE FIELD INFORMATION tables in Navision that are populated by the SQL DATA TYPE comparision routines but the SQL DATATYPE fields in those tables are not editable(of course, I know I could make them editable and make the changes....but would that solution work?). Since I'm not NFSQL certified, tech support won't even speak with me about this issue. Seems that if I could tell Navision to create a field in SQL SERVER as an INTEGER instead of VARCHAR, then my sorting problems would vanish.

infox · 2000-08-11

Hi Barry!

I tried that. There is a field in the navision called "SQL Data Type" and I wanted to convert this form "varchar" to "Interger", but Navision is "cleverer" then that and won't let me!
So up to now the only possible solution for me is letting the Customernumbers that only have 5 digits start with a zero in front.
Thanks everyone for the replies!
Infox

BarryW · 2000-08-11

That's unreal....or at least I wish it was. I was afraid you'd say that.

By the way, does anyone have any insight into the performance of NFSQL vs. standard Navision. I expected it to be slower when running under SQL Server. However, I just tested a couple of intensive reports and found the performance of crunching those reports to be (to my amazement) faster. Any experiences/comments?

Thanks!

jp · 2000-08-11

I suppose you could work around the problem by adding an INTEGER field called Customer No., fill it in the OnInsert trigger with the integer value of No. (and back fill the existing records using a quick report). Add the value as a Key.

Then just change the sort keys and a few form fields, and you should get the behavior you want.

-jp

infox · 2000-08-11

Hi Barry!

when severval clients are used (up to 70) there seems to be a performance problem. Always remember ... the slowest client will determine the performance!!!!!
The navision server (normal) is supposed to be up to 30 % faster.

Hallo to all!

I have also found a lot of other suggestions from Navision itself about my problem. Believe it or not, they suggest either make an Interger out of the Code field or the other suggestion is declare the Numeric Code fields as the same Length and add zeros in front i.e. 40000 will be 0040000.

Well I guess we will have to live with that solution! Too bad! It would have been great for more information from Navision itself about the problem, but you all were a great help. Thanks a lot!

Infox :-)

Howdy, Stranger!

Quick Links

Categories

Navision Financials with SQL

Comments