Knowledgebase
[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 (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????

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 to test QODBC SQL queries only 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:

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))
Helpful
Not helpful

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