QuickBooks integration summary

Stitch’s QuickBooks integration replicates data using the QuickBooks Online API. Refer to the Schema section for a list of objects available for replication.

Note: Currently, replicating data from QuickBooks desktop apps isn’t supported.

QuickBooks feature snapshot

A high-level look at Stitch's QuickBooks (v1) integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release status

Released on September 3, 2020

Supported by

Stitch

Stitch plan

Standard

API availability

Not available

Singer GitHub repository

singer-io/tap-quickbooks

REPLICATION SETTINGS
Anchor Scheduling

Supported

Advanced Scheduling

Supported

Table-level reset

Unsupported

Configurable Replication Methods

Unsupported

DATA SELECTION
Table selection

Supported

Column selection

Supported

Select all

Supported

TRANSPARENCY
Extraction Logs

Supported

Loading Reports

Supported

Connecting QuickBooks

QuickBooks setup requirements

To set up QuickBooks in Stitch, you need:

  • An online QuickBooks instance. Desktop instances aren’t currently supported.


Step 1: Add QuickBooks as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the QuickBooks icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch QuickBooks” would create a schema called stitch_quickbooks in the destination. Note: Schema names cannot be changed after you save the integration.

Step 2: Define the historical replication start date

The Sync Historical Data setting defines the starting date for your QuickBooks integration. This means that data equal to or newer than this date will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond QuickBooks’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 3: Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

QuickBooks integrations support the following replication scheduling methods:

To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 4: Authorize Stitch to access QuickBooks

Lastly, you’ll be directed to QuickBooks’ website to complete the setup.

  1. If you’re not already signed into your QuickBooks account, enter your credentials and click Login.
  2. A screen asking for authorization to QuickBooks will display.
  3. Click Authorize.
  4. After the authorization process successfully completes, you’ll be redirected back to Stitch.
  5. Click All Done.

Step 5: Set objects to replicate

The last step is to select the tables and columns you want to replicate. Learn about the available tables for this integration.

Note: If a replication job is currently in progress, new selections won’t be used until the next job starts.

For QuickBooks integrations, you can select:

  1. Individual tables and columns

  2. All tables and columns

Click the tabs to view instructions for each selection method.

  1. In the integration’s Tables to Replicate tab, locate a table you want to replicate.
  2. To track a table, click the checkbox next to the table’s name. A blue checkmark means the table is set to replicate.

  3. To track a column, click the checkbox next to the column’s name. A blue checkmark means the column is set to replicate.

  4. Repeat this process for all the tables and columns you want to replicate.
  5. When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.
  1. Click into the integration from the Stitch Dashboard page.
  2. Click the Tables to Replicate tab.

  3. In the list of tables, click the box next to the Table Names column.
  4. In the menu that displays, click Track all Tables and Fields:

    The Track all Tables and Fields menu in the Tables to Replicate tab

  5. Click the Finalize Your Selections button at the bottom of the page to save your data selections.

Initial and historical replication jobs

After you finish setting up QuickBooks, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


QuickBooks table reference

Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query an account

The accounts table contains info about the various account types in you QuickBooks instance. This table includes all account types: asset, liability, revenue (income), expenses, and equity.

Note: Both active and inactive accounts are included in the data for this table.

Id
STRING

The account ID.

Reference:

AccountSubType
STRING

AccountType
STRING

Active
BOOLEAN

Classification
STRING

CurrencyRef
OBJECT

Details about the currency used for the account.

CurrentBalance
DECIMAL

CurrentBalanceWithSubAccounts
DECIMAL

domain
STRING

FullyQualifiedName
STRING

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

accounts (table), MetaData (attribute)

Name
STRING

ParentRef
OBJECT

If the account is a subaccount, this object will contain the ID of the parent account.

SubAccount
BOOLEAN

SyncToken
STRING


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a bill

The bills table contains info about the bills, or requests for payment from third parties, in your QuickBooks instance.

Id
STRING

The bill ID.

Reference:

APAccountRef
OBJECT

Details about the AP account associated with the bill.

Balance
DECIMAL

CurrencyRef
OBJECT

Details about the currency used in the bill.

DepartmentRef
OBJECT

Details about the department associated with the bill.

domain
STRING

DueDate
DATE-TIME

ExchangeRate
DECIMAL

Line
ARRAY

AccountBasedExpenseLineDetail
OBJECT

AccountRef
OBJECT

Details about the account associated with the line item.

BillableStatus
STRING

CustomerRef
OBJECT

Details about the customer associated with the line item.

TaxCodeRef
OBJECT

Details about the tax code associated with the line item.

bills (table), AccountBasedExpenseLineDetail (attribute)

Amount
DECIMAL

Description
STRING

DetailType
STRING

Id
STRING

ItemBasedExpenseLineDetail
OBJECT

BillableStatus
STRING

ItemRef
OBJECT

Details about the item associated with the line item.

Qty
INTEGER

TaxCodeRef
OBJECT

Details about the tax code associated with the line item.

UnitPrice
INTEGER

bills (table), ItemBasedExpenseLineDetail (attribute)

LineNum
INTEGER

bills (table), Line (attribute)

LinkedTxn
ARRAY

A list of transactions associated with the bill.

TxnId
STRING

The ID of the linked transaction.

Depending on the TxnType value, this may be a foreign key to the following tables:

Reference:

TxnType
STRING

The type of the linked transaction. Possible values are:

  • BillPaymentCheck
  • PurchaseOrder

TxnLineId
STRING

bills (table), LinkedTxn (attribute)

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

bills (table), MetaData (attribute)

PrivateNote
STRING

SalesTermRef
OBJECT

Details about the sales term associated with the bill.

SyncToken
STRING

TotalAmt
DECIMAL

TxnDate
DATE-TIME

VendorRef
OBJECT

Details about the vendor associated with the bill.


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a billpayment

The bill_payments table contains info about payments made on bills received from vendors for goods or services.

Id
STRING

The bill payment ID.

Reference:

APAccountRef
OBJECT

Details about the AP account associated with the bill payment.

CheckPayment
OBJECT

CreditCardPayment
OBJECT

CurrencyRef
OBJECT

Details about the currency used in the bill payment.

DepartmentRef
OBJECT

Details about the department associated with the bill payment.

DocNumber
STRING

domain
STRING

ExchangeRate
DECIMAL

Line
ARRAY

Details about the individual line items associated with the bill payment.

Amount
DECIMAL

LinkedTxn
ARRAY

A list of transactions associated with the bill payment.

TxnId
STRING

The ID of the linked transaction.

Depending on the TxnType value, this may be a foreign key to the following tables:

Reference:

TxnType
STRING

The type of the linked transaction. Possible values are:

  • Bill
  • JournalEntry
  • VendorCredit
bill_payments (table), LinkedTxn (attribute)
bill_payments (table), Line (attribute)

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

bill_payments (table), MetaData (attribute)

PayType
STRING

SyncToken
STRING

TotalAmt
DECIMAL

TxnDate
DATE-TIME

VendorRef
OBJECT

Details about the vendor associated with the bill payment.


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query the budget

The budgets table contains info about the current state of budgets in your QuickBooks instance. Note: Both active and inactive budgets are included in the data for this table.

Id
STRING

The budget ID.

Active
BOOLEAN

BudgetDetail
ARRAY

AccountRef
OBJECT

Details about the account associated with the budget.

ClassRef
OBJECT

Details about the class associated with the budget.

CustomerRef
OBJECT

Details about the customer associated with the budget.

DepartmentRef
OBJECT

Details about the department associated with the budget.

Amount
DECIMAL

BudgetDate
DATE-TIME

budgets (table), BudgetDetail (attribute)

BudgetEntryType
STRING

BudgetType
STRING

domain
STRING

EndDate
DATE-TIME

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

budgets (table), MetaData (attribute)

Name
STRING

StartDate
DATE-TIME

SyncToken
STRING


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a class

The classes table contains info about the classes set up in your QuickBooks instance. Note: Both active and inactive classes are included in the data for this table.

Id
STRING

The class ID.

Reference:

Active
BOOLEAN

domain
STRING

FullyQualifiedName
STRING

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

classes (table), MetaData (attribute)

ParentRef
OBJECT

Details about the parent class associated with the class.

Name
STRING

SubClass
BOOLEAN

SyncToken
STRING


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a credit memo

The credit_memos table contains info about the credit memos in your QuickBooks instance. A credit memo is a transaction representing a refund or credit of payment for goods or services that have been sold.

Id
STRING

The credit memo ID.

Reference:

ApplyTaxAfterDiscount
BOOLEAN

Balance
INTEGER

BillAddr
OBJECT

Id
STRING

Lat
STRING

Line1
STRING

Line2
STRING

Line3
STRING

Line4
STRING

Long
STRING

credit_memos (table), BillAddr (attribute)

BillEmail
OBJECT

Address
STRING

credit_memos (table), BillEmail (attribute)

ClassRef
OBJECT

Details about the class associated with the credit memo.

CurrencyRef
OBJECT

Details about the currency used in the credit memo.

CustomField
ARRAY

Custom fields associated with the credit memo.

DefinitionId
STRING

Name
STRING

Type
STRING

credit_memos (table), CustomField (attribute)

CustomerMemo
OBJECT

value
STRING

credit_memos (table), CustomerMemo (attribute)

CustomerRef
OBJECT

Details about the customer associated with the credit memo.

DocNumber
STRING

EmailStatus
STRING

ExchangeRate
DECIMAL

HomeTotalAmt
DECIMAL

Line
ARRAY

Amount
DECIMAL

Description
STRING

DetailType
STRING

Id
STRING

LineNum
INTEGER

SalesItemLineDetail
OBJECT

ItemRef
OBJECT

Details about the item associated with the line item.

Qty
INTEGER

TaxCodeRef
OBJECT

Details about the tax code associated with the line item.

UnitPrice
INTEGER

credit_memos (table), SalesItemLineDetail (attribute)
credit_memos (table), Line (attribute)

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

credit_memos (table), MetaData (attribute)

PrintStatus
STRING

RemainingCredit
INTEGER

SalesTermRef
OBJECT

Details about the term associated with the credit memo.

ShipAddr
OBJECT

City
STRING

CountrySubDivisionCode
STRING

Id
STRING

Lat
STRING

Line1
STRING

Long
STRING

PostalCode
STRING

credit_memos (table), ShipAddr (attribute)

SyncToken
STRING

TotalAmt
DECIMAL

TxnDate
DATE-TIME

TxnTaxDetail
OBJECT

TotalTax
INTEGER

credit_memos (table), TxnTaxDetail (attribute)

domain
STRING


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a customer

The customers table contains info about the consumers of your business’s goods and services. Note: Both active and inactive customers are included in the data for this table.

Id
STRING

The customer ID.

Reference:

Active
BOOLEAN

Balance
DECIMAL

BalanceWithJobs
DECIMAL

BillAddr
OBJECT

City
STRING

Country
STRING

CountrySubDivisionCode
STRING

Id
STRING

Lat
STRING

Line1
STRING

Long
STRING

PostalCode
STRING

customers (table), BillAddr (attribute)

BillWithParent
BOOLEAN

CompanyName
STRING

CurrencyRef
OBJECT

Details about the currency used by the customer.

DefaultTaxCodeRef
OBJECT

Details about the default tax code used for the customer.

DisplayName
STRING

domain
STRING

FamilyName
STRING

Fax
OBJECT

FreeFormNumber
STRING

customers (table), Fax (attribute)

FullyQualifiedName
STRING

GivenName
STRING

Job
BOOLEAN

Level
INTEGER

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

customers (table), MetaData (attribute)

MiddleName
STRING

Mobile
OBJECT

FreeFormNumber
STRING

customers (table), Mobile (attribute)

ParentRef
OBJECT

If the customer is a sub-customer, this will contain details about the parent-level customer.

PaymentMethodRef
OBJECT

Details about the payment method used for the customer.

PreferredDeliveryMethod
STRING

PrimaryEmailAddr
OBJECT

Address
STRING

customers (table), PrimaryEmailAddr (attribute)

PrimaryPhone
OBJECT

FreeFormNumber
STRING

customers (table), PrimaryPhone (attribute)

PrintOnCheckName
STRING

ResaleNum
STRING

SalesTermRef
OBJECT

Details about the sales term associated with the customer.

ShipAddr
OBJECT

City
STRING

Country
STRING

CountrySubDivisionCode
STRING

Id
STRING

Lat
STRING

Line1
STRING

Long
STRING

PostalCode
STRING

customers (table), ShipAddr (attribute)

SyncToken
STRING

Taxable
BOOLEAN

WebAddr
OBJECT

URI
STRING

customers (table), WebAddr (attribute)

Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a department

The departments table contains info about the departments in your QuickBooks instance. Note: Both active and inactive departments are included in the data for this table.


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query a deposit

The deposits table contains info about deposits in your QuickBooks instance. These can be customer payments or new direct deposits.

Id
STRING

The deposit ID.

CashBack
OBJECT

CurrencyRef
OBJECT

Details about the currency the deposit is in.

DepartmentRef
OBJECT

Details about the department associated with the deposit.

DepositToAccountRef
OBJECT

Details about the account to which the deposit was made.

domain
STRING

ExchangeRate
DECIMAL

Line
ARRAY

Amount
DECIMAL

DepositLineDetail
OBJECT

AccountRef
OBJECT

Details about the account associated with the line item.

CheckNum
STRING

PaymentMethodRef
OBJECT

Details about the payment method associated with the line item.

deposits (table), DepositLineDetail (attribute)

DetailType
STRING

Id
STRING

LineNum
INTEGER

LinkedTxn
ARRAY

Details about the transactions linked with the deposit.

TxnId
STRING

The ID of the linked transaction.

Depending on the TxnType value, this may be a foreign key to the following tables:

Reference:

TxnType
STRING

The type of the linked transaction. Possible values are:

  • JournalEntry
  • Payment
  • RefundReceipt
  • SalesReceipt
  • Transfer

TxnLineId
STRING

deposits (table), LinkedTxn (attribute)
deposits (table), Line (attribute)

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

deposits (table), MetaData (attribute)

PrivateNote
STRING

SyncToken
STRING

TotalAmt
DECIMAL

TxnDate
DATE-TIME


Replication Method :

Key-based Incremental

Replication Key :

Metadata.LastUpdatedTime

Primary Key :

Id

API endpoint :

Query an employee

The employees table contains info about the people working for your company. Note: Both active and inactive employees are included in the data for this table.

Id
STRING

The employee ID.

Reference:

Active
BOOLEAN

BillableTime
BOOLEAN

BillRate
NUMBER

BirthDate
DATE-TIME

DisplayName
STRING

EmployeeNumber
STRING

FamilyName
STRING

Gender
STRING

GivenName
STRING

HiredDate
DATE-TIME

MetaData
OBJECT

CreateTime
DATE-TIME

LastUpdatedTime
DATE-TIME

employees (table), MetaData (attribute)

MiddleName
STRING

Mobile
OBJECT

FreeFormNumber
STRING

employees (table), Mobile (attribute)

Organization
BOOLEAN

PrimaryAddr
OBJECT

City
STRING

Country
STRING

CountrySubDivisionCode
STRING

Id
STRING

Lat
STRING

Line1
STRING

Line2
STRING

Line3
STRING

Line4