Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : sp_lastinsertid InvoiceLineSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC SQL Support Forum

 New Topic 
 
 Post Reply 
[1]  
 sp_lastinsertid InvoiceLine 
 Author   Message 
  jeff 
  
 Group: Members 
 Posts: 76 
 Joined: 2006-06-03 
 Profile
 Posted : 2006-09-13 11:09:29

How can I get the last insert id (last line) when creating a new invoice line. I do not want that TxnId. Instead I would like to get the "InvoiceLineTxnLineId". Is this possible using the sp_lastinsertid

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-09-13 12:14:35

If you are creating invoices by just using InvoiceLine inserts without using group items, run:

SP_LASTINSERTID invoiceline

and grab the TxnID and do:

select Top 1 TxnID, InvoiceLineTxnLineId, InvoiceLineSeqNo from InvoiceLine
where TxnID='5D43-1197768681' order by InvoiceLineSeqNo Desc

Otherwise, if you do invoice group items you will need to do something like this:

select Top 1 TxnID, InvoiceLineTxnLineId, InvoiceLineSeqNo from invoiceline
where TxnID='5D43-1197768681' and InvoiceLineTxnLineId is not null
order by InvoiceLineSeqNo Desc

but if you are the only one creating invoices at the time, simply jump the gun by just doing:

select Top 1 TxnID, InvoiceLineTxnLineId, InvoiceLineSeqNo from invoiceline
where InvoiceLineTxnLineId is not null
order by TimeModified DESC, InvoiceLineSeqNo Desc

BTW: You can leave out the null test if you don't use Itemgroups.

 

  Top 
  jeff 
  
 Group: Members 
 Posts: 76 
 Joined: 2006-06-03 
 Profile
 Posted : 2006-09-14 03:56:49

i am attempting to get the invoicelinetxnlineid from a new invoiceline from an existing invoice.

Here is my sequence.

Insert into InvoiceLine(TxnId,InvoiceLineItemRefListId,InvoiceLineDesc,InvoiceLineRate,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) Values ('2463-1197758746','240000-1071531214','Delivery Service Fee (free for orders over $100)',25,'20000-999021789',false)

sp_lastinsertid invoiceline  (This doesn't return any results, but it doesn't matter because I alreayd have the TxnId)

select Top 1 * from invoiceline where TxnID='2463-1197758746' and InvoiceLineTxnLineId is not null order by TimeModified DESC

This returns 1 row but its not the most recent recent insertet row. The problem is that the DateCreated and timeModified all hold the same values for each invoiceline, therefore sorting by TimeModified does nothing. It just pulls back the 1st row indiscriminatley.

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-09-14 09:13:10

You are correct, I've changed the "order by" clause to InvoiceLineSeqNo in my statements above and you should now be able to see the InvoiceLineTxnLineId for the last line on the invoice!

Note: In order for sp_lastinsertid invoiceline to work, you must be using QODBC v6.00.00.176 (or higher).

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to