Database to QuickBooks Integration

Configure your Database for Integration with your QuickBooks file by following the steps given below.

1. For this Tutorial I will use SQL Server Database Named "DBSyncDB" and 2011 US Edition of QuickBooks.

2. Create tables "Account" and "Invoice" in Database with following the SQL script.

Database Script: SQLSERVER
USE [DBSyncDB]

CREATE TABLE [dbo].[account](
[id] [varchar](20) NULL,[Name] [varchar](50) NULL,[firstname] [varchar](50) NULL,[lastname] [varchar](50) NULL,
[phone] [nchar](20) NULL,[fax] [nchar](20) NULL,[billingstreet] [varchar](200) NULL,[billingcity] [varchar](50) NULL,
[billingstate] [varchar](50) NULL,[billingcountry] [varchar](50) NULL,[billingcode] [varchar](50) NULL,[company] [nchar](100) NULL
) ON [PRIMARY]

GO

USE [DBSyncDB]

CREATE TABLE [dbo].[invoice](
[invoice_no] [nchar](10) NULL,[customerid] [nchar](100) NULL,[item] [nchar](100) NULL,[itemdesc] [nchar](100) NULL,
[quantity] [nchar](10) NULL,[uom] [nchar](50) NULL,[salesprice] [nchar](10) NULL,[total] [nchar](10) NULL,
[billaddress] [nchar](100) NULL,[billingcity] [nchar](100) NULL,[billingstate] [nchar](100) NULL,
[billingpostalcode] [nchar](10) NULL,[billingcountry] [nchar](100) NULL,[date] [nchar](50) NULL
) ON [PRIMARY]

GO

Database Script: MYSQL

CREATE TABLE `dbsyncdb`.`account` (`id` VARCHAR(20) DEFAULT NULL,`Name` VARCHAR(50) DEFAULT NULL,`firstname` VARCHAR(50) DEFAULT NULL,
`lastname` VARCHAR(50) DEFAULT NULL,`phone` VARCHAR(20) DEFAULT NULL,`fax` VARCHAR(20) DEFAULT NULL,
`billingstreet` VARCHAR(200) DEFAULT NULL,`billingcity` VARCHAR(50) DEFAULT NULL,`billingstate` VARCHAR(50) DEFAULT NULL,
`billingcountry` VARCHAR(50) DEFAULT NULL,`billingcode` VARCHAR(50) DEFAULT NULL,`company` VARCHAR(100) DEFAULT NULL
)
ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Table structure for table `invoice` */

CREATE TABLE `dbsyncdb`.`invoice` (`invoice_no` CHAR(10) DEFAULT NULL,`customerid` CHAR(100) DEFAULT NULL,`item` CHAR(100) DEFAULT NULL,
`itemdesc` CHAR(100) DEFAULT NULL,`quantity` CHAR(10) DEFAULT NULL,`uom` CHAR(50) DEFAULT NULL,`salesprice` CHAR(10) DEFAULT NULL,
`total` CHAR(10) DEFAULT NULL,`billaddress` CHAR(100) DEFAULT NULL,`billingcity` CHAR(100) DEFAULT NULL,
`billingstate` CHAR(100) DEFAULT NULL,`billingpostalcode` CHAR(10) DEFAULT NULL,`billingcountry` CHAR(100) DEFAULT NULL,
`date` CHAR(50) DEFAULT NULL)

ENGINE=INNODB DEFAULT CHARSET=utf8;

Database Script: ORACLE

CREATE TABLE "SYS"."ACCOUNT" ("id" VARCHAR(20  BYTE) DEFAULT NULL,"Name" VARCHAR(50  BYTE) DEFAULT NULL,"firstname" VARCHAR(50  BYTE) DEFAULT NULL,
"lastname" VARCHAR(50  BYTE) DEFAULT NULL,"phone" VARCHAR(20  BYTE) DEFAULT NULL,"fax" VARCHAR(20  BYTE) DEFAULT NULL,
"billingstreet" VARCHAR(200  BYTE) DEFAULT NULL,"billingcity" VARCHAR(50  BYTE) DEFAULT NULL,"billingstate" VARCHAR(50  BYTE) DEFAULT NULL,
"billingcountry" VARCHAR(50  BYTE) DEFAULT NULL,"billingcode" VARCHAR(50  BYTE) DEFAULT NULL,
"company" VARCHAR(100  BYTE) DEFAULT NULL
);

