Knowledgebase
[QODBC-Desktop] How to delete an Invoice Line in the InvoiceLine table using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Deleting an InvoiceLine

Create an Invoice with Three Lines

First Line

     INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
     "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
     "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
     VALUES ('4C0000-1040154668', 'QODBCNeg1', '670004-1044572237', 'Building permit Additional Room',
     100.00000, 100.00, '20000-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", "RefNumber",
     "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
     "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
     VALUES ('4C0000-1040154668', 'QODBCNeg1', '700003-1197741298', 'Bin Permit Renovations',
     200.00000, 200.00, '20000-1011136881', 1)

Third (Last) Line

     INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
     "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
     "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
     VALUES ('4C0000-1040154668', 'QODBCNeg1', '80000092-1481798847', 'Less Council Rebate',
     -50.00000, -50.00, '20000-1011136881', 0)

 

The result is Displayed in QuickBooks.

     Once these three SQL INSERT commands have been run in sequence (without breaking the QODBC connection), QODBC will generate the next invoice in the Sample company file in the USA edition of QuickBooks 2022.

Note: See Line 3 of the invoice below for a line item discount of $50.00 :

 

Locate the TxnID and InvoiceLineTxnLineID

     To locate the TxnID and InvoiceLineTxnLineID for the new invoice lines, we run the following query using QODBC Test Tool:

     SELECT TxnID, InvoiceLineTxnLineID, InvoiceLineDesc, InvoiceLineAmount,
     RefNumber FROM InvoiceLine unoptimized where RefNumber = 'QODBCNeg1'

 

Delete the Last Line in InvoiceLine Table

     To delete the last line, we run the following query using the TxnID and InvoiceLIneTxnLineID values for the last invoice line in QODBC Test Tool:

     delete from InvoiceLine where TxnID='5BAB-1197758658'
     and InvoiceLineTxnLineID='5BAF-1197758658'

The result Displayed in QODBC

     The last invoice line is now deleted, and it can be verified by re-running:

     SELECT TxnID, InvoiceLineTxnLineID, InvoiceLineDesc, InvoiceLineAmount,
     RefNumber FROM InvoiceLine unoptimized where RefNumber = 'QODBCNeg1'

Note: All regions outside the United States should use TaxCode instead of SalesTaxCode.

(155 vote(s))
Helpful
Not helpful

Comments (1)
Max Kothari
17 October 2012 02:00 PM
Dear All,

I have try to delete invoice line

delete from InvoiceLine where TxnID='5BAB-1197758658'
and InvoiceLineTxnLineID='5BAF-1197758658'

i am getting Error ..

actually it work on VBDEMO .. but not work in my code ..
thank you
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).