Overview
DBSync for QuickBooks Desktop and Database Integration is an easy to use solution to integrate QuickBooks desktop objects like customers, items, job, sales order & Invoice. QuickBooks to Database uni-directional solution is also useful to migrate historical QuickBooks data into database. DBSync provides uni-directional sync with pre-built field to field map along with flexibility for more complex and dynamic mapping capabilities. DBSync also has an automatic online update, enabling DBSync customers to enjoy all the product updates and features with every new release.
Data Flow
This tutorial serves as an illustration for reading various records from QuickBooks desktop and writing to the configured tables of a database. The below table depicts the data flow between various objects of QuickBooks objects to the chosen database tables.
Process Map
The process map of the uni-directional solution between QuickBooks desktop and a database is as shown in below image.
Prerequisites For Integration
The prerequisites for this integration are as follows
- Have a valid license to DBSync QuickBooks desktop connector, Database connector and username, password to connect to DBSync iPaaS platform.
- QuickBooks Web-Connector should be configured to connect to DBSync to read from QuickBooks desktop. you can download the web connector from Intuit marketplace by click on the link and steps to configure your web connector are depicted in the QuickBooks desktop connector .
- you must have QuickBooks administrative rights.
- Should have a valid database username, password, host/IP address and port on which database is hosted.
The username and password configured for Database connector instance should have sufficient privilege to access the database which is required for integration requirement.
Configuring Source App
The below are the steps to get started with your QuickBooks company file and to configure the QuickBooks connector.
- Click on the Edit of the QuickBooks connector to input the QuickBooks parameters.
- Click on Save to save the settings.
The below table gives the required and optional parameter to be set for establishing a connection of QuickBooks desktop from which you would like to read the data and write to database of your choice.
| Property | Description | Required |
|---|---|---|
| QuickBooks File | The path of the QuickBooks file where it is hosted. The file path can be obtained in the following way
| yes |
| QuickBooks Version | you can choose the relevant version of your QuickBooks Desktop from available values. | yes |
| Process Name | The process name is the process name in DBSync which is trying to access the QuickBooks file and the convention is as follows "processdefinition_processname.xml" | yes |
Configuring Target App
The below are the steps to get started with a database and to configure the database connector.
- The below table gives the required and optional parameter to be set for establishing a connection of database and to write the various tables as chosen in the relevant workflows .
- Click on save to save the parameter settings.
| Property | Description | Required |
|---|---|---|
| Username | Database username | yes |
| Password | Database password | yes |
| Host | Host name or IP address of the database server | yes |
| Port | The port used to connect to database server | yes |
| Database | The name of your database | yes |
| Type | JDBC Driver for the Databases mentioned in the URL section in the same order.
| yes |
| Database URL | JDBC connection string
| yes |
For the sake of understanding, I have chosen MySQL database with tables as contacts, customers, items, oppheader, opplineitem in which data from QuickBooks is dumped. you can download the sql script from this link and import the table structures in-case if you have the same integration requirement which this solution is built.
DBSync Project Setup
- Login into www.mydbsync.com and click on Customer Login.
- Enter your DBSync Username and Password and click on Login.
- Once logged in, Click on template library menu from the left side navigation.
- Search for template name QuickBooks To Database : uni-directional template and import to your workspace.
- You will be re-directed to connector listing of the QuickBooks To Database : uni-directional template
- In the connector listing page, You can see QuickBooks desktop and database connector.
- Click on Edit to configure connectors respectively and individual connector configuration are explained in the subsequent sections.
- Now, you can navigate to the process listing page to view the pre-defined mappings .
- The various transformations can be applied to the each field through the mapping section and save the workflow
- Click on the Run now button to initiate the integration of the chosen workflow.
- The sync can be monitored through the log section of the and in the event of the error, you can drill down the each event log to debug and to fix the error at its occurrence.
Guide Lines For customization And Mapping
Run Integrations
Integration can be run using scheduler or Run Now button. Scheduler job are used to trigger process at scheduled time interval automatically while as manual trigger of the sync can be initiated by hitting the Run Now button from the process listing page / workflow page.
Scheduler
To create a scheduler job , one have to follow the below steps.
- Inside a project, Navigate to the scheduler page from the left side navigation.
- From the Add Schedule section, Create a schedule job by selecting the process from the drop down, start on date , start on time, repeat frequency and save the job.
- If repeat frequency is set to no repeat then scheduled job trigger sync just only once as per scheduled date and time.
- If repeat frequency is set to either minutes, hourly, weekly then the process is scheduled to run at the repeat set frequency.
Run Now
To manually trigger the sync, one can hit the Run Now button in the following manner.
- Hitting the Run Now button from the process listing page.
- Hitting the Run Bow button from workflow listing page.
- By hitting the Run Now button from the Trigger page.
Related References
Attachments:
worddav6546d08dda6098f55db8465728d1ef80.png (image/png)
worddav01d2844458c546f7fac73f35e44a9cf1.png (image/png)
worddava5ac8da50025e53fc566a5e5a3813283.png (image/png)
worddav8bc646fe4d4e88712e09f9c6a6351864.png (image/png)
worddavc1755bcda5f6dded63a93e8139c9ee4c.png (image/png)
worddavb21d700be07ccf91b5d29dee4f77cdc0.png (image/png)
worddav2b43777fcb13c0ae394a0be8185ffd10.png (image/png)
worddavc02447fc335ab325154a01ec35c588c8.png (image/png)
migration_script.sql (application/octet-stream)
QBToDB.qwc (application/octet-stream)
QBToDB.png (image/png)
QB_DB_flow.png (image/png)
QB_DB_dataflow.png (image/png)