QODBC sp_LastInsertID invoiceline

Here is a tidbit I learned about sp_lastinsertid.

First, an explanation of sp_lastinsertid. This is a QODBC stored command that returns the last TxnID or ListID of the last insert done in QuickBooks®.

I use this command to verify data was written. For example, if I insert a check into QuickBooks®, my code would do the following:
  1. create a database table to hold data for a check I wish to insert into QuickBooks®
  2. run a query to insert the table data into QuickBooks® (the check)
  3. run a query to retrieve the TxnID of the last check inserted into QuickBooks®
  4. use the retrieved TxnID in a query to retrieve the check data
  5. compare that check data to the table data
  6. declare a boolean variable with a value of true
  7. iterate through the retrieved data comparing it to the table data 
  8. change the boolean variable to false the first time field values do not equal

An announcement from the computer finalizes the transaction:
  1. text to speech "Insert confirmed." or;
  2. green check mark on the form or;
  3. message box "Insert confirmed."
If all this sounds complicated, remember two things:
  1. Program once and you're done.
  2. Computers are fast! All this takes only seconds to complete.

What I Learned Today

Sometimes it becomes necessary to add a line to a transaction. I need to occassionally add lines to existing invoices. This is accomplished with a query like:

INSERT INTO INVOICELINE ("TxnID","InvoiceLineItemRefListID","InvoiceLineItemRefFullName", "InvoiceLineQuantity", "InvoiceLineRate","InvoiceLineClassRefListID", "InvoiceLineAmount", "FQSaveToCache") VALUES ('5A4D-1330608845','800000DC-1233356154','I P2.R.2',2,80,'8000005F-1233356210',160,0)

TxnID is the TxnID of the invoice not the invoice line. Remember, we are inserting this new line into an existing invoice so we need to know which invoice to put it in.

So, the SQL above inserts one line into an existing invoice. If this were a new invoice, TxnID would not be known and therefore not included in the SQL.

To confirm the line was inserted, I tried retrieving the lastinsertid with this SQL:

sp_lastinsertID invoiceline

This does not work because sp_lastinsertid only retrieves the last inserted ListID or TxnID.

InvoiceLine unique identifiers are InvoiceLineTxnLineID, not TxnID. Since the invoice already exists, you cannot retrieve its TxnID with sp_lastinsertid.

You must use another method to confirm insert.

Since you already know the TxnID of the invoice, you can use it to retrieve the lines from the invoice like this:

          "select * from invoice where TxnID='5A4D-1330608845' "

The above SQL returns all the invoice lines for that invoice. Then:
  1. assign true to a boolean variable
  2. create a recordset for the returned data
  3. move to the last record in the recordset since that will be the last inserted invoice line
  4. interate through the fields comparing returned data to the table data
  5. change the boolean variable to false the first time field values do not equal
Now that wasn't so hard, was it?


Popular Posts