QuickBooks Online to Database

Learn how to sync Accounting Applications with on premise Database

Introduction

DBSync provides a user-friendly template for integrating QuickBooks Online and Database of your choice. It connects information of QuickBooks Online entities like Accounts Payable - Vendor Credit, Purchase Order, Bills. On the Accounts Receivable DBSync integrates - Sales Receipt, Estimates, Invoice, Credit Memos, Payments etc., with on-premise Database. DBSync is helpful for transferring historical data between QuickBooks Online and offers unidirectional synchronization with pre-defined field-to-field mapping, as well as the option for more intricate and dynamic mapping abilities. DBSync's automated online update feature allows customers to access all product updates and new features with each release.

Data Flow

Data flow of unidirectional integration from QuickBooks Online to Database is shown in the table below:

QuickBooks Online

QuickBooks Online	Database
Customer, Jobs	Customer, Jobs
Item Inventory, Non Inventory, Service	Item
Invoice, Sales receipts & Estimates	Invoice, Sales Receipts & Estimates
Vendors	Vendors
Purchase Order, Bills	Purchase Order, Bills

Database Schema

Firstly, you need to create a schema to obtain any type of table that you are looking to create in your Database. Regardless of the type of table you are creating, it is important to adhere to a particular structure because all of the fields in a Database Schema are mapped with the DBSync Cloud Workflow template.

Given below are various types of schemas for your reference.

Vendor Database Table

CREATE TABLE `QuickBooks Database`.`Vendor` (
`Name` VARCHAR(100) NOT NULL,
`Id` VARCHAR(45) NOT NULL,
`Email` VARCHAR(45) NULL,
`PhoneNumber` VARCHAR(45) NULL,
`MobileNumber` VARCHAR(45) NULL,
`Fax` VARCHAR(45) NULL,
`Website` VARCHAR(45) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingPostalCode` VARCHAR(45) NULL,
`PaymentTerms` VARCHAR(45) NULL,
`AccountNumber` VARCHAR(45) NULL,
PRIMARY KEY (`Id`));

Customer Database Table

CREATE TABLE `QuickBooks Database`.`Customer` (
`Name` VARCHAR(100) NOT NULL,
`Id` VARCHAR(45) NOT NULL,
`CurrencyRef` VARCHAR(45) NULL,
`Description` VARCHAR(45) NULL,
`Active` VARCHAR(45) NULL,
`CompanyName` VARCHAR(100) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingPostalCode` VARCHAR(45) NULL,
`ShippingStreet` VARCHAR(100) NULL,
`ShippingCountry` VARCHAR(45) NULL,
`ShippingState` VARCHAR(45) NULL,
`ShippingPostalCode` VARCHAR(45) NULL,
`ShippingCity` VARCHAR(45) NULL,
`MobileNo` VARCHAR(45) NULL,
`Fax` VARCHAR(45) NULL,
`PhoneNumber` VARCHAR(45) NULL,
`PaymentTerms` VARCHAR(45) NULL,
`Email` VARCHAR(100) NULL,
PRIMARY KEY (`Id`));

Item Database Table

CREATE TABLE `QuickBooks Database`.`Item` (
`Name` VARCHAR(100) NOT NULL,
`Id` VARCHAR(45) NOT NULL,
`Description` VARCHAR(200) NULL,
`UnitPrice` VARCHAR(45) NULL,
`Type` VARCHAR(45) NULL,
`IncomeAccount` VARCHAR(100) NULL,
`PurchaseCost` VARCHAR(45) NULL,
`ExpenseAccount` VARCHAR(100) NULL,
`AssetAccount` VARCHAR(100) NULL,
`QuantityonHand` VARCHAR(45) NULL,
`QuantityonPO` VARCHAR(45) NULL,
`InvStartDate` VARCHAR(45) NULL,
`PurchaseDescription` VARCHAR(100) NULL,
`SKU` VARCHAR(100) NULL,
PRIMARY KEY (`Id`));

Estimate Database Table

CREATE TABLE `QuickBooks Database`.`Estimate` (
`TxnId` INT NOT NULL,
`RefNumber` VARCHAR(45) NULL,
`CustomerName` VARCHAR(100) NULL,
`Email` VARCHAR(100) NULL,
`TxnDate` VARCHAR(45) NULL,
`ExpirationDate` VARCHAR(45) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingPostalCode` VARCHAR(45) NULL,
`Memo` VARCHAR(200) NULL,
`PrivateNote` VARCHAR(200) NULL,
`TotalTax` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
PRIMARY KEY (`TxnId`));

Estimate Line Database Table

CREATE TABLE `QuickBooks Database`.`EstimateLine` (
`TxnLineId` VARCHAR(45) NOT NULL,
`EstimateRefNo` VARCHAR(45) NULL,
`ItemName` VARCHAR(45) NULL,
`Quantity` VARCHAR(45) NULL,
`UnitPrice` VARCHAR(45) NULL,
`TotalPrice` VARCHAR(45) NULL,
`ServiceDate` VARCHAR(45) NULL,
`Description` VARCHAR(100) NULL,
`TxnId` VARCHAR(45) NULL,
PRIMARY KEY (`TxnLineId`));

Sales Receipt Database Table

CREATE TABLE `QuickBooks Database`.`SalesReceipt` (
`TxnId` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`Customer` VARCHAR(100) NULL,
`Email` VARCHAR(100) NULL,
`Date` VARCHAR(45) NULL,
`DepositToAccountRef` VARCHAR(45) NULL,
`PaymentMethod` VARCHAR(45) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingZipCode` VARCHAR(45) NULL,
`ShippingStreet` VARCHAR(45) NULL,
`ShippingCity` VARCHAR(45) NULL,
`ShippingState` VARCHAR(45) NULL,
`ShippingCountry` VARCHAR(45) NULL,
`ShippingPostalCode` VARCHAR(45) NULL,
`TotalTax` VARCHAR(45) NULL,
`Memo` VARCHAR(100) NULL,
`PrivateNote` VARCHAR(100) NULL,
`CurrencyRef` VARCHAR(45) NULL,
PRIMARY KEY (`TxnId`));

Sales Receipt Line Database Table

CREATE TABLE `QuickBooks Database`.`SalesReceiptLine` (
`TxnLineId` VARCHAR(45) NOT NULL,
`ServiceDate` VARCHAR(45) NULL,
`Item` VARCHAR(100) NULL,
`Description` VARCHAR(100) NULL,
`Quantity` VARCHAR(45) NULL,
`Rate` VARCHAR(45) NULL,
`LineAmount` VARCHAR(45) NULL,
`DocNumber` VARCHAR(45) NULL,
PRIMARY KEY (`TxnLineId`));

Invoice Database Table

