Knowledgebase
[QODBC-Desktop] How to Invoice ItemGroups using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Invoice Item Groups

Create an ItemGroup with Three Lines

Note: Let's start by creating 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, '390000-1071512145', 1.0, 1)


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only 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, '320000-1071525597', 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, '10000-933272655', 3.0, 0)

Result in QuickBooks

The result looks like this in QuickBooks 2006:

 

For more about creating ItemGroups, please see: How to create Item Groups using QODBC.

Locate the List ID of the New ItemGroup

To locate the ListID of the new Item Group (or any group), simply do:

SELECT ListID, Name, ItemDesc, IsActive FROM ItemGroup

Invoice the ItemGroup

 

Note: We can now invoice the item group by using the ListID:'580000-1197762645' and doing one simple QODBC insert statement below.

 

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineGroupItemGroupRefListID","FQSaveToCache")
VALUES ('470001-1071525403', 'Group2', '580000-1197762645', 0)

Result in QuickBooks

When we look at the new invoice in QuickBooks 2006 we will see:

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

 

Add Additional Lines to the Invoice

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

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

to get the TxnID of the invoice 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 ('5CFE-1197770436', '250000-933272656', 'Test Add a Line',
1.00000, 1.00, '20000-999022286')

Result in QuickBooks

(131 vote(s))
Helpful
Not helpful

Comments (5)
Chris Kelley
30 January 2013 01:06 AM
Is there not a way to programmatically set the item group line rate?
Tom Eagle
27 September 2013 02:33 AM
Does anyone have an example of doing the above using VB programming in ACCESS (2010)
Thanks,
Tom
Jack
08 July 2014 10:44 AM
Hi Tom,

Please refer below mentioned article for Example Code of Update QuickBooks from MS Access. You need to do some modification in code & you can get VBA code as per your requirement:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2571/0/using-quickbooks-data-with-vba
John Lundberg
15 April 2016 06:50 PM
I can add a group to an invoice using the above SQL but there is no place to enter the quantity in the SQL example. In my case it is common to add different quantities of groups. In other words say for group A at one time I might want to add a quantity of 2 and another time a quantity of 4 of group A. How do I add a quantity field to the SQL?
Jack
18 April 2016 07:03 AM
Hi John,

I kindly request you to please refer below sample query for inserting InvoiceLineGroupQuantity in Invoice. I kindly request you to please change value according to your company file & try to insert and share outcome.

Query:
INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount,InvoiceLineGroupItemGroupRefListID,InvoiceLineGroupQuantity , InvoiceLineSalesTaxCodeRefListID,FQSaveToCache)
VALUES ('670000-1071517519', '91047', '320000-1071525597', 'POWERTRAK-2000', 200.00000, 200.00,'300000-933272656',11, '20000-999022286',0)


If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).