QuickBooks Desktop to Database
Learn how to sync QuickBooks Desktop Accounting Application with on premise 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:
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
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 Desktop to Database follow the below steps:
In the home screen, click Add Template next to the Projects icon.
Select the template: QuickBooks Desktop to Database template which allows for running multiple workflows and integration between the two environments.
The template will be added to the list of Your Projects.
Click the template and you will be redirected to the Task Screen.
Click Setup to access various workflows pre-built for the integration.
Setup Wizard
You will be redirected to the setup screen.
Verify your applications are connected QuickBooks Desktop and Database of your preference.
Connect
Click Test Connection and you will get a pop-up confirming the same. Ensure you verify the connection for both QuickBooks Desktop and Database.
Click Next and you will be redirected to the Setup screen.
Setup
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.
You can choose No to refrain from inserting the data of a specific field to the Database.
Click Next to run the integration.
Run
Click Next to Run Now to complete the sync.
Click Finish to complete the sync.
Last updated
