Knowledgebase
[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 

Preparation      

      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 "O”.

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:

SELECT * FROM Template

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

 


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

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:

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

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

SELECT * FROM Customer

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

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:

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

How to find the InvoiceLineItemRefListID

     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)

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 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 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.

 


 

(272 vote(s))
Helpful
Not helpful

Comments (2)
Tom Eagle
27 September 2013 02:35 AM
Does anyone have an example of doing the above using VB programming in ACCESS (2010)
Thanks,
Tom
Jack
08 July 2014 10:24 AM
Hi Tom,

Please refer below mentioned article for Example Code of Update QuickBooks from MS Access. You need to do some modification in code & you can get VBA code as per your requirement:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2571/0/using-quickbooks-data-with-vba
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).