QODBC News May 2004

May, 2004


Product Update
From the Support Desk
Websites and Product Links

Product Update

QODBC Release is now available for download and contains a special fix to help combat a memory leak reported by some users of the ODBC driver.

What's New?

New tables added for QuickBooks 2004:
ItemFixedAssetTable, ItemReceipt, ItemReceiptExpenseLine, ItemReceiptItemLine, ItemReceiptLinkedTxn, PayrollItemNonWage, ListDeleted.

You can now modify these tables in QuickBooks 2004:
ItemSubTotal, ItemSalesTax, ItemSalesTaxGroupLine, ItemPayment, ItemOtherCharge, ItemDiscount, SaleRep, ItemGroup

The Stored Procedure Report command SP_REPORT has been completely reworked. Only columns that apply, (as used by the QuickBooks user interface for reports) can be requested. Instead of a fixed column layout being returned, many times with mostly null data, only relevant columns are now returned. Columns that have a varying number based on a date range or list data auto expand to the correct number of columns. An example would be Amount, Amount might expand to Amount_1, Amount_2, … Amount_13 for a report with 12 months totaled by month. With the Amount_13 being the Total column. Amount_Count contains the number of Amount Columns in the expansion. In this example, 13.

New Stored Procedures added. These will be helpful for programming environments like Visual.NET and COLD Fusion.
SP_COLUMNS tablename

Product Fixes:

ApplicationCode Error Fixed that appeared on some users upgrading from old QODBC v3 to v4 for QuickBooks 2004. There was a random “.”, being added to the value returned from the .INI file.

Fixed ability to insert a new line item into tables like Invoice:
To insert a new line include TxnID of an existing Invoice like this:
INSERT INTO "InvoiceLine" ("TxnID", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID") VALUES ('562A-1197757292', '250000-933272656', 'Test Add a Line', 1.00000, 1.00, '20000-999022286')

The bug that caused an error to be reported after a successful update of the Invoice table has been fixed.

The operation of QODBC was changed to handle one connection handling multiple threads.

Some memory handling issues were found and corrected.

Some LineItemGroup data was not returning correctly. This has been corrected.

In non-US versions, the Version type is detected in most cases so it does not call the incorrect Country version and receive Error = 80040423 in the log file.

BillPaymentCheckLine and BillPaymentCreditLine both had a bug in that it would only save the last line of a multiple line insert.

Changed all CHAR types to VARCHAR types. SQL Linked servers complained that we were returning a shorten version of the CHAR. No other applications seem to care. This should also help some applications that were getting spaces padded at the end. Most applications don’t seem to even notice we changed the type we are returning. This makes SQL linked servers almost work. A fix is still required to the handling being called by multiple threads on the same connection.

There was a bug in QuickBooks 2004 when inserting into VendorType table.

To update your copy of QODBC, click on the download button, download QODBC.exe to a temporary folder on your computer and install QODBC v4 Build 40 over the top of your existing installation.

Back to Top

From the Support Desk

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

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 "NULL" InvoiceLine line between two invoice lines, run each of these insert statements in VB Demo one after each other 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 (NULL, 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 in our answers to frequently asked technical questions on QODBC section at: http://www.qodbc.com/qodbcfaq.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. 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.

Back to Top

Websites and Product Links

QODBC is an ODBC driver for reading and writing QuickBooks 2004 & 2003 Pro, Premier and Enterprise accounting data files by using standard SQL queries. The Basic edition is FREE!
QTableGrabber is an Excel Add-In that gives you access to QuickBooks data tables with virtually no limitation on transaction accessibility other than certain payroll information that Intuit does not make available outside its QuickBooks product line.
Last Price & Cost Grabber is a utility that displays a listing of both QuickBooks vendor costs and customer selling prices for a particular item – or range of items – in one central location. There's no need to run multiple reports, take extra time to select item filters, or re-arrange multiple windows to see both what you pay for an item, and what price you charge your customer. Each tab in Last Price & Cost Grabber can be used independently or in conjunction with each other.
QDataViewer is a multi-purpose reporting and data formatting tool designed specifically to use QuickBooks 2004 & 2003 Accounting data. The expandable platform of QDataViewer allows the customer to add an unlimited number of report variations to the included "canned" templates based on a basic knowledge of Crystal Reports 9. If they are not using Crystal Reports, Data Access Worldwide can also custom design reports based on customer needs.
Try our QuickBooks Tools & Utilities for 30 Days FREE!

Do you know someone who would benefit from receiving QODBC News? If so, please forward this issue to them and ask them to subscribe by visiting the link below. We genuinely appreciate your help in spreading the good news about QODBC!

To subscribe to this newsletter, click here

Send comments using our feedback form. FLEXquarters.com LLC is located at 2620 S Maryland Pkwy #557 Las Vegas NV 89109 USA. Tel: 248-620-2400.

Back to Top

Copyright © 2004 Data Access Worldwide. All Rights Reserved.