How to create an Item Inventory Assembly through MS Access using QODBC

An Example of Creating One ItemInventoryAssembly with Two Lines

First of all please create new MS Access database & link ItemInventoryAssembly, ItemInventoryAssemblyLine table.
Please refer below mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking table, we need to execute the query in MS Access query editor:
MS Access>> create tab >> Query Design >> It should popup for choosing table, please click "close" button & click on "SQL" from left top corner below File menu.
Note the FQSaveToCache field, set to True, except on the last one.

First Line

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, COGSAccountRefListID, AssetAccountRefListID, ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity, FQSaveToCache) VALUES ('NewAssembly1', 1, 'QODBC New Assembly1', '340000-933270542', '1E0000-933270542', '60000-933270541', '160000-933272656', 2.0, 1)

Second Line

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, COGSAccountRefListID, AssetAccountRefListID, ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity, FQSaveToCache) VALUES ('NewAssembly1', 1, 'QODBC New Assembly1', '340000-933270542', '1E0000-933270542', '60000-933270541', '130000-933272656', 1.0, 0)

Result in QuickBooks  

Questions and Answers

Question

     This seems to work for adding multiple items (ItemInventoryAssemblyLine records) to an assembly when the assembly is first created.

     However, I am having trouble adding new ItemInventoryAssemblyLine records to an existing assembly. When I try to repeat an INSERT with FQSaveToCache set to 0, then I get an error

     QODBC Error 3100 - The name "NewAssembly1" of the list element is already in use (#10053)

Answer

   I don't think we can add items to an existing assembly. The workaround would be to create a new assembly with the additional items 

Problem Description:

I want to add new assemblies to the item list. I've tried the two queries below. Both result in this error:

invalid operand for operator: <assignment>
code 11024
Can you suggest a query that will work? 

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName) VALUES ('12082-74262', 'Active', 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income', 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold:Materials - Cost of Goods Sold', '80000021-1351453684', 'Inventory Asset'); 

INSERT INTO ItemInventoryAssembly (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, CustomFieldcoo, CustomFielditemClass) VALUES ('12082-74262', 'Active', 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income', 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold:Materials - Cost of Goods Sold', '80000021-1351453684', 'Inventory Asset', 'TW', 'am');

Solution:

You have used 'Active' as a value in IsActive field due to this issue happen. IsActive field contains boolean datatype & it accepts 1/0 or true/false as input. Please use below query & try again.

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName,FQSaveToCache) VALUES ('12082-74262', 1, 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income', 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold:Materials - Cost of Goods Sold', '80000021-1351453684', 'Inventory Asset',1)

INSERT INTO ItemInventoryAssembly (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, CustomFieldcoo, CustomFielditemClass,FQSaveToCache) VALUES ('12082-74262', 1, 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income', 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold:Materials - Cost of Goods Sold', '80000021-1351453684', 'Inventory Asset', 'TW', 'am',0)


Please refer below-mentioned link for getting details about QODBC Table schema:

QODBC Data Layouts