/*Table structure for table `invoice` */

CREATE TABLE "SYS"."INVOICE" ("invoice_no" CHAR(10 BYTE) DEFAULT NULL,"customerid" CHAR(100 BYTE) DEFAULT NULL,"item" CHAR(100 BYTE) DEFAULT NULL,
"itemdesc" CHAR(100 BYTE) DEFAULT NULL,"quantity" CHAR(10 BYTE) DEFAULT NULL,"uom" CHAR(50 BYTE) DEFAULT NULL,"salesprice" CHAR(10 BYTE) DEFAULT NULL,
"total" CHAR(10 BYTE) DEFAULT NULL,"billaddress" CHAR(100) DEFAULT NULL,"billingcity" CHAR(100) DEFAULT NULL,
"billingstate" CHAR(100 BYTE) DEFAULT NULL,"billingpostalcode" CHAR(10 BYTE) DEFAULT NULL,"billingcountry" CHAR(100 BYTE) DEFAULT NULL,
"date" CHAR(50 BYTE) DEFAULT NULL);

NOTE: Make sure both the tables have sample data in them before they can be mapped within DBSync.

Database Setup

1. Open www.mydbsync.com in your browser. Then, click on Customer Login.

2. Enter in your DBSync Username and Password to Sign in. Once logged in, click on Launch to access DBSync Integration Console.

3. Once logged into the DBSync console, click on Create New Project button .

4. You can use any naming convention for a project as long as there are no white spaces in the name of the Project.

5. Once the Project is created, click on Connector --- Edit to configure the Connectors.

6. Click on the Create New Connector from the top right section of the page.

7. Enter the name for the Connector and select Database Adapter from the drop down. Click on Save button once done.

8. Similarly, click on the Create New Connector button again to add QuickBooks Connector to your DBSync profile.

9. Once both the Connectors are added, you will see them listed in the Connector listing page.

10. Click Edit link next to the Database Connector. Enter your Database credentials as shown. In this tutorial, the database used is SQL Server 2005 and corresponding credentials are entered.

NOTE: If you are using a different Database server, you can enter the connection string for the same as shown here.

1. Click on Validate link for the Database adapter. If the credentials entered are accurate, you will get a pop-up message saying the "Connection Settings are Valid".

2. At this point, you have successfully validated your Database Adapter.

QuickBooks setup

1. Click Edit link next to the QuickBooks Connector from the Connector listing page.

2. Under the file section, enter the full path of your QuickBooks file as shown.

3. You can retrieve full path/location of your QuickBooks file by pressing 'F2' key on your keyboard when in QuickBooks 'Home' page - as shown below.

1. Click on configuration Link at the bottom of the page. A Pop-up box will appear, prompting you to open the file with "QuickBooks Web Connector (default)". Click OK as highlighted in the image given belowbelow.

1. Click OK in the next window (first screenshot below). Then, select the option button "Yes, always; allow access even if QuickBooks is not running" and check the check box for "Allow this application to access personal data". Finally, click continue.

1. In the Access Confirmation window, click Done - as shown above . And the profile will get added to your QuickBooks Web Connector. A screenshot of the QuickBooks Web Connector is shown below.

1. Enter your DBSync password in the password section of the Web Connector and Save.

2. At this point, you have successfully completed configuring your QuickBooks with DBSync. You can run the integration by clicking on the Update Selected button.

Custom Account Mapping For Account And Invoice Tables

1. Create a Project, Process with Names of your choices.

2. Create a Workflow with name "DB2Customer" *and likewise create another Workflow with name "DB2QBInvoice"*.

3. Click Edit on workflow "DB2QBCustomer". This will redirect you to Workflow detail page.

4. Create a rule. Then, select the Target as QuickBooks and choose the object to which you want insert/upsert the records.

5. From the Trigger section, choose the DB connector. From Advanced Query builder, enter the following query. User can run the query and view the result in Query Designer.

