When inserting an invoice via a looping SQL procedure, I have invoice lines (InvoiceLine table) items that can potentially be assigned to varying classes.  Using the ClassRefListID field, I class the lines accordingly, and do not address the ClassRefListID in the main Invoice (header/parent) table.  When I examine the invoice in Quickbooks, it seems that ALL invoice line items have been forced to the SAME class (looks like the class that I last defined in the last child InvoiceLine item).  I need to insert invoices with lines that point to DIFFERENT classes as I can manually in Quickbooks, is this possible using QODBC????  Thank you!

You are correct, when you create or add InvoiceLines, QODBC will always use the last Class referenced in the Invoice (if applicable). For example, this three line invoice:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ClassRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '30000-933272658', 'QODBCClass1', '250000-933272656',
'Building permit Additional Room', 100.00000, 100.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ClassRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '60000-933272658', 'QODBCClass1', '250000-933272656',
'Bin Permit Renovations', 200.00000, 200.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCClass1', '250000-933272656', 'Less Council Rebate',
-50.00000, -50.00, '20000-999022286', 0)

incorrectly appears in QuickBooks with all three invoice lines showing the "Remodel:Referral" Class:

So, you will need to use Plan B:

To get around this you can first create the Invoice and then add invoice lines and specify the Class to use, or not to use, like this:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ClassRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '30000-933272658', 'QODBCClass2', '250000-933272656',
'Building permit Additional Room', 100.00000, 100.00, '20000-999022286', 0)

Once the invoice is created, we can locate the TxnID using the QODBC stored procedure (as long as we haven't broken our QODBC connection):

SP_LASTINSERTID InvoiceLine

The TxnID for the new invoice is 5CA9-1197764583, so we can now add additional lines to the Invoice by doing:

INSERT INTO "InvoiceLine" ("TxnID", "ClassRefListID", 
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID")
VALUES ('5CA9-1197764583', '60000-933272658', '250000-933272656',
'Bin Permit Renovations', 200.00000, 200.00, '20000-999022286')

INSERT INTO "InvoiceLine" ("TxnID", "ClassRefListID",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID")
VALUES ('5CA9-1197764583', '', '250000-933272656', 'Less Council Rebate',
-50.00000, -50.00, '20000-999022286')

The invoice now appears in QuickBooks with different Class values for each line:

The Invoice is the Main Invoice Table and contains a header record for each invoice, while InvoiceLine provides all the line item detail for a customer invoice and contains all the information included in the Main Invoice Table. So you don't need to use the Invoice table if you don't want too. For multi-line invoices, some may find it easier to not include all the header information with each line item, but rather to have seperate smaller commands for the Invoice Lines and a final big INSERT for the Invoice header.

To insert a new Invoice line into the InvoiceLine table for an existing invoice we just need to supply the TxnID as the first column. It's that simple. sp_columns doesn't have an insertable on add column, but if it did, TxnID would be true.

If you are getting a field not being allowed in insert when you try to add the second line, then your FQSaveToCache caching flag is still set true (1) on the first insert, it need's to be set false (0).

Tom, I am in fact setting FQSaveToCache = 0 in the first statement, and then not using this field all together in the second statement.  The procedure fails on the second statement...  Here is the exact SQL that is being executed:

1.

INSERT INTO InvoiceLine
 (Memo, BillAddressAddr1, IsToBePrinted, CustomerSalesTaxCodeRefListID,
 DueDate, ShipDate, IsPending, TxnDate, ARAccountRefListID,
 CustomerRefListID, ClassRefListID, InvoiceLineItemRefListID,
 InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID,
 FQSaveToCache)
VALUES
 ('[PE] - ', 'KARLA FOUNTAIN', 0, '20000-1012341377',
 {d'2006-06-24'}, {d'2006-06-24'}, 0, {d'2006-06-08'}, '4C0000-979674056',
 '4E160000-1150227958', '1FF0000-1104418069', '30000-979670045',
 '', 0.00, 75.00, '20000-1012341377',
 0)

2.

INSERT INTO InvoiceLine
 (TxnID, ClassRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
 InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID)
VALUES
 ('14B6DC-1150298902', '18C0000-1085753034', 'E0000-980263422', '',
 0.00, 500, '20000-1012341377')

-----

Fails upon executing #2.  Error: "Field not allowed in insert."  I also tried removing FQSaveToCache from the first statement all together, but I get the same experience.  What am I missing here?

Thanks!

Check that the first line did create an invoice and that your TxnID is for the invoice. I am already using QODBC v6.00.00.172 which has changes to make TxnID work correctly when adding lines to Sales Receipts. So you could have a broken InvoiceLine revision of QODBC. You will need to update QODBC to v6.00.00.174 (or later) when it get's released.

You can check when the release is available by looking at the QODBC Updates and Bug Fixes topic in this forum.

This is interesting.  After upgrading to version 6.00.00.155, I have a new and different problem.  Now all of the sudden when I execute the "sp_LastInserID InvoiceLine" statement -- nothing is returned.  I haven't modified this section of the code at all, and it was working fine with 6.00.00.134.  I noticed that running this stored procedure doesn't even work using the "VB Demo," application.  Nothing errors, but null values are returned.  Because I can't get by this part now I can't see if upgrading solved my issue with the second insert using the TxnID....  Thanks!

You can get around

SP_LASTINSERTID InvoiceLine

not working in QODBC v6.00.00.155 by locating the TxnID yourself, like in my example by using the RefNumber:

SELECT TxnID FROM Invoice unoptimized where RefNumber = 'QODBCClass2'

Yes, there's a bug in QODBC v6.00.00.155, that's been fixed in v6.00.00.172. Try using:

SELECT  TOP 1 TxnID FROM Invoice WHERE CustomerRefListID = '470001-1071525403' ORDER BY TimeModified DESC

for now.