PRODUCT   :  R:BASE                  VERSION      :  3.1
   From Marva Dasef, 80770 Turkey Run Road, Creswell, OR 97426. Marva is
   an award-winning writer and database programmer who recently wrote
   "Using R:BASE," a new book on R:BASE 3.0 published by Que. You can
   reach Marva at 503-942-5228.
   If you write applications for other people's use, you know you won't
   usually be able to cover all the possibilities for data inquiry that
   users might want. Often, users don't know what they want until they
   want it.
   By using the R:BASE 3.1 CHOOSE command, you can create a generic
   selection routine like COMMON.CMD (listed below) that people can use
   to pick and choose data to their heart's content, without having to
   learn anything about the database structure.
   COMMON.CMD works as is with any database, without any modification to
   the code. All table and column names are selected by using the CHOOSE
   command, rather than by naming them specifically in the command file.
   Step by Step to Ad Hoc Queries
   STEP 1--Create a table in each database named COMMON with two columns:
   COMCOLUMN and COMTABLE. Enter the names of the common columns and
   their respective tables. For example, here's COMMON for CONCOMP:
             ComColumn                  ComTable
             Bonuspct                   bonusrate
             Bonuspct                   salesbonus
             Compid                     component
             Compid                     compused
             Custid                     contact
             Custid                     customer
             Custid                     transmaster
             Empid                      employee
             Empid                      transmaster
             Model                      compused
             Model                      prodlocation
             Model                      product
             Model                      transdetail
             Netamount                  salesbonus
             Netamount                  transmaster
             Transdate                  salesbonus
             Transdate                  transmaster
             Transid                    transdetail
             Transid                    transmaster
   Find the common columns by using the LIST COLUMN command and noting
   when a column name occurs in more than one table. Only include common
   columns that have the same name. You can't, for example, tell R:BASE
   that two differently named columns are common. In fact, that wouldn't
   be a good database design.
   STEP 2--Next, create a CHKBOX menu file for each table. These menus
   will provide a current list of the columns in each table when the
   command file executes. For example, here's the menu file for
     #COLUMNS IN transmaster
   Use the first eight characters of each table name as the menu name.
   These will be unique in the database, because R:BASE, although
   allowing up to 18 characters for a table name, only uses the first
   eight characters to identify a table.
   STEP 3--Create the COMMON.CMD command file.
     *( COMMON.CMD--Ad Hoc Selection for Browsing.)
     *( Set BELL OFF so when checking for common column values,)
     *( it won't beep if it doesn't find a match. It doesn't)
     *( require a common column unless combining data from two tables.)
     *( Display a menu of databases in the current directory. To include this)
     *( command file as a block in an application by using the Macro option)
     *( omit this section so the program uses the currently open database.)
     IF vdatab IS NULL THEN
     CONNECT .vdatab
     *( The LABEL identifies where control will return after a pick, so you can
     *( choose again without reconnecting to the database.)
     LABEL pickagin
     WRITE 'Choose the primary table. [Esc] to exit.' AT 23 10
     *( Display a menu of the tables in the database.)
     CHOOSE vtable1 FROM #TABLES
     *( Provide an "out," so if you don't want to continue, you can)
     *( press [Esc] to leave the command file.)
     IF vtable1 = '[Esc]' THEN
       CLEAR VAR vchkcom, vcols1, vcols2, vcommon, vdatab, vsecond, +
         vshort1, vshort2, vsort, vtable1, vtable2
       SET BELL ON ; CLS
     *( Use SET VAR to ensure that you have a menu name for the table.)
     *( You provided a menu file for each table using the first eight)
     *( characters of the table name as the menu name. SGET moves)
     *( the first eight characters of the table name stored in VTABLE1 into)
     *( the variable VSHORT1.)
     SET VAR vshort1 = (SGET(.vtable1,8,1))
     *( Display an explanation and a list of columns from the table selected)
     *( earlier, so you can choose the columns from the first table. Notice the
     *( CASCADE RIGHT option to  "tile" menus on the screen in the same)
     *( way that R:BASE 3.1 does with its own menus.)
     WRITE 'Choose the columns you want to display. F2 to continue.' +
       AT 23, 10
     CHOOSE vcols1 FROM &vshort1 CASCADE RIGHT
     *( Use SET VAR to check COMMON table to determine if a second table)
     *( selection is appropriate. It does this by moving the table name into)
     *( the variable VCHKCOM. If the table name exists in COMMON, then)
     *( you know that at least one other table in the database has a column)
     *( in common with the first table. If VCHKCOM is null, the table doesn't)
     *( exist in COMMON, so command file sets variable VSECOND to N)
     *( and doesn't ask for a second table--it skips the FILLIN.)
     SET VAR vchkcom = comtable IN common WHERE comtable=.vtable1
     IF vchkcom IS NULL THEN
        SET VAR vsecond = 'N'
        GOTO onetab
     *( Initialize VSECOND to Y to make a second table request the default.)
     *( Note that FILLIN now has an EDIT clause so you can edit the current)
     *( contents of the FILLIN variable.)
     SET VAR vsecond = 'Y'
     CLS FROM 23 TO 23
     FILLIN vsecond=1 USING 'Do you want to combine tables? ' AT 23 10 EDIT
     LABEL onetab
     *( These commands execute if you do want a second table.)
     IF vsecond EQ 'Y' THEN
       CLS FROM 23 TO 23
       WRITE 'Choose a combining column.' AT 23 10
       CHOOSE vcommon FROM #VALUES FOR DISTINCT comcolumn +
         FROM common WHERE comtable = .vtable1 CASCADE RIGHT
       CLS ; WRITE 'Choose the secondary table.' AT 23 10
       CHOOSE vtable2 FROM #VALUES FOR DISTINCT comtable +
         FROM common WHERE comcolumn = .vcommon
       SET VAR vshort2 = (SGET(.vtable2,8,1))
       CLS FROM 23 TO 23
       WRITE 'Choose the columns you want to display. F2 to execute. ' +
         AT 23 10
       CHOOSE vcols2 FROM &vshort2 CASCADE RIGHT
       BROWSE DISTINCT &vcols1, &vcols2 FROM &vtable1, &vtable2 +
          WHERE &vcommon = &vcommon ORDER BY &vcommon
     ELSE  *( These commands execute if you don't want a second table.)
       CLS FROM 23 TO 23
       WRITE 'Choose one or more columns to sort by (ascending).' AT 23 10
       CHOOSE vsort FROM &vshort1 CASCADE RIGHT
       BROWSE DISTINCT &vcols1 FROM &vtable1 ORDER BY &vsort
     *( When you exit from the tabular edit display, clear the variables and)
     *( pass control to the top of the command file at the PICKAGIN label.)
     CLEAR VAR vchkcom, vcols1, vcols2, vcommon, vdatab, vsecond, +
       vshort1, vshort2, vsort, vtable1, vtable2
     GOTO pickagin
   The section of COMMON.CMD that executes when you want a second table,
   accomplishes these tasks:
     o  Display a list of columns from the COMMON table for the first
        selected table. This is the column by which the data from the two
        tables will be combined. This ensures that the list of tables for
        the second table selection will only be those with a common
        column with the first table. The user won't be able to select
        tables that aren't related.
     o  Display a list of tables that have a column in common with the
        first table. By using the COMMON table list, the user will only
        be able to select appropriate second tables.
     o  Create the eight character name for the second table (to match
        the menu file name for that table).
     o  Display a list of columns from the second table using the menu
        file to let the user select the columns to display from the
        second table.
     o  Display the data selected from both tables in the tabular edit
        format. BROWSE disallows any data modification because it uses
        the DISTINCT option.
   The section of COMMON.CMD file that executes when you don't want a
   second table or when there is no second table available, accomplishes
   these tasks:
     o  Display a list of columns from the table to select for sorting.
        R:BASE has a CHKSORT menu type that lets you choose a column for
        sorting and choose whether you want the sort to be in ascending
        or descending order. For simplicity, this program assumes an
        ascending sort and uses the same CHKBOX menu as used for
        selecting the columns to display.
     o  Display the selected data sorted by the appropriate columns.