6. If a user wishes to set the properties, s/he can do it from the Properties Tab.

 Select * from account 

1. For a rule created, set the following properties by selecting the Properties Tab.

Table : CustomerAddRq

maxbatchsize : 1

writetype_ _ : insert

autocreate : True

1. Click on the Map link from the available options under the Rule to set the following field to field maps.

Target	=	Source
CustomerAddRq/CustomerAdd/BillAddress/Addr1	=	VALUE("billingstreet")
CustomerAddRq/CustomerAdd/BillAddress/City	=	VALUE("billingcity")
CustomerAddRq/CustomerAdd/BillAddress/Country	=	VALUE("billingcountry")
CustomerAddRq/CustomerAdd/BillAddress/PostalCode	=	VALUE("billingcode")
CustomerAddRq/CustomerAdd/BillAddress/State	=	VALUE("billingstate")
CustomerAddRq/CustomerAdd/CompanyName	=	VALUE("company")
CustomerAddRq/CustomerAdd/Fax	=	VALUE("fax")
CustomerAddRq/CustomerAdd/FirstName	=	VALUE("firstname")
CustomerAddRq/CustomerAdd/IsActive	=	"true"
CustomerAddRq/CustomerAdd/LastName	=	VALUE("lastname")
CustomerAddRq/CustomerAdd/Name	=	VALUE("Name")
CustomerAddRq/CustomerAdd/Phone	=	VALUE("phone")
VALIDATEROW	=	"true"

At this point, your DBSync is set to move Account records from your database table to QuickBooks Customer.

Invoice Mappings

1. From the Workflow listing page, click on the Edit link to open the "DB2QBInovoice" worfklow.

2. Follow the same procedure for Creating and Configuring Rule as explained in earlier section.

3. Configure the Trigger for the DB2QBInvoice state with the following code from the Advanced query builder section.

select invoice_no,invoice_no as invoicenumber,customerid,item,itemdesc,quantity,uom,
salesprice,total,billaddress,billingcity,billingstate,billingpostalcode,billingcountry,
convert(varchar,cast(date as datetime),126) as 'date' from invoice

1. Make sure the XML Formatter section is populated with the following code.

customerid,billaddress,billingcity,billingstate,billingpostalcode,billingcountry,date,
invoice_no,[item,itemdesc,quantity,salesprice]

5. Now configure the Rule Properties as given below.

Table : InvoiceAddRq

maxbatchsize : 1

writetype_ _ : insert

autocreate : True

The Rule mapping can be mapped from the following table .

Target	=	Source
InvoiceAddRq/InvoiceAdd/BillAddress/Addr1	=	VALUE("billingaddress")
InvoiceAddRq/InvoiceAdd/BillAddress/City	=	VALUE("billingcity")
InvoiceAddRq/InvoiceAdd/BillAddress/Country	=	VALUE("billingcountry")
InvoiceAddRq/InvoiceAdd/BillAddress/PostalCode	=	VALUE("billingpostalcode")
InvoiceAddRq/InvoiceAdd/BillAddress/State	=	VALUE("billingpostalcode")
InvoiceAddRq/InvoiceAdd/CustomerRef/FullName	=	VALUE("customerid")
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd	loop	"invoice_no/list"
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Desc	=	VALUE("itemdesc")
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/ItemRef/FullName	=	VALUE("item")
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Quantity	=	VALUE("quantity")
InvoiceAddRq/InvoiceAdd/InvoiceLineAdd/Rate	=	VALUE("salesprice")
InvoiceAddRq/InvoiceAdd/IsPending	=	"true"
InvoiceAddRq/InvoiceAdd/TxnDate	=	VALUE("date")
VALIDATEROW	=	"true"

In the above mappings, we are using the operator Type as Loop so that line items are grouped as a list. For more info on the loop operator, refer to the above XML Formatter link .

At this point, you have configured your DBSync profile to integrate both the Accounts and Invoice tables that were created in your SQL Server Database. A similar procedure can be followed for other databases like MYSQL,Oracle,DB2 etc. The only thing that would be different, within DBSync, would be the connection strings for the respective databases.