# QuickBooks Online to 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 OnlineDatabase
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\`)); | {% hint style="warning" %} *Running the script mentioned above in your Database is mandatory before creating a table.* {% endhint %} ## 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 6. You will be redirected to the setup screen. 7. Verify your applications are connected **QuickBooks Online** and **Database** of your preference. #### Connect 8. Click **Test Connection** and you will get a pop-up confirming the same. Ensure you verify the connection for both **QuickBooks Online** and **Database**. 9. Click **Next** and you will be redirected to the **Setup** screen.

Connect Screen

#### Setup 10. 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. 11. You can choose **No** to refrain from inserting the data of a specific field to the Database. 12. Click **Next** to run the integration.

Setup Screen

#### Run 13. Click **Next** to **Run Now** to complete the sync.

Run Screen

#### Schedule 14. 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

15. Click **Finish** to complete the sync.