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:
| 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 |
Running the script mentioned above in your Database is mandatory before creating a table.
Prerequisites
To access the DBSync Cloud Workflow, follow these steps:
Login to DBSync Cloud Workflow by navigating to the DBSync website.
Click on Login and choose Cloud Workflow as shown in the image below:
Enter the Username and Password credentials that you provided during signup and click Login.
Home Screen
In the Setup tab, click on Database and select the appropriate end point from the list of available Database.
Click on QuickBooks Desktop Connector. Subsequently, click on the link Connect to QuickBooks. Sign into QuickBooks Desktop to authorize DBSync integration to connect to Intuit.
Click Save and Test Connection.
You are now ready to run the integration using pre-built forms.
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
