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 customerjob" field checked.

Instructions for this Option

      There's no column for "This item is used in assemblies or is purchased for a specific customerjob." It's actually controlled by the columns used in the INSERT statement. For example, when I execute as follows:

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 only to test QODBC SQL queries 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 simplified INSERT statement like the one 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 complete item 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 names, or Vendor already. It must be a unique new ENTITY FullName in QuickBooks!

See also: How to add an Inventory Item using QODBC.

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