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, '1A0000-933270542', 'PurchaseNew', 0.005, '1E0000-933270542', 'A40000-1197774038', '60000-933270541', 25.0, 50.0, 10.0, {d'2005-10-07'})

Note:  VB DEMO is to be used for testing of 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

Above queries results in this in the USA Sample Rock Castle Construction company file in QuickBooks 2006:

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 common 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 VB Demo:

     SELECT ListID, FullName, Description, Type FROM Item

     In my case, my Australian QuickBooks 2004 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 common 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!