Doc# 744
=====================================================================
Building IN Lists From Checkbox Menus
=====================================================================
Products: R:BASE Version: 3.x, R:BASE 4.0x, R:BASE 4.5
=====================================================================
Area: Application Solutions Catalog: Programming in R:BASE
=====================================================================
Check box menus are a handy feature in R:BASE. You can check off a
number of items and then view the selected records with a form or
Browse/edit or print a report. Using the IN operator in a WHERE
clause lets you list a number of items to be retrieved and uses index
processing for quick retrieval.
Making the IN list from the check box menu result, however, often
leads to tearing of hair and beating of breast. The basic technique
is the same regardless of datatype and number of choices selected.
This article shows four different techniques starting with the most
basic. Following through them enables you to understand the process
and to add this capability to your applications.
The sample database Concomp is used with all the examples. Note that
the techniques shown here are not the only way to solve this
programming problem, but they demonstrate the basic technique.
The basic technique
-------------------
The checkbox menu puts all the choices into one variable separated by
commas (or the current DELIMIT setting). A sample choose variable
from a checkbox menu looks like this:
133 Coffin,160 Simpson,167 Watson
The following steps are repeated until all the menu choices have been
processed.
1. Get a menu choice. The SSUB function is often used for this,
it automatically selects items based on the DELIMIT character.
2. Parse the item and retrieve the part needed for the IN list
variable.
3. Add quotes around the item if necessary and add it to the IN
list variable.
After all the choices have been processed, parentheses are placed
around the final IN list variable.
Technique 1
-----------
This technique is the quick and easy method when the data parsed from
the menu choice is not text and the text displayed on the menu
(if any) does not contain any embedded commas. The example code below
displays a two-column popup menu of employee id numbers and last
names. The employees are selected, then the code parses out the id
numbers and builds an IN list for the id's. This IN list is then
used in a WHERE clause.
-- display the menu
CLS
CHOOSE vchoice FROM #VALUES FOR (CTXT(empid) & emplname) +
FROM employee AT CENTER,CENTER CHKBOX
VCHOICE looks like this:
133 Coffin, 160 Simpson, 167 Watson
Notice there are three items separated by commas. Each item
represents one menu selection. Three choices were selected
from the menu.
-- simple error checking
IF vchoice = '[esc]' THEN
PAUSE FOR 10 USING 'You didn''t + select anything from +
the menu' AT CENTER,CENTER DEFAULT
RETURN
ENDIF
-- initialize variables
SET VAR vcount INT = 1
SET VAR vtemp TEXT = (SSUB(.vchoice,.vcount))
VTEMP now has the first comma delimited item. The SSUB function
gets the items from VCHOICE separated by commas:
vtemp = 133 Coffin
-- parse the values
SET VAR vlist TEXT = (SSUB(.vtemp,-1))
VLIST gets the first ID number, 133. The -1 in the SSUB function tells
R:BASE to get data based on a space not a comma. The variable VLIST
continues to have id numbers added to it for each menu selection. When
done, it contains the list of selected numbers.
vlist = 133
-- get each menu selection, increment the count to get the next menu
selection and put it into vtemp
WHILE #PI > 0.00 THEN
SET VAR vcount = (.vcount + 1)
SET VAR vtemp = (SSUB(.vchoice,.vcount))
The WHILE loop gets new values for the variables vtemp and vlist. It
cycles until all the menu selections have been processed.
-- exit when there are no more items to get from vchoice
IF vtemp IS NULL THEN
BREAK
ENDIF
-- build the list adding a comma between each item.
SET VAR vlist = (.vlist + ',' + + (SSUB(.vtemp,-1)) )
ENDWH
-- add the parentheses around the completed list
SET VAR vlist = ( '(' + .vlist + ')' )
vlist = (133,160,167)
It looks just like you would type the list in at the R> prompt.
R:BASE requires that the variable value includes the parentheses
to enclose the list.
-- edit the employee data for the selected employees
EDIT USING employee WHERE empid IN &vlist
Technique 2
-----------
This technique is the quick and easy method when the data parsed from
the menu is text and there are no embedded commas or spaces in the
data. The same menu is used as in the previous example, but we
retrieve the last names instead of the id's. The second item from
vtemp is used instead of the first and the items in the IN list have
quotes around them.
To retrieve the second item, we simply use the SSUB function with a
-2 parameter. That tells R:BASE to return the second item from a
space delimited string.
In Technique 1, we placed a comma between the items by concatenating
a literal comma to the values. Because the comma is a text value, we
needed to surround the comma with quotes. You can do the same thing
to concatenate a literal quote to a value. But, to tell R:BASE that
you want a literal quote, and you are not ending or beginning a
string, you double the quote character. So to concatenate a single
quote to the beginning of a text string you place four quotes right
together, '''', no spaces. The first quote tells R:BASE a text string
is starting, the next two tells R:BASE you want a literal quote in
the result, and the final quote ends the text string. In the
following code, we concatenate the necessary quotes by using four
quotes together in the SET VAR command for vlist.
-- display the menu
CLS
CHOOSE vchoice FROM #VALUES FOR (CTXT(empid) & emplname) +
FROM employee AT CENTER,CENTER CHKBOX
-- simple error checking
IF vchoice = '[esc]' THEN
PAUSE FOR 10 USING 'You didn''t select anything from +
the menu' AT CENTER,CENTER DEFAULT
RETURN
ENDIF
-- initialize variables
SET VAR vcount INT = 1
SET VAR vtemp = (SSUB(.vchoice,.vcount))
SET VAR vlist = ( '''' + (SSUB(.vtemp,-2)) + '''' )
WHILE #PI > 0.00 THEN
SET VAR vcount = (.vcount + 1)
SET VAR vtemp = (SSUB(.vchoice,.vcount))
IF vtemp IS NULL THEN
BREAK
ENDIF
-- quotes are added around the text value retrieved using SSUB
SET VAR vlist = (.vlist + ',' + '''' + (SSUB(.vtemp,-2)) + '''')
ENDWH
SET VAR vlist = ('(' + .vlist + ')')
The variable vlist looks like this:
('Coffin','Simpson','Watson')
-- edit the employee data for the selected employees
EDIT USING employee WHERE emplname IN &vlist
Technique 3
-----------
Many times the items on a menu are spaced for clarity, particularly
if multi-word columns such as company names or street addresses are
displayed. When you have multi-word items, you can't use the space
delimited SSUB option. You may not get the whole item. The space
delimit option of SSUB looks at each space as a potential delimiter,
thus four spaces are not considered a single delimiter, but as two
spaces delimited by spaces. A space thus gets returned as part of
the resulting IN list. One way to do menus with multi-word text that
makes for a nice display and for easy retrieval of the choices is to
place the columns in specific locations. Again, this technique
assumes there are no commas in the data displayed on the menus.
This technique uses SGET instead of SSUB to retrieve the desired
value from the menu choice. The difference from the previous
techniques is in how the vlist value is retrieved. As with
Technique 2, we are retrieving a text value, the company, so the
result is enclosed in quotes. The menu here is a two table display
of contact names and company names. The company name is repeated
for each contact.
-- display the menu
CLS
CHOOSE vchoice FROM #VALUES FOR (LJS((contfname & contlname) +
,16) + company) FROM contact, customer +
WHERE contact.custid = customer.custid AT CENTER,CENTER CHKBOX
-- simple error checking
IF vchoice = '[esc]' THEN
PAUSE FOR 10 USING 'You didn''t select anything from +
the menu' AT CENTER,CENTER DEFAULT
RETURN
ENDIF
-- initialize variables
SET VAR vcount INT = 1,vlist TEXT, vtemp TEXT
SET VAR vtemp = (SSUB(.vchoice,.vcount))
-- SGET is used instead of SSUB to parse the menu choice, the company
-- name begins at position 17
SET VAR vlist = (''''+(SGET(.vtemp,30,17 ) )+'''')
WHILE #PI > 0.00 THEN
SET VAR vcount = (.vcount + 1)
SET VAR vtemp = (SSUB(.vchoice,.vcount))
IF vtemp is null THEN
BREAK
ENDIF
SET VAR vlist = +
(.vlist + ',' + '''' + (SGET(.vtemp,30,17 )) + '''')
ENDWH
SET VAR vlist = ('(' + .vlist + ')')
The variable VLIST looks like this when done:
vlist = ('PC Consultation and Design','Computer
Warehouse','Midtown Computer Co.')
-- edit the customer data for the selected companies
EDIT USING custform WHERE company IN &vlist
It's a little bit more work to extract the last name from the choice,
but not much. You need a second temporary variable. It holds the
first name/last name portion of the menu choice. You get it by using
the SGET function and returning the first 16 characters. Then the
vlist value is extracted from this second temporary variable using
the space delimit option of SSUB and getting the second item. For
example,
vchoice = Andy Chin PC Consultation and Design,Bill Jones
Computer Warehouse,Sharon Brady Midtown
Computer Co.
SET VAR vtemp = (SSUB(.vchoice,.vcount))
vtemp = Andy Chin PC Consultation and Design
SET VAR vtemp2 = (SGET(.vtemp,16,1))
vtemp2 = Andy Chin
SET VAR vlist = (''''+(SSUB(.vtemp2,-2))+ '''')
vlist = 'Chin'
Technique 4
-----------
So far creating the IN list has been relatively easy even when the
data contains embedded spaces. The problem becomes more difficult
when the data displayed on the menu contains embedded commas and
spaces. You can't use the SSUB function to split apart the menu
choices. SSUB breaks strings apart by spaces or commas. When your
data contains spaces and commas the strings you get with SSUB are
not the complete menu choices. One way to resolve this is to set
the DELIMIT character before the CHOOSE so that the menu selections
are delimited by something other than commas.
You can leave the DELIMIT character set and use the SSUB function to
retrieve the data -- it works based on the current DELIMIT setting --
or you can set the DELIMIT back to a comma and use the SLOC and SGET
functions to retrieve the data. If you leave the DELIMIT set you need
to remember that commas must be replaced by the current DELIMIT
character in all commands. It can make the code harder to read, but
requires fewer commands. Both examples are shown below.
1. This example changes the DELIMIT character and leaves it set
until the menu choices are parsed. Notice that once the
DELIMIT character is changed, that character is used in all
commands where you would normally use a comma to delimit
items. The DELIMIT character is set to ^ (Shift-6), a
character that is unlikely to occur in data. Another common
choice for the DELIMIT character is @ (Shift-2). The code here
is basically identical to that in Technique 3 except for the
different delimiter.
SET DELIMIT=NULL
SET DELIMIT=^
CHOOSE vchoice FROM #VALUES FOR (LJS((contfname & contlname +
)^16) + company) FROM contact^ customer +
WHERE contact.custid = customer.custid AT CENTER^CENTER CHKBOX
The changed DELIMIT character does not appear on the menu, but is in
the choose variable. vchoice looks like this:
VCHOICE = Sarah James PC Distribution, Inc.^Walter Finnegan
Computer Distributors, Inc.^Jane Ferguson Industrial
Computers, Inc.
IF vchoice = '[Esc]' THEN
PAUSE FOR 10 USING 'You didn''t select anything +
FROM the menu' AT CENTER^CENTER DEFAULT
SET DELIMIT=NULL
SET DELIMIT=,
RETURN
ENDIF
SET VAR vlist TEXT^ vtemp TEXT^ vcount INT = 1
SET VAR vtemp = ( SSUB(.vchoice^.vcount ) )
SET VAR vlist = ('''' + (SGET(.vtemp^30^17)) + '''')
vtemp looks like this, the same as in the previous technique:
VTEMP = Sarah James PC Distribution, Inc.
-- The SGET function is used to retrieve the company name.
WHILE #PI > 0.00 THEN
SET VAR vcount = (.vcount + 1)
SET VAR vtemp = ( SSUB(.vchoice^.vcount))
IF vtemp IS NULL THEN
BREAK
ENDIF
SET VAR vlist = +
(.vlist + ',' + ''''+(SGET(.vtemp^30^17))+'''' )
ENDWH
SET VAR vlist = ('(' + .vlist + ')')
SET DELIMIT=NULL
SET DELIMIT=,
VLIST = ('PC Distribution, Inc.','Computer Distributors,
Inc.','Industrial Computers, Inc.')
-- edit the customer data for the selected companies
EDIT USING custform WHERE company IN &vlist
2. The other approach sets the DELIMIT character for the duration of
the CHOOSE command only. The code then sets it back to a comma
and uses the character it was to split the menu choices apart
using the SLOC and SGET functions. The functions are more complex
because you need to put the first part of vchoice into one
variable, the second part into another variable. Then the second
variable replaces the first for the next iteration.
SET DELIMIT=NULL
SET DELIMIT=@
CHOOSE vchoice FROM #VALUES FOR (LJS((contfname & contlname +
)@16) + company) FROM contact@ customer +
WHERE contact.custid = customer.custid AT CENTER@CENTER CHKBOX
SET DELIMIT=NULL
SET DELIMIT=,
IF vchoice = '[Esc]' THEN
PAUSE FOR 10 USING 'You didn''t select anything FROM +
the menu' AT CENTER, CENTER DEFAULT
RETURN
ENDIF
SET VAR vlist TEXT, vtemp1 TEXT, vtemp2 TEXT
-- put the first menu choice in vtemp1, put the second into vtemp2
-- using the location of the first @. The number 200 in the vtemp2
-- expression is arbitrary, make it long enough to get all the
-- remaining data from vchoice
SET VAR vtemp1 = (SGET(.vchoice,(SLOC(.vchoice,'@')-1),1 ))
SET VAR vtemp2 = (SGET(.vchoice,200,(SLOC(.vchoice,'@')+1)))
VCHOICE = Sarah James PC Distribution, Inc.@Walter
Finnegan Computer Distributors, Inc.@Jane Ferguson
Industrial Computers, Inc.
VTEMP1 = Sarah James PC Distribution, Inc.
VTEMP2 = Walter Finnegan Computer Distributors,
Inc.@Jane Ferguson Industrial Computers, Inc.
SET VAR vlist = ('''' + (SGET(.vtemp1,30,17)) + '''')
WHILE #PI > 0.00 THEN
-- we need to make sure there is a valid value for
-- SGET, if there are no more values, the SLOC returns 0
-- but subtracting 1 from 0 makes a negative argument for
-- the SGET - that is illegal. The embedded IFEQ function
-- sets that argument to 0 if there are no more menu choices
SET VAR vtemp1 = (SGET(.vtemp2,(IFEQ((SLOC(.vtemp2,'@')), +
0,0,(SLOC(.vtemp2,'@')-1))),1))
IF vtemp1 IS NULL THEN
-- need to get the last choice into vlist
SET VAR vlist = +
(.vlist + ',' + '''' + (SGET(.vtemp2,30,17)) + '''')
BREAK
ENDIF
SET VAR vtemp2 = (SGET (.vtemp2,200,(SLOC(.vtemp2,'@')+1) ))
SET VAR vlist = +
(.vlist + ',' + ''''+(SGET(.vtemp1,30,17))+'''' )
ENDWH
SET VAR vlist = ('(' + .vlist + ')')
-- edit the customer data for the selected companies
EDIT USING custform WHERE company IN &vlist
The same basic techniques are used throughout these examples. The
differences lie in how the data is parsed from the menu selection
variable, vchoice. The way you set up your menu and the data it
displays determines the parsing method to use. Certainly there are
other options as well. Some developers use a specific character to
delimit the menu items, such as an Alt 255 character or a vertical
bar, |, and then search for that character rather than changing the
DELIMIT character. Whatever parsing method you use, a WHILE loop
repeats to parse all the choices and place them in a single variable
list. Then after the parsing is complete, parentheses are added for
your finished IN list variable.
Please Note: The preceding article contains commands that are
specific to R:BASE 4.5. The R:BASE 4.5 commands are included to
demonstrate new features and capabilities. These commands may be
excluded if you choose to use these programming techniques with
prior versions of R:BASE.