""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
CREATE AN AD HOC BROWSER USING THE POWER OF CHOOSE
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
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
TRANSMASTER:
transmas
CHKBOX
#COLUMNS IN transmaster
ENDC
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 MESSAGES OFF ; SET ERR MESSAGES OFF
*( 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.)
SET BELL OFF
CLS
*( 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.)
CHOOSE vdatab FROM #DATABASES
IF vdatab IS NULL THEN
QUIT
ENDIF
CONNECT .vdatab
*( The LABEL identifies where control will return after a pick, so you can
)
*( choose again without reconnecting to the database.)
LABEL pickagin
CLS
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 MESSAGES ON
SET ERROR MESSAGES ON
SET BELL ON ; CLS
RETURN
ENDIF
*( 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
ENDIF
*( 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
ENDIF
*( 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.