Connecting to QuickBooks

QuickBooks Online is a cloud-based email marketing solution, that allows designing and sending marketing emails.

Data integration: Skyvia supports importing data to and from QuickBooks Online, exporting QuickBooks Online data to CSV files, replicating QuickBooks Online data to relational databases, and synchronizing QuickBooks Online data with other cloud apps and relational databases.

Backup: Skyvia Backup supports QuickBooks Online backup.

Query: Skyvia Query supports QuickBooks Online.

QuickBooks-Specific Features and Limitations

Skyvia has the following limitations for QuickBooks:

Synchronization and replication with Incremental Updates enabled are not supported for objects without MetaData_CreateTime or MetaData_LastUpdatedTime fields. Both fields must be present for this functionality.
Skyvia does not support custom QuickBooks fields having double quotation marks in their name.

Some of the QuickBooks tables store complex structured data. These are the following tables: Invoice, Estimate, Bill, BillPayment, CreditMemo, JournalEntry, Payment, Purchase, PurchaseOrder, RefundReceipt, SalesReceipt, VendorCredit. For example, an invoice or bill can have several lines. Skyvia represent this information in such tables as a JSON field Lines. Here is an example of the Lines field value from the Invoice table:

[
  {
    "Id": "1",
    "LineNum": 1.0,
    "Description": "Weekly Gardening Service",
    "Amount": 140.0,
    "DetailType": "SalesItemLineDetail",
    "SalesItemLineDetail_ItemRefId": "6",
    "SalesItemLineDetail_ItemRefName": "Gardening",
    "SalesItemLineDetail_UnitPrice": 35.0,
    "SalesItemLineDetail_Qty": 4.0,
    "SalesItemLineDetail_TaxCodeRefId": "NON"
  },
  {
    "Id": "2",
    "LineNum": 2.0,
    "Description": "Pest Control Services",
    "Amount": 35.0,
    "DetailType": "SalesItemLineDetail",
    "SalesItemLineDetail_ItemRefId": "10",
    "SalesItemLineDetail_ItemRefName": "Pest Control",
    "SalesItemLineDetail_UnitPrice": 35.0,
    "SalesItemLineDetail_Qty": 1.0,
    "SalesItemLineDetail_TaxCodeRefId": "NON"
  },
  {
    "Amount": 175.0,
    "DetailType": "SubTotalLineDetail"
  }
] 

For user convenience, lines of these objects are also available as separate records via read-only tables with names containing LineItem suffix, like InvoiceLineItem, BillLineItem, etc. They allow you to view these lines in a tabular form, with Query, export them to CSV with Export, import them from QuickBooks to a cloud application or database, where these lines should be stored in a separate table, etc.

Since these tables (that have the LineItem suffix in their name) are read-only, they are not available in Import packages as a target or in Synchronization packages. To modify lines of an invoice, bill, etc. you need to provide values in JSON format for the Lines field of the corresponding main table - Invoice, Bill, etc. For example, to import invoices from a CSV files to QuickBooks, you need to specify invoice lines in the JSON format, as in the example above, in one of the CSV file columns, and import this file to the Invoice table. You need to map the Lines field of the Invoice table to this column of the CSV file.

The tables with the LineItem suffix in their name are available in backup packages, but you cannot restore data to these tables, because they are read-only. Since they store the same information as the Lines field of the corresponding main tables, you don't actually need to backup them. All the information in *LineItem tables is present in the corresponding main tables, and you can back up and restore data in the main table only.

 

QuickBooks Connections

When creating a QuickBooks connection you simply log in with QuickBooks, and OAuth authentication token is stored on the Skyvia server. Your QuickBooks account login and password are not stored on the Skyvia server.

To create a QuickBooks connection, perform the following steps:

1.Open the Connections page by clicking Connections in the menu on the left side of the page.
2.Сlick the New button.
3.In the opened pane click QuickBooks.
4.In the opened New Connection dialog box, specify a connection Name that will be used to identify the connection.
 
quickbooks-connection-editor
5.Optionally change the Metadata Cache parameter value. It determines how often to update cached metadata for the connection. By default, Skyvia caches metadata of available objects for cloud sources. You can configure how often the cache is refreshed automatically or reset in manually on the Connection Details page of the corresponding connection by clicking the Clear link in the Metadata cache parameter in the Parameters pane. The following values are available for this setting:
oDisabled - the metadata cache is not created, and metadata are queried automatically whenever the connection is opened.
oOne Hour - the metadata cache expires after one hour since the previous refresh, and it is refreshed after this when the connection is opened.
oOne Day - the metadata cache expires after one day since the previous refresh, and it is refreshed after this when the connection is opened.
oOne Week - the metadata cache expires after one week since the previous refresh, and it is refreshed after this when the connection is opened.
oOne Month - the metadata cache expires after one month since the previous refresh, and it is refreshed after this when the connection is opened.
oInfinite - the cache is never reset automatically. Default value.
6.Click Log In with QuickBooks.
7.In the opened window enter your QuickBooks credentials and click the Log In button.
 
quickbooks-connection-login
8.Select a company to query data from (click the link with the corresponding company name).
 
quickbooks-connection-select-company
9.Click Authorize.
 
quickbooks-connection-authorize
10.  Wait until the necessary data are transmitted, and then Save the connection.