Prior to attempting to connect Excel or any spread sheet to FIX you need to have an ODBC connection to the database.  See your control panel settings for an ODBC DSN(Data Set Name) and ensure it is using the ZTI driver pointing at the FIX database folder.  For more details on this procedure see the Microsoft help on creating a DSN using ODBC.

Using standard MSOffice installations MSQuery is not always easily available.  You may find this software on your PC at

"C:\Program Files\Microsoft office\Office10\MSQRY32.EXE" (Office2K/XP)
or "C:\Program Files\Microsoft office\Office\MSQRY32.EXE" (Office9X). 
By placing a short cut on your desktop, you can easily access MSQuery and create saved queries to be re-used within many spread sheets and word documents or open other example queries.

When prompted to select Tables from FIX, certain settings in the ‘options’ dialogue box make it easier to scroll through the tables and ignore many ‘system’ related tables.  You only need the option to see 'Tables'.

To access item’s summary information, you will probably only need to select the item master entity set (Table) FSITM.  Once selected, then expand the FSITM control and select individual fields.  The most common method of determining which fields you will need is to use the FIX software itself.  In most cases you will almost certainly select the customer number field C1ANO and the item code field ITEM.

By selecting from the FIX main menu, the item master in either change or inquiry mode, you will be able to select an item.  Once the item is open, navigate within the various windows until you see the field you want in your query.  Once your cursor is on top of the field, press the F11 button. 

The F11 button will display a field attributes dialogue box.  Within this dialogue box the fieldname is displayed.  For an example to access the item master the first field where your cursor is located is the item’s description field.  The figure below shows what the F11 button displays.

Figure 2 - F11 Dialogue showing field Attributes

To create a catalog of all items within the database you will need the fields: C1ANO – Customer Number, ITEM – Item Code, ITDSC – Item Description, IUNDS – Item Unit Quantity Description, IUNQT – Item Unit Quantity, ICRDAT – Item creation date (Revision Date).

More documentation exists within the Books sub-folder to the FIX database directory (“c:\program files\fixeval\book” for evaluation systems and “c:\fix” for production systems).  Within this directory/folder are Excel spread sheets for the definition of all tables (Entity Sets) within FIX, DICENTS.XLS and a definition for every field within the FIX database DICFLDS.XLS.  If a Excel is unavailable, a browser will also open up FIXDIC.htm.  FIXDIC.htm is a web page of all FIX database fields and tables.

 

The easiest method of become familiar with MSQuery and FIX is to actually create an example such as the catalog.

To accomplish this in Word perform the following steps:

1. Open Word
2. Using the View – Toolbars menu ensure the Database Toolbar is visible
3. Click the ‘Insert Database’ button on the toolbar

 

4. Click ‘Get Data’
5. Depending upon the revision of Windows Operating system/Office Products you will see different option next.  The object of the next step is to either use an existing MSQuery or define an MSQuery.  MSQuery is a Microsoft product used to connect Office products to a data source, such as FIX.  Assuming that you are starting new, you will be creating a new MSQuery connection to FIX.  If you are using Windows9x/NT/ME then you should click ‘MSQUERY’ to create a new query.  If you are using Windows/2K/XP/Longhorn then you would click ‘New Source’.  Using either method, setup a MSQuery using the DSN(Data Source Name) to FIX for Table FSITM with the above Fieldnames.

To see a sample item catalog word document you may download from http://www.fixpro.com/sw/report/sample_item_catalog_in_word_format.htm.  After installation, this word document uses a MSQuery produce a sample catalog from the evaluation database (c:\program files\fixeval).  To examine the query, download and use MSQuery to examine it’s contents located at c:\program files\fixeval\books\item_catalog.dqy.  If you’re operating with a production copy of FIX and wish to move the sample to create a production catalog, edit the saved MSQuery file “c:\program files\fixeval\books\item_catalog.dqy” with a text editor and save it to production with the corrected location for the database in the FIX=DSN= statement. 

To select information related to the purchases (and corresponding received quantities from purchase orders) on a FIX database, the item master and the receiving transactions should be tables selected.

The following figure and table outlines the tables needed and their corresponding criteria to perform this query.

 

Entity Set	Criteria	Relationship	Connected Table	Comment
FSCUS	FSCUS.C1ANO=1 (depending upon the installation and how many companies/customers are using the database)	FSCUS.C1ANO=FSITM.C1ANO	FSITM	Customer Master to get company name, and special freight & handling codes
FSITM	Where FSCUS.HITMCD <> FSITM.ITEM and FSCUS.FITMCD FSCUS.HITMCD <> FSITM.CD	FSITM.ITEM=FSREQD3.RITEM	FSREQD3	Item Master
FSREQD3	Where FSREQD3.RITEM=” F-00-001646” (If a specific form is being querried such as F-00-001646)	FSREQD3.RTRANS=FSREQH3.RTRANS and FSREQD3.RCUST=FSREQH3.RCUST	FSREQH3	History Detail Transactions
FSREQH3	Where FSREQH3.RCODE$=”1”			Only Transactions that are Receiving

 

For this purchasing example more than one table is selected from the database.  Connecting these tables may be made easier done by manually connecting them.  To connect (relate) tables to each other drag and drop fields from one table onto another.

An example of this query is available on the web from http://www.fixpro.com/_download/itempurchasereceivinghistory.exe