I have successfully created invoices in QB using Alpha 5 V8. However, I cannot  populate a custom field in QuickBooks called CustomFieldInvoiceLineSr. This field was created in the Item list and show on invoices and the screen. I can populate CustomFieldInvoiceLineOther1, but not the one mentioned above.

Anyone know why?

Thanks.

Jay

InvoiceLine.CustomFieldInvoiceLinecustomfieldname fields are updateable only and can't be used in an INSERT statement.

You need to first create the InvoiceLine and then using the TxnID and InvoiceLineTxnLineID update the CustomFieldInvoiceLinecustomfieldname  instead like this:

Update InvoiceLine Set CustomFieldInvoiceLineColor='Blue'
where TxnID='2B55-1071523067' and InvoiceLineTxnLineID='2B57-1071523067'

Tom,

Thank you for the quick, succinct reply.

My only remaining question is, I am using Alpha 5 v 8 to insert the record, but how do I know what the TxnID and InvoiceLineTxnLineID are so that when I create the records I can immediately update the custom field in the same script?

Thanks again for the help.

Jay

Well you are going to have to run a query like this after you create the the complete invoice for the invoice line in the invoice you want to change:

Select TxnId, InvoiceLineTxnLineID, InvoiceLineSeqNo, RefNumber
from InvoiceLine where RefNumber='invoicenumber'
and InvoiceLineSeqNo=invoicelinenumber
order by refnumber

for example:

Select TxnId, InvoiceLineTxnLineID, InvoiceLineSeqNo, RefNumber
from InvoiceLine where RefNumber='1'
and InvoiceLineSeqNo=3
order by refnumber

but why bother, just do:

Update InvoiceLine Set CustomFieldInvoiceLineColor='Blue'
where RefNumber='1' and InvoiceLineSeqNo=3

instead.

Thanks again, Tom. I appreciate your quick, clear replies.

One more question (hopefully the last one).  Since the user for whom I am creating this project is also creating invoices manually in QuickBooks, is there any way to know what invoice number QB assigns to an invoice when my database creates the invoice?

To use your suggestion below (which does work when I manually put the invoice number in the insert statement), I would like my database program to create the invoice and invoice lines and then go through each line and put the serial number in the custom fields without any user input.

Is there a way to do this automatically?

Perhaps another approach would be to write a select statement which grabs the invoice number of the last invoice that was created.

Any thoughts?

Thanks again.

Jay

If you are creating invoices by just using InvoiceLine inserts run:

SP_LASTINSERTID invoiceline

immediately after the last invoice line insert (without breaking your connection of QODBC) and grab the TxnID and do:

Update InvoiceLine Set CustomFieldInvoiceLineColor='Blue'
where TxnID='5D43-1197768681' and InvoiceLineSeqNo=3

instead.

Tom,

Sorry to be a bit dense about this, but after researching this, I see that SP_LASTINSERTID invoiceline is a stored procedure.

How can I use that in a script to automatically grab the RefNumber of the last invoice line that was inserted into the table?

I also tried using
select Top 1 txnid from PurchaseOrder ORDER BY TimeModified DESC
but got the following:
13 - 'line 1:8: unexpected token: Top'

Any thoughts?

Thanks.

Jay

You just run / execute it like any other select statement:

SP_LASTINSERTID invoiceline

As far as:

select Top 1 txnid from PurchaseOrder ORDER BY TimeModified DESC

is concerned the SQL Syntax is correct for QODBC:

The error must be coming from your Alpha IDE.