Skip to content
This repository
tag: REL_4_9_8
Fetching contributors…

Octocat-spinner-32-eaf2f5

Cannot retrieve contributors at this time

file 303 lines (291 sloc) 18.646 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302
<!doctype html public "-//W30//DTD W3 HTML 2.0//EN">

<HTML>

<!-- This file was generated using SDF 2.001 by
Ian Clatworthy (ianc@mincom.com). SDF is freely
available from http://www.mincom.com/mtr/sdf. -->

<HEAD>
<TITLE>Interchange + QuickBooks HOWTO</TITLE>
</HEAD>
<BODY BGCOLOR="ffffff" LINK="993333">

<DIV CLASS="header">
<DIV CLASS="navigate">
<P ALIGN="Center"><A HREF="index.html" TARGET="_top">Catalog</A></P>
</DIV>
</DIV>
<DIV CLASS="title">
<P><IMG SRC="iclogo.gif" ALIGN="Right"></P>
<H1 CLASS="doc-title">Interchange + QuickBooks HOWTO</H1>
<ADDRESS CLASS="doc-author"></ADDRESS>
<BR CLEAR="All">
</DIV>
<DIV CLASS="contents">
<HR>
<H2>Table of Contents</H2>
<UL>
<A HREF="#Introduction">1. Introduction</A><UL>
<A HREF="#Summary Description">1.1. Summary Description</A>
<BR>
<A HREF="#Audience">1.2. Audience</A>
<BR>
<A HREF="#Contact the author">1.3. Contact the author</A>
<BR>
<A HREF="#Version">1.4. Version</A></UL>
<BR>
<A HREF="#Description">2. Description</A>
<BR>
<A HREF="#Contents">3. Contents</A>
<BR>
<A HREF="#Installation">4. Installation</A><UL>
<A HREF="#Terms and locations">4.1. Terms and locations</A>
<BR>
<A HREF="#Create and copy directories and files">4.2. Create and copy directories and files</A>
<BR>
<A HREF="#Quick Installation Script">4.3. Quick Installation Script</A>
<BR>
<A HREF="#Admin UI Usage">4.4. Admin UI Usage</A>
<BR>
<A HREF="#Additional database fields -- userdb">4.5. Additional database fields -- userdb</A>
<BR>
<A HREF="#Additional database fields -- inventory">4.6. Additional database fields -- inventory</A>
<BR>
<A HREF="#Modify catalog.cfg with additions:">4.7. Modify catalog.cfg with additions:</A>
<BR>
<A HREF="#Add quickbooks order route">4.8. Add quickbooks order route</A>
<BR>
<A HREF="#Additional Variables">4.9. Additional Variables</A>
<BR>
<A HREF="#Restart the catalog">4.10. Restart the catalog</A>
<BR>
<A HREF="#Export the items">4.11. Export the items</A>
<BR>
<A HREF="#Test">4.12. Test</A></UL>
<BR>
<A HREF="#Discussion">5. Discussion</A><UL>
<A HREF="#Sales Tax">5.1. Sales Tax</A>
<BR>
<A HREF="#Shipping">5.2. Shipping</A>
<BR>
<A HREF="#Customer Imports">5.3. Customer Imports</A>
<BR>
<A HREF="#IIF generation at time of order">5.4. IIF generation at time of order</A></UL>
<BR>
<A HREF="#Credits">A. Credits</A>
<BR>
<A HREF="#Document history">B. Document history</A>
<BR>
<A HREF="#Resources">C. Resources</A><UL>
<A HREF="#Documentation">C.1. Documentation</A></UL></UL>
</DIV>
<DIV CLASS="main">
<HR>
<H1><A NAME="Introduction">1. Introduction</A></H1>
<H2><A NAME="Summary Description">1.1. Summary Description</A></H2>
<P>Interchange QuickBooks -- QuickBooks support for transactions and items</P>
<H2><A NAME="Audience">1.2. Audience</A></H2>
<P>Users who already have Quickbooks setup and are familiar with it, in addition to having the foundation (or other) catalog correctly working.</P>
<H2><A NAME="Contact the author">1.3. Contact the author</A></H2>
<P>If you find any spelling errors, technical slip-ups, mistakes, subliminal messages, or if you wish to send feedback, critique, remarks, comments, or if you wish to contribute examples, instructions for alternative platforms, chapters, or other material, please do so.</P>
<P>The preferred method of submitting changes is in the form of a context diff against the SDF source file (ic_howto_qb.sdf). Please address your correspondence to:</P>
<P>Volunteer Maintainer, Dan Browning <A HREF="mailto:db@kavod.com">db@kavod.com</A></P>
<P>or</P>
<P>Original Author, Mike Heins <A HREF="mailto:mike@perusion.com">mike@perusion.com</A></P>
<H2><A NAME="Version">1.4. Version</A></H2>
<P>This document describes software based on Interchange 4.5 and later.</P>
<HR>
<H1><A NAME="Description">2. Description</A></H1>
<P>Interchange is a business-to-business and business-to-consumer internet ordering and cataloguing product. It has the ability to take orders via the World Wide Web, and store transaction data.</P>
<P>This document describes how to interface Interchange with QuickBooks, the popular small-business accounting program from Intuit.</P>
<P>QuickBooks has an import/export format called IIF, a mnemonic for Intuit Interchange Format. Fitting, eh?</P>
<P>The standard capabilities of Interchange allow production of IIF files for transaction passing. With some support from Interchange UserTags, it can even import and export item listings.</P>
<HR>
<H1><A NAME="Contents">3. Contents</A></H1>
<P>The extension files can be found in the Interchange tarball under the '<TT>extensions/quickbooks</TT>' directory. The following files are used with this extension:</P>
<PRE>
  usertag/import_quicken_items UserTag for importing items
  usertag/export_quicken_items UserTag for exporting items
  pages/admin/quickbooks/* Menu support for Interchange UI
  qb.catalog.cfg Quickbooks configuration.
</PRE>
<HR>
<H1><A NAME="Installation">4. Installation</A></H1>
<P>To set up this extension, the basic steps are:</P>
<UL>
<LI>Create and copy directories and files.
<LI>Add additional database fields.
<LI>Modify catalog.cfg with additions.
<LI>Add &quot;quickbooks&quot; order route to checkout pages.
<LI>Restart Interchange.
<LI>Export your items from Interchange catalog (or import your existing QuickBooks items to Interchange).
<LI>Test.</UL>
<H2><A NAME="Terms and locations">4.1. Terms and locations</A></H2>
<P>Several terms are used in the examples.</P>
<P><B>Catalog Directory</B></P>
<UL>
This is the main directory for the catalog, where catalog.cfg resides. It will have a NAME, the name for the catalog. (Some common Interchange demo names are <TT>foundation</TT>, <TT>construct</TT>, <TT>barry</TT>, and <TT>simple</TT>.)
<BR>
Common locations:
<BR>
/var/lib/interchange/NAME /usr/local/interchange/catalogs/NAME $HOME/catalogs/NAME
<BR>
We will use the path <TT>/var/lib/interchange/foundation</TT> in these examples.</UL>
<P><B>Interchange software directory</B></P>
<UL>
This is the main directory for your Interchange server, where the file <TT>interchange.cfg</TT> resides. Common locations:
<BR>
/usr/lib/interchange /usr/local/interchange $HOME/ic
<BR>
We will use the path <TT>/usr/lib/interchange</TT> in these examples.</UL>
<P><B>Interchange tarball directory</B></P>
<UL>
The quickbooks files are located in the untarred distribution file, before installation of Interchange is performed.</UL>
<P><B>Interchange User</B></P>
<UL>
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.
<BR>
We will use the user ID <TT>interch</TT> in these examples.</UL>
<H2><A NAME="Create and copy directories and files">4.2. Create and copy directories and files</A></H2>
<P>This extension requires you to add some files to your catalog.</P>
<P>It is assumed you have tools and knowledge to create directories with the proper permissions. Any directories that will contain varying files like order transaction logs will require write permission for the Interchange daemon user; pages and configuration only need have read permission.</P>
<H2><A NAME="Quick Installation Script">4.3. Quick Installation Script</A></H2>
<P>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.</P>
<PRE>

# Modify these three variables to match your environment.
export QB=/path/to/interchange/extensions/quickbooks
export VENDROOT=/usr/local/interchange
export CATROOT=/home/interch/catalogs/foundation

mkdir -p $CATROOT/include/menus $CATROOT/vars
cp -r $QB/TRANS_QUICKBOOKS \
      $CATROOT/vars
cp -r $QB/pages/admin/quickbooks \
      $CATROOT/pages/admin
cp -i $QB/usertag/* \
      $VENDROOT/code/UI_Tag

# Variables that optionally modify the export process, along with
# their help entries.
cat $QB/variable.txt-additions &gt;&gt; \
      $CATROOT/products/variable.txt
cat $QB/mv_metadata.asc-additions &gt;&gt; \
      $CATROOT/products/mv_metadata.asc

# Menu entries: start with the existing menu, then add ours.
cp -i $VENDROOT/lib/UI/pages/include/menus/Admin.txt \
      $CATROOT/include/menus
cat $QB/menus/Admin.txt.append &gt;&gt; \
      $CATROOT/include/menus/Admin.txt

# Some configuration changes.
cat &gt;&gt; $CATROOT/catalog.cfg &lt;&lt;EOF
# Allows vars/TRANS_QUICKBOOKS
DirConfig 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
EOF

</PRE>
<H2><A NAME="Admin UI Usage">4.4. Admin UI Usage</A></H2>
<P>After successful installation, there should be a &quot;Quickbooks&quot; menu item under the &quot;Admin&quot; category. From there, you can &quot;generate IIF files&quot;, download them, and perform other Quickbooks-related tasks.</P>
<P><B>Make orders directory</B></P>
<UL>
Create the directory <TT>orders</TT> in your Catalog Directory if it doesn't already exist. (It may be a symbolic link to another location.) It must have write permission on it.
<BR>
cd /var/lib/interchange/foundation mkdir orders
<BR>
If you are doing this as root, also do:
<BR>
chown interch orders
<BR>
This directory is used to store the QuickBooks IIF files produced for orders. The files are created with the form:
<BR>
qbYYYYMMDD.iif
<BR>
Each day will have a file, and when a day is complete you should download the orders. (There are other schemes possible.)</UL>
<P><B>Copy pages</B></P>
<UL>
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:
<BR>
cd /usr/lib/interchange/ cp -r extensions/quickbooks/pages/admin/quickbooks \ /var/lib/interchange/foundation/pages</UL>
<P><B>Copy report generation file etc/trans_quickbooks</B></P>
<UL>
This file is used to generate the IIF file(s) for transaction import into QuickBooks.
<BR>
cd /usr/lib/interchange/ cp extensions/quickbooks/etc/trans_quickbooks \ /var/lib/interchange/foundation/etc</UL>
<P><B>Copy usertags</B></P>
<UL>
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 <TT>lib/UI/usertag</TT>, which is #included as a part of the UI configuration file.
<BR>
cd /usr/lib/interchange cp -i extensions/quickbooks/usertag/* lib/UI/usertag</UL>
<H2><A NAME="Additional database fields -- userdb">4.5. Additional database fields -- userdb</A></H2>
<P>If your catalog is not based on a 4.6+ version of the foundation catalog, you may not have some of the additional database fields necessary. If you want the user to retain their customer number, add the following field to the &quot;userdb&quot; table:</P>
<P>customer_number</P>
<P>It can be an integer number field if your database needs that information. To add the field in MySQL, you can issue the following queries at the mysql prompt:</P>
<P>alter table userdb add column customer_number int;</P>
<P>If you don't add it, it just means that a new customer number will be assigned every time.</P>
<P>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 first.</P>
<H2><A NAME="Additional database fields -- inventory">4.6. Additional database fields -- inventory</A></H2>
<P>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 &quot;Other Income&quot; will be used in the exports.</P>
<P>Add the following fields to the &quot;inventory&quot; table:</P>
<P>account cogs_account</P>
<P>To add the fields in MySQL, you can issue the following queries at the mysql prompt:</P>
<P>alter table inventory add column account char(20); alter table inventory add column cogs_account char(20);</P>
<P>Other SQL databases will have similar facilities.</P>
<P>If you are using Interchange DBM files, just export the inventory database, stop the Interchange server (to prevent corruption), add the fields on the first line by editing the <TT>inventory.txt</TT> file, then restart Interchange.</P>
<H2><A NAME="Modify catalog.cfg with additions:">4.7. Modify catalog.cfg with additions:</A></H2>
<P>Add the entries in qb.catalog.cfg to catalog.cfg (you can use an include statement if you wish).</P>
<P>There are some Require directives to ensure that the needed UserTag definitions are included in the catalog, as well as the Route which is used</P>
<H2><A NAME="Add quickbooks order route">4.8. Add quickbooks order route</A></H2>
<P>In the Interchange UI, there is a Preferences area &quot;ORDER_ROUTES&quot;. You should add the <TT>quickbooks</TT> route. Place it after the transaction logging step, i.e.</P>
<P>code ORDER_ROUTES Variable log quickbooks main copy_user</P>
<P>ADVANCED, If you know Interchange Variable settings, you can add it directly:</P>
<P>Variable ORDER_ROUTES log quickbooks main copy_user</P>
<P>Also, you can use other methods to set order routes. See the Interchange reference documentation.</P>
<H2><A NAME="Additional Variables">4.9. Additional Variables</A></H2>
<P>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 your mv_metadata.asc file for automatic display by the Admin UI.</P>
<P>See the installation script at the top of this document for commands that will append the empty variables to your variable.txt and the help information to your mv_metadata.asc files.</P>
<H2><A NAME="Restart the catalog">4.10. Restart the catalog</A></H2>
<P>This can be done by restarting the Interchange server or by clicking <TT>Apply Changes</TT> in the UI.</P>
<H2><A NAME="Export the items">4.11. Export the items</A></H2>
<P>You can access the Quickbooks UI index by making your URL:</P>
<P><A HREF="http://YOURCATALOG_URL/admin/quickbooks/index">http://YOURCATALOG_URL/admin/quickbooks/index</A></P>
<P>It will provide options for importing and exporting items. This is necessary so QuickBooks will be able to take orders for your items.</P>
<P>QuickBooks uses the product &quot;name&quot; as an SKU, along with an integer reference number. Either you need to make your SKUs match the integer reference number, or you must ensure your product title is unique.</P>
<H2><A NAME="Test">4.12. Test</A></H2>
<P>Place a test order on your Interchange catalog once you have finished installing. You should find a file in the <TT>orders</TT> directory with the name <TT>qbYYYYMMDD.iif</TT>. (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 shipping definitions, discussed below.</P>
<HR>
<H1><A NAME="Discussion">5. Discussion</A></H1>
<P>The interface provided works for the sample company data distributed with QuickBooks. There are certain requirements to make sure it works in your environment.</P>
<P>Also, you can change the configuration by editing the file etc/trans_quickbooks to suit your IIF file needs.</P>
<H2><A NAME="Sales Tax">5.1. Sales Tax</A></H2>
<P>QuickBooks has a taxing system whereby tax rates are defined by customer location. There is usually also a generic <TT>Sales Tax Item</TT>, such as contained in the sample company data. This allows Interchange 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 <TT>QB_SALES_TAX_ITEM</TT> variable.</P>
<H2><A NAME="Shipping">5.2. Shipping</A></H2>
<P>Interchange will add a generic item <TT>Shipping</TT> 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 <TT>QB_SHIPPING_ITEM</TT> variable.</P>
<H2><A NAME="Customer Imports">5.3. Customer Imports</A></H2>
<P>To generate a QuickBooks transtype of INVOICE, a CUSTOMER is required. Interchange outputs a CUST IIF record for each sale with the customer 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.</P>
<H2><A NAME="IIF generation at time of order">5.4. IIF generation at time of order</A></H2>
<P>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 <TT>extensions/quickbooks/legacy</TT>.</P>
<HR>
<H1><A NAME="Credits">A. Credits</A></H1>
<UL>
<LI><B>Mike Heins</B>: This document was copied from the original POD documentation (<TT>extensions/quickbooks/ic_qb.pod</TT>) written by Mike Heins <A HREF="mailto:mike@perusion.com">mike@perusion.com</A>.
<LI><B>Dan Browning</B>: Updated by Dan Browning <A HREF="mailto:db@kavod.com">db@kavod.com</A>.</UL>
<HR>
<H1><A NAME="Document history">B. Document history</A></H1>
<UL>
<LI>July 20, 2002. Initial revision.</UL>
<HR>
<H1><A NAME="Resources">C. Resources</A></H1>
<H2><A NAME="Documentation">C.1. Documentation</A></H2>
<UL>
<LI>What are the IIF File Headers? <A HREF="http://www.quickbooks.com/support/faqs/qbw2000/121756.html">http://www.quickbooks.com/support/faqs/qbw2000/121756.html</A>
<LI>Also see the Quickbooks Help item, &quot;Reference guide to import files&quot;</UL>
<P>Copyright 2002 ICDEVGROUP. Freely redistributable under terms of the GNU General Public License.</P>
</DIV>
<DIV CLASS="footer">
<DIV CLASS="navigate">
<P ALIGN="Center"><A HREF="index.html" TARGET="_top">Catalog</A></P>
</DIV>
<HR>
<ADDRESS><SPAN CLASS="doc-id">ic_howto_qb-1.3</SPAN> <SPAN CLASS="doc-status">(Draft)</SPAN></ADDRESS>
<ADDRESS CLASS="doc-modified">4 June 2003</ADDRESS>
<ADDRESS CLASS="copyright">Copyright &copy; 2001,2002 Mike Heins &lt;<A HREF="mailto:mike@perusion.com">mike@perusion.com</A>&gt;, Dan Browning &lt;<A HREF="mailto:db@kavod.com">db@kavod.com</A>&gt;</ADDRESS>
</DIV>

</BODY>
</HTML>
Something went wrong with that request. Please try again.