Quickbooks Conversion To NAV - ADO Decimal Data

Business Central/NAV Developers Forum
1

I am tasked with moving companies that are using Quickbooks Pro and Quickbooks Enterprise to NAV. I have been using the QODBC driver to access the Quickbooks data structure and have survived the QB → Excel → NAV process using Excel Buf (table 370) to pull QB data into tables that mirror QB tables. From there I have converted G/L Chart, G/L Transactions, Customers, Sales History, Vendors. The larger companies will generally use a vertical package for Customers / Sales / Inventory and QB Enterprise for G/L and A/P.

QB conversion has been a long and winding road. QB likes to change all the rules regularly by forcing upgrades and changing the data structure with each upgrade. In order to speed up the QB data pull, and insulate it from table structure changes, I have started to pursue ADO data access from C/SIDE using the QODBC driver. I was really excited until I ran into my first decimal data type in QB. I am pulling the data into NAV with a variant data type as in:

VariantTemp := ADO_RecSet.Fields().Item(‘Amount’).Value;

However, no matter what set of logic I try, I can’t get VariantTemp to give me a decimal value. The VariantTemp.ISDECIMAL property comes back false.

The local function shown below returns TRUE, (thank you Erik), which you might construe to mean that it was null. However, a little VBA code that parallels my C/SIDE logic proves that -8,159.02 is the value of Amount. I’ve got a support intance started with QODBC to see if I can monkey with their driver somehow. If that isn’t possible I may end up in some flavor of VB for this project. I CAN read from QB with one ADO connection and write to NAV using a 2nd ADO connection. I’m wondering if anyone has any suggestions, (technical please [8-|] ).

Procedure VarCheck(InVariant) : Boolean

EXIT(NOT (

InVariant.ISCODE OR

InVariant.ISBINARY OR

InVariant.ISTEXT OR

InVariant.ISINTEGER OR

InVariant.ISDECIMAL OR

InVariant.ISBOOLEAN OR

InVariant.ISOPTION OR

InVariant.ISCHAR OR

InVariant.ISDATE OR

InVariant.ISTIME));

2

Hi,

we actually had this problem and created ADO component that converts any Type into readable NAV type.

Actually we went even further… We created tool that can access any ODBC source and read all data directly into NAV - no programming or creating data or creating data port.

We also have mapping tool that allow mapping let say QuickBooks, Peachtree, MAS 90 – or any other data into nav and create packaged conversions…

You can see complete description at:

http://rt-it-group.com/index.php?option=com_resource&controller=article&article=34&category_id=5&Itemid=60

3

QODBC support just came back with a suggestion to use an Access linked table to insulate NAV from the raw QODBC driver. This might work as I can see the data in VBA. I will post the result.

4

Hi Mark,

this has been very helpful when i have worked on ADO from NAV: http://mibuso.com/dlinfo.asp?FileID=589

Basically you will be reading the values from the ADORecordset via ADOStream, here is quick example:

IF ADORecSet.Fields.Item(‘decimaltest’).Value.ISDECIMAL THEN BEGIN
ADOStream.Open;
ADOStream.WriteText(ADORecSet.Fields.Item(‘decimaltest’).Value);
ADOStream.Position:= 0;
FieldValue:= ADOStream.ReadText;
ADOStream.Close;
MESSAGE(FieldValue);
END;

5

another option is when you call your sql statement to cast it into float datatype and in nave you can assign that into a decimal value.