Tools of the Pros: OpenSync by Synergration

by

OpenSync provides the ability to synchronize multiple QuickBooks company files with multiple external databases including Access, SQL Server, MySQL and Oracle. Changes made in QuickBooks are automatically replicated to the external database and changes marked in the database are automatically posted back to QuickBooks. OpenSync always treats QuickBooks as the database of record and never overwrites updates made in QuickBooks.

The fundamental work level of OpenSync is something called a ‘task.’ OpenSync performs a task each time an update of your external database is performed. Configuration of OpenSync tasks are essential to the proper use of OpenSync. Once configured, tasks can either run interactively or on a scheduled basis.

The first step in using OpenSync is creating a companion database that will be associated with a specific QuickBooks Company file. OpenSync only works with databases it creates and those created by other Synergration compatible products. This is unfortunate because it means that you can not use OpenSync to link to a previously configured database; however, this limitation also means that the database configured by OpenSync should match the QuickBooks data schema without compromise.

To add a database to OpenSync, right click on the Databases node within the tree view and select ‘New,’ as shown below.

Once you follow the step-by-step instructions you will initially have created a test database. As you can see in the illustration below we created a Microsoft 'Access' database for use with your QuickBooks Company file.

At this point, we are ready to associate the database with our QuickBooks Company file. Make sure QuickBooks is running and then open the QuickBooks Company file you want to associate with your OpenSync ‘Access’ test database. You must be logged-in as the Admin user during this initial connection.

To add a company file to OpenSync, right click on the Company Files node in the tree-view and select ‘New’ as shown below.

You will then proceed through the normal SDK Integrated Application related connection process. Make sure to select the options to ‘always allow access even if QuickBooks is not running’ and to allow ‘access to personal data such as Social Security Numbers and customer credit card information’.

After you finalize the connection, your QuickBooks Company file is added to OpenSync.

You are now ready to create your first ‘task.’ Remember, the fundamental work level of OpenSync is a ‘task,’ and the essential configuration relating to OpenSync is the configuration of tasks.

To add a task to OpenSync, right click on the Tasks node within the tree-view and select ‘New’ as show below.

OpenSync will then launch the Add Task wizard to take you through the configuration steps:

  • Select the company file and database this task is to use,
  • Select the default operation which will populate the database from QuickBooks by replicating All Tables,
  • Schedule the task, in this case we are going to select the ‘Do not schedule task’ scheduling option, and
  • Assign a name to the task. I chose 'Populate Access Test'

After saving the task setup you have configured, the task will now appear in the tree view of OpenSync.

Clicking on the task shows the details you configured as seen below.

You are ready to run the task which will replicate all of your QuickBooks Company file data into the OpenSync ‘Access’ database you created.

To run the task, either right click on the task node and select Run or select the task in the tree view and click run on the tool bar.  In the illustration below we have magnified the task bar icon (icon identification message) for the Run task operation.

Alternatively, you could have configured a ‘scheduled time’ and date frequency during the scheduling portion of the new task wizard setup.

Once you click ‘Run’ OpenSync will begin the process of copying your QuickBooks Company file into the Access database, the length of time the process may take can vary significantly.  Both the size and complexity of your QuickBooks Company file can impact this process. OpenSync will display the ‘Task Status’ as shown below

You have the option to ‘Cancel’ the process at any time without compromising the integrity of your QuickBooks file. When the 'Cancel' button is active and available for use it will display a red icon, otherwise it is 'gray'.

The OpenSync console will display the ‘status’ of the last tasks once completed.

You can easily check the status of your OpenSync ‘Access’ database by simply clicking on the database name within the tree view.

Pending Adds within this status represent the number of records in the table set to ‘Add,’ these records will be processed the next time an Update table-related task is run. Pending Updates represent the number of records in the table set to ‘Update.’ These records will be processed the next time an Update table-related task is run. Pending Deletes represent the number of records in the table set to ‘Delete,’ these records will be processed the next time an Update table-related task is run.

