Cannot connects sql tables to quickbook tables

  • 241Views
  • Last Post 02 August 2017
Hoang Van posted this 06 July 2017

Hi there, 

We are trying to add the invoice tables from sql server database with invoice table in quickbook. But it is surprising that the column in quickbook online database was not accurate and the data type was wrong. The amount column in Quickbooks are in string type instead of numeric datatype. There are also couple other column that looks weird on quickbooks. There is also columns that I have no idea why it was on it for example the line column in the attached photo. 

Thank you

Attached Files

Order By: Standard | Newest | Votes
Simon Bubnov posted this 07 July 2017

Skyvia uses column data types, returned by QuickBooks API. If a field in QuickBoooks has a textual or numeric type, Skyvia will use respectively textual or numeric type. Please tell us, for which columns you think the type is specified incorrectly. Specify which type Skyvia uses for them, and which type, you think, is correct. 


The Line field in the QuickBooks Invoice entity is mandatory and must be mapped. The Line field stores the specification of the Invoice and it is stored in the JSON format. During the execution of an import or synchronization package that import data into QuickBooks invoices, data will be automatically added to Invoice and to the InvoiceLineItem detail table that is mapped to it. Note that data in the Line column should be in the JSON format. For example you can insert the following value in the Line field:




""Id"": ""1"",

""LineNum"": 1.0,

""Amount"": 189.78,

""DetailType"": ""SalesItemLineDetail"",

""SalesItemLineDetail_ItemRefId"": ""1"",

""SalesItemLineDetail_ItemRefName"": ""Sales"",

""SalesItemLineDetail_UnitPrice"": 189.78,

""SalesItemLineDetail_Qty"": 1.0



""Amount"": 189.78,

""DetailType"": ""SubTotalLineDetail""




Thus you must have a text column in SQL Server which will store text in corresponding JSON format, for mapping this column to the Line field.

Brandon Ramey posted this 01 August 2017

I'm trying to do the same. I have added a TEXT column and added [{""LineNum"": 1.0}] as the column default. I'm not sure exactly what needs to go into that column.

Does it have to be something related to other mapped items? Is it something I can find in my QuickBooks account?

Mariia Zaharova posted this 02 August 2017

Brandon, please take a look at QuickBooks documentation: