QuickBooks Data Availability in QQube | CLEARIFY

QuickBooks Desktop Data Availability and Design

Intuit does not give developers direct access to the QuickBooks database, but have exposed 98% of the available fields through its SDK

History of QuickBooks Data Extraction

In 2001, Intuit introduced their Software Development Kit (SDK). It is not a direct connection to the database; rather it is an XML (hierarchical text) extraction which can be slow on large files. 

In 2006, Intuit added an additional Custom Reporting feature for Enterprise users that allowed a more direct access to QuickBooks.  It is not an Intuit sanctioned method for extracting data in a 3rd party application and does not include all of the available data.  There is no Intuit support for that feature.

In 2014, Intuit introduced Advanced Reporting including a subset of the QlikView visualization product.  However it still requires QuickBooks to be open to use it and it is too difficult for many users. It is also not available to developers.

Intuit's last major SDK release was back in 2013.  They have since come out with several minor releases that contain only a handful of items - and mostly for writing back to QuickBooks, not reading from it.  In August of 2022, they released a 64 bit SDK, but it has shown little - if any - performance improvement.

There are bugs from the early years of the SDK that have never been fixed - and never will be.  We have just learned to work around them - even though it might take a little longer to synch the data.  Accuracy is the most important thing.

QQube, using the SDK, was created in 2010, and is still the most robust tool for getting answers from your QuickBooks data, because it is not a raw connector.  Rather it is a data warehouse with complete models containing fields and measures that a raw connector will never have.

QuickBooks Data Extraction Limitations

Users often make assumptions that because they 'see' things when they use QuickBooks, that the database operates in a similar logical manner.  As you will see below, this is not always the case.

Much of it has to do with the logistics of adding volume to the underlying QuickBooks database, other times, it requires complexities that are only found in higher end accounting programs and database vendors.

Don't assume that because you see something in QuickBooks, it is stored in the database accordingly.  It is NOT always the case.

1. Customer Invoices are not linked to Vendor Bills. There is no link between a particular bill item/expense that has been billed to a particular customer invoice.  QuickBooks only changes the status on the bill to 'has been billed'.  It doesn't record WHERE it has been billed.
2. Cost of a specific inventory item is not available. You cannot link the cost of a particular item in a PO or bill to a particular item on a customer invoice.
3. Estimate change orders are not stored in a table. Change orders are stored only as text in a description field on the original estimate
4. Receive payments. Payments are applied against a whole transaction - not any particular item
5. No Historical state of a Purchase Order or Sales Order.  These are not stored in QuickBooks. If it is open, it is open
6. Time Tracking and Payroll are not linked. Time Tracking entries are NOT linked to the payroll checks.  They are used to generate payroll, but there is no referential integrity between the two as you can change the time tracking entry without changing it in the paycheck, an vice-versa
7. Balance Sheet by Class is Calculated when the report is requested.  When you create, say, a bill with multiple line items, each with a different class, Intuit does NOT split up the A/P amount by class to store in the database.  Rather QB calculates this when someone runs the report in QB.  This makes it impossible to"reverse engineer" a balance sheet by class from the General Ledger Detail. (QQube runs the actual report so that it matches what QB produces.
8. There are NO item links underneath the hood.  All links exposed by QuickBooks are between transactions - NOT the items on each transaction. For instance Bills show a PO number next to the item on the Bill, but QuickBooks can't tell you WHICH item on the PO it refers to.

Specific QuickBooks Unavailable Fields

• Payroll Item Information

• Payroll and Compensation Info
  • Taxes Setup
  • Direct Deposit Setup

• Raises and Promotions
• Leave of Absence

• Termination

• Payroll Dates

• Payroll Liability Dates

• Templates - Custom Field Aliases created under Layout Customization

• Job Status - Custom Alias Names

• Bank Reconciliation - Cleared Status

• Exchange Rate, Foreign Rate, Exchange Gain/Loss in the Credit Card Refund Transaction

Multi-Currency Issue - Pro/Premiere only

• Credit Card Refund Gain/Loss on Exchange.  The ability to get this information is not-possible because of a bug in the SDK.  This amount cannot be even reverse engineered because of how QuickBooks may - or may not - assign the Exchange difference.  In this case the exchange rate was the same for both days, but QB decided to create a penny difference:  This works fine in the US Edition

Pending Payments

• Authorized Payment - non-posting.  It is possible in QuickBooks when processing a payment, that you authorize funds, but do not actually capture the funds.  The result is a non-posting receive payment.  The Intuit SDK does not let us know whether this transaction is pending - rather it comes across as posted.

Class in Header

• Class Name Not Available.  Not available in the SDK for item receipts, bills, vendor credits, checks, credit card charges, credit card credits.

Build Assemblies

• Build Assembly Information.  Of all things in the SDK - and Custom Reporting - this is, by far the biggest piece of swiss cheese - e.g. too many holes to make this work in all situations. We have, through some magic, made this work to grab the most important information for use in the G/L Detail, Profit and Loss Detail, and Inventory Analytics.
  • Enterprise will have all details, including class, UofM, Site, and Bin Location, $ and Qty, and serial #/lot # - should match 99.99% of the time.
  • Pro/Premier will NOT HAVE line class, uofm (and of course no site and bin location) dollars and qty should be ok.
  • Both Enterprise and Pro/Premier - there will be limitations on available information for pending assemblies. e.g. no accounts, line class, uofm,. But the important parts will be there e.g. qty, site id, bin location for the inventory analytic. 

Pending Assemblies

• Quantities (Printed) for Advanced Inventory. The Intuit SDK does not make available the Unit of Measurement - and the link to the UofM extrapolation tables. So the quantity for Pending Assemblies is always shown in the base unit.  This is ok for inventory forecasting, but will not have a matching column on a QB report.

Inventory Transfer

• Inventory Transfer Dollars.  Another SDK missing piece.  Although the net affect is 0.00 it still matters to the Detail Trial Balance e.g. G/L Detail.
  • Enterprise will have the dollars as well as qty and item.
  • Pro/Premier will only have the qty and item - no dollars.