Troubleshooting - Invoice RefNumber FAQ

Problem Description:

I have the invoice number (RefNumber) from my table outside QuickBooks. But if a QuickBooks user creates invoices directly in QuickBooks, then QuickBooks keeps new incrementing invoices automatically, and the next invoice I create using my outside table will be incorrect.

I need to replicate what QuickBooks does in determining the following QuickBooks invoice number. What do you recommend?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically manage and assign a RefNumber to the newly inserted invoice.

If you would like to specify the RefNumber, and RefNumber is numeric, it should be easy to sync up. Just get the max RefNumber from the Invoice table( of the current year), add 1 to it, and specify it in the insert statement.

Problem Description:

How do I Auto increment the Ref Number for the Invoice?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically increment and assign a RefNumber to the newly inserted invoice.

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I Get max and insert RefNumber for the Invoice?

Solutions:

You can get the max RefNumber for Invoice by using the below query & add 1 to the result and can create a new invoice:

SELECT Max(RefNumber) FROM InvoiceLine  

We got 2581 as the max RefNumber. Now we are creating the new invoice with RefNumber 2582.

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('620000-1071526513', '2582', '240000-933272656', 'POWER TRAK-2000', 200.00000, 200.00, '20000-999022286',0)

Problem Description:

What happens If I use the same RefNumber for Invoice creation?

Solutions:

QuickBooks is allowing duplicate RefNumber. So when you use the same RefNumber for Invoice creation. QuickBooks will create an invoice with a duplicate RefNumber/

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I Auto Increment Alphanumeric RefNumber for Invoice?

Solutions:

First, create any Invoice from QuickBooks UI & assign the alphanumeric RefNumber "R0001T". Now create a new Invoice from QODBC & do not pass RefNumber. QuickBooks will automatically increment and assign a RefNumber (i.e., "R0002T") to the newly inserted invoice.