If no value appears in any of these columns then the specified operation (Add, Update or Delete) is not supported for the specified table.

The Browse DB button (highlighted with a red box) in the picture above will be discussed a little later in this article.

The Error Table displays all errors that result when posting data back to QuickBooks. When this tab is activated, buttons to clear the error table and view the details are also activated.

For users familiar with working within a database of any of the types supported by OpenSync, this utility provides a valuable data resource. OpenSync supports ‘tasks’ for the following data operations:

  • Populate tasks delete all data in the selected table and repopulates it from the QuickBooks Company file.
  • Refresh tasks query QuickBooks for all new or modified data since the last populate or refresh task.
  • Update tasks migrate changes from the external database back to QuickBooks with a table setting of either Add, Update or Delete. Users must be aware of which tables support which functions since not all QuickBooks tables support every operation.
  • Refresh and Update, this task performs a Refresh task followed by an Update task. It is more efficient to perform this combined task then to perform each of the two tasks independently of each other.

Refresh Report is a task that queries a QuickBooks report data-set and returns the data to a database table. Some data not readily available directly from QuickBooks tables maybe available using a QuickBooks report data-set. Users will need to become aware of the data table limitations and the report data-sets supported via the QuickBooks SDK which is used by OpenSync.

The Refresh Report task will display the Report Parameters window within OpenSync to allow you to select a specific report and configure the report parameters (as shown below).

A few paragraphs back I mentioned the Browse DB button that opens the OpenSync browser tool allowing users to browse actual company data. While this is an important feature, experience has shown that large company files may cause their browser tool to fail.

Since the purpose of the tool is to help users learn the QuickBooks database schema, one of the best ways to use this tool is to create an OpenSync ‘Access’ database into which one of the standard QuickBooks sample company files is populated. These sample files are small enough, and typically arranged in a way that enables users to easily browse the schema and understand the various table relationships.

In the example above you can see that the OpenSync DB Browser consists of a tree view listing all of the database tables on the left and one or more table grids on the right. The upper grid on the right displays any data within the table selected in the tree view on the left. Child-table data are displayed in the lower section on the right. 

But the real reason for using OpenSync is to product a database containing the same information, in an identical data schema, as contained within your QuickBooks Company file.  The illustration below shows the data-set results of the populate task we performed within our Access database.

The 'Bill' table has been selected to show the various fields of data queried and returned via OpenSync. You can see by having a complete set of your QuickBooks Company file data outside of QuickBooks in a comprehensive database such as MS-SQL (or in this case a more simplistic database using Access) there is a lot of capabilities ranging from identification of issues or manipulation of the data. 

OpenSync is a professional tool (utility) in that it has a considerable learning curve associated with developing an understanding of the QuickBooks data schema. Since the database, regardless of type selected (Access, SQL Server, MySQL or Oracle) will be configured in essentially an identical fashion to the QuickBooks Company file database structure, the tool will provide information necessary to learn from actual data. However, as I mentioned, I recommend learning this utility using a ‘sample company file’ populated into an OpenSync database.

As a ‘professional tool’ OpenSync is also a powerful utility which allows you to process one or more records back from the OpenSync database you create to your QuickBooks Company file. In order for OpenSync to process a record back to QuickBooks, the status must be set to Add, Update, or Delete depending on how you want the record handled.

As I mentioned earlier, not all operations are supported for all QuickBooks Company file tables. This again necessitates the learning curve associated with development of a knowledge of the database schema, as well as the database you choose for your OpenSync database.

It is clearly beyond the scope of this article to attempt to provide a ‘database schema’ education regarding the QuickBooks Company file, or any of the various database tools with which you might use OpenSync. If you feel that your QuickBooks niche may be data services, then OpenSync is one of the tools you should definitely consider in assisting you in learning the underlying QuickBooks data schema as well as database technology as a whole. 

If you would like more information about OpenSync then visit the Synergration website, and more specifically the OpenSync webpage.