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 InvoiceLines, 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 ('470001-1071525403', '30000-933272658', 'QODBCClass1', '250000-933272656', 'Building permit Additional Room', 100.00000, 100.00, '20000-999022286', 1)

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step1.png

 

Note:  VB DEMO is to be used for testing of 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 ('470001-1071525403', '60000-933272658', 'QODBCClass1', '250000-933272656', 'Bin Permit Renovations', 200.00000, 200.00, '20000-999022286', 1)

 

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step2.png

Third Line

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)

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step3.png

 

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

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step4.png

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)

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step5.png

 

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

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step6.png

 

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

 

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step7.png

 

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

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step8.png

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

http://support.flexquarters.com/esupport/newimages/ClassRefListIDInvoice/Step9.png

(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).