[QODBC-Desktop] A detailed tutorial about how to create a multi-line Invoice
Posted by Juliet (QODBC Support) on 29 October 2009 09:16 AM
Note: Here I take " How do I create a multi-line invoice using QODBC with a blank line between two items for a job" as an example to tell the detailed steps of creating a multi-line invoice.
Step 1 - Find the Template ListID
With your QuickBooks company file open, click on "Start" and "Programs" and locate QODBC Driver for the QuickBooks program group. Click on the QODBC Test Tool icon to launch the program supplied with QODBC. To establish a connection, from the dropdown list, select the "QuickBooks Data" DSN, Click on "Connect”.
How to find the Template ListID
When creating an invoice you can select which Invoice Template to use by including a "TemplateRefListID" column in the InvoiceLine insert statement. To determine what template ListID are available to you, run the following query in VBDemo:
How to use the Template ListID
To use the Stadium Tax invoice template we use the ListID:'A0000-1045701421' (in Row 9 above), like this in InvoiceLine SQL insert statements:
You can substitute the TemplateRefListID with the ListID value of whatever Invoice Template type you would like to use.
Step 2 - Find the Customer Job ListID
How to find the Customer Job ListID
In QuickBooks, job invoices are created by invoicing a customer followed by the job name or number. For this example, I created job number 1000 for Acmer Pty Ltd. In QuickBooks jobs are separated by a ":", and I would use "Acmer Pty Ltd:1000" to raise invoices against the job 1000. What you need to do is find the "CustomerRefListID" for the job you want to create an invoice for by running the following query in QODBC Test Tool:
How to use the Customer Job ListID
I'm interested in Job: "Acmer Pty Ltd:1000". For Job 1000 we use the ListID:'300000-1081400329' (in Row 2 above), like this in InvoiceLine SQL insert statements:
Step 3 - Create the Job Invoice
How to find the InvoiceLineItemRefListID
Now that we know "TemplateRefListID" for the Invoice Template we want to use and the "CustomerRefListID" for the job we want to create an invoice for, it's time to create the actual multi-line invoice by running three SQL insert statements. What we also need to do is find the "InvoiceLineItemRefListID" for all the items we want to invoice against the job by running the following query in QODBC Test Tool:
I'm interested in the following Inventory Items:
How to create a Job Invoice
To create a job invoice, with a "EMPTY" InvoiceLine line between two invoice lines, run each of these insert statements in QODBC Test Tool one after each other without disconnecting like this:
Step 4 - View the Job Invoice in QuickBooks
After running each of the insert scripts in Step 3, when I looked up Tax Invoice# 56 in QuickBooks I found an Invoice for the job "1000" with two line items with a space between them just like the user wanted:
Note: Further examples of QODBC SQL Scripts for QuickBooks Transactions may be found with these FAQs. In order to create QuickBooks transactions using QODBC, you must have either a 30 Day QODBC Evaluation or a QODBC Pro Read Write license.
The examples and screen dumps shown here are from the Australian edition of QuickBooks. USA, Canadian, and UK versions of QuickBooks do differ in appearance and operation, however, the technique shown still applies.
IMPORTANT NOTE: InvoiceLineTaxCodeRefListID should be used outside USA only. In USA use InvoiceLineSalesTaxCodeRefListID instead.