Sync Datarails with QuickBooks Desktop

Note: This article explains the initial set-up of your QuickBooks integration. If you'd like to add a second entity, follow these instructions instead.

 

Datarails Integration solution lets you consolidate all your data sources and seamlessly connect your external apps to the Datarails web app. For more information contact our integrations team here.

What you need to get started:

  • Admin user in Datarails. 
  • Admin user In QuickBooks Desktop.
  • Your QuickBooks Desktop account admin username and password. 

Step 1: Prepare QuickBooks Desktop Data for Syncing

Important: Make sure you are working on the computer (generally a server) that is permanently active. This is because you will install the QuickBooks Web Connector file, and the QuickBooks Web Connector must be active permanently. If it is not, the synchronization will not update at the set intervals (default is set to one minute).

To install and connect the Web Connector, you must login as an admin into QuickBooks Desktop.

Step 2: Create a New Data Source in Datarails

  1. From the left pane in Workspace, select Admin > Data Sources. 
  2. From the top right of the screen, click the + icon to open the Add Data Source dialog box

sync_quickbooks_desktop_edit_data_no_email_field.png

  1. Complete the dialog box: 
    • Organization: Your company 
    • Type: QuickBooks Desktop 
    • Sync User: Your company's Sync User email 
    • Name: Give the integration a name
  1. At the bottom of the screen, click Save. A new button will appear titled, Send invitation on email. Click it.

sync_quickbooks_desktop_edit_data_with_email_field.png

Step 3: Download & Install Web Connector

  1. Leave the screen open and go to your email account.

sync_quickbooks_desktop_email_with_password.png

  1. Important: Make a note of the password. You will need it later.
  2. Click Download File and save it to the computer / server of your choice to install the Web Connector. If you are not viewing the email with the download link on the permanently active server, please copy the download URL and open in a browser on the server so that the Web Connector is downloaded onto the remote server that hosts QBD.
    • The Web Connector that has been installed must be active permanently. This file is the QuickBooks Web Connector that needs to be active permanently.
  3. You will need to be logged in to Datarails on the same server where QuickBooks Desktop is located, and then you will be able to open the file on the same browser where Datarails is open. Double click the file to open it. 

sync_quickbooks_desktop_application_certificate-1.jpg

  1. Select the radio button "Yes, always access even if QuickBooks is not running". A dialog box opens. 

sync_quickbooks_desktop_connector_certificate_screen_2.png

  1. Click Yes. The dialog box closes, and you are back to the Certificate. 
  2. Select the Admin user for the login. 

The username that is used for the synchronization cannot be logged into QuickBooks Desktop when synchronizations are active. We recommend that that you create a special user for QuickBooks Desktop - Datarails synchronizations.

  1. After you have entered an appropriate username, click Continue. Another dialog box opens. 

sync_quickbooks_desktop_connector_certificate_screen_3-1.jpg

  1. Click Done. The Web Connector opens. Before you continue, you must verify the following: 
    • The checkbox on the far left is Active.
    • Auto-Run: is active.
    • Every-Min: is set to the synchronization interval required. The default is 1 minute.
    • Status: Last Result shows OK
    • Password: You have entered the password that was in the email you received earlier.

sync_quickbooks_desktop_web_connector_OK-1.jpg

  1. If all is correct, click the button Update Selected. Be sure to leave the Web Connector window open.

Step 4: Check within Datarails if Connection is Set

  1. With the Web Connector open, go back to Datarails. 
  2. From the left pane in Workspace, select Admin > Data Sources > Edit Data Source. You will return to this screen. 
  3. Click Test at the bottom of the screen.

sync_test_sand_save_buttons.png

If the settings are accepted, the Test Results box will read, OK. Last connection at [current date and time stamp].

Important! The datetime stamp must be the current time. If it is not the current time, this means that you have lost the connection with the Web Connector.

Step 5: Create a New Filebox

For the most up-to-date and complete instructions on how to create a new filebox via the workspace or the Excel add-in, please go here.

