826.TXT
=====================================================================
Custom Pop-up Menus
=====================================================================
PRODUCT: R:BASE VERSION: 4.5++ or Higher
=====================================================================
CATALOG: Programming in R:BASE AREA : Data Manipulation
=====================================================================
When developing an application, sometimes you have a need for a
small pop-up menu, such as to select a particular printer. You can
make a separate ASCII menu file for the pop-up menu, but then you end
up with many small files to keep track of. Or you may decide to store
the data in tables or views. Either way you add clutter to your
database and applications. Using the CHOOSE command with the
#VALUES option you can customize menus to include literal text as well
as data from tables. In addition, use the #VALUES option to create
small menus with just custom text; you don't need to select data from
a table.
The CHOOSE command with the #VALUES option can be looked at as having
three parts:
The command name, return variable, and #VALUES
keyword_CHOOSE vchoice FROM #VALUES. The part of the
command names the variable to hold the return value and
tells R:BASE you want to retrieve data from a table not
from a menu file.
The command options, which start with AT scrnrow, scrncol
in the command syntax. These are all optional, but any of
them can be used with this technique. R:BASE 5.5 includes
two new options_CAPTION and LINES.
The information in the FOR section tells R:BASE what data
to retrieve. Think of this part of the CHOOSE command as
being the same a SELECT command. Replace the word FOR
with SELECT and then you can begin to see the options
available with CHOOSE. This part of the CHOOSE command
can become quite large.
A menu can retrieve data from multiple tables, use the GROUP BY
option, and use the UNION operator_all features of the SELECT command.
And don't forget the WHERE clause can use sub-SELECTS. Using these
features you can greatly enhance and expand the capabilities of the
CHOOSE command.
The difference between the standard SELECT command and what you have
available with the CHOOSE command is that in the CHOOSE command you
are selecting or displaying a single item only. You can't specify a
list of items, if you want the menu to display more than one column
you build an expression to create a single item.
Examples more clearly demonstrate the power of the CHOOSE command.
Example of a Multi-Table Menu
With the CHOOSE command you are not limited to displaying data from
one table. You can join tables just like with the SELECT command. For
example, display the company name along with the transaction number
and date:
CHOOSE vchoice FROM #VALUES +
FOR (CTXT(transid) & CTXT(transdate) & ' ' & company) +
FROM transmaster, customer +
WHERE customer.custid = transmaster.custid +
ORDER BY custid, transdate +
AT 7, center +
TITLE 'Select a transaction' +
CAPTION 'Edit Transactions' +
LINES 10
You can add other conditions to the WHERE clause to display a menu of
transactions for a particular customer only; you can even use the
return column option. Enhance the command to display the last
transaction for each customer by using the technique described in the
article "Find the Maximum Record for a Customer":
CHOOSE vchoice FROM #VALUES +
FOR (CTXT(transid) & CTXT(transdate) & ' ' & company),
transid +
FROM transmaster, customer +
WHERE customer.custid = transmaster.custid AND +
transdate = (SELECT MAX(transdate) +
FROM transmaster t2 WHERE t2.custid = transmaster.custid) +
ORDER BY transdate +
AT 7, center +
TITLE 'Select a transaction' +
CAPTION 'Edit Transactions' +
LINES 10
Example of a Menu using GROUP BY
Use the GROUP BY clause of SELECT with aggregate functions to display
menus of summary information. For example, you can display a menu of
customer numbers, current balance, and amount owing over 30 days.
R:BASE 5.5 gives you both a title and a caption so you can easily
describe the menu data for the user. The LINES option sets the number
of items to display before scrolling.
CHOOSE vchoice FROM #VALUES FOR +
(RJS(CTXT( custid), 12) + ' ' + +
CTXT( SUM(invoicetotal)) + ' ' + +
CTXT(SUM(invoicetotal) - +
SUM(IFGT((.#DATE - transdate), +
30, invoicetotal, 0)))), custid +
FROM transmaster GROUP BY custid +
AT 7, CENTER +
TITLE 'custid total due over 30 ' +
CAPTION 'Accounts Receivable' +
LINES 10
The expression uses the system date to determine records over 30 days,
you might replace #DATE with a variable filled in by the user
indicating the date to calculate from. In addition, use the return
value option to return the customer ID number, and add the customer
table to the query if you want to see the company name on the menu.
When aligning a title or caption over menu items you may need to
include a hard space at the end of the caption or title. Use the
[Alt]-0160 character for a hard space. This is equivalent to the
[Alt]-255 character in DOS.
Example of a Menu using UNION
The UNION operator of the SELECT command is used to join two SELECT
commands together. With the CHOOSE command, you can use this feature
to add constant text to data from a table.
For example, you might display a list of customers and include on the
menu the option to add a new customer:
CHOOSE vchoice FROM #VALUES +
FOR company +
FROM customer +
UNION SELECT '(New Customer)' FROM customer +
WHERE LIMIT = 1 +
AT 7, CENTER +
CAPTION 'Customer List' +
LINES 15
By default, using the UNION operator sorts the data and removes
duplicate values before displaying the menu (it is like adding
DISTINCT to the SELECT command). Putting the constant text in
parentheses takes advantage of the sort and puts the constant at the
top of the menu as the first choice; a parentheses comes before any
letter in the sort order.
The WHERE clause WHERE LIMIT = 1 tells the SELECT command to return
the constant text just one time. The UNION would suppress the
duplicate display of the constant text, but it is quicker to just
return one value.
Not only can you place constant text on your menu, you can still use
the return column option of the CHOOSE command. In the UNION...SELECT
you place a constant value after the text you want displayed on the
menu. The constant value must be the same data type as the return
column. The command below returns the value from the column custid
when a customer name is selected; the value 999 is returned when the
menu item "(New Customer)" is selected.
CHOOSE vchoice FROM #VALUES +
FOR company, custid +
FROM customer +
UNION SELECT '(New Customer)', 999 FROM customer +
WHERE LIMIT = 1 +
AT 7, CENTER +
CAPTION 'Customer List' +
LINES 15
Example of a Menu with Custom Text Only
Using the technique of the SELECT...UNION you can display a menu that
just contains custom text. You no longer need to keep track of many
small menu files, just include the appropriate CHOOSE command in your
application. We could reference any table in the database using the
WHERE clause WHERE LIMIT = 1, but we can also create and use a
single-column, one-row dummy table. Below is a CHOOSE command that
presents a menu of print output options: a choice of 2 printers,
screen, or file.
CHOOSE vchoice FROM #VALUES FOR 'Printer 1' FROM dummy +
UNION ALL SELECT 'Printer 2' FROM dummy +
UNION ALL SELECT 'Screen' FROM dummy +
UNION ALL SELECT 'File' FROM dummy +
AT CENTER, CENTER +
TITLE 'Select Report Destination'
The ALL option of UNION disables the sorting; the values are displayed
in the order of the SELECT commands.
You can use the return column option here also. Just include a second
item, an integer number, after each constant.
CHOOSE vchoice FROM #VALUES FOR 'Printer 1', 100 FROM dummy +
UNION ALL SELECT 'Printer 2', 200 FROM dummy +
UNION ALL SELECT 'Screen', 300 FROM dummy +
UNION ALL SELECT 'File', 400 FROM dummy +
AT CENTER, CENTER +
TITLE 'Select Report Destination'