Example of Add an Inventory Item

Note: This is an example of how to add an ItemInventory Item. You can also use the stored procedure "sp_lastinsertid ItemInventory" immediately after the insert statement to retrieve a recordset that has the ListID of the new item you just inserted.

Query in QODBC

INSERT INTO ItemInventory ("Name", "IsActive", "SalesDesc", "SalesPrice", "IncomeAccountRefListID", "PurchaseDesc", "PurchaseCost", "COGSAccountRefListID", "PrefVendorRefListID", "AssetAccountRefListID", "ReorderPoint", "QuantityOnHand", "TotalValue", "InventoryDate") VALUES ('NewItemInventory', TRUE, 'SalesNew', 0.0013, 650002-1046372567', 'PurchaseNew', 0.005, '630003-1046215073', '390000-1039739488', '5A0002-1044562956', 25.0, 50.0, 10.0, {d'2021-12-15'})

Note:  QODBC Support Wizard is used to test QODBC SQL queries only and is not a development tool.

Updating Inventory  Item

update  ItemInventory set PurchaseDesc='Here is my details on purchase', salesdesc='Sales description goes here' where FullName='Air Conditioning'

Result in QuickBooks

The above queries result in this in the USA Sample company file in QuickBooks 2022:

Notes

Unique Item FullName

     Before you add any new Inventory Items, you should check the ITEM table first to see if the new Inventory Item Name is not already used in QuickBooks. The ITEM table is a combined list of all Item tables in QuickBooks as below:

     ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, and ItemGroup (with minimum standard fields)

     When inserting a new ItemInventory item, the Name can't already be, for example, an ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, or ItemGroup FullName already. It must be a unique new ITEM FullName!

Locate Item FullName

     To see the item full names, you can run the following query in QODBC Support Wizard:

     SELECT ListID, FullName, Description, Type FROM Item

     In my case, my USA QuickBooks 2022 sample file returned the following items:

Entity Table Reference

      And like ITEM, there's an ENTITY table that's the combined list of all entity tables in QuickBooks:

      Customers, Employees, OtherNames, and Vendors (with minimum standard fields)

      So when inserting a new Customer name, the FullName can't be, for example, a Customer, Employee, OtherName, or Vendor already. It must be a unique new ENTITY FullName in QuickBooks!