Knowledgebase: Syntax
[QODBC-Desktop] Using ClassRefListID when Inserting Invoice lines
Posted by Jack - QODBC Support on 05 August 2014 02:03 PM

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????


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)


 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):



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:

(0 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).