Knowledgebase
[QODBC-Desktop] How to insert data into ItemNonInventory using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Add a Non-Inventory Item with the "This item is used in assemblies or is purchased for a specific customer:job" field checked

Instructions for this Option

      Actually there's not a column for "This item is used in assemblies or is purchased for a specific customer:job". It's actually controlled by the columns used in the INSERT statement. For example, when I execute as follow:

Query in QODBC

INSERT INTO ItemNonInventory
("Name", "IsActive", "SalesTaxCodeRefListID", "SalesAndPurchaseSalesDesc",
"SalesAndPurchaseSalesPrice","SalesAndPurchaseIncomeAccountRefListID", "SalesAndPurchasePurchaseDesc",
"SalesAndPurchasePurchaseCost", "SalesAndPurchaseExpenseAccountRefListID", "SalesAndPurchasePrefVendorRefListID")
VALUES
('Support Ticket', TRUE, '10000-999022286', 'Callback VoIP Charge',
33.00, '1D0000-933270542', 'CallBack VoIP',
33.00, '4D0000-933270542', '140000-933272657')


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

Result in QuickBooks

      Then can get the result with the option "This item is used in assemblies or is purchased for a specific customer:job":

 


Example of Creating a Standard Non Inventory Item

Note: To create a standard Non Inventory Part you can use a more simplified INSERT statement like below.

Query in QODBC

INSERT INTO ItemNonInventory
("Name", "IsActive", "SalesORPurchaseDesc",
"SalesTaxCodeRefListID", "SalesOrPurchasePrice", "SalesOrPurchaseAccountRefListID")
VALUES
('QODBC NonInventory Test', TRUE, 'Test Insert of Non Inventory Part',
'20000-999022286', 100.00, '190000-933270541')

Result in QuickBooks

      Which looks like this in QuickBooks 2006 instead:

Notes

Unique Item FullName

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

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

      When inserting a new ItemNonInventory 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, Other names, and Vendors (with minimum common fields)

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

See also: How to add an Inventory Item using QODBC

(173 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).