Step 6: Prepare the Data for Synchronization

Choose Your Sync Option - Basic or Advanced

Before you synchronize, decide which of two options you will use to filter out data:

  • Basic: We recommend this option because much of the hard work has been done. We’ve selected the tables most used and made the most common filters available with dropdowns to make it easy to apply them.
  • Advanced: This option has all the fields for you to create more complex filters.

The rest of this article assumes you are using the Basic option.

Determine the Sync Settings 

  1. From Workspace, select the Filebox you just created and click the Settings tab.
  2. Scroll down to the Data Connection section. 

sync_quickbooks_desktop_basic_advanced.png

  1. Complete the following fields: 

    • Filebox Data Connection Type: Click Edit and select QuickBooks Desktop.

    • Connection Settings: Select Basic. 

    • Object Types - Object to Query: From the dropdown, select one of the three (3) tables available (Journal Entry, Transaction or Account). Here we've selected Journal Entry.

    • Sync Query: The data should by synced by a defined period set by the date tag. Do not leave the Settings tab and scroll up to the Version Tags section.

sync_settings_version_tags_month.png

  1. In that section, select Date and Month (or the period required). 

  2. Go back to the Data Connection section and filter the data you will sync.

    Basic Filters: To get a description of the different filters for each of the 3 tables General Ledger Details, Budget and GL Budget, see the section Filter Options Before Syncing.

  3. When you have finalized the optimization for syncing, select the period for the synchronization.sync_sage_intacct_sync_now_month.png

  1. When complete, click Save
    • If the settings are accepted, you will see the message Query Saved at the bottom of the screen.

  2. Next click Sync Now.

    • If the settings are accepted, in the top right of the screen you will see the message Command Sync Now sent successfully

sync_success.png

  1. The message confirms that the information sent to QuickBooks Desktop meets the necessary requirements. Now QuickBooks needs to receive the message and start the synchronization. This is done via the QuickBooks Desktop Web Connector.
  2. When all the settings are accepted, you're ready to move to Step 7: Receive Data from the Web Connector.

Filter Options Before Syncing

Each table has different filter options. 

Journal Entry Filters

If you use the Basic option, these are the filters for the Journal Entry table:

sync_quickbooks_desktop_filter_journalentry.png

Filter Description
From / To Modified Date The default is Start of Month and End of Month. Click the dropdown for more options
Include Line Items The default has the toggle ON

To complete the synchronization, go back to the step, Determine the Sync Settings. 

Transaction Filters

If you use the Basic option, these are the filters for the Transaction table:

sync_sage_intacct_general_ledger_detail_filters.png

Filter Description
From / To Modified Date Default is Start of Month and End of Month. Click the dropdown for more options.
Transaction Detail Level Select from the dropdown: None, All, Summary Only, All Except Summary
Transaction Paid Status Select from the dropdown: None, Either, Closed, Open.
Transaction Posting Status Select from the dropdown: None, Either, Non Posting, Posting.

To complete the synchronization, go back to the step, Determine the Sync Settings. 

Account Filters 

If you use the Basic option, these are the filters for the Account table:

sync_quickbooks_desktop_filter_account_table.png

Filter Description
Active Status Select from the dropdown: None, Active Only, Inactive Only, All.

To complete the synchronization, go back to the step, Determine the Sync Settings. 

Step 7: Receive Data from the Web Connector

sync_quickbooks_desktop_collaboration_feed_notification-2.png

  1. From Workspace, select the Filebox and go to the Collaboration tab. Look at the Feed and wait for the Web Connector to connect. If the sync time is set to the default (1 minute), there should be almost no time lag before the connection is activated.

  2. Once the Web Connector starts, you will see several notifications in the Feed showing the different data packets sent by the Web Connector to Datarails.

  3. When the process is complete, you will see the message Session close success.

