Page History

Have you ever thought about integrating QuickBooks to a database Database of your choice? The benefits are legion . From - from backing up your data in QuickBooks, to copying selected data for use in Analytics or other business tools.

In this article, you will learn how to create this the integration with DBSync. This The tool is very user-friendly , - making integration a task for anyonebreeze.

The results are also fast. By defining workflows, you can quickly create different integration paths according to meet your business needs, which . Workflows can be then run manually or automatically via a scheduler.

The integration process involves nine steps, which . The steps are explained via below by providing an example.

In order to show how to integrate QuickBooks into a databaseDatabase, we will use an example on of how to transfer Accounts data from QuickBooks to a MySQL databaseDatabase.

In order to repeat this example, you first need to create a MySQL database Database named “qbtodb”. This database must contain a table named “Accounts”, which contains . The table must contain two fields: “AccountName” and “AccountAmount”.

Although the example shows how to transfer records from QuickBooks to a databaseDatabase, the process could be defined inversely. That is, you could do a transfer from a database Database to QuickBooks in a similar manner. That is, by just changing the source and destination data.

So, let’s start with our integration process.

In order to integrate QuickBooks to a databaseDatabase, the first thing that you need to do , is to create a project Project that will contain all the necessary information.

...

Your second step is to create a connector Connector that will allow you to send and retrieve information from your QuickBooks app.

In order to do so, you need to click on the Create New Connector button. The system will ask for a connector’s Connector’s name and type. Enter any name. Just remember that a connector’s Connector’s name cannot include any space between letters. For the connector’s Connector’s type, select QuickBooks Online Rest Connector.

...

Now the system will ask for your Quickbook QuickBook credentials. Enter them and click on the Connect button. The connector’s Connector’s name will appear on the left-hand side menu. And, and the setting parameters will be on the right-hand screen.

Figure 5: Quickbook’s credentials.

...

Figure 6: Connector’s details.

Your next step is to create a connector Connector to your databaseDatabase. In order to do this, click on the Create New Connector button again.

A screen asking for a connector’s Connector’s name and type will appear. Enter a name of your choice and select Database Connector as type. Then, click on the Save button.

Figure 7: Create a database connectorDatabase Connector.

Now you need to enter the connection details of the database, Database - namely, database Database type, host, port, and database Database name.

In our case, we will be using a MySQL database Database that runs on a local machine (localhost). The port will be the default 3306, and we will use a database Database named “qbtodb”.

The system will then automatically generate the URL based on the information provided.

...

Figure 8: Database connection details.

In order to create a new processProcess, click on the Create New Process button. The system will ask you for a process’ name of your Process. Enter a name of your choice. Just, once more, remember that the name cannot include blank spaces. Then, click on the Save button. Your process Process will be added to the list of processesProcesses.

Figure 9: Create a new processProcess.

Now that you have created a processProcess, you need to include at least one workflowWorkflow. Workflows define the data transfer , that is, i.e., what data is transmitted, the source and, the destination.

To create a workflowWorkflow, click on the Create New Workflow button. The system will ask you for a workflow’s Workflow’s name. Enter any name of your choice. Once again, remember that workflow Workflow names must not include blank spaces. Then, click on the Save button.

Figure 10: Create a new workflowWorkflow.

As explained earlier, workflows Workflows include the data transfer information. Therefore, you need to define a data source or trigger; and, and a data destination or rule. The rule must include a mapping between the source and the destination data.

Create a

...

Trigger

In order to define a triggerTrigger, you need to input the data source Data Source or connector first, which in Connector first. In this case is , it would be the QuickBooks’ connectorConnector.

The system will now load all the objects available in that data sourceData Source. Select one of them. In this example, we will use the “Account” object.

It is important to remember that you can have only have one data source Data Source.

Figure 11: Create a triggerTrigger.

Create a

...

Rule:

Now, you need to define a data destination. This is done via a rule Rule definition. However, notice that now you can define more than one rule Rule or data destinationdestinations.

Click on the Add New Rule button. A new rule Rule line will appear in the list of rulesRules. You can either change the Rule name of the rule or, keep the default onename.

Select the data destination connector Connector from the menuMenu. In this case, the MySQL database connectorDatabase Connector. Then, select an operation. Your choices are:

1. Insert: inserts Inserts data from the source to the destination.
2. Update: updates Updates data on the destination, based on changes on the source.
3. Upsert: updates Updates and inserts Inserts at the same time.
4. Delete: deletes Deletes data on the destination , - based on the information from the source.

Now, select Select a target object from the menuMenu. In our example, we have created a table named “Accounts” in the database “qbtodb”.

Figure 12: Create a ruleRule.

Create a

...

Mapping

Up to this point, you have defined a data source and a data destination. The next step is to define the relationship between the two. In order to do so, click on the Map link. A screen showing both , - data source objects and data destination objects - will appear.

Mapping fields on the Mapping Screen is very easy. You just need to drag and drop fields from the data source schema into the target or data destination list.

In this example, the QuickBooks fields “Name” and “Current Balance” are mapped to the database Database fields - “AccountName” and “AccountAmount” - respectively.

Figure 13: Mapping Screen

Save your

...

Workflow

Although it may not look so important, this step is crucial, because . Because if you don’t save the workflow Workflow details, you will lose the information and have to redo all of it again.

To save the workflowWorkflow, simply click on the Save Workflow button. A message indicating success will appear. Click on the OKbutton, and your workflow Workflow will be added to the list of available workflows Workflows in the process.

Figure 14: Save the workflowWorkflow.

Figure 15 shows the screen with all the processes Processes available. The example has one process Process with one workflowWorkflow.

This screen also shows a Properties section, where you can define the type of log level. And, and an email where add an Email so that the results of the run will can be sent there.

In order to run the processProcess, click on the Run Now button. You can see the completed steps done by the system on the Console (figure 16).

Figure 15: Run the processProcess.

Figure 16: Console.

Once the run is done, you can go to the Logs section that appears appear on the menu on the left-hand side. The Log Summary shows information about the run , - such as the number of records read from the source and the number of records written on the destination. There is also an icon showing success (green background, check symbol); or, failure (red background, exclamation symbol). There is also a link to the logs, which can be Logs. This can come handy in case of running you run into problems.

In our example, the process Process was successfully run. And, and 25 records were read from QuickBooks, and sent to the MySQL database.

...

Figure 17: Log Summary.

Now that you know that your process Process was successfully run, it is wise to check the databaseDatabase.

In our example, we open the “qbtodb” database Database with the phpMyAdmin tool (figure 18), and verify that all the 25 records have been properly transferred.

...

A useful feature of DBSync is its Scheduler. It allows you to automate your processes Processes by defining running schedules.

Creating a new schedule is straightforward. Select Scheduler from your the left-hand side menu. The Scheduler screen will ask for several parameters , - such as process name, start date and time, and , Process Name, Start Date and Time and, a repeat routine that can be defined in minutes, hours, weekly, or at a specific day and time. The tool also accepts Cron expressions.

...

Throughout this series of three articles, you have seen saw how to integrate QuickBooks with a databaseDatabase, by using the DBSync application.

In the first part, you have learnt how to create a project, Project and connectors Connectors for QuickBooks and a database Database respectively.

In the second part, you have seen saw how to define your integration flow. That is, how to define a process Process and a workflowWorkflow. A key aspect of this section was to define a mapping between QuickBooks objects and database Database fields.

In this third and last section, you have learnt how to run the process Process and check that all values were are properly saved in the databaseDatabase.

The example showed how to transfer data from QuickBooks to a MySQL databaseDatabase. Similarly, you could have transferred to another database, Database - such as Oracle or MSSQL. DBSync integration also works bi-directionally , - meaning that you can transfer data from QuickBooks to a database Database and vice versa.