How to Use QuickBooks to Cross Reference Items Part 1

This is a two part tutorial that:

      1. examines reasons for cross-referencing item names
      2. provides a principle that can be applied with different applications
      3. provides copy and paste code that demonstrates the method using:
            a. QuickBooks®
            b. QODBC
            c. Microsoft® Access

Why Cross Reference Items?

Contractors generally work under awarded contracts and invoice from a list of items in their bookkeeping application.

Contracts generally list items, quantities and prices. However, contract item names often differ from the contractor's item names.

Let us examine a real-life example from RUS (Rural Utilities Services division of The United States Department of Agricultural). One of the RUS unit names is: LCN N1.2. However, some electric cooperatives, engineers and contractors still use the old unit name: N M5-19.

Creating two separate items in the invoice application makes comparing bids and jobs and creating invoices from staking sheets (or work orders) difficult.

The method of cross-referencing item names in QuickBooks® outlined in this post solved a real need in a real business and has been used and tested for many years. It works and is an integral part of any invoice extensibility program.

Invoice Extensibility

A previous article:

            Using QODBC to Create a Customized Data Entry Interface for QuickBooks Invoices

...explained how customized invoice programs can:

      1. reduce invoice entry time
      2. reduce human error in data entry
      3. offer item cross-referencing or item lookup capabilities

This post:

      (a) explains how to cross reference item names directly in QuickBooks® and;
      (b) outlines a process to use that method and;
      (c) provides copy and paste code for beginners

No matter what bookkeeping or database application you use, you will benefit from this post because it explains a principle. Copy and paste code demonstrates the method using QODBC and Microsoft® Access but you can still benefit from the information here without either.

If you do not use QODBC, you can apply this method using your favorite spreadsheet or database application because once you know a principle, application of that principle is almost limitless.

For example, you could use the QuickBooks® export-to-spreadsheet feature to import the same data used in this post into your favorite database application, eliminating the need for QODBC.
Alternatively, you could use another ODBC connection or even the QuickBooks® SDK itself to import data into your database or a customized program you wrote yourself.

Quick Introduction to QODBC

QODBC is an ODBC (Open Database Connection) driver that uses SQL (Structured Query Language) to read and write data between QuickBooks® and databases. You do not need the QuickBooks® SDK (Software Development Kit) to use QODBC. You only need an application that lets you write your own SQL, like Microsoft® Access, Microsoft® Excel and others.

If you do not know SQL, this post provides copy and paste code. Copy and paste codes for QODBC exist here ( and on other sites for beginners.

If you would like a tutorial on using Microsoft® Excel, please let me know. I often write posts when enough interest is generated.

QuickBooks® Item Names

When customers, engineers and supervisors use different names for the same item, the invoice entry technician needs a way cross reference directly in the invoice application.

For example, a typical RUS (Rural Utilities Service - an agency of USDA) construction unit LCN N1.2 may vary as follows:

      RUS:             LCN N1.2
      Contract 1:    N N1.2
      Contract 2:    N M5-19
      Field Term:    Neutral Upset

A quick way to cross reference on a database form is to use a text box for string entry and a combo box for item selection as follows:

      1. string is entered into text box
      2. combo box is filtered for:
            a) customer, contract, supervisor or engineer and;
            b) item string from text box
      3. user selects item from drop-down combo box

The code in this post uses QODBC to import the QuickBooks® item list into a Microsoft® Access database and fill and filter a combo box.

With a few tweaks, this method can use Microsoft® Excel, QuickBooks® IIF files or another ODBC connection to QuickBooks® to accomplish the same goal.

The combo box can be altered for use with:

      1. invoice entry
      2. estimate entry
      3. price comparison
      4. price level per item price lists

What You Need

To use the principle, you need only read and understand the information.

To use the code, you need:

      1. QODBC, QuickBooks® and Microsoft® Access properly installed and working on the system
      2. QuickBooks® has been granted permission to connect to Microsoft® Access using QODBC.
      3. The user knows how to create a form and controls and use the Visual Basic Editor

The code in this article was tested using:

      1. QuickBooks® Premier Contractor 2009
      2. Microsoft® Access 2002
      3. QODBC Version 9

It does not matter what versions of the above applications you are using because this is a principle.

If no invoice program exists in Microsoft® Access, this process may be used to simply look up QuickBooks® item names by customer, contract, engineer or field term.

Adding Cross-References to the QuickBooks File

Cross referencing items may be accomplished by creating an item table in Microsoft® Access and joining it to an imported QuickBooks® item table. However, that method requires:

      1. two applications to store data
      2. two tables to store data

This post explains how to store all the data in one application and one table and use the database only to filter and manipulate the data.

The benefits of storing all the data in the QuickBooks® file are:

      1. you may use any database application or change database applications if desired
      2. QuickBooks® is less likely to become corrupted
      3. the data transfers automatically to future QuickBooks® files
      4. QuickBooks® reports can display and filter the data

To store all data in QuickBooks®, you need a method to add data to existing QuickBooks® items and that is the foundation of this principle.

Understanding QODBC and QuickBooks® Invoice Tables

The two invoice tables are:

      1. Invoice
      2. InvoiceLine

You write to the Invoice table to create an invoice. The Invoice table stores the customer, date, invoice number, due date, ie.: the invoice header information.

You write to the InvoiceLine table to create each individual line on the invoice.

You choose items for each InvoiceLine from the Item table (list of items).

The Item table includes the field: Description.

The InvoiceLine table includes the field: InvoiceLineDesc.

          Item Table                     InvoiceLine Table
          ----------                        ------------------
          Description                      InvoiceLineDesc

You enter descriptions into the item table through the New Item window in QuickBooks®.

The screenshot below highlights the description box where you enter descriptions and item names.

The descriptions that you type into the text box highlighted above:

      1. are stored in the Item table
      2. appear on the Invoice Line in QuickBooks® but are not stored in the InvoiceLine table

When you create an invoice in QuickBooks® and select an item, this description appears on the description line of the invoice.

When you type over this description, QuickBooks® stores that information in the InvoiceLine table. The information you typed only appears on that invoice on that line and no other invoice or line.

Just as you may change the item description for any invoice line, you may also change it with VBA. Further, you may use VBA to extract characters from the original Item description and store them in the InvoiceLine.

In other words, you may use VBA to extract the string N VP1.01 from the orginal description and store only that string in only that invoice line. You may also use VBA to store that string in all invoice lines where that particular Item appears. That is up to you and your needs.

How to accomplish this may be addressed in another post. This post deals only with creating a Combo Box for cross referencing. The Combo Box lets you enter the contract, customer, engineer or supervisor's item name to find the desired QuickBooks® item to invoice.

The table below illustrates extraction of an Item name for the contract CREC UH765. The item name N M5-6 may be inserted into the Invoice Line as the InvoiceLineDesc. It then replaces the Item table description: LCN VP1.01 but only on that specific invoice line.

Item Table                                                    InvoiceLine Table

Item Name                       Item Description                      InvoiceLineDesc
--------------                   -----------------                     -----------------------

LCN VP1.01                   Default: N VP1.01                   N M5-6
                                        Contract 1: LCN VP1.01
                                        ABC Elec: N VP1.01
                                        BWRec: Transformer Arrestor
                                        Cust4: Arrestor
                                        CREC UH765: N M5-6
                                        Other : Arrestor on Transformer

When programming for extraction, use the colon and carriage return characters to extract Item names from the item list.

Before doing this; however, you must designate a permanent Customer Field to use for cross-referencing.

Customer Field

You designate a specific field in the Customer table to use for cross referencing because:

      1. If a customer name changes, you would then have to change the name for that customer in all your item descriptions where it appears. A dedicated customer name field stays the same even if the customer name changes.
      2. Abbreviated customer references require less character space and are also easier to recognize.

If you use full customer names instead of a designated Customer Field, you may need to program the database in the future to change customer names in the Item table when they change. You may prefer doing this over using abbreviated customer names.

If you designate a Customer Field for cross referencing, you may use:

      1. the Customer Account Number field or;
      2. a custom field created for this purpose

To create a custom field in the customer table:

      1. open the Edit Customer window by double-clicking a customer in the customer center
      2. select the Additional Information tab
      3. click the Define Fields button
      4. create a new label
      5. make sure the customer box is checked
      6. reference this custom field in your database

This article uses the AccountNumber field from the Customer table in the following code. If you will copy and paste this code, use the AccountNumber field at least for now.

Adding Item Names

With a Customer Field designated in QuickBooks®, add unique customer item names using this new customer reference. Type the customer reference followed by a colon and the additional item name. Make sure each item has its own line. Follow the example above: New Item Window

If you add Other and Default item names, you can include more descriptive strings for your invoice lines. Program the invoice form to use the Default item name when the customer abbreviation is not on the list.

Engineer and supervisor item names can also be included. Precede the colon with the engineer's or foreman's name as follows:

          LCN VP1.01      Default: N VP1.01
                                     Cust1: LCN VP1.01
                                     Cust2: N VP1.01
                                     Cust3: Transformer Arrestor
                                     Cust4: Arrestor
                                     Cust5: N M5-6
                                     Other : Arrestor on Transformer
                                     J. Smith: Trans Arrestor
                                     O. Black: M5

The Description field of the QuickBooks® Item table provides 4,095 character spaces; plenty of room for additional item names.

Warning: Once you add item names to the Item Description field for cross referencing, the entire list appears on invoice lines in the description. See the illustration above:  Description On Invoice. Therefore, this method is best used when:

      1. You use another application than QuickBooks® to print invoices or;
      2. You set Item Description to do not print so only item names appear on invoice lines.

Next: Importing the Item Table from QuickBooks® Using QODBC


Post a Comment

Popular Posts