October 4, 2001
====================================================================
>From the Edge: Creating Dynamic CHOOSE Options
Section: New Aggregate Function
Chapter: Running R:BASE Your Way!
Platform: R:BASE 2000 (ver 6.5++) for Windows
Build: 1.843xRT03 and Higher
====================================================================
With the introduction of new LISTOF aggregate function in R:BASE
2000 (version 6.5++) for Windows, In-Line Patch-1, Build:1.843xRT03,
to be released shortly, now you can create Dynamic CHOOSE Options!
The aggregate functions (AVG, COUNT, MAXIMUM, MINIMUM, STDEV, SUM,
VARIANCE) used in SELECT commands now have a new member of their
family called "LISTOF" which creates a text string of the values
separated by the current comma delimiter character.
The LISTOF function can be used with the "SELECT ... INTO ..." to
populate a variable with a list of values which can then be used
in a CHOOSE command with the #LIST option. It can also be used
in Forms, Reports or Labels to look up values from multiple rows.
Syntax:
At the R> prompt or in a command file:
SELECT (LISTOF(ColumnName)) INTO VariableName INDICATOR IndVar +
FROM TableView WHERE ...
In a Form, Report or Label Expression:
VariableName = (LISTOF(ColumnName)) IN LookUpTableView WHERE +
KeyColumn = KeyColumn
Example 01:
SET VAR vValueList TEXT = NULL
SELECT (LISTOF(ColumnName)) INTO vValueList INDIC IvValueList +
FROM TableName WHERE ...
The variable vValueList will be a text string of the values
separated by the current comma delimiter character.
Example 02:
CONNECT Concomp IDENTIFIED BY NONE
SET CAPTION ' '
SET AUTODROP OFF
SET RBGSIZE CENTER CENTER 800 600
SET VAR vLines INTEGER = 0
SET VAR vValueList TEXT = NULL
SET VAR vLastName TEXT = NULL
SET VAR vTitle TEXT = 'List Created Using LISTOF Function'
SET VAR vCaption TEXT = 'Using #LIST Options in CHOOSE Command!'
CLS
PAUSE 3 USING 'Collecting Values ...' CAPTION .vCaption AT 16 30
SELECT (COUNT(*)), (LISTOF(EmpLName)) INTO +
vLines INDIC IvLines, vValueList INDIC IvValueList FROM Employee
IF vLines > 18 THEN
SET VAR vLines = 18
ENDIF
CLS
CHOOSE vLastname FROM #LIST .vValueList AT 6 30 +
TITLE .vTitle CAPTION .vCaption LINES .vLines FORMATTED
IF vLastName IS NULL OR vLastName = '[Esc]' THEN
GOTO Done
ELSE
CLEAR ALL VAR EXCEPT vLastName
ENDIF
-- Do what you have to do here ...
LABEL Done
CLS
CLEAR ALL VAR
QUIT TO MainMenu.RMD
RETURN
Stay tuned for more ....
Very Best Regards,
Razzak.
===================================-============================
Official R:BASE List Server: mailto:[EMAIL PROTECTED]
RBTI Events/Training: http://www.rbase2000.com/events
R:DCC Members: http://www.rbase2000.com/rdcc
================================================================
R:BASE, Oterro & R:Tango are registered trademarks of RBTI.
==================================-=============================
