Moving Information from a Database into a Lookup Table

    A user may wish to have access to information in a database, but 
for some reason, that database is not available for xlookups.  The 
following creates a lookup table from the data in the database.

    First, create a report from the database, listing the key column 
and up to four lookup columns.  You will need to add a derived column 
between each of the columns from the database.  The derived columns 
must contain a character string which does not occur in the data.  For 
this example, we will use "*".

    A sample derived column would be specified as follows:

              Heading: *
              Formula: "*"
              Column Spec: 2

    Set 0 top and bottom margins for your report page (F8 from Report
print options) and send to DISK instead of the printer.

    Now, go into Write and get the file you created when you made the 
report.  (It is an ASCII file.)  At this point, your report might look 
like this:

   Last name    *   First name   *     City       *  State    *   Zip
   ----------   -   -----------  -   ----------   -  -----    -  -----
   Eisenstein   *   Joseph       *   Waterloo     *  PA       *  01573
   Brothers     *   John         *   Pittsburgh   *  PA       *  01532
   Johnson      *   Nick         *   Pittsburgh   *  PA       *  15327

    In Write, you can use F7 to search for characters and replace with
others.  First, remove the dashes.

              Search for: -
              Replace with: ..
              Method: Fast Automatic

    The next steps handle the less than and greater than signs("<" and
">").  If there is absolutely no chance that your data could include
either, you may skip to the section dealing with spaces.  To deal with 
these characters, first convert the signs to text:

              Search for: <
              Replace with: LS.THAN
              Method: Fast Automatic

    And

              Search for: >
              Replace with: GR.THAN
              Method: Fast Automatic





Q & A Application Note       Rev. 6/92           #2106     Page 1 of 3


        Moving Information from a Database into a Lookup Table     

    Then convert the text to the appropriate keystroke:

              Search for: LS.THAN
              Replace with: <caps,>
              Method: Fast Automatic

    And

              Search for: GR.THAN
              Replace with: <caps.>
              Method: Fast Automatic

    Next, we remove the extra spaces.  If you are not concerned about 
preserving spaces between words in the data destined for the lookup 
table you can do a single pass with:

              Search for:  \  \   (note: there is one space)
              Replace with: ..
              Method: Fast Automatic


    If your data does contain spaces you will have to repeat the 
following until no more changes are made.

              Search for: \   \  (note: there are two spaces)
              with: \ \  (one space)
              Method: Fast Automatic

    Then mark the remaining single spaces with:

              Search for: \ \  (again, one space)
              Replace with: <sp>
              Method: Fast Automatic


    Next replace the derived column:

              Search for: *
              Replace with: <tab><f6>
              Method: Fast Automatic


    The final replace gets to new lines in the table:

Search for: @cr
Replace with: <enter><f6>
Method: Fast Automatic

    At the beginning of the text, insert, "<begdef><altx><f6>" and at the end 
of the text append, "<enddef>".





Q & A Application Note       Rev. 6/92           #2106     Page 2 of 3


        Moving Information from a Database into a Lookup Table 


    You have now created a macro file.  It might look like this:

<begdef><altx><f6>Last<sp>name<tab><f6gh<tab>First<sp>name<tab><f6>Cit
y<tab><f6>State<tab><f6>Zip<enter><f6>Einsenstein<tab><f6>Joseph<tab><
f6>Waterloo<tab><f6>PA<tab><f6>01573<enter><f6><Normal>Brothers<tab><f
6>John<tab><f6>Pittsburgh<tab><f6>PA<tab><f6>01532<enter><f6>Johnson<t
ab><f6>Nick<tab><f6>Pittsburgh<tab><f6>15327<enddef>


    Save the macro file with Ctl-F8.  Call it LOOKUP.MAC.

    Go into Customize, Edit lookup table.  Then Shift-F2, get the macro file 
and type Alt-x.  Your macro will the fill the lookup table.

    You will probably want to hit Shift-F2 again to get your usual macro file 
back.

    Note that you could create a macro which would print and edit the report 
to create the file, which in turn becomes the macro which fills the lookup 
table.

    Limits to lookup table size are discussed on page F-171.





























Q & A Application Note       Rev. 6/92           #2106     Page 3 of 3