Multi-columnar reports

 Many users have asked our support staff whether R&R can format a multi-
 column directory. An example of this format is the telephone book. Names
 are printed in alphabetical order in four columns per page. Unlike a
 normal report that prints names in a single column per page, the directory
 format lists names down the first column, then down the second column,
 etc.

 There is a simple trick you can use to print this type of format. However,
 there are a few restrictions. In most applications, these restrictions are
 acceptable.

 The first restriction is that you cannot sort or group records. If you
 wish to have records sorted, you must sort the database in dBase before
 running the report.

 The second restriction is that you cannot use a query. If you want to
 print only a subset of the records in the database, you will have to
 extract the desired records into another database file using the dBase
 COPY command.

 The third and final restriction is that each column on the page must
 contain the same number of entries. This means you cannot use the
 word-wrap format, compress blank body lines, or insert blank lines at the
 end of the body section.

 The following six steps show you how to format a multi-column directory.

 1. FORMAT THE FIRST COLUMN
    Insert fields in the body area to create the desired format of a
    directory entry. These fields will produce the first column on the
    page. At this time you may also create a page header and footer, if
    desired.

 2. COMPUTE THE NUMBER OF ENTRIES PER COLUMN
    You must compute the number of entries that will fit in one column on a
    page. First use the /Print command to set the desired page length. Then
    print a test-pattern to see how many entries fit on a page.

 3. SET A RELATION TO THE SECOND COLUMN
    The fields used to format the second column come from relating the
    database to itself. This creates two record pointers into the same
    file. The second pointer must be n records ahead of the first pointer,
    where n is the number of entries per column.

    For example, if there is enough space for thirteen entries per column,
    then the first record in column one is record 1, and the first record
    in column two is record 14. (Record 14 is thirteen records ahead of
    record 1.)

                         Sample Two-Column Directory
    ------------------------------------------------------------------------

     Ace Computer Forms (record #1)       Boston Computer Co. (record #14)
     123 First Street                     413 Congress Street
     Cambridge, MA  02142                 Boston, MA  02128

     Acme Algorithms, Inc.                Computer Commuter Corp.
     8826 B Street                        37 Oak Street
     Burlington, MA  01803                Natick, MA  01760

               .                                    .
               .                                    .
               .                                    .


   ------------------------------------------------------------------------

    To set this relation, you must first create a calculated field to
    calculate the desired record number. Select the /Field Calculate Create
    command and enter the name Col_2_link. Then enter the following
    expression, assuming the master database is named LIST.

    RECNO(LIST) + 13

    Now you can set the relation with the /Database Relation Set command.
    Use Col_2_link as the linking field, select Exact-Lookup, select the
    master database, enter COL2 as the alias, select <<RECNO>> as the
    index, and then select Blank as the failure action.

 4. FORMAT THE SECOND COLUMN
    Now you can format the second column by inserting fields from the COL2
    database to the right of the first entry. Your screen should now look
    something like this:

       Header                  Sample Two-Column Directory
       Header                  ---------------------------
       Header
       Body        'XXXXXXXXXXXXXXXXXXX            'XXXXXXXXXXXXXXXXXXX
       Body        'XXXXXXXXXXXXXXXXXXXXX          'XXXXXXXXXXXXXXXXXXXXX
       Body        'XXXXXXXXX, 'X  'XXXX           'XXXXXXXXX, 'X  'XXXX
       Body

    The left column 'XXXXXXXX's are fields from the LIST database, and the
    right column 'XXXXXXX's are fields from the COL2 database.

 5. QUERY OUT DUPLICATES
    In our two-column example, there is a need to eliminate every second
    group of thirteen records from the master file. The reason for this is
    that when a group of thirteen records from the master file is printed
    in the first column, the next thirteen records from the related file
    are printed in the second column.

    For example, page one will include records 1-13 in the first column,
    and records 14-26 in the second column. Then you want page two to print
    records 27-39 in the first column and records 40-52 in the second
    column. Therefore, you need to read records 1-13 from the master file,
    and then skip to record 27 on page two.

    How can you tell R&R to read thirteen records and then skip the next
    thirteen records?  The trick is to create a calculated field that
    contains one value for the wanted records and another value for the
    unwanted records. Then you can use a query to select the wanted
    records.

    Select the /Field Calculate Create command and enter the name Wanted.
    Then enter the following expression:

    MOD(INT((RECNO(LIST)-1)/13),2)

    The best way to see how this works is to build a table of values for
    each part of the formula.  Then you can see why the query will work.

    In the following table, note the pattern in the last column. The
    thirteen wanted records contain a 0, while the next thirteen unwanted
    records contain a 1. As you can see, this pattern repeats every
    thirteen records.

 RECNO(LIST) RECNO(LIST)-1 INT(RECNO(LIST)-1/13) MOD(INT((RECNO(LIST)-1/13),2)
 ----------- ------------- --------------------- -----------------------------
      1            0                  0                         0
      2            1                  0                         0
      3            2                  0                         0
      .            .                  .                         .
      .            .                  .                         .
      .            .                  .                         .
     13           12                  0                         0

     14           13                  1                         1
     15           14                  1                         1
     16           15                  1                         1
      .            .                  .                         .
      .            .                  .                         .
      .            .                  .                         .
     26           25                  1                         1

     27           26                  2                         0
      .            .                  .                         .
      .            .                  .                         .
      .            .                  .                         .


    To finish this step, select the /Query command and create the query:
    Select all records where (Wanted is equal to "0").

 6. OPTIONALLY CREATE MORE COLUMNS
    You can keep adding columns as long as there is enough room on the
    page. To create another column, just repeat steps 3 through 5.

    In step 3, add another calculated linking field, changing the
    expression to skip an extra thirteen records for each additional
    column. For example, for a three-column report, create a field named
    Col_3_link defined by the expression RECNO(LIST) + 26.

    In step 5, change the second number in the MOD function to the number
    of columns in the report. For example, for a three-column report,
    change the expression to MOD(INT((RECNO(LIST)-1_/13),3).