*(**********************************************************
** FILENAME:    ANYDB2.RMD                                **
** CREATED BY:  David Blocker                             **
** PURPOSE:     Demonstrate how automatic # options with  **
**              CHOOSE allow you to create an all purpose **
**              query option off of your menu system.     **
**                                                        **
**             Uses CHOOSE .. CHKBOX, CHKSORT             **
** CALLED BY:   Any application or R> prompt              **
** CALLS:       Returns                                   **
** LAST UPDATE: 01/15/98                                  **
**              07/2000: for 6.5 Windows by DB            **
***********************************************************)
 
*(Come back  here to start again)
LABEL TOP
SET MESS OFF
 
-- Part 1:  Pick a database
SET COLOR FORE BLACK
SET COLOR BACK GRAY
CLS

CHOOSE VDB FROM #DATABASES AT 10 5
 
-- User wants out
IF VDB = '[ESC]' OR VDB IS NULL THEN
  GOTO BOTT
ENDIF
 
-- Connnect database and display choice at top of screen
CONNECT &VDB
 
CLS
WRITE 'DATABASE:' AT 1 3
WRITE .VDB AT 1 25 
 
-- Popup alone is not enough info; need directions on top:
-- choose a table/view
WRITE 'Select a table or view from database' AT 3 5 
CHOOSE VTAB FROM #TBLVIEWS AT 10 5 LINES 12
 
-- User pressed [ESC]; start over
IF VTAB = '[ESC]' OR VTAB IS NULL THEN; GOTO TOP; ENDIF
 
-- Display table selected below database
CLS FROM 3
WRITE 'TABLE/VIEW:' AT 2 3
WRITE .VTAB AT 2 25 
WRITE 'Select categories to view; Mark/unmark with [Space] / mouse, OK when done +
' AT 4 5 
 
-- Note you no longer need to create the columns menu 
-- on the fly
CHOOSE VCOLS FROM #COLUMNS IN .VTAB  AT 10 5 +
CHKBOX LINES 12
 
-- User pressed [ESC]; start over; NOTE BUG!!! If you press 
-- [F2] without first marking a table, R:BASE shows the 
-- variable as NULL, but VCOLS IS NULL evaluates as NOT 
-- true!  So does VCOLS = '-0-'; only the following works!
-- This true for CHKBOX and CHKSORT menus.
IF VCOLS = '[ESC]' OR VCOLS LIKE '-%' THEN
  GOTO TOP
ENDIF
 
-- Display columns selected and prompt for sort order
CLS FROM 4
WRITE 'CATEGORIES SELECTED:' AT 3 3
WRITE .VCOLS AT 3 25
 
-- Note this menu no longer needs to be created on the 
-- fly as well
WRITE 'Pick sort order; mark field(s) to sort by with +
mouse/[Space], OK when done' at 5 5 
-- BUG NOTE in 6.5: if you do NOT use a CAPTION in a 
-- "CHOOSE .. FROM #COLUMNS" command, R:Base places garbage
-- characters in the caption
CHOOSE VORDER FROM #COLUMNS IN .VTAB AT 12 5 +
CHKSORT CAPTION ' ' LINES 12
 
-- Show sort order and prompt for WHERE clause
CLS FROM 5
WRITE 'ROWS ORDERED BY:' AT 4 3
 
-- User might not want sorting; display appropriate 
-- message; SEE BUG NOTE ABOVE
IF VORDER IS NULL OR VORDER LIKE '-%' +
OR VORDER = '[ESC]' THEN
  WRITE 'Original order' AT 4 25
ELSE
  WRITE .VORDER AT 4 23
ENDIF

CHOOSE VWHER FROM #WHERE IN &VTAB 
 
-- Display final command; any key to start.
CLS FROM 6
WRITE 'CONDITIONS: ' AT 5 3
IF VWHER = '[ESC]' OR VWHER IS NULL THEN
  WRITE 'All rows' AT 5 25
ELSE
  WRITE .VWHER AT 5 25
ENDIF
PAUSE 1 USING 'Press any key to view this data; [Esc] +
when done.' AT 10 10
 
-- 4 possible commands to build: no sort or where; no sort; 
-- no where; all parts present.  NOTE USE OF & before each 
-- part.  This is REQUIRED in R:BASE 3.x when you replace 
-- part of a command with a variable
IF VORDER IS NULL OR VORDER LIKE '-%' OR +
VORDER = '[ESC]' THEN
  IF VWHER = '[ESC]' OR VWHER IS NULL THEN
    BROWSE &VCOLS FROM &VTAB
  ELSE
    BROWSE &VCOLS FROM &VTAB  &VWHER NOCHG
  ENDIF
ELSE
 IF VWHER = '[ESC]' OR VWHER IS NULL THEN
   BROWSE &VCOLS FROM &VTAB ORDER BY &VORDER NOCHG
 ELSE
   BROWSE &VCOLS FROM &VTAB ORDER BY &VORDER  &VWHER NOCHG
 ENDIF
ENDIF

-- Must remaximize screen: R:Base makes it normal size after
-- leaving the BROWSE screen!
MAXIMIZE
 
-- Do you want to do it again?  Yes is a default.
SET VAR VYN = 'Yes'
DIALOG 'Make another query? '  vYN vKey YES
 
IF VYN = 'Yes' and vKey <> '[Esc]' THEN
  GOTO TOP
ENDIF
LABEL BOTT
SET MESS ON
CLE VAR VDB,VTAB,VCOLS,VORDER,VWHER,VYN,vKey,vendkey,+
vMovie1,vversion,vwalkon 
CONNECT NEWMOVIE
RETURN

