QuickBooks Desktop Data Availability | CLEARIFY

QuickBooks Desktop Data Availability and Design

In 2001, Intuit introduced their Software Development Kit (SDK), but 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 2011, 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.

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

QuickBooks Analytics 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.

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. 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.

Don't assume that because you see something in QuickBooks that it is stored in the database accordingly.

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.