Quickbooks Data Schema?

Question

I'm trying to develop a simple app for my staff to log their time and expenses on a remote (access) db, and then sync that with QuickbooksPro. I have managed to get QBFC to work, my problem now is understanding the underlying database structure (field names etc) in Quickbooks - e.g. so I know which elements to add to queries etc.

I have found the following (EntityRef, CustomerRef) by trial and error, and by looking at a table pulled using QOBDC; however, there must be documentation of this somewhere - anyone know where?

    TimeDataQry.IncludeRetElementList.Add("EntityRef")
    TimeDataQry.IncludeRetElementList.Add("CustomerRef")
    TimeDataQry.IncludeRetElementList.Add("TxnDate")
    TimeDataQry.IncludeRetElementList.Add("BillableStatus")
    TimeDataQry.IncludeRetElementList.Add("Duration")
    TimeDataQry.IncludeRetElementList.Add("TimeModified")

Answer 1

to be honest, the actual QB data schema is horrible - huge and confusing. It's not worth trying to figure out how to interact directly with their API. That's one of the reasons people like QODBC created simpler ways to interact with it. Have you purchased QODBC?

I've created apps using QODBC and RSSBus, and I like RSSBus better. There are many reasons, but for starters, QODBC is actually a driver that uses DNS's, which can be confusing and have to be set up on each computer that you want to use this on whereas RSSBus does not have any of that. What QODBC and RSSBus do is wrap the QB db in a sort of sqlserver-like db, so any table structures you pull from the QODBC tool will most likely have no correlation to anything you could use through QBFC.

I prefer RSSBus now, but here are links to documentation for both:

RSSBus - http://www.rssbus.com/kb/help/RQR3-A/pg_imetracking.rst

QODBC - http://doc.qodbc.com/qodbc/Qodbc_20_us.html

Also, this SO question may help - QuickBooks QBFC explanation. Notice that he says there are 300+ pages of documentation for this. A little too much imo.

Answer 2

Use the QuickBooks OSR (it works best in Chrome):

• https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html

Choose the request/object type you're dealing with from the "Select Message" drop-down, and click on any of the nodes to view documentation on the node.

For example, what you posted above looks like you're looking at Time Tracking entries. Here's what you get from the OSR about EntityRef:

Entity

A QuickBooks "entity" is a customer, vendor, employee, or person on the QuickBooks "other names" list. Special cases to note:

• In a BillToPayQuery message, EntityRef refers to the vendor name.
• In JournalCreditLine and JournalDebitLine messages for A/R accounts, EntityRef must refer to a customer, or else the transaction will not be recorded. For A/P accounts the EntityRef must refer to a vendor, or else the transaction will not be recorded.
• In a TimeTracking message, EntityRef cannot refer to a customer, only to an employee, vendor, or person on the "other names" list whose time is being tracked.

Pretty simple, pretty detailed.

It's also worth noting that the QuickBooks APIs almost exactly mirror the QuickBooks GUI. For example, when creating an invoice in the QuickBooks GUI, you'll see fields like:

• Customer
• Date
• Invoice #
• Item
• PO Number

And then if you look at InvoiceAdd in the API, you'll find these fields:

• CustomerRef
• TxnDate
• RefNumber
• ItemRef
• PONumber

It shouldn't be terribly difficult to put two and two together and figure out that these fields match the fields presented in the GUI.

Answer 3

Here is the QODBC documentation on table schemas: All Tables. If you want specific US related table groupings like Customer and AR then check here: Tables by Group