[docs] docs - danb modified ic_howto_qb.sdf
docs@interchange.redhat.com
docs@interchange.redhat.com
Sat Jul 20 19:53:00 2002
User: danb
Date: 2002-07-20 23:52:45 GMT
Added: . ic_howto_qb.sdf
Log:
* Initial revision of Interchange + Quickbooks HOWTO
- Taken from ic_qb.pod
- Plus a few minor modifications/additions
Revision Changes Path
1.1 docs/ic_howto_qb.sdf
rev 1.1, prev_rev 1.0
Index: ic_howto_qb.sdf
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
!init OPT_LOOK=3D"akopia"; OPT_STYLE=3D"manual"
# $Id: ic_howto_qb.sdf,v 1.1 2002/07/20 23:52:45 danb Exp $
!define DOC_NAME "Interchange + QuickBooks HOWTO"
!define DOC_TYPE ""
!define DOC_CODE "ic_howto_qb"
!define DOC_VERSION substr('$Revision: 1.1 $', 11, -2)
!define DOC_STATUS "Draft"
!define DOC_PROJECT "Interchange"
!define DOC_URL "http://www.icdevgroup.org/doc-4.8/ic_howto_qb.html"
!define DOC_OWNER "2001,2002 Mike Heins E<lt>{{EMAIL:mike@perusion.com}}E<g=
t>, minor additions by Dan Browning E<lt>{{EMAIL:dan.browning@kavod.com}}E<=
gt>"
!build_title
H1:Introduction
H2: Summary Description
Interchange QuickBooks -- QuickBooks support for transactions and items
H2:Audience
N:Users who already have Quickbooks setup and are familiar with it, in addi=
tion
to having the foundation (or other) catalog correctly working.
H2:Contact the author
N:If you find any spelling errors, technical slip-ups, mistakes, subliminal=
=20
messages, or if you wish to send feedback, critique, remarks, comments, or =
if
you wish to contribute examples, instructions for alternative platforms,=20
chapters, or other material, please do so.=20=20=20=20=20=20=20
N:The preferred method of submitting changes is in the form of a context di=
ff
against the SDF source file (ic_howto_qb.sdf). Please address your corresp=
ondence
to:
N:Volunteer Maintainer, Dan Browning {{EMAIL:dan.browning@kavod.com}}
or=20
N:Original Author, Mike Heins {{EMAIL:mike@perusion.com}}
H2: Version
This document describes software based on Interchange 4.5 and later.
=3Dhead1 Description
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.
This document describes how to interface Interchange with QuickBooks, 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 UserTags,
can even import and export item listings.
=3Dhead1 Contents
The extension files can be found in the Interchange tarball under the=20
'C<extensions/quickbooks>' directory. The following files are used with th=
is=20
extension:
!block example
usertag/import_quicken_items UserTag for importing items
usertag/export_quicken_items UserTag for exporting items
pages/admin/quickbooks/* Menu support for Interchange UI
etc/trans_quickbooks Report file used with quickbooks route
qb.catalog.cfg Route for quickbooks transaction logging
!endblock
=20=20
=3Dhead1 Installation
To set up this extension, the basic steps are:
* Create and copy directories and files.
* Add additional database fields.
* Modify catalog.cfg with additions.
* Add "quickbooks" order route to checkout pages.
* Restart Interchange.
* Export your items from Interchange catalog (or import your
existing QuickBooks items to Interchange).
* Test.
=3Dhead2 Terms and locations
Several terms are used in the examples.
=3Dover 4
=3Ditem Catalog Directory
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 C<foundation>, C<construct>, C<barry>, and C<simple>.)
Common locations:
/var/lib/interchange/NAME
/usr/local/interchange/catalogs/NAME
$HOME/catalogs/NAME
We will use the path C</var/lib/interchange/foundation> in these examples.
=3Ditem Interchange software directory
This is the main directory for your Interchange server, where the file
C<interchange.cfg> resides. Common locations:
/usr/lib/interchange
/usr/local/interchange
$HOME/ic
We will use the path C</usr/lib/interchange> in these examples.
=3Ditem Interchange User
The Interchange daemon runs as a user ID that cannot be root. It will requi=
re
write permission on directories it must modify to do its work.
We will use the user ID C<interch> in these examples.
=3Dback
=3Dhead2 Create and copy directories and files
This extension requires you to add some files to your catalog.
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.
=3Dover 4
=3Ditem Make orders directory
Create the directory C<orders> in your Catalog Directory if it doesn't alre=
ady
exist. (It may be a symbolic link to another 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 order=
s.
The files are created with the form:
qbYYYYMMDD.iif
Each day will have a file, and when a day is complete you should download
the orders. (There are other schemes possible.)
=3Ditem Copy pages
You will want the Interchange UI support if you are using the UI. It provid=
es
links for importing/exporting items, downloading and viewing IIF files, and
possibly other functions over time. At the UNIX command line:
cp -r /usr/lib/interchange/extensions/quickbooks/pages/admin/quickbooks=
\
/var/lib/interchange/foundation/pages
=3Ditem Copy report generation file etc/trans_quickbooks
This file is used to generate the IIF file(s) for transaction oimport
into QuickBooks.
cp /usr/lib/interchange/extensions/quickbooks/etc/trans_quickbooks \
/var/lib/interchange/foundation/etc
=3Ditem 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 C<lib/UI/usertag>, which is #included as a
part of the UI configuration file.
cd /usr/lib/interchange
cp -i extensions/quickbooks/usertag/* lib/UI/usertag
=3Dback
=3Dhead2 Additional database fields -- userdb
Quickbooks requires a couple more fields than Interchange has. If you
want the user to retain their customer number, add the following field to t=
he
"userdb" table:
customer_number
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:
alter table userdb add column customer_number int;
If you don't add it, it just means that a new customer number will be assig=
ned
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 first.
=3Dhead2 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 exports.
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 table inventory add column cogs_account char(20);
Other SQL databases will have similar facilities.
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 C<inventory.txt> file, then
restart Interchange.
=3Dhead2 Modify catalog.cfg with additions:
Add the entries in qb.catalog.cfg to catalog.cfg (you can use a #include
statement if you wish).
There are some Require directives to ensure that the needed UserTag definit=
ions
are included in the catalog, as well as the Route which is used
=3Dhead2 Add quickbooks order route
In the Interchange UI, there is a Preferences area "ORDER_ROUTES". You shou=
ld
add the C<quickbooks> route. Place it after the transaction logging step, i=
e.
code ORDER_ROUTES
Variable log quickbooks main copy_user
ADVANCED, If you know Interchange Variable settings, you can add it directl=
y:
Variable ORDER_ROUTES log quickbooks main copy_user
Also, you can use other methods to set order routes. See the Interchange
reference documentation.
=3Dhead2 Additional Variables
Optionally, you may specify some variables that modify the behavior of the
Quickbooks export feature. Below is a command that will append the empty v=
ariables to
your variable.txt file.
!block example
cat >>/path/to/your/products/variable.txt <<EOF
QB_DISABLE_DOCNUM Quickbooks
QB_ITEM_PREFIX Quickbooks
QB_ITEM_LENGTH Quickbooks
QB_FILE_SUFFIX Quickbooks
QB_SHIPPING_ACCOUNT Quickbooks
QB_SHIPPING_ITEM Quickbooks
QB_DEFAULT_ACCOUNT Quickbooks
QB_SALES_TAX_ACCOUNT Quickbooks
QB_SALES_TAX_AGENCY Quickbooks
QB_SALES_TAX_ITEM Quickbooks
EOF
!endblock
Documentation for these variables is provided via item-specific meta data, =
which can
be added to your mv_metadata.asc file in a similar way as above for automat=
ic=20
display by the Admin UI:
!block example
cat >>/path/to/your/products/mv_metadata.asc <<EOF
variable::Variable::QB_DISABLE_DOCNUM yesno <b>Yes</b> will cause =
the invoice number (AKA DOCNUM & Document Number) to be blank. No by defau=
lt. <b>No</b> will cause etc/trans_quickbooks the use of the Interchange o=
rder number as the Quickbooks invoice number. Some prefer to have the numb=
er left blank so as not to cause Quickbooks to change its auto-count.
variable::Variable::QB_ITEM_PREFIX text A prefix applied to all pr=
oduct names (the unique identifier in Quickbooks). Empty by default. For =
example, some Quickbooks users may have a category for all the products sol=
d on their website such as "E-Commerce Products:" (the colon is a category =
deliniator for Quickbooks). Other Quickbooks users may use the same catego=
ry scheme in in their Interchange store as they do in their Quickbooks item=
lists, in which case something to this effect would be applicable: "[item=
-field category]:"=20=20
variable::Variable::QB_ITEM_LENGTH text The item name maximum leng=
th for product line items (any characters past this length will be truncate=
d). 30 by default. Quickbooks versions 6.0 and 99 may have a problem with=
item names that are longer than 30 characters; however, lengths of 45 and =
longer have been known to work on Quickbooks 2000 and later version. Pleas=
e report any compatibility problems/information to the interchange-users@ic=
devgroup.org mailing list.
variable::Variable::QB_FILE_SUFFIX text If left blank, the current=
date will be used and each order will be appended to the file. <BR><BR>An=
other option is to use [value mv_order_number] to cause the creation of a s=
eparate file for each order.
variable::Variable::QB_SHIPPING_ACCOUNT text The income account as=
sociated with shipping. "Other Income" by default.
variable::Variable::QB_SHIPPING_ITEM text The item name given for =
the shipping. "Shipping" by default.
variable::Variable::QB_DEFAULT_ACCOUNT text The income account ass=
ociated with each product line item. "Other Income" by default; however, t=
he highest precence is 'account' field in the 'inventory' table.=20
variable::Variable::QB_SALES_TAX_ACCOUNT text The liability accoun=
t associated with sales tax. "Sales Tax Payable" by default.
variable::Variable::QB_SALES_TAX_AGENCY text The name of the sales=
tax agency for your locality. "State Board of Equalization" by default (t=
he name given in the Quickbooks 2000 sample products based business).
variable::Variable::QB_SALES_TAX_ITEM text The item name (Quickboo=
ks unique identifier) for the sales tax. "Sales Tax Item" by default (from=
Quickbooks 2000 sample products based business).
EOF
!endblock
=3Dhead2 Restart the catalog
This can be done by restarting the Interchange server or by clicking
C<Apply Changes> in the UI.
=3Dhead2 Export the items=20
You can access the Quickbooks UI index by making your URL:
{{URL:http://YOURCATALOG_URL/admin/quickbooks/index}}
It will provide options for importing and exporting items. This is necessary
so QuickBooks will be able to take orders for your items.
QuickBooks uses the product "name" as an SKU, along with an integer referen=
ce
number. Either you need to make your SKUs match the integer reference numbe=
r,
or you must ensure your product title is unique.
=3Dhead2 Test
Place a test order on your Interchange catalog once you have finished insta=
lling.
You should find a file in the C<orders> directory with the name C<qbYYYYMMD=
D.iif>.
(YYYY=3Dyear, MM=3Dmonth, DD=3Dday.) 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 sal=
es tax
or shipping definitions, discussed below.
=3Dhead1 Discussion
The interface provided works for the sample company data distributed 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_quickb=
ooks
to suit your IIF file needs.
=3Dhead2 Sales Tax
QuickBooks has a taxing system whereby tax rates are defined by customer lo=
cation.
There is usually also a generic C<Sales Tax Item>, such as contained in the=
sample
company data. This allows Interchange to calculate the sales tax. If that i=
tem is
not present then you will need to create it, or specify your tax item using=
the
C<QB_SALES_TAX_ITEM> variable.
=3Dhead2 Shipping
Interchange will add a generic item C<Shipping> to each order that has a sh=
ipping
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 spe=
cify
your shipping item using the C<QB_SHIPPING_ITEM> variable.
=3Dhead2 Customer Imports
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.
A1:Credits
*{{B:Mike Heins}}: This document was copied from the original POD documenta=
tion
({{EX:extensions/quickbooks/ic_qb.pod}}) written by Mike Heins {{EMAIL:mike=
@perusion.com}}.
*{{B:Dan Browning}}: Slightly modified by Dan Browning
{{EMAIL:dan.browning@kavod.com}}.
A1:Document history
*July 20, 2002. Initial revision.
A1:Resources
A2:Documentation
* What are the IIF File Headers? {{URL:http://www.quickbooks.com/support/f=
aqs/qbw2000/121756.html}}
* See the Quickbooks Help item, "Reference guide to import files"