# QuickBooks Desktop to Database

## Introduction

DBSync provides a user-friendly template for integrating QuickBooks Desktop and Database of your choice. It connects information of QuickBooks Desktop 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 Desktop 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 Desktop to Database is shown in the table below:

| QuickBooks Desktop                                                                                                          | Database                                |
| --------------------------------------------------------------------------------------------------------------------------- | --------------------------------------- |
| Customer, Jobs                                                                                                              | Customer, Jobs                          |
| Invoices                                                                                                                    | Invoice Header, Invoice Lines           |
| Sales Orders                                                                                                                | SalesorderHeader, SalesorderLines       |
| Estimates                                                                                                                   | EstimateHeader, EstimateLines           |
| Item Receipts                                                                                                               | ItemReceipt, ItemReceipt Lines          |
| Receive Payments                                                                                                            | ReceivePayment, PaymentAppliedTo Txn    |
| Credit Memo                                                                                                                 | CreditMemoHeader, CreditMemoLines       |
| Vendor                                                                                                                      | Vendor                                  |
| Bills                                                                                                                       | Bills, BillLines                        |
| Bill PaymentCreditCard                                                                                                      | BillPaymentCC                           |
| BillPaymentCheck                                                                                                            | BillPaymentCheck                        |
| Checks                                                                                                                      | Checks, CheckLines                      |
| CreditCardCharge                                                                                                            | CreditCardCharge, CreditCardChargeLines |
| Deposits                                                                                                                    | Deposit, DepositLines                   |
| Purchase Order                                                                                                              | PurchaseOrder, PurchaseOrderLines       |
| Sales Receipt                                                                                                               | SalesReceiptHeader, SalesReceiptLines   |
| Item Inventory, Item Non inventory, Item Service, Item inventory Assembly, Item Sales Tax, Item Discount, Item Other Charge | Items                                   |

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

Create a Schema with the name of your choice. In my case, we have kept it as a **QBDtoDBTemplate**. The formula is:\
\
CREATE SCHEMA **QBDtoDBTemplate**;\
\
Given below are various types of Schemas for your reference.