CREATE TABLE `QuickBooks Database`.`Invoice` (
`TxnId` VARCHAR(45) NOT NULL,
`TxnDate` VARCHAR(45) NULL,
`DocNumber` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
`PrivateNote` VARCHAR(100) NULL,
`Memo` VARCHAR(100) NULL,
`Tax` VARCHAR(45) NULL,
`Customer` VARCHAR(45) NULL,
`SalesTermRef` VARCHAR(45) NULL,
`Email` VARCHAR(45) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingZipCode` VARCHAR(45) NULL,
`ShippingStreet` VARCHAR(100) NULL,
`ShippingState` VARCHAR(45) NULL,
`ShippingCity` VARCHAR(45) NULL,
`ShippingCountry` VARCHAR(45) NULL,
`ShippingPostalCode` VARCHAR(45) NULL,
PRIMARY KEY (`TxnId`));

Invoice Line Database

CREATE TABLE `QuickBooks Database`.`InvoiceLine` (
`TxnLineId` VARCHAR(45) NOT NULL,
`ServiceDate` VARCHAR(45) NULL,
`Item` VARCHAR(45) NULL,
`Description` VARCHAR(100) NULL,
`Quantity` VARCHAR(45) NULL,
`Rate` VARCHAR(45) NULL,
`LineAmount` VARCHAR(45) NULL,
`DocNumber` VARCHAR(45) NULL,
PRIMARY KEY (`TxnLineId`));

Expenses Database Table

CREATE TABLE `QuickBooks Database`.`Expense` (
`Id` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`TxnDate` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
`PrivateNote` VARCHAR(100) NULL,
`AccountRef` VARCHAR(100) NULL,
`PaymentType` VARCHAR(45) NULL,
`Vendor` VARCHAR(100) NULL,
`TotalAmount` VARCHAR(45) NULL,
PRIMARY KEY (`Id`));

Expense Line Database Table

CREATE TABLE `QuickBooks Database`.`ExpenseLine` (
`LineId` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`Amount` VARCHAR(45) NULL,
`Customer` VARCHAR(45) NULL,
`AccountRef` VARCHAR(45) NULL,
PRIMARY KEY (`LineId`));

Vendor Credit Database Table

CREATE TABLE `QuickBooks Database`.`VendorCredit` (
`Id` VARCHAR(45) NOT NULL,
`TxnDate` VARCHAR(45) NULL,
`DocNumber` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
`VendorRef` VARCHAR(100) NULL,
`APAccountRef` VARCHAR(45) NULL,
`TotalAmount` VARCHAR(45) NULL, `Memo` VARCHAR(100) NULL,
PRIMARY KEY (`Id`));

VendorCreditLine Database Table

CREATE TABLE `QuickBooks Database`.`VendorCreditLine` (
`LineId` INT NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`AccountRef` VARCHAR(45) NULL,
`Description` VARCHAR(100) NULL,
`Amount` VARCHAR(45) NULL,
`Customer` VARCHAR(45) NULL,
PRIMARY KEY (`LineId`));

Credit Memo Database Table

CREATE TABLE `QuickBooks Database`.`CreditMemo` (
`Id` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`TxnDate` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
`PrivateNote` VARCHAR(100) NULL,
`TotalTax` VARCHAR(45) NULL,
`Customer` VARCHAR(100) NULL,
`Memo` VARCHAR(100) NULL,
`TotalAmount` VARCHAR(45) NULL,
`Email` VARCHAR(45) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingPostalCode` VARCHAR(45) NULL,
`ShippingStreet` VARCHAR(100) NULL,
`ShippingCity` VARCHAR(45) NULL,
`ShippingCountry` VARCHAR(45) NULL,
`ShippingPostalCode` VARCHAR(45) NULL,
`ShippingState` VARCHAR(45) NULL,
`Balance` VARCHAR(45) NULL,
PRIMARY KEY (`Id`));

Credit Memo Line Database Table

CREATE TABLE `QuickBooks Database`.`CreditMemoLine` (
`TxnLineId` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`Description` VARCHAR(100) NULL,
`ItemName` VARCHAR(45) NULL,
`UnitPrice` VARCHAR(45) NULL,
`Quantity` VARCHAR(45) NULL,
`Amount` VARCHAR(45) NULL,
PRIMARY KEY (`TxnLineId`));

Bills Database Table

CREATE TABLE `QuickBooks Database`.`Bill` (
`Id` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`TxnDate` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
`Vendor` VARCHAR(100) NULL,
`APAccountRef` VARCHAR(100) NULL,
`TotalAmount` VARCHAR(45) NULL,
`SalesTerm` VARCHAR(45) NULL,
`Balance` VARCHAR(45) NULL,
PRIMARY KEY (`Id`));

Bills Line Item Database Table

CREATE TABLE `QuickBooks Database`.`BillLine` (
`LineId` VARCHAR(45) NOT NULL,
`AccountRef` VARCHAR(100) NULL,
`Description` VARCHAR(100) NULL,
`CustomerRef` VARCHAR(100) NULL,
`Amount` VARCHAR(45) NULL,
`DocNumber` VARCHAR(45) NULL,
PRIMARY KEY (`LineId`));

Purchase Order Database Table

