[QODBC Online] Inventory and Item Tables in QODBC Online vs. QODBC Desktop

Problem Description:

Customers migrating from QODBC Desktop (QuickBooks Desktop) to QODBC Online (QuickBooks Online) often look for the same table names they used in the Desktop edition - particularly Item and ItemInventory. These tables do not exist in QODBC Online because QODBC Online uses the QuickBooks Online REST API, which has a different set of available entities than the QuickBooks Desktop SDK.

Affected products: QODBC Online, QuickBooks Online
Expected outcome: Inventory items in QODBC Online are accessed via the ItemService table. For Group items 'ItemGroup' table, and for individual lines of a group item, use 'ItemGroupLine'.

Table Mapping - Desktop vs Online:

Querying Inventory Items in QODBC Online:

To retrieve inventory items (including quantity on hand) from QODBC Online, use the ItemService table. Despite its name, this table contains both service and inventory items in the QuickBooks Online API:

SELECT * FROM ItemService WHERE Type = 'Inventory'

To filter by a specific inventory item name:

SELECT * FROM ItemService WHERE Name = 'Widget'

To get the quantity on hand for inventory items:

SELECT Name, QtyOnHand, Type FROM ItemService WHERE Type = 'Inventory'

Filtering Inactive Items:

By default, the QuickBooks Online API returns only active items. To retrieve inactive inventory items:

SELECT * FROM ItemService WHERE IsActive = 0

Additional Notes:

• Not all Desktop tables are available in Online. The QuickBooks Online API exposes fewer entities than the Desktop SDK. For example, the Transaction table (available in QODBC Desktop) does not exist in QODBC Online - customers need to query individual transaction-type tables (Invoice, Bill, SalesReceipt, CreditMemo, JournalEntry, Payment, Purchase, TimeTracking) or use the GeneralLedger report via SP_reports. For a complete list of available tables in QODBC Online, refer to the QODBC Online Table List.
• Field names may differ. Even when table names overlap between Desktop and Online, field names can vary. Refer to the ItemService table details for the full schema in QODBC Online.
• Assembly items are not available in QODBC Online. The QuickBooks Online API does not expose Assembly items. You must use QODBC Desktop to access assembly data from a QuickBooks Desktop company file.

Related Articles:

Difference Between QODBC for QuickBooks Desktop and Online QODBC Online Optimizer Full Sync - Running Multiple Queries Simultaneously Fails Does QODBC Send My QuickBooks Data to FLEXquarters? Understanding QODBC Data Security and Privacy

Keywords: