Database to Quickbooks Integration

DBSync – Database to Quickbooks Integration

• Configure your Database for Integration with your Quickbooks file. 
• For this Tutorial I will be using SQL Server Database Named "DBSyncDB" and 2011 US Edition of Quickbooks.
• Create tables "account" and "invoice" in Database with following SQL script.

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

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;

 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

• Open www.mydbsync.com in your browser and click on Customer Login.
• Enter in your DBSync username and password to login.
• Once logged in, click on My Home from the menu and click on Launch button in the following page as shown. 

• Once logged into the DBSync console, click on Create Profile button as shown below.

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

• Once the profile is created, click on Manage — Edit to configure the adapters.

• Click on the Create Adapter on the top left hand side of your window and select the required adapters from the drop down menu as shown. 

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

• Similarly, click on the Create Adapter button again to add Quickbooks Adapter to your DBSync profile.

• Once both the adapters are added, you will see them listed within the console page as shown.

• Now click on the Edit link next to the Database Adapter and 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.

• 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".
• At this point you have successfully validated your Database Adapter.

Quickbooks Setup

• Click on the Edit link next to the Quickbooks adapter in the console page.
• Under the file section, enter the full path of your Quickbooks file as shown.
  

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

• 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 on OK button as highlighted below in screenshot.

• Click on OK in the next window (first screenshot below) and on the next window 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" and click on continue button (second screenshot below).

• In the Access Confirmation window click on the Done button as shown above . And the profile will get added to your QuickBooks Web Connector and a screenshot of the QuickBooks Web Connector is shown below.

• Please enter your DBSync password in the password section of the Web Connector and save it. 
• 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 Mapping for Account and Invoice tables.

• Click on Manage — Edit to navigate to adapter configuration page.

• Click on Add Process Icon on the right-hand side of your screen. Enter Process Name and Click on Ok. A new process will be created and listed under profile name on the right side of the screen.

• Double-click on the Process Name "SyncProcess" or the one you have created with a desired name to navigate to the Process Builder Console. The process will contain 2 States by default:

1. Start_state
2. End_state

• Click on [X] icon on Start_state to de-link Start_State to End_state.

• Drag the Create State Button onto the control flow area to create a new state. Enter the state name as "DB2Customer". Similarly create another state called "DB2QBInvoice" and click on OK Button. There will be two new boxes/states in the Control flow area. Link all the states by first right-clicking on each box and left-click on the box to establish connection with. A screenshot below illustrates the same. 

• Once the connection is established between all the boxes / states, you can see an arrow connecting each state. This constitutes the Process Definition Language for your integration profile.

Accounts Mappings:

• Double-click on "DB2QBCustomer" state or right-click on the same and click on "Open Dataflow" to open the state.
• Drag Create Reader, Create Map & Create Writer into the center of the console and name each one as "DBReader", "DBmap" & "QBWriter" respectively. A screenshot below illustrates the same.
• Establish connection between Reader, Map and Writer by first right-clicking on a box and select "connect" and left clicking on the following box as shown.

Note: Always establish connection between states in the sequence of Reader: — Map: — Writer:

• Once the connection is established, all control flows will be displayed with arrows. A screenshot below illustrates the same. 

• Right Click\Properties on DBReader. The properties will be displayed in the bottom left area of DBSync Console. Select Database from Adapter Drop Down. 

• Enter the following query within the query section of the database adapter.  

   select * from account 

• Right-click on the DBwriter now and select "properties" and configure the following.

Table :  CustomerAddRq

maxbatchsize  :  1

writetype_ _ :  insert

autocreate  :  True

• Similarly right-click on the DBmap state now and select "properties". Click on* *Edit link besides  ****_map.xml file to open the mapping page in a new window or a new tab. Enter the following mapping.

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

Invoice Mappings:

• From the process builder page, double click on the SyncProcess and open the "DB2QBInovoice" state by double-clicking on the same.
• Now follow the same procedure as you did for Account Mappings by dragging Create Reader, Create Map & Create Writer to create DBreader, DBmap & DBwriter respectively and establish connection between the same.
• Configure the DBreader for the DB2QBInvoice state with the following code.

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

• Make sure the XML Formatter section is filled up with the following code.

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

• Now configure the DBwriter 

Table :  InvoiceAddRq

maxbatchsize  :  1

writetype_ _ :  insert

autocreate  :  True

•  Now open the DBmap and enter the following mapping in the mapping window.

• 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 changing within DBSync would be the connection strings for the respective databases.