Mirroring QuickBooks Data to an SQL Database

The purpose of the QuickBooks SQL Mirror is to facilitate easier access to QuickBooks desktop editions data by extracting the data and making it available in normal SQL tables. The data can then be manipulated within the SQL database and, if desired, written back to QuickBooks automatically.

To understand the “how” and the “why” of this process, it is important that you understand the process for communicating with QuickBooks and a little bit about QuickBooks internals. Most editions of QuickBooks *are not* based on an SQL database platform. QuickBooks uses an internal proprietary database system for the most part, and there is no way to natively read or write data in that system. Some newer versions of QuickBooks use an SQL database based on SQL Anywhere, but even then, the data is locked in that database and cannot be read or written to.

Intuit instead provides a way to communicate with QuickBooks via an XML language dubbed qbXML. While robust, qbXML can be difficult to work with and program interfaces against.

This software instead extracts the data from QuickBooks using qbXML queries, and then writes changes back to QuickBooks by converting SQL records to qbXML requests.

OK, So How Do I Use It?

The software is going to create a huge pile of tables in your SQL database, probably around ~140 of them. If you have it set up in read/write mode, then the software will do it's best to try to make sure everything stays in sync automatically. If you have it set to read-only mode, it will just pull records in from QuickBooks, but never send any data back. If you have it set to write-only mode, then it will only write data to QuickBooks, but never pull any data back to the SQL database.

That means that every time the Web Connector application runs, the software will automatically pull any new or updated records from QuickBooks and update the SQL database.

Likewise, if you do any UPDATEs or INSERTs into the SQL tables, it will try to send that data back to QuickBooks. If you're using MySQL (which supports a TIMESTAMP trigger that automatically updates whenever a record is UPDATed/INSERTed), all you have to do is UPDATE or INSERT a record. If you're using any other database, you need to UPDATE or INSERT the record and make sure to set the qbsql_modify_timestamp field to the current date/time!

If an error occurs during the syncing process, the error will be logged to the qbsql_last_errnum and qbsql_last_errmsg fields of the record for which the error occurred.

Frequently Asked Questions

This is the first time I've run the mirror, and I keep seeing output like "SalesRepRet node: ListID SalesRepRet node: TimeCreated SalesRepRet node: TimeModified"

This only happens the very first time the script runs. The first time the script runs, it builds an SQL schema for you and dumps out a bunch of debug output. The second time it runs, it'll work fine.

This is the first time I've run the mirror, and it's taking **forever** to sync the data!

The initial sync of the data may take a very long time to complete if you have a large number of transactions within QuickBooks. No, there is nothing we can do about it at this time.

However, please keep in mind that only the initial sync takes that long! Once you have an initial copy of the data, the scripts only pull in things that have been added, modified, or deleted in QuickBooks, so updates after the initial sync are much, much, much quicker.

QuickBooks SQL Mirror Gotchas

  • Blank lines on invoices/purchase orders/bills/etc.: To enter a blank line for an invoice/purchase order/bill/estimate/sales receipt line item line, you *must* set at least one ' ' (space) character for the 'Descrip' field of the object type, or the line item will instead take on the default value of the item description within QuickBooks. e.g. UPDATE qb_order_orderline SET Descrip = ' ' WHERE TxnLineID = …
  • Updating line items for invoices/purchase orders/bills/etc.: If you update the line items for a transaction, you must update the qbsql_modified_timestamp to the parent record in order for the change to be caught and synced back to QuickBooks. If you just modify the line item and never touch the parent record, the sync will never be caught by the framework. This applies to tables such as:
    • qb_invoice_invoiceline
    • qb_estimate_estimateline
    • qb_purchaseorder_purchaseorderline
    • qb_receivepayment_appliedtotxn
    • qb_bill_itemline
    • qb_bill_expenseline

QuickBooks SQL Mirror Table Schemas

Fields Common to all SQL Tables

There are a few fields that are common to all SQL tables in the schema. There are documented below:

  • TxnID - This field is common to all transaction-type objects (invoices, payments, purchase orders, credits, etc.) within QuickBooks. If you're using the code in READ/WRITE mode, when you INSERT a record into one of these tables you should make up a random, unused TxnID and INSERT that value into the table. The TxnID you use should be globally unique because some TxnID references/foreign keys are shared across multiple record types. GUIDs or CONCAT(microtime(), mt_rand()) values are good choices. Once QuickBooks assigns a real TxnID to this field, you should not change it.
  • ListID - This field is common to all non-transaction-type objects (customers, vendors, payment methods, etc.) within QuickBooks. If you're using the code in READ/WRITE mode, when you INSERT a record into one of these tables you should make up a random, unused ListID and INSERT that value into the table. The ListID you use should be globally unique because some ListID references/foreign keys are shared across multiple record types. GUIDs or CONCAT(microtime(), mt_rand()) values are good choices. Once QuickBooks assigns a real ListID to this field, you should not change it.
  • qbsql_discov_datetime - This field indicates the date/time the SQL record was discovered by the framework (the time it was initially added from QuickBooks or your application). DO NOT UPDATE THIS FIELD.
  • qbsql_resync_datetime - This field indicates the date/time the SQL record was last synced with the data in QuickBooks. DO NOT UPDATE THIS FIELD (unless you really know what you're doing and you're trying to avoid the default behavior of the framework to update the record in QuickBooks whenever an update occurs on the SQL record).
  • qbsql_modify_datetime - This field indicates the date/time the SQL record was last modified. For most database systems, this will update automatically whenever you change the record (using a database trigger or SQL timestamp field). However, it is good practice to update this field to the current timestamp (NOW(), or GETDATE(), or CURRENT_TIMESTAMP, or etc. etc. etc.) whenever you change the record and want the change to be synced back to QuickBooks.
  • qbsql_last_hash - This is an internal checksum value for the SQL record. DO NOT UPDATE.
  • qbsql_last_errnum - If an error occurred when syncing this SQL record to QuickBooks, the last error number/code will be indicated here. Note that if this field contains an error message, the record will not sync with QuickBooks until you fix the problem, and clear this field.
  • qbsql_last_errmsg - If an error occurred when syncing this SQL record to QuickBooks, the last error message will be indicated here. Note that if this field contains an error message, the record will not sync with QuickBooks until you fix the problem, and clear this field.
  • qbsql_enqueue_datetime -
  • qbsql_dequeue_datetime -
  • qbsql_to_sync - Don't use this field. It's not supported right now.
  • qbsql_to_delete - When you want to delete a record from QuickBooks, you should set this field to 1 (or TRUE). Your application should not display records with a qbsql_to_delete field value of 1.
  • qbsql_to_void - When you want to VOID a transaction in QuickBooks, you should set this field to 1 (or TRUE).
  • qbsql_to_skip - If for some reason you want QuickBooks to *not* do anything with this record (i.e. don't send the UPDATE or INSERT to QuickBooks), then set this to 1 and the framework will ignore this record.
  • qbsql_flag_deleted - When a record is deleted in QuickBooks, this field will be set to 1. Your application should not display records with a qbsql_flag_deleted field value of 1.
  • qbsql_flag_voided - When a record is voided in QuickBooks, this field will be set to 1.
  • qbsql_flag_skipped -

Customers

Customer-related Transactions: Invoices, Sales Receipts, etc.

Items

Vendors

Vendor-related Transactions: Bills, Purchase Orders, etc.

Miscellaneous Other Types

QuickBooks DB Table SQL API

quickbooks_integration_php_consolibyte_sqlmirror.txt · Last modified: 2013/01/21 12:44 (external edit)