Knowledgebase: Data & Table
[QXL-ALL] - Table Schema and Join information.
Posted by Rajendra Dewani (QODBC Support) on 22 October 2025 01:56 PM

[QXL-ALL] - Table Schema and Join information.

Problem Description:

I need list of all joins for QuickBooks tables

Solution:

QuickBooks tables are designed around two primary joining patterns: Header-to-Detail (linking a transaction to its line items) and Transaction-to-List (linking a transaction to its associated master records).

The joins are primarily made using unique QuickBooks identifiers: TxnID for transactions and ListID for master records (like Customers, Items, Accounts).

Header Table Detail Table Join Condition Purpose
Invoice InvoiceLine Invoice.TxnID = InvoiceLine.TxnID Get all line items for an invoice.
Bill BillLine Bill.TxnID = BillLine.TxnID Get all line items for a vendor bill.
SalesReceipt SalesReceiptLine SalesReceipt.TxnID = SalesReceiptLine.TxnID Get all line items for a sales receipt.
PurchaseOrder PurchaseOrderLine PurchaseOrder.TxnID = PurchaseOrderLine.TxnID Get all line items for a purchase order.

Transaction/Detail-to-List Joins (Linking to Master Records)

These joins link transaction or line item tables to List tables (e.g., Customer, Item, Account, Vendor) to retrieve descriptive names and details.

The convention is to join a RefListID field (found in the transaction or detail table) to the corresponding ListID field in the master table.

Transaction Field List Table List Table Field Purpose
CustomerRefListID Customer Customer.ListID Find the customer's full name and details.
ItemRefListID Item Item.ListID Find the item's name and details.
AccountRefListID Account Account.ListID Find the account's name and details.
VendorRefListID Vendor Vendor.ListID Find the vendor's full name and details.

These “Relations” entries are embedded throughout each table page of the QODBC schema documentation. To view all joins comprehensively:

  1. Visit https://doc.qodbc.com/qodbc/usa/

  2. Select “Table List (USA)” under “Data Layouts”.

  3. Each table entry includes “Relations / Jumpins / Detail”, describing its join paths and matching foreign keys.

For further precision, you can programmatically list joinable tables using QODBC’s internal stored procedure:

 
sp_tables
 
sp_columns TableName

Running sp_columns on any table (for example, sp_columns InvoiceLine) shows which columns are keys that link to other tables — essentially revealing all possible join relationships.

 

(0 vote(s))
Helpful
Not helpful
Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).