CREATE TABLE `QuickBooks Database`.`PurchaseOrder` (
`Id` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`TxnDate` VARCHAR(45) NULL,
`CurrencyRef` VARCHAR(45) NULL,
`PrivateNote` VARCHAR(45) NULL,
`Vendor` VARCHAR(45) NULL,
`APAccountRef` VARCHAR(100) NULL,
`TotalAmount` VARCHAR(45) NULL,
`Memo` VARCHAR(100) NULL,
`ShipMethod` VARCHAR(45) NULL,
`VendorStreet` VARCHAR(100) NULL,
`VendorCity` VARCHAR(45) NULL,
`VendorState` VARCHAR(45) NULL,
`VendorCountry` VARCHAR(45) NULL,
`VendorPostalCode` VARCHAR(45) NULL,
`ShippingStreet` VARCHAR(100) NULL,
`ShippingCity` VARCHAR(45) NULL,
`ShippingCountry` VARCHAR(45) NULL,
`ShippingState` VARCHAR(45) NULL,
`ShippingPostalCode` VARCHAR(45) NULL,
PRIMARY KEY (`Id`));

Purchase Order Line Database Table

CREATE TABLE `QuickBooks Database`.`PurchaseOrderLine` (
`LineId` VARCHAR(45) NOT NULL,
`DocNumber` VARCHAR(45) NULL,
`ItemName` VARCHAR(100) NULL,
`Description` VARCHAR(100) NULL,
`Quantity` VARCHAR(45) NULL,
`UnitPrice` VARCHAR(45) NULL,
`Amount` VARCHAR(45) NULL,
PRIMARY KEY (`LineId`));

Jobs Database Table

CREATE TABLE `QuickBooks Database`.`Job` (
`Name` VARCHAR(100) NOT NULL,
`Id` VARCHAR(45) NOT NULL,
`CurrencyRef` VARCHAR(45) NULL,
`CompanyName` VARCHAR(45) NULL,
`BillingStreet` VARCHAR(100) NULL,
`BillingCity` VARCHAR(45) NULL,
`BillingState` VARCHAR(45) NULL,
`BillingCountry` VARCHAR(45) NULL,
`BillingPostalCode` VARCHAR(45) NULL,
`ShippingStreet` VARCHAR(100) NULL,
`ShippingCountry` VARCHAR(45) NULL,
`ShippingState` VARCHAR(45) NULL,
`ShippingPostalCode` VARCHAR(45) NULL,
`ShippingCity` VARCHAR(45) NULL,
`MobileNo` VARCHAR(45) NULL,
`Fax` VARCHAR(45) NULL,
`PhoneNumber` VARCHAR(45) NULL,
`PaymentTerms` VARCHAR(45) NULL,
`ParentName` VARCHAR(45) NULL,
`Email` VARCHAR(100) NULL,
PRIMARY KEY (`Id`));

Payment Database Table

CREATE TABLE `QuickBooks Database`.`Payment` (
`Id` VARCHAR(45) NOT NULL,
`TxnDate` VARCHAR(45) NULL,
`DocNumber` VARCHAR(45) NULL,
`Amount` VARCHAR(45) NULL,
`Customer` VARCHAR(100) NULL,
`UnappliedAmount` VARCHAR(45) NULL,
PRIMARY KEY (`Id`));

Running the script mentioned above in your Database is mandatory before creating a table.

Process to Run the Integration

To utilize the pre-built template QuickBooks Online to Database follow the below steps:

1. In the home screen, click Add Template next to the Projects icon.

2. Select the template: QuickBooks Online to Database template which allows for running multiple workflows and integration between the two environments.

3. The template will be added to the list of Your Projects.

4. Click the template and you will be redirected to the Task Screen.

5. Click Setup to access various workflows pre-built for the integration.

Setup Wizard

1. You will be redirected to the setup screen.

2. Verify your applications are connected QuickBooks Online and Database of your preference.

Connect

1. Click Test Connection and you will get a pop-up confirming the same. Ensure you verify the connection for both QuickBooks Online and Database.

2. Click Next and you will be redirected to the Setup screen.

Connect Screen

Setup

1. The Setup screen displays a form like questionnaire which you can choose to answer Yes to insert the data of a specific field to the Database.

2. You can choose No to refrain from inserting the data of a specific field to the Database.

3. Click Next to run the integration.

Setup Screen

Run

1. Click Next to Run Now to complete the sync.

Run Screen

Schedule

1. You can also choose to set a schedule to your sync: Update the task, start and end time along with frequency which ensures that both the applications are always in common sync.

Schedule Screen

1. Click Finish to complete the sync.