Step 8: Review and Verify the Synchronization

  1. From the Review tab, you can see the file that was imported.

  2. To verify that the data is correct, click the ellipsis (3 dots ⋮ icon) and select Preview Version.

  3. Verify that the data is as expected.

  4. If all is OK, the synchronization of the first company file is complete.

If there are no more company files to synchronize, the process is complete. If you wish to synchronize more than one company file, continue below.

Step 9: Add Additional Company Files (Optional)

Each additional company file needs its own Filebox and synchronization. 

Prepare QuickBooks Desktop Data for Syncing

Login to the company you wish to add to Datarails in QuickBooks Desktop. Keep it open during the connection process.

Add Additional Company Files

There are some slight differences. Read on! 

  1. From the left pane in Workspace, select Admin > Data Sources >Edit Data Source. The first company file shows automatically.

sync_quickbooks_desktop_edit_data_source_add_more_files-1.png

  1. Click the Authenticate Current Company File button. Wait a few moments and then the Application Certificate screen opens. This is a screen that you used when you added the first company file

sync_quickbooks_desktop_application_certificate-1__1_.jpg

  1. Select the radio button "Yes, always access even if QuickBooks is not running". A dialog box opens

sync_quickbooks_desktop_connector_certificate_screen_2__1_.png

  1. Click Yes. The dialog box closes and you are back to the Certificate
  2. Select an appropriate user for the login

The username that is used for the synchronization cannot be logged into QuickBooks Desktop when synchronizations are active. We recommend that that you create a special user for QuickBooks Desktop - Datarails synchronizations.

  1. After you have entered an appropriate username, click Continue. Another dialog box opens.

sync_quickbooks_desktop_connector_certificate_screen_3-1__1_.jpg

  1. Click Done. The Web Connector opens but the Status section shows an error, and the Web Connector locks.

sync_quickbooks_desktop_web_connector_error-1.png

Workaround when adding more company files: Currently, the QuickBooks Web Connector locks when you add more company files. To unlock the Web Connector, continue as described below...

  1. Click the Exit button. This closes the Web Connector. 
  1. In this next step, we will be reopening the Web Connector. From Workspace, select Admin > Data Sources >Edit Data Source.

sync_quickbooks_desktop_edit_data_source_add_more_files-1__2_.png

  1. Click the Authenticate Current Company File button to open the Web Connector
  2. You will see a new message in the Status section

sync_quickbooks_desktop_web_connector_received_request_screen-1__1_.png

  1. You may need to wait a moment or two for the connection handshake. When successful, you will see the screen you saw in the first synchronization. The Status Last Result shows "OK". 

sync_quickbooks_desktop_web_connector_OK-1__1_.jpg

  1. Before you continue, verify all fields:
    • The checkbox on the far left is Active.
    • Auto-Run: is active.
    • Every-Min: is set to the synchronization interval required. The default is 1 minute.
    • Status: Last Result shows OK.
    • Password: You have entered the password that was in the email you received earlier on in the process from QuickBooks Desktop.

sync_quickbooks_desktop_web_connector_OK-1__2_.jpg

  1. If all is correct, click the button Update Selected and leave the Web Connector window open
  2. With the Web Connector open, go back to Datarails.
  3. From the left pane in Workspace, select Admin > Data Sources > Edit Data Source
    • You will be back at the Edit Data Source screen

sync_quickbooks_desktop_data_source_company_file_2_shows.png

  1. Click the Refresh Company Files button. This time you will see that the second company file has been added
  2. Click Test at the bottom of the screen

sync_test_sand_save_buttons.png

If the settings are accepted, the Test Results box will read, OK. Last connection at [current date and time stamp]

  1. Click Close. You will then see your integration in the Data Sources window.

Step 10: Review and Verify the Synchronization

  1. From the Review tab, you can see the file that was imported
  2. To verify that the data is correct, click the ellipsis (3 dots ⋮ icon) and select Preview Version
  3. Verify that the data is as expected
  4. If all is OK, the synchronization of the second company file is complete

If there are no more company files to synchronize, the process is complete. If you wish to synchronize more company files, repeat the process.



© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.