You can download all the data regarding your orders into several different file formats.
These include:
- SDF - space delimited file (columns of data with specific widths)
- CSV - comma separated values (data separated with ,)
- TAB - data separated with tabs
- XLS - excel 5.0 + file (max capacity of 64,000 rows)
- WKS - lotus (yup, some people still use it)
- FOX2X - dbf file (dbase, foxpro 2.0+, clipper)
- VFP - visual foxpro database
- XML - xml markup (if you don't know, you don't want it)
In the store settings edit screen there is a field called, "qbconfig". To get the data you desire in the format you desire you must add these tags.
The second tag above controls the format. Future versions of this link will allow you to choose the file type download at run time. Currently you must assign it in the store settings (5/15/04).
Note: If you want to download data in the quickbooks format, the tag would be:
The data for all orders comes from three files. They are:
wws_invoice.dbf - Invoice Header File
wws_customers.dbf - Customer Master File
wws_lineitems.dbf - Invoice Detail File (line items)
The download runs a query that collects all data using the invoice number and customer number as the keys. These are the pk fields you see in the control panel.
The fields of data contained in the download are as follows.
Wws_invoice.sessionid - dynamic value created for each visitor
Wws_invoice.custpk - customer number
Wws_invoice.storepk - store id
Wws_invoice.warehouse - warehouse id
Wws_invoice.invno - dynamic value assigned at checkout
Wws_invoice.invdate - invoice date
Wws_invoice.COMPLETED - date invoice is completed
Wws_invoice.downloaded - logical field for whether or not the record was downloaded
Wws_invoice.invtotal - invoice total including freight and tax
Wws_invoice.ponumber - po number
Wws_invoice.taxrate - tax rate
Wws_invoice.tax - tax amount
Wws_invoice.handling - shipping cost to customer
Wws_invoice.discount as tdiscount - invoice discount amount
Wws_invoice.discountid - discount id used
Wws_invoice.cctype - credit card type
Wws_invoice.cc - credit card number
Wws_invoice.ccexp - credit card expiration date
Wws_invoice.ccresult - credit card results
Wws_invoice.XML - supplier error messages
Wws_invoice.ordercode - order code
Wws_invoice.invcode - invoice code
Wws_invoice.WHEN - time and date of order
Wws_invoice.coretotal - total value of core charge
Wws_invoice.st_name - ship to name
Wws_invoice.st_Addr - ship to address
Wws_invoice.st_Addr2 - ship to address line 2
Wws_invoice.st_City - ship to city
Wws_invoice.st_State - ship to state
Wws_invoice.st_zip - ship to zip
Wws_invoice.st_phone - ship to phone
Wws_invoice.st_country - ship to country
Wws_lineitems.pk AS lineitempk - line item primary key (pk)
Wws_lineitems.sup_ord_id - supplier order number
Wws_lineitems.mfr - manufacturer of product ie: brand
Wws_lineitems.sku - part number
Wws_lineitems.qty - quantity ordered
Wws_lineitems.qty_bo - quantity back ordered
Wws_lineitems.LIST - list price
Wws_lineitems.price - selling price
Wws_lineitems.Core - core amount
Wws_lineitems.itemtotal - line item total extended
Wws_lineitems.discount - discount by line item
Wws_lineitems.weight - weight of product
Wws_lineitems.timein - time item was added to shopping cart
Wws_lineitems.TIMEOUT - time item was sent to supplier or fulfilled
Wws_lineitems.COST - cost of item
Wws_lineitems.coreprice - core cost
Wws_lineitems.make - make of vehicle
Wws_lineitems.sup - supplier id
Wws_lineitems.whse - supplier's whse id
Wws_lineitems.linecode - suppliers line code of part
Wws_lineitems.skuform - formatted sku (spaces,comma's, tab)
Wws_lineitems.itemform - 2nd formatted sku
Wws_customers.Company - customers company
Wws_customers.LASTNAME - customer lastname
Wws_customers.FIRSTNAME - customer firstname
Wws_customers.Address2 - customer billing address line 2
Wws_customers.City - customer billing city
Wws_customers.State - customer billing state
Wws_customers.Zip - customer billing zip
Wws_customers.zip4 - customer billing zip + four
Wws_customers.Country - customer billing country
Wws_customers.Countryid - customer billing country id
Wws_customers.EMAIL - customer email address
Wws_customers.entered - customer added to system date
Wws_customers.UPDATED - customer last udated profile
Wws_customers.USERID - customer user id (b2b)
Wws_customers.PASSWORD - customer password
Wws_customers.ACTIVE - customer status
Wws_customers.SOURCE - customer marketing id
Wws_customers.salesrep - sales rep id
Wws_customers.custtype - customer type
Wws_customers.created - date and time customer added
Wws_customers.laston - last time and date customer visited
Wws_customers.expires - if subscription site, expiration date of customer
Wws_customers.model - customer model of car
Wws_customers.ip - customer ip address
Wws_customers.optin - customer wants marketing info
Wws_customers.TAXABLE - if true, customer is taxable, else no tax
LEFT(Wws_invoice.notes,100) AS invnotes - notes from invoice
Wws_invoice.weight AS invweight - invoice weight total
LEFT(Wws_invoice.shipping,10) AS shipmeth - shipping method id
LEFT(Wws_invoice.ccresultx,100) AS ccdetails - detailed credit card results
LEFT(Wws_invoice.track_num,100) AS trackingid - shippers tracking number
LEFT(Wws_customers.Address,100) AS Address - billing address
LEFT(Wws_customers.url,60) AS url - customers website if any
LEFT(Wws_customers.HeardFrom,50) AS HeardFrom - referral info
LEFT(Wws_customers.referral,50) AS referral - more referral info
The fields above represent all fields in a b2c and b2b order. If you have trouble formatting your file for upload into your own system, please email support with a detailed file and field name.