#### Customer Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Customer\` ( |
| ------------------------------------------------------------- |
| \`ListId\` VARCHAR(100) NOT NULL,                             |
| \`FullName\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`AccountNumber\` VARCHAR(100) NULL DEFAULT NULL,             |
| \`AltContact\` VARCHAR(100) NULL DEFAULT NULL,                |
| \`AltPhone\` VARCHAR(50) NULL DEFAULT NULL,                   |
| \`Balance\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`BillingZipcode\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`CompanyName\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`Contact\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`DeliveryMethod\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`Email\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`Fax\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`FirstName\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`LastName\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`ItemSalesTax\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`JobDesc\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`JobEnddate\` VARCHAR(100) NULL DEFAULT NULL,                |
| \`Jobprojectedenddate\` VARCHAR(100) NULL DEFAULT NULL,       |
| \`JobStartDate\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`JobStatus\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`Jobtitle\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`Mobile\` VARCHAR(50) NULL DEFAULT NULL,                     |
| \`Name\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Notes\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`Phone\` VARCHAR(50) NULL DEFAULT NULL,                      |
| \`PreferredDeliveryMethod\` VARCHAR(100) NULL DEFAULT NULL,   |
| \`PreferredPaymentMethodref\` VARCHAR(100) NULL DEFAULT NULL, |
| \`ResaleNumber\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`SalesTaxCode\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`SalesTaxCountry\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`ShippingZipCode\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`Sublevel\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`TaxRegistrationNumber\` VARCHAR(100) NULL DEFAULT NULL,     |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`TimeCreated\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`TotalBalance\` VARCHAR(100) NULL DEFAULT NULL,              |
| PRIMARY KEY (\`ListId\`))                                     |
| ENGINE = InnoDB                                               |
| DEFAULT CHARACTER SET = utf8mb4                               |
| COLLATE = utf8mb4\_0900\_ai\_ci                               |

 

#### Job Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Job\` (      |
| ------------------------------------------------------------- |
| \`ListId\` VARCHAR(100) NOT NULL,                             |
| \`FullName\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`ParentRef\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`AccountNumber\` VARCHAR(100) NULL DEFAULT NULL,             |
| \`AltContact\` VARCHAR(100) NULL DEFAULT NULL,                |
| \`AltPhone\` VARCHAR(50) NULL DEFAULT NULL,                   |
| \`Balance\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`BillingZipcode\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`CompanyName\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`Contact\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`DeliveryMethod\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`Email\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`Fax\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`FirstName\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`LastName\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`ItemSalesTax\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`JobDesc\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`JobEnddate\` VARCHAR(100) NULL DEFAULT NULL,                |
| \`Jobprojectedenddate\` VARCHAR(100) NULL DEFAULT NULL,       |
| \`JobStartDate\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`JobStatus\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`Jobtitle\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`Mobile\` VARCHAR(50) NULL DEFAULT NULL,                     |
| \`Name\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Notes\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`Phone\` VARCHAR(50) NULL DEFAULT NULL,                      |
| \`PreferredDeliveryMethod\` VARCHAR(100) NULL DEFAULT NULL,   |
| \`PreferredPaymentMethodref\` VARCHAR(100) NULL DEFAULT NULL, |
| \`ResaleNumber\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`SalesTaxCode\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`SalesTaxCountry\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`ShippingZipCode\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`Sublevel\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`TaxRegistrationNumber\` VARCHAR(100) NULL DEFAULT NULL,     |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`TimeCreated\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`TotalBalance\` VARCHAR(100) NULL DEFAULT NULL,              |
| PRIMARY KEY (\`ListId\`))                                     |
| ENGINE = InnoDB                                               |
| DEFAULT CHARACTER SET = utf8mb4                               |
| COLLATE = utf8mb4\_0900\_ai\_ci                               |

#### InvoiceHeader Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`InvoiceHeader\` ( |
| ------------------------------------------------------------------ |
| \`InvoiceNumber\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`ARAccount\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`AppliedAmount\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`BalalnceRemaining\` VARCHAR(100) NULL DEFAULT NULL,              |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`BillingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`Ispaid\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`Istobeemailed\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`ItemSalesTaxRef\` VARCHAR(100) NULL DEFAULT NULL,                |
| \`Memo\` MEDIUMTEXT NULL DEFAULT NULL,                             |
| \`PONumber\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`SalesTaxLineAmount\` VARCHAR(100) NULL DEFAULT NULL,             |
| \`SalesTaxLineAccount\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`ShippingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`ShipDate\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`Subtotal\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`TemplateRef\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT 'None',                     |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TxnId\` VARCHAR(100) NOT NULL,                                   |
| \`TxnNumber\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Duedate\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`AccountID\` VARCHAR(500) NULL DEFAULT NULL,                      |
| PRIMARY KEY (\`TxnId\`))                                           |
| ENGINE = InnoDB                                                    |
| DEFAULT CHARACTER SET = utf8mb4                                    |
| COLLATE = utf8mb4\_0900\_ai\_ci                                    |

#### InvoiceLine Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`InvoiceLines\` ( |
| ----------------------------------------------------------------- |
| \`InvoiceNumber\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Description\` MEDIUMTEXT NULL DEFAULT NULL,                     |
| \`Inventorysite\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`IsTaxable\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ItemName\` VARCHAR(500)' NULL DEFAULT NULL,                     |
| \`Quantity\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`Rate\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`SalesTaxCode\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`SerialNumber\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`ServiceDate\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`TaxAmount\` VARCHAR(100)  NULL DEFAULT NULL,                    |
| \`TxnLineId\` VARCHAR(100) NOT NULL,                              |
| \`UnitOfMeasure\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`ItemID\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`Priceeach\` VARCHAR(500) NULL DEFAULT NULL,                     |
| PRIMARY KEY (\`TxnLineId\`))                                      |
| ENGINE = InnoDB                                                   |
| DEFAULT CHARACTER SET = utf8mb4                                   |
| COLLATE = utf8mb4\_0900\_ai\_ci                                   |

#### EstimatesHeader Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`EstimateHeader\` ( |
| ------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`BillingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`DueDate\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`ExchangeRate\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`FOB\` VARCHAR(100) NULL DEFAULT NULL,                             |
| \`IsActive\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`ItemSalesTax\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`Memo\` MEDIUMTEXT NULL DEFAULT NULL,                              |
| \`PONumber\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`Istobeemailed\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`SalesTaxAmount\` VARCHAR(100) 'NULL DEFAULT NULL,                 |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`ShippingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`TotalAmount\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`Subtotal\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TemplateRef\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`TxnId\` VARCHAR(100) NOT NULL,                                    |
| \`TxnNumber\` VARCHAR(100) NULL DEFAULT NULL,                       |
| PRIMARY KEY (\`TxnId\`))                                            |
| ENGINE = InnoDB                                                     |
| DEFAULT CHARACTER SET = utf8mb4                                     |
| COLLATE = utf8mb4\_0900\_ai\_ci                                     |

#### EstimatesLines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`EstimateLines\` ( |
| ------------------------------------------------------------------ |
| \`DocumentNumber\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`Description\` MEDIUMTEXT NULL DEFAULT NULL,                      |
| \`Inventorysite\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`MarkUpRate\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`Quantity\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`Rate\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`SalesTaxCode\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`TaxAmount\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`TxnLineId\` VARCHAR(100) NOT NULL,                               |
| \`UnitOfMeasure\` VARCHAR(100) NULL DEFAULT NULL,                  |
| PRIMARY KEY (\`TxnLineId\`))                                       |
| ENGINE = InnoDB                                                    |
| DEFAULT CHARACTER SET = utf8mb4                                    |
| COLLATE = utf8mb4\_0900\_ai\_ci                                    |

####  SalesOrder Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`SalesorderHeader\` ( |
| --------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`BillingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`DueDate\` VARCHAR(100) NULL DEFAULT NULL,                           |
| \`ExchangeRate\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Ismanuallyclosed\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`IsFullyInvoiced\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`FOB\` VARCHAR(100) NULL DEFAULT NULL,                               |
| \`IstobeEmailed\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`IsActive\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`ItemSalesTax\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Memo\` VARCHAR(100)NULL DEFAULT NULL,                               |
| \`PONumber\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`SalesTaxAmount\` VARCHAR(100)NULL DEFAULT NULL,                     |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ShippingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`Shipdate\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`ShipMethod\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TotalAmount\` VARCHAR(100)NULL DEFAULT NULL,                        |
| \`Subtotal\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`TemplateRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                           |
| \`TxnId\` VARCHAR(100) NOT NULL,                                      |
| \`TxnNumber\` VARCHAR(100) NULL DEFAULT NULL,                         |
| PRIMARY KEY (\`TxnId\`))                                              |
| ENGINE = InnoDB                                                       |
| DEFAULT CHARACTER SET = utf8mb4                                       |
| COLLATE = utf8mb4\_0900\_ai\_ci                                       |

####  SalesOrderLines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`SalesOrderLines\` ( |
| -------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100)NULL DEFAULT NULL,                    |
| \`Amount\` VARCHAR(500)NULL DEFAULT NULL,                            |
| \`ClassRef\` VARCHAR(100)NULL DEFAULT NULL,                          |
| \`Description\` VARCHAR(100)NULL DEFAULT NULL,                       |
| \`Inventorysite\` VARCHAR(100)NULL DEFAULT NULL,                     |
| \`Invoiced\` VARCHAR(500)NULL DEFAULT NULL,                          |
| \`IsmanuallyClosed\` VARCHAR(100) NULL DEFAULT NULL,                 |
| \`ItemName\` VARCHAR(500)NULL DEFAULT NULL,                          |
| \`LotNumber\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`Quantity\` VARCHAR(500)NULL DEFAULT NULL,                          |
| \`Rate\` VARCHAR(500)NULL DEFAULT NULL,                              |
| \`SalesTaxCode\` VARCHAR(500)NULL DEFAULT NULL,                      |
| \`SerialNumber\` VARCHAR(100)NULL DEFAULT NULL,                      |
| \`TaxAmount\` VARCHAR(100)NULL DEFAULT NULL,                         |
| \`TxnLineId\` VARCHAR(100)NOT NULL,                                  |
| \`UnitOfMeasure\` VARCHAR(100)NULL DEFAULT NULL,                     |
| PRIMARY KEY (\`TxnLineId\`))                                         |
| ENGINE = InnoDB                                                      |
| DEFAULT CHARACTER SET = utf8mb4                                      |
| COLLATE = utf8mb4\_0900\_ai\_ci                                      |

#### SalesReceiptHeader Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`SalesReceiptHeader\` ( |
| ----------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100)NULL DEFAULT NULL,                       |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`BillingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`CheckNumber\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                            |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`CustomerSalesTax\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`DepositToAccount\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`DueDate\` VARCHAR(100)NULL DEFAULT NULL,                              |
| \`ExchangeRate\` VARCHAR(100)NULL DEFAULT NULL,                         |
| \`FOB\` VARCHAR(100)NULL DEFAULT NULL,                                  |
| \`IstobeEmailed\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`Istobeprinted\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`IsTaxIncluded\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`IsPending\` VARCHAR(100)NULL DEFAULT NULL,                            |
| \`ItemSalesTax\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`Memo\` MEDIUMTEXT NULL DEFAULT NULL,                                  |
| \`PaymentMethod\` VARCHAR(100)NULL DEFAULT NULL,                        |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`SalesTaxAccount\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`SalesTaxAmount\` VARCHAR(100)NULL DEFAULT NULL,                       |
| \`SalesTaxTotal\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ShippingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`Shipdate\` VARCHAR(100) NULL DEFAULT NULL,                            |
| \`ShipMethod\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`TotalAmount\` VARCHAR(100)NULL DEFAULT NULL,                          |
| \`Subtotal\` VARCHAR(100) NULL DEFAULT NULL,                            |
| \`TemplateRef\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT NULL,                            |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                             |
| \`TxnId\` VARCHAR(100) NOT NULL,                                        |
| \`TxnNumber\` VARCHAR(100) NULL DEFAULT NULL,                           |
| PRIMARY KEY (\`TxnId\`))                                                |
| ENGINE = InnoDB                                                         |
| DEFAULT CHARACTER SET = utf8mb4                                         |
| COLLATE = utf8mb4\_0900\_ai\_ci                                         |

#### SalesReceiptLines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`SalesReceiptLines\` ( |
| ---------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100)NULL DEFAULT NULL,                      |
| \`Amount\` VARCHAR(500)NULL DEFAULT NULL,                              |
| \`ClassRef\` VARCHAR(100)NULL DEFAULT NULL,                            |
| \`Description\` MEDIUMTEXT NULL DEFAULT NULL,                          |
| \`Inventorysite\` VARCHAR(100)NULL DEFAULT NULL,                       |
| \`Istaxable\` VARCHAR(500)NULL DEFAULT NULL,                           |
| \`ServiceDate\` VARCHAR(100)NULL DEFAULT NULL,                         |
| \`ItemName\` VARCHAR(500)NULL DEFAULT NULL,                            |
| \`LotNumber\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`Quantity\` VARCHAR(500)NULL DEFAULT NULL,                            |
| \`Rate\` VARCHAR(500)NULL DEFAULT NULL,                                |
| \`SalesTaxCode\` VARCHAR(500)NULL DEFAULT NULL,                        |
| \`SerialNumber\` VARCHAR(100)NULL DEFAULT NULL,                        |
| \`TaxAmount\` VARCHAR(100)NULL DEFAULT NULL,                           |
| \`TxnLineId\` VARCHAR(100)NOT NULL,                                    |
| \`UnitOfMeasure\` VARCHAR(100)NULL DEFAULT NULL,                       |
| PRIMARY KEY (\`TxnLineId\`))                                           |
| ENGINE = InnoDB                                                        |
| DEFAULT CHARACTER SET = utf8mb4                                        |
| COLLATE = utf8mb4\_0900\_ai\_ci                                        |

#### ReceivePayment Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`ReceivePayment\` ( |
| ------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`ARAccount\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`DeposittoAccount\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`Memo\` VARCHAR(100) NULL DEFAULT NULL,                            |
| \`PaymentMethod\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`TotalAmount\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`TxnID\` VARCHAR(100) NOT NULL,                                    |
| \`TxnNumber\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`UnusedCredit\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`UnusedPayment\` VARCHAR(100) NULL DEFAULT NULL,                   |
| PRIMARY KEY (\`TxnID\`))                                            |
| ENGINE = InnoDB                                                     |
| DEFAULT CHARACTER SET = utf8mb4                                     |
| COLLATE = utf8mb4\_0900\_ai\_ci                                     |

#### PaymentAppliedToTxn Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`PaymentAppliedToTxn\` ( |
| ------------------------------------------------------------------------ |
| \`Amount\` VARCHAR(100) NULL DEFAULT NULL,                               |
| \`BalanceRemaining\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`DiscountAmount\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`DiscountClass\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`InvoiceNumber\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                              |
| \`TxnId\` VARCHAR(100) NULL DEFAULT NULL,                                |
| \`TxnType\` VARCHAR(100) NULL DEFAULT NULL,                              |
| \`PaymentNumber\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TxnLineAmount\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TxnLineId\` VARCHAR(100) NOT NULL,                                     |
| PRIMARY KEY (\`TxnLineId\`))                                             |
| ENGINE = InnoDB                                                          |
| DEFAULT CHARACTER SET = utf8mb4                                          |
| COLLATE = utf8mb4\_0900\_ai\_ci                                          |

#### Credit Memo Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`CreditMemoHeader\` ( |
| --------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100)NULL DEFAULT NULL,                     |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`ARAccount\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`BillingStreet\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`BillingCity\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`BillingState\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`BillingCountry\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`BillingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`CreditRemaining\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`CustomerSalesTax\` VARCHAR(100) NULL DEFAULT NULL,                  |
| \`CurrencyRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`DueDate\` VARCHAR(100)NULL DEFAULT NULL,                            |
| \`ExchangeRate\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`FOB\` VARCHAR(100) NULL DEFAULT NULL,                               |
| \`Istobeprinted\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`ItemSalesTax\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Memo\` VARCHAR(100) NULL DEFAULT NULL,                              |
| \`PONumber\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`Istobeemailed\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`SalesRepRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`SalesTaxAccount\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`SalesTaxAmount\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`SalesTaxTotal\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ShippingZipcode\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`Shipdate\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`ShipMethod\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`ShippinglineAccount\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`ShippingLineAmount\` VARCHAR(100) NULL DEFAULT NULL,                |
| \`TotalAmount\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`Subtotal\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`TemplateRef\` VARCHAR(100) NULL DEFAULT NULL,                       |
| \`TermsRef\` VARCHAR(100) NULL DEFAULT NULL,                          |
| \`TxnDate\` VARCHAR(100) NULL DEFAULT NULL,                           |
| \`TxnId\` VARCHAR(100) NOT NULL,                                      |
| \`TxnNumber\` VARCHAR(100) NULL DEFAULT NULL,                         |
| PRIMARY KEY (\`TxnId\`))                                              |
| ENGINE = InnoDB                                                       |
| DEFAULT CHARACTER SET = utf8mb4                                       |
| COLLATE = utf8mb4\_0900\_ai\_ci                                       |

#### Credit Memo Lines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`CreditMemoLines\` ( |
| -------------------------------------------------------------------- |
| \`DocumentNumber\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`ClassRef\` VARCHAR(100) NULL DEFAULT NULL,                         |
| \`Description\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`Inventorysite\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`IsTaxable\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`LotNumber\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`Quantity\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`Rate\` VARCHAR(500) NULL DEFAULT NULL,                             |
| \`SalesTaxCode\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`SerialNumber\` VARCHAR(100) NULL DEFAULT NULL,                     |
| \`Servicedate\` VARCHAR(100) NULL DEFAULT NULL,                      |
| \`TaxAmount\` VARCHAR(100) NULL DEFAULT NULL,                        |
| \`TxnLineId\` VARCHAR(100) NOT NULL,                                 |
| \`UnitOfMeasure\` VARCHAR(100) NULL DEFAULT NULL,                    |
| PRIMARY KEY (\`TxnLineId\`))                                         |
| ENGINE = InnoDB                                                      |
| DEFAULT CHARACTER SET = utf8mb4                                      |
| COLLATE = utf8mb4\_0900\_ai\_ci                                      |

#### Vendor Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Vendor\` ( |
| ----------------------------------------------------------- |
| \`AccountNumber\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`Balance\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`CompanyName\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`CreditLimit\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`CurrencyRef\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`Email\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`Fax\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`FirstName\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`JobTitle\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`LastName\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`ListID\` VARCHAR(500) NOT NULL,                           |
| \`Mobile\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`Name\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`NameOnCheck\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`Phone\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,          |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,         |
| \`ShippingPostalCode\` VARCHAR(500) NULL DEFAULT NULL,      |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`Terms\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`VendorStreet\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`VendorCity\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`VendorCountry\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`VendorPostalCode\` VARCHAR(500) NULL DEFAULT NULL,        |
| \`VendorState\` VARCHAR(500) NULL DEFAULT NULL,             |
| PRIMARY KEY (\`ListID\`))                                   |
| ENGINE = InnoDB                                             |
| DEFAULT CHARACTER SET = utf8mb4                             |
| COLLATE = utf8mb4\_0900\_ai\_ci                             |

#### Bill Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Bills\` ( |
| ---------------------------------------------------------- |
| \`APAccount\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`AmountDue\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`DueDate\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`IsPaid\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`OpenAmount\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`Terms\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                    |
| \`VendorStreet\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`VendorCity\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`VendorState\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`VendorZipCode\` VARCHAR(500) NULL DEFAULT NULL,          |
| \`VendorCountry\` VARCHAR(500) NULL DEFAULT NULL,          |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,             |
| PRIMARY KEY (\`QuickBooksID\`))                            |
| ENGINE = InnoDB                                            |
| DEFAULT CHARACTER SET = utf8mb4                            |
| COLLATE = utf8mb4\_0900\_ai\_ci                            |

#### Bill Lines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`BillLines\` ( |
| -------------------------------------------------------------- |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`SalesRep\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`TaxAmount\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                        |
| \`Cost\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`Description\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`InventorySiteLoc\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`InventorySite\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`LotNumber\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`Qty\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`SerialNumber\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`UOM\` VARCHAR(500) NULL DEFAULT NULL,                        |
| PRIMARY KEY (\`QuickBooksID\`))                                |
| ENGINE = InnoDB                                                |
| DEFAULT CHARACTER SET = utf8mb4                                |
| COLLATE = utf8mb4\_0900\_ai\_ci                                |

#### BillPaymentCC Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`BillPaymentCC\` ( |
| ------------------------------------------------------------------ |
| \`APAccount\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`CCAccount\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                            |
| PRIMARY KEY (\`QuickBooksID\`))                                    |
| ENGINE = InnoDB                                                    |
| DEFAULT CHARACTER SET = utf8mb4                                    |
| COLLATE = utf8mb4\_0900\_ai\_ci                                    |

#### BillPaymentCheck Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`BillPaymentCheck\` ( |
| --------------------------------------------------------------------- |
| \`APAccount\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`Street\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`City\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`State\` VARCHAR(500) NULL DEFAULT NULL,                             |
| \`ZipCode\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`Country\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`BankAccount\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                               |
| PRIMARY KEY (\`QuickBooksID\`))                                       |
| ENGINE = InnoDB                                                       |
| DEFAULT CHARACTER SET = utf8mb4                                       |
| COLLATE = utf8mb4\_0900\_ai\_ci                                       |

#### ItemReceipt Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`ItemReceipt\` ( |
| ---------------------------------------------------------------- |
| \`APAccount\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`TotalAmount\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                          |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,                   |
| PRIMARY KEY (\`QuickBooksID\`))                                  |
| ENGINE = InnoDB                                                  |
| DEFAULT CHARACTER SET = utf8mb4                                  |
| COLLATE = utf8mb4\_0900\_ai\_ci                                  |

#### ItemReceiptLines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`ItemReceiptLines\` ( |
| --------------------------------------------------------------------- |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                             |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`SalesRep\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`TaxAmount\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                               |
| \`Cost\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`Description\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`InventorySiteLoc\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`InventorySite\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`LotNumber\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`Qty\` VARCHAR(500) NULL DEFAULT NULL,                               |
| \`SerialNumber\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`UOM\` VARCHAR(500) NULL DEFAULT NULL,                               |
| PRIMARY KEY (\`QuickBooksID\`))                                       |
| ENGINE = InnoDB                                                       |
| DEFAULT CHARACTER SET = utf8mb4                                       |
| COLLATE = utf8mb4\_0900\_ai\_ci                                       |

#### Purchase Order Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`PurchaseOrder\` ( |
| ------------------------------------------------------------------ |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`DueDate\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`FOB\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`IsFullyReceived\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`IsManuallyClosed\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`ShippingStreet\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`ShippingCity\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ShippingCountry\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`ShippingZipCode\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`ShippingState\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`ShippingMethod\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`Terms\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`TotalAmount\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                            |
| \`VendorStreet\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`VendorCity\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`VendorCountry\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`VendorState\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`VendorZipCode\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`VendorMsg\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,                     |
| PRIMARY KEY (\`QuickBooksID\`))                                    |
| ENGINE = InnoDB                                                    |
| DEFAULT CHARACTER SET = utf8mb4                                    |
| COLLATE = utf8mb4\_0900\_ai\_ci                                    |

#### PurchaseOrder Lines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`PurchaseOrderLine\` ( |
| ---------------------------------------------------------------------- |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                             |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`Description\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`IsManuallyClosed\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`ManfacturerPartNo\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`Qty\` VARCHAR(500) NULL DEFAULT NULL,                                |
| \`Rate\` VARCHAR(500) NULL DEFAULT NULL,                               |
| \`ReceivedQty\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`ServiceDate\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`TaxAmount\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                                |
| \`PONumber\` VARCHAR(100) NULL DEFAULT NULL,                           |
| PRIMARY KEY (\`QuickBooksID\`))                                        |
| ENGINE = InnoDB                                                        |
| DEFAULT CHARACTER SET = utf8mb4                                        |
| COLLATE = utf8mb4\_0900\_ai\_ci                                        |

#### Checks Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Checks\` ( |
| ----------------------------------------------------------- |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                     |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`Street\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`City\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`State\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`ZipCode\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`Country\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                 |
| PRIMARY KEY (\`QuickBooksID\`))                             |
| ENGINE = InnoDB                                             |
| DEFAULT CHARACTER SET = utf8mb4                             |
| COLLATE = utf8mb4\_0900\_ai\_ci                             |

#### Check Lines Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`CheckLines\` ( |
| --------------------------------------------------------------- |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                      |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`SalesRep\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`Cost\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`Description\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`InvSiteLoc\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`InvSite\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                    |
| \`LotNumber\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`Qty\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`SerialNumber\` VARCHAR(500) NULL DEFAULT NULL,                |
| \`TaxAmount\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                         |
| \`CheckNumber\` VARCHAR(100) NULL DEFAULT NULL,                 |
| PRIMARY KEY (\`QuickBooksID\`))                                 |
| ENGINE = InnoDB                                                 |
| DEFAULT CHARACTER SET = utf8mb4                                 |
| COLLATE = utf8mb4\_0900\_ai\_ci                                 |

#### Credit Card Charge Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`CreditCardCharge\` ( |
| --------------------------------------------------------------------- |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`VendorName\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`RefNumber\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                               |
| PRIMARY KEY (\`QuickBooksID\`))                                       |
| ENGINE = InnoDB                                                       |
| DEFAULT CHARACTER SET = utf8mb4                                       |
| COLLATE = utf8mb4\_0900\_ai\_ci                                       |

####  Credit Card Charge Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`CreditCardChargeLines\` ( |
| -------------------------------------------------------------------------- |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                                |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                                 |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                                  |
| \`Customer\` VARCHAR(500) NULL DEFAULT NULL,                               |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                                   |
| \`SalesRep\` VARCHAR(500) NULL DEFAULT NULL,                               |
| \`TaxAmount\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`Cost\` VARCHAR(500) NULL DEFAULT NULL,                                   |
| \`Description\` VARCHAR(500) NULL DEFAULT NULL,                            |
| \`InvSiteLoc\` VARCHAR(500) NULL DEFAULT NULL,                             |
| \`InvSite\` VARCHAR(500) NULL DEFAULT NULL,                                |
| \`ItemName\` VARCHAR(500) NULL DEFAULT NULL,                               |
| \`LotNumber\` VARCHAR(500) NULL DEFAULT NULL,                              |
| \`Qty\` VARCHAR(500) NULL DEFAULT NULL,                                    |
| \`SerialNumber\` VARCHAR(500) NULL DEFAULT NULL,                           |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                                    |
| PRIMARY KEY (\`QuickBooksID\`))                                            |
| ENGINE = InnoDB                                                            |
| DEFAULT CHARACTER SET = utf8mb4                                            |
| COLLATE = utf8mb4\_0900\_ai\_ci                                            |

#### Deposit Header Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Deposit\` ( |
| ------------------------------------------------------------ |
| \`DepAccount\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`DepositTotal\` VARCHAR(500) NULL DEFAULT NULL,             |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                     |
| \`TxnDate\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                      |
| PRIMARY KEY (\`QuickBooksID\`))                              |
| ENGINE = InnoDB                                              |
| DEFAULT CHARACTER SET = utf8mb4                              |
| COLLATE = utf8mb4\_0900\_ai\_ci                              |

#### Deposit Line Database Table 

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`DepositLines\` ( |
| ----------------------------------------------------------------- |
| \`Account\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`Amount\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`CheckNumber\` VARCHAR(500) NULL DEFAULT NULL,                   |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                         |
| \`Entity\` VARCHAR(500) NULL DEFAULT NULL,                        |
| \`Memo\` VARCHAR(500) NULL DEFAULT NULL,                          |
| \`PaymentMethod\` VARCHAR(500) NULL DEFAULT NULL,                 |
| \`TxnType\` VARCHAR(500) NULL DEFAULT NULL,                       |
| \`QuickBooksID\` VARCHAR(500) NOT NULL,                           |
| PRIMARY KEY (\`QuickBooksID\`))                                   |
| ENGINE = InnoDB                                                   |
| DEFAULT CHARACTER SET = utf8mb4                                   |
| COLLATE = utf8mb4\_0900\_ai\_ci                                   |

 Items Database Table

| CREATE TABLE IF NOT EXISTS \`QBDtoDBTemplate\`.\`Items\` ( |
| ---------------------------------------------------------- |
| \`ListId\` VARCHAR(100) NOT NULL,                          |
| \`FullName\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`AssetAccount\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`AverageCost\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`BarcodeValue\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`BuildPoint\` VARCHAR(100) NULL DEFAULT NULL,             |
| \`COGSAccount\` VARCHAR(500) NULL DEFAULT NULL,            |
| \`Class\` VARCHAR(500) NULL DEFAULT NULL,                  |
| \`IncomeAccount\` VARCHAR(500) NULL DEFAULT NULL,          |
| \`IsActive\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`IsTaxIncluded\` VARCHAR(100) NULL DEFAULT NULL,          |
| \`ManufacturerPartNumber\` VARCHAR(100) NULL DEFAULT NULL, |
| \`Max\` VARCHAR(100) NULL DEFAULT NULL,                    |
| \`Name\` VARCHAR(100) NULL DEFAULT NULL,                   |
| \`PrefVendor\` VARCHAR(100) NULL DEFAULT NULL,             |
| \`PurchaseCost\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`PurchaseDesc\` VARCHAR(500) NULL DEFAULT NULL,           |
| \`PurchaseTaxCode\` VARCHAR(100) NULL DEFAULT NULL,        |
| \`QuantityonHand\` VARCHAR(100) NULL DEFAULT NULL,         |
| \`QuantityonOrder\` VARCHAR(100) NULL DEFAULT NULL,        |
| \`QuantityonSalesOrder\` VARCHAR(500) NULL DEFAULT NULL,   |
| \`SalesDesc\` VARCHAR(500) NULL DEFAULT NULL,              |
| \`SalesPrice\` VARCHAR(100) NULL DEFAULT NULL,             |
| \`SalesTaxCode\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`Sublevel\` VARCHAR(100) NULL DEFAULT NULL,               |
| \`TimeCreated\` VARCHAR(100) NULL DEFAULT NULL,            |
| \`UnitofMesure\` VARCHAR(100) NULL DEFAULT NULL,           |
| \`ExpenseAccount\` VARCHAR(500) NULL DEFAULT NULL,         |
| \`ItemType\` VARCHAR(500) NULL DEFAULT NULL,               |
| \`ItemDiscount\` VARCHAR(300) NULL DEFAULT NULL,           |
| \`DiscountRate\` VARCHAR(300) NULL DEFAULT NULL,           |
| PRIMARY KEY (\`ListId\`))                                  |
| ENGINE = InnoDB                                            |
| DEFAULT CHARACTER SET = utf8mb4                            |
| COLLATE = utf8mb4\_0900\_ai\_ci                            |

{% 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 Desktop to Database** follow the below steps:

1. In the home screen, click **Add Template** next to the Projects icon.
2. Select the template: **QuickBooks Desktop 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 Desktop** 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 Desktop** and **Database**.
9. Click **Next** and you will be redirected to the **Setup** screen.

<figure><img src="https://1036205596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fv9avy716UiAsS24zOznZ%2Fuploads%2FTBtNPQn8hV23VATeVaK7%2FScreenshot%202024-08-22%20201030.jpg?alt=media&#x26;token=bb201124-dd2d-460a-9bba-02d128f65c0d" alt=""><figcaption><p>Connect Screen</p></figcaption></figure>

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

<figure><img src="https://1036205596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fv9avy716UiAsS24zOznZ%2Fuploads%2FasgrdYFEA2Hgk5FQssaT%2FScreenshot%202024-08-22%20201137.jpg?alt=media&#x26;token=27678daa-6cd0-4d05-9172-e4977d17cc74" alt=""><figcaption><p>Setup Screen</p></figcaption></figure>

#### Run

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

<figure><img src="https://1036205596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fv9avy716UiAsS24zOznZ%2Fuploads%2FZyJOZEAcROnULYVqrHtS%2FScreenshot%202024-08-12%20at%2012.40.54%20PM.png?alt=media&#x26;token=2d4082ba-09ea-464d-9362-1a70bc9a663e" alt=""><figcaption><p>Run Screen</p></figcaption></figure>

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