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

An Example of Creating One ItemInventoryAssembly with Two Lines

First, please create a new MS Access database & link ItemInventoryAssembly, and ItemInventoryAssemblyLine tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the 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, 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 the 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:

Due to this issue, the IsActive field contains a boolean datatype & it accepts 1/0 or true/false as input. I have used 'Active' as a value in the IsActive field. Please use the 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 the QODBC Table schema:

QODBC Data Layouts