This month's support incident article comes from a QODBC Pro Read Write Edition user that asked,

"How do I create a multi-line invoice using QODBC with a blank line between two items for a job and specify what invoice format I would like to use?"

Step 1 - 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 ListIDs are available to you, run the following query in VBDemo. With your QuickBooks company file open, click on "Start" and "Programs" and locate QODBC Driver for QuickBooks program group. Click on the VB Demo icon to launch the Visual Basic Demo program supplied with QODBC. To establish a connection, from the menu bar select "Connections" / "Add a new connection", select the "Quickbooks Data" DSN, Click "OK” and enter the following query:

SELECT * FROM Template

Click on the Query button. In my case my QuickBooks 2004 sample file returned the following templates:

To use the Stadium Tax invoice template we use the ListID:'A0000-1045701421' (in Row 9 above), like this in InvoiceLine SQL insert statements:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '90000-1045537150', '4m Steel Ladder', 1.00000, 150.00, '90000-1045536338', 0)

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
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 VB Demo:

SELECT * FROM Customer

In my case, my QuickBooks 2004 sample file returned the following customers:

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:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '90000-1045537150', '4m Steel Ladder', 1.00000, 150.00, '90000-1045536338', 0)

Step 3 - Create the Job Invoice
Now that we know "TemplateRefListID" for 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 VB Demo:

SELECT ListID, FullName, Description, Type FROM Item

In my case, my QuickBooks 2004 sample file returned the following items:

I'm interested in the following Inventory Items:
"4m Steel Ladder" ListID:'90000-1045537150' (in Row 17 above) and
"Paint Brush:Big" ListID:'150001-1045625669' (in Row 25 above)

Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below we create a 3 line job invoice using three SQL INSERT commands in sequence. The key to this process is the field named "FQSaveToCache". This field is not part of the table, but is used as a flag to the QODBC driver. In the sequence below, you should note that the value of "FQSaveToCache" is set to 1 (true) for the first two line item insert statements, and then it is set to 0 (false) for the final statement.

A true (1) setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 (false), the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks.

QODBC maintains a connection for each application using the driver, and this cache is specific for each connection, so multiple applications or users of QODBC will not interfere with the cached transactions of other users. There is no set limit to the number of lines that can be cached for a single transaction, other than what QuickBooks would limit you to.

Since the data on the INSERT statement is being cached and not written to QuickBooks, some application tools (most notably Microsoft Access) will re-check that the data was saved properly to the target database by running a SELECT statement following a successful INSERT statement. In this example, this re-check will fail.

To get around this, use a pass-thru query (Append Query) and ignore any errors in the processing of the transaction, except for the last one. After the final line has been saved and the record is inserted into QuickBooks, then you can do your own validation by SELECTing the results of the Invoice number you just created in the Invoice and InvoiceLines tables to see that it was inserted correctly.

To create a job invoice, with a "EMPTY" InvoiceLine line between two invoice lines, run each of these insert statements in VB Demo one after each other without disconnecting like this:

1st SQL Statement
INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '90000-1045537150', '4m Steel Ladder', 1.00000, 150.00, '90000-1045536338', 1)

2nd SQL Statement
INSERT INTO "InvoiceLine" ("InvoiceLineDesc", "FQSaveToCache") VALUES ('', 1)

3rd SQL Statement
INSERT INTO "InvoiceLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","FQSaveToCache") VALUES ('300000-1081400329','A0000-1045701421','56', '150001-1045625669', 'Paint Brush:Big', 1.00000, 11.60, '90000-1045536338', 0)

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 a invoice for the job "1000" with two line items with a space between them just like the user wanted!!:

Further examples of QODBC SQL Scripts for QuickBooks Transactions may be found on our website at: http://www.qodbc.com.au/scripts.htm. In order to create QuickBooks transactions using QODBC you must have either a 30 Day QODBC Pro Evaluation or QODBC Pro Read Write license.

Back to Top