An Example of Invoice Item Groups

Create an ItemGroup with Three Lines

Note: Let's create a new item group example with three lines using QODBC. Note the FQSaveToCache field, set to True except on the last one.

First Line

INSERT INTO "ItemGroupLine" ("Name", "IsActive", "ItemDesc", "IsPrintItemsInGroup", "ItemGroupLineItemRefListID", "ItemGroupLineQuantity", "FQSaveToCache") VALUES ('Test1 W3 Lines 1', 1, 'Test Item Desc', 1, '670004-1044572237', 1.0, 1)

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Second Line

INSERT INTO "ItemGroupLine" ("Name", "IsActive", "ItemDesc", "IsPrintItemsInGroup", "ItemGroupLineItemRefListID", "ItemGroupLineQuantity", "FQSaveToCache") VALUES ('Test1 W3 Lines 2', 1, 'Test Item Desc', 1, '6D0003-1044572301', 2.0, 1)

Third (Last) Line

INSERT INTO "ItemGroupLine" ("Name", "IsActive", "ItemDesc", "IsPrintItemsInGroup", "ItemGroupLineItemRefListID", "ItemGroupLineQuantity", "FQSaveToCache") VALUES ('Test1 W3 Lines 3', 1, 'Test Item Desc', 1, '500004-1044567946', 3.0, 0)

Result in QuickBooks

The result looks like this in QuickBooks 2022:

SELECT ListID, Name, ItemDesc, IsActive FROM ItemGroup

Invoice the ItemGroup

Note: We can now invoice the item group using the ListID:'80000091-1481802639' and make one simple QODBC insert statement below.

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineGroupItemGroupRefListID","FQSaveToCache")
VALUES ('10002-1048342233', 'Group2', '80000091-1481802639', 0)

Result in QuickBooks

When we look at the new Invoice in QuickBooks 2022, we will see the following:

Note: The Qty, Rate, and Amount are shown as 0 for Floor Plans and Concrete Slab, not because QODBC made a mistake. You need to correct the rate of the service or stock item:

Add Additional Lines to the Invoice

Note: You can add additional lines to the Invoice later by locating the TxnID for the Invoice by using (for example) below.

SELECT Top 10 TxnID, RefNumber, CustomerRefFullName FROM Invoice ORDER BY TimeCreated DESC

To get the TxnID of the Invoice that you inserted earlier, and then add new normal line items after the group by doing, for example:

INSERT INTO "InvoiceLine" ("TxnID", "InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID")
VALUES ('8BF1-1481803738', '80000091-1481802639', 'Test Add a Line',
1.00000, 1.00, '20000-1011136881')

Result in QuickBooks