Problem Description:

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 (it looks like the class I last defined in the last child InvoiceLine item). I need to insert invoices with lines pointing to DIFFERENT courses, as I can manually do so in QuickBooks. Is this possible using QODBC????

Solution:

You are correct. When you create or add InvoiceLine, QODBC will always use the last class referenced in the invoice (if applicable). For example, this three-line invoice:

First Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '60001-1019495266', 'QODBCClass1', '670004-1044572237', 'Building permit Additional Room,' 100.00000, 100.00, '10000-1011136881', 1)

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

Second Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '80001-1026828072', 'QODBCClass1', '670004-1044572237', 'Bin Permit Renovations,' 200.00000, 200.00, '10000-1011136881', 1)

Third Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', 'QODBCClass1', '670004-1044572237', 'Less Council Rebate,' -50.00000, -50.00, '10000-1011136881', 0)

Incorrectly appears in QuickBooks with all three invoice lines showing the "Manual Pool Systems" 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 ('4C0000-1040154668', '60001-1019495266', 'QODBCClass2', '670004-1044572237', 'Building permit Additional Room,' 100.00000, 100.00, '10000-1011136881', 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 8D0E-1481798988, so we can now add additional lines to the invoice by doing the following:

INSERT INTO "InvoiceLine" ("TxnID," "ClassRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID") VALUES ('8D0E-1481798988', '80001-1026828072', '670004-1044572237', 'Bin Permit Renovations,' 200.00000, 200.00, '10000-1011136881')

INSERT INTO "InvoiceLine" ("TxnID," "ClassRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID") VALUES ('8D0E-1481798988', '', '670004-1044572237', 'Less Council Rebate,' -50.00000, -50.00, '10000-1011136881')

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