[ic] Quickbooks installation problems - documentation changes

Tim Good draco at edsd.com
Mon Nov 17 13:10:31 EST 2003

I just got the Quickbooks module to work and wanted to share
imput. Within the README files there are conflicting

I have added changes and additions to the documentation in
it will help others in installing the Quickbooks module. I
posting the changes as a suggested change to the

# Changes marked with '**' characters
# these changes are what got the quickbooks module to work
with my
# environment. Interchange 4.9.9 from tar, perl 5.8.2 from
tar, and
# Redhat 8.0. I believe these changes would work for all
4.9+ version
# setups. A lot of the changes are adjustments due to
directory changes 
# in the newer versions. 
# -- Tim Good

Interchange + QuickBooks HOWTO

ic_howto_qb.1.4 (Draft)

1. Introduction

1.1. Summary Description

Interchange QuickBooks -- QuickBooks support for
transactions and

1.2. Audience

Users who already have Quickbooks setup and are familiar
with it, in
addition to having the foundation (or other) catalog

1.3. Contact the author

If you find any spelling errors, technical slip-ups,
subliminal messages, or if you wish to send feedback,
remarks, comments, or if you wish to contribute examples,
for alternative platforms, chapters, or other material,
please do so.

The preferred method of submitting changes is in the form of
a context
diff against the SDF source file (ic_howto_qb.sdf).  Please
your correspondence to:

Volunteer Maintainer, Dan Browning db at kavod.com


Original Author, Mike Heins mike at perusion.com

1.4. Version

This document describes software based on Interchange 4.5
and later.

2. Description

Interchange is a business-to-business and
internet ordering and cataloguing product. It has the
ability to take
orders via the World Wide Web, and store transaction data.

This document describes how to interface Interchange with
the popular small-business accounting program from Intuit.

QuickBooks has an import/export format called IIF, a
mnemonic for
Intuit Interchange Format. Fitting, eh?

The standard capabilities of Interchange allow production of
IIF files
for transaction passing.  With some support from Interchange
it can even import and export item listings.

3. Contents

The extension files can be found in the Interchange tarball
under the
'extensions/quickbooks' directory.  The following files are
used with
this extension:

  usertag/import_quicken_items  UserTag for importing items
  usertag/export_quicken_items  UserTag for exporting items
**  usertag/export_quicken_coa.tag  UserTag for exporting
**  usertag/get_quicken_orders.tag  UserTag for exporting
  pages/admin/quickbooks/*      Menu support for Interchange
**   legacy/qb.catalog.cfg                Quickbooks

4. Installation

To set up this extension, the basic steps are:

o    Create and copy directories and files.

o    Add additional database fields.

o    Modify catalog.cfg with additions.

**  o    Add "quickbooks" order route to checkout pages.

o    Restart Interchange. 

o    Export your items from Interchange catalog (or import
     existing QuickBooks items to Interchange).

o    Test.

4.1. Terms and locations

Several terms are used in the examples.

Catalog Directory

     This is the main directory for the catalog, where
     resides. It will have a NAME, the name for the catalog.
     common Interchange demo names are foundation,
construct, barry,
     and simple.)

     Common locations:


     We will use the path /var/lib/interchange/foundation in

Interchange software directory

     This is the main directory for your Interchange server,
where the
     file interchange.cfg resides. Common locations:

     /usr/lib/interchange /usr/local/interchange $HOME/ic

     We will use the path /usr/lib/interchange in these

Interchange tarball directory

     The quickbooks files are located in the untarred
     file, before installation of Interchange is performed.

Interchange User

     The Interchange daemon runs as a user ID that cannot be
root. It
     will require write permission on directories it must
modify to do
     its work.

     We will use the user ID interch in these examples.

4.2. Create and copy directories and files

This extension requires you to add some files to your

It is assumed you have tools and knowledge to create
directories with
the proper permissions. Any directories that will contain
files like order transaction logs will require write
permission for
the Interchange daemon user; pages and configuration only
need have
read permission.

4.3. Quick Installation Script

** You will need to create the script file and copy the
** below to the new file.
This script will install the necessary files for you,
provided that
you modify the variables to your environment.  Alternately,
you can
follow the more detailed installation instructions that
follow it.

Note that if you are not using a 4.9.8+ version of
Interchange, you
will need to manually install the qb_safe.filter by copying
it from
the 4.9.8 code/Filter/qb_safe.filter into your Interchange

** Copy starting below here to script file.
**  # Modify these seven variables to match your
**  # and the interchange user/group for CHOWN command
export QB=/path/to/interchange/extensions/quickbooks
export VENDROOT=/usr/local/interchange
export CATROOT=/home/interch/catalogs/foundation
**  export CP=/path/to/cp
**  export CAT=/path/to/cat
**  export MAKEDIR=/path/to/makedir
**  export CHOWN=/path/to/chown

**  $MAKEDIR -p $CATROOT/include/menus $CATROOT/vars
**  $CP -r $QB/legacy/TRANS_QUICKBOOKS \
**  $CP -r $QB/pages/admin/quickbooks \
**  $CP -i $QB/usertag/* \
**  $CHOWN interch.interch $VENDROOT/code/UI_Tag/*_quicken*

# Alternate usertag installation style:
#makedir -p $CATROOT/usertags/global
#cp -i $QB/usertag/* \
#      $CATROOT/usertags/global
# Then include the global/*.tag in your interchange.cfg

# Variables that optionally modify the export process, along
# their help entries.
**  $CAT   $QB/products/variable.txt.append >> \
**  $CAT   $QB/products/mv_metadata.asc.append >> \

# Menu entries: start with the existing menu, then add ours.
**  $CP -i $VENDROOT/lib/UI/pages/include/menus/Admin.txt \
**  $CAT   $QB/menus/Admin.txt.append >> \

# Some configuration changes.
** $CAT >> $CATROOT/catalog.cfg <<EOF
** DirConfig Variable vars
# You can remove these requires if you don't want to use the
# Quickbooks UI menu items
Require usertag import_quicken_items
Require usertag export_quicken_items

4.4. Admin UI Usage

After successful installation, there should be a
"Quickbooks" menu
item under the "Admin" category.  From there, you can
"generate IIF
files", download them, and perform other Quickbooks-related

Make orders directory

     Create the directory orders in your Catalog Directory
if it
     doesn't already exist. (It may be a symbolic link to
     location.) It must have write permission on it.

     cd /var/lib/interchange/foundation mkdir orders

     If you are doing this as root, also do:

     chown interch orders

     This directory is used to store the QuickBooks IIF
files produced
     for orders. The files are created with the form:


     Each day will have a file, and when a day is complete
you should
     download the orders. (There are other schemes

Copy pages

     You will want the Interchange UI support if you are
using the UI.
     It provides links for importing/exporting items,
downloading and
     viewing IIF files, and possibly other functions over
time. At the
     UNIX command line:

     cd /usr/lib/interchange/ cp -r
     extensions/quickbooks/pages/admin/quickbooks \

Copy report generation file etc/trans_quickbooks

     This file is used to generate the IIF file(s) for
     import into QuickBooks.

     cd /usr/lib/interchange/ cp
     extensions/quickbooks/etc/trans_quickbooks \
**      /var/lib/interchange/foundation/vars

Copy usertags

     If you want to use the UI item import/export, two
usertags are
     required. The easiest thing is just to copy them to the
     Interchange software directory subdirectory
lib/UI/usertag, which
     is #included as a part of the UI configuration file.

     cd /usr/lib/interchange cp -i
**      code/UI_Tag

4.5. Additional database fields -- userdb

If your catalog is not based on a 4.6+ version of the
catalog, you may not have some of the additional database
necessary.  If you want the user to retain their customer
number, add
the following field to the "userdb" table:


It can be an integer number field if your database needs
information. To add the field in MySQL, you can issue the
queries at the mysql prompt:

alter table userdb add column customer_number int;

If you don't add it, it just means that a new customer
number will be
assigned every time.

WARNING If you are using Interchange DBM files and have live
data it
is not recommended you add this field unless you are
positive you will
not overwrite your data. If you are not a developer, get one
to help
you. In any case, back up your userdb.gdbm or userdb.db file

4.6. Additional database fields -- inventory

Quicken also needs an account to debit for the split
transactions it
uses to track item sales. If you don't create these fields
to relate
to each SKU, the account "Other Income" will be used in the

Add the following fields to the "inventory" table:

account cogs_account

To add the fields in MySQL, you can issue the following
queries at the
mysql prompt:

alter table inventory add column account char(20); alter
inventory add column cogs_account char(20);

Other SQL databases will have similar facilities.

If you are using Interchange DBM files, just export the
database, stop the Interchange server (to prevent
corruption), add the
fields on the first line by editing the inventory.txt file,
restart Interchange.

**  4.6.1 Additional database fields -- inventory
**  --------------------------------------------
**  Quicken also needs an tax agency field for sales tax

**  Add the following fields to the "state" table:

**  account cogs_account

**  To add the fields in MySQL, you can issue the following
queries at the
**  mysql prompt:

**  alter table state add column qb_tax_agency varchar(255);

**  Other SQL databases will have similar facilities.

**  If you are using Interchange DBM files, just export the
**  database, stop the Interchange server (to prevent
corruption), add the
**  fields on the first line by editing the state.txt file,
**  restart Interchange.

4.7. Modify catalog.cfg with additions:

Add the entries in qb.catalog.cfg to catalog.cfg (you can
use an
include statement if you wish).

There are some Require directives to ensure that the needed
definitions are included in the catalog, as well as the
Route which is

** 4.8. Add quickbooks order route (Optional)

In the Interchange UI, there is a Preferences area
should add the quickbooks route. Place it after the
logging step, i.e.

code     ORDER_ROUTES Variable log quickbooks main copy_user

ADVANCED, If you know Interchange Variable settings, you can
add it

Variable ORDER_ROUTES  log quickbooks main copy_user

Also, you can use other methods to set order routes. See the
Interchange reference documentation.

4.9. Additional Variables

Optionally, you may specify some variables that modify the
behavior of
the Quickbooks export feature.  Documentation for these
variables is
provided via item-specific meta data, which can be added to
mv_metadata.asc file for automatic display by the Admin UI.

See the installation script at the top of this document for
that will append the empty variables to your variable.txt
and the help
information to your mv_metadata.asc files.

4.10. Restart the catalog

This can be done by restarting the Interchange server or by
Apply Changes in the UI.

4.11. Export the items

You can access the Quickbooks UI index by making your URL:


It will provide options for importing and exporting items.
This is
necessary so QuickBooks will be able to take orders for your

QuickBooks uses the product "name" as an SKU, along with an
reference number. Either you need to make your SKUs match
the integer
reference number, or you must ensure your product title is

4.12. Test

Place a test order on your Interchange catalog once you have
installing. You should find a file in the orders directory
with the
name qbYYYYMMDD.iif. (YYYY=year, MM=month, DD=day.) Transfer
this file
to your QuickBooks machine and run File/Import and select
that file as
the source. This should import the customer and order into
the system.
If it doesn't work, it may be due to lack of sales tax or
definitions, discussed below.

5. Usage

5.1. Accessing Admin UI Features

A typical installation will cause the Administrative User
Features to become available via the top level menu:

o    Login to the Admin UI

o    Administration

o    Quickbooks

You should then be presented with a menu of the Admin UI

5.2. Generating IIF Files

To generate the IIF files, access the corresponding page
from the
Admin UI Quickbooks Menu (Administration -> Quickbooks ->
Generate IIF

You will be presented with a query tool.  Select the query
that you would like and submit your query.  Among the query
you have the option to input a QB transaction number.  This
will be
the first number that is used when generating the IIF files,
and it
will be incremented for each sequential order in the query.

You will be notified of its success or failure.  The
resulting page

o    Inform you of the success or failure of the query.

o    Provide a link to the "results" IIF file (which
includes all of
     the orders found by the query).  Note that this
"results" IIf
     file is overwritten every time a query is run.

o    Provide a link for each IIF file (one per order).  This
can be
     used as a backup, or for importing one-by-one instead
of all at

6. Discussion

The interface provided works for the sample company data
with QuickBooks. There are certain requirements to make sure
it works
in your environment.

Also, you can change the configuration by editing the file
etc/trans_quickbooks to suit your IIF file needs.

6.1. Sales Tax

QuickBooks has a taxing system whereby tax rates are defined
customer location. There is usually also a generic Sales Tax
such as contained in the sample company data. This allows
to calculate the sales tax. If that item is not present then
you will
need to create it, or specify your tax item using the

6.2. Shipping

Interchange will add a generic item Shipping to each order
that has a
shipping cost. Its MEMO field will contain the text
description of the
mode. If that item is not in your QuickBooks item
definitions, then
you must create it, or specify your shipping item using the

6.3. Customer Imports

To generate a QuickBooks transtype of INVOICE, a CUSTOMER is
Interchange outputs a CUST IIF record for each sale with the
information. Since QuickBooks uses the customer name or
company to
generate the unique listing, we place the Interchange
username in
parentheses after the company or name.

6.4. IIF generation at time of order

As of 4.9, the IIF generation was moved from an order route
into the
Admin UI. This was done so that the IIF generation process
could be
fine tuned without restarting Interchange and placing an
order.  If
you need the IIF file generated at the time of order, you
can still
access the pre-4.9.6 files in extensions/quickbooks/legacy.

A. Credits

o    Mike Heins: This document was copied from the original
     documentation (extensions/quickbooks/ic_qb.pod) written
by Mike
     Heins mike at perusion.com.

o    Dan Browning: Updated by Dan Browning db at kavod.com.

B. Document history

o    July 20, 2002.  Initial revision.

C. Resources

C.1. Documentation

o    What are the IIF File Headers?

o    Also see the Quickbooks Help item, "Reference guide to

Copyright 2002 ICDEVGROUP. Freely redistributable under
terms of the
GNU General Public License.


More information about the interchange-users mailing list