We ran into a problem when converting a client from a native Navision database to a SQL database. Item numbers, Vendor Numbers, and Customer numbers. In the Navision database the Item numbers would show up as 1,2,3,4,5…10,11,12,13…20. After we switched to the SQL database they are showing up as 1,10,2,20,3,30… This appears to be a difference in how Navision looks at a field type of “Code”, and the way SQL looks at a field type of “Code”. Has anyone ran into this before? Are we missing somthing? and, more importantly, have you figured out a fix? Thanks
Hi! This is standard behavior of SQL-Server with NAVISION. If you want to enforce the “old”/native sorting, you could set the field property (e.g. field “No.” in “Customer”) “SQL Data Type” to “Integer”. But be careful, read the helpfile and test it intensively!!!
… and another issue: As far as I remember, also the fields that are related to this field (e.g. “Sell-to-Customer No.” in T36) have to be changed, too … (I’m not sure about this, so TEST, TEST, TEST)
Or use the Variant sub-type if you need alphanumeric - this is the closest to the Navision Code format.
When working with SQL Server 2000 and Navision 3.70, in Code fields you can select SQL Data Type property to be “Variant” that is supposed to work like Code fields in Native DB. If this solve your problem, you can create a Form based on the field table to quickly modify all Code fields to SQL Data Type “Variant” using Search and Replace (Varchar to Variant). I don’t remember if previous versions of Navision allows this option on the SQL Data Type. Hope this helps,
The only problem I found is that the sort order for code fields in Native DB is numbers first, while in SQL Variant Data Type Numbers are last.
One more problem with Variant is that you can’t use leading zeros because it convert 0005 to 5, while as VarChar you can. Any one with more experience with SQL Data Type = Variant?
Have seen some cases where the “item no.” equals an EAN13 barcode number allthough cross references can be used for barcodes. If using SQL data type variant for the “item no.” a barcode containing only digits will be translated into an integer which can have a max value of 2,147,483,647, and this will definately give us a problem with a barcode like 5700522130173.
How about Renaming the Numbers to be of same length: i.e 12A,1,2,3,10,123 becomes 12A,001,002,003,010,123 This won’t require changing the datatypes in SQL DB and is version independent too.