01 July 2010

With our custom Drupal-based CRM, any form of database sync required at least some level of customization. I settled on a two-step sync. First, I enhanced the capabilities of a PHP-based QuickBooks Web Connector server script. Once all of the relevant configurations were being synced bidirectionally, the Ubercart order data (and corresponding CRM data) simply needs to be synced unidirectionally into the server-side QB data.

The system is based on the principle of eventual consistency. Any online order with modifications in the prior 7 days is synced with some frequency to the online database. The QB Web Connector only allows updates while QuickBooks is open. While it is open, it re-syncs every X minutes. It takes 4-6 web+QB syncs to get all of the data into QB: create products, create contacts, create invoice, create payment, and clear payments.

Limitations of the QB sync:

  1. QB Web Connector only runs when the same Windows user has QuickBooks open.
  2. There are strict (undefined) limits on which objects can be update. Ex: paid invoices do not seem to update.
  3. Depositing from undeposited funds does not work. Our solution was to use undeposited funds as a holding account. Payments go into undeposited funds, and then payments are grouped as an adjusting entry is cleared from the undeposited funds account. The undeposited funds account should always be at zero.

blog comments powered by Disqus