Search code examples
sql-serverdata-structuresquickbooksqodbc

Quick Books Data Layout in DB in SQL Server

I am new to QuickBooks. I am working on a staging SQL Server 2017 (v14) for grocery store data.

The QuickBook data was uploaded to server.And many tables are empty.

The datalayout is as in: https://doc.qodbc.com/qodbc/usa

I am looking to understand the data structure, to be able find the Purchasing Amount of Inventory, grouped by department per week.

The data is grocery store data. The QB has Payroll data tables. I am able to make sense of this payroll data.

But unable to find Purchasing Data- I do not see how the items can be grouped (class?) and where is the DateField (TxnDate?) and how do I summarize for a week.

There are some reports on QuickBooks that can brought into Excel; should I use that? Any pointers on which one?

I am not able to understand the column names ListIDs (a lot of this - may be descriptors) and Txn ID and TxnlineID.

Any pointers on how to understand how the inventory purchasing data is filed and kept in QBs- will help a lot.

https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2369/0/how-to-use-the-quickbooks-reporting-engine-with-qodbc

Solution

  • QuickBooks has two types of data, Lists and Transactions (Txn). The ListID is the primary key for the list table, and the TxnID is the primary key for transaction tables. If a transaction has line items (like a Bill) each line has it's own TxnLineID. Inventory can be purchased (or returned) through four transactions: Bill, Check, CreditCardCharge, or VendorCredit (for returning inventory to vendor).

    The Bill/Check/CC/VC tables will also have their corresponding LineItem tables, as these transactions can have more than one item purchased at a time. These will have the ItemLine after the parent table name, i.e. BillItemLine. Each of these lines will have a Item reference back to the ItemList table to know what item was purchased. The IDs that QuickBooks uses is (like 4651C-1355327815) is it's own internal generated ID, but it functions just like a primary key, and the other tables that have references (like ItemLineItemRefListID) are the Foreign Key to the other tables.

    https://doc.qodbc.com/qodbc/usa/TableList.php?categoryName=Purchases shows all the purchasing transactions, but you only need to look at the ones that have ItemLines. Other purchasing transactions, like PurchaseOrders do NOT effect inventory quantities in QuickBooks. Only Bill, Check, CreditCardCharge, and VendorCredit have an effect.