Document #
=====================================================================
Using RECALC in R:BASE 4.5
=====================================================================
PRODUCT: R:BASE VERSION: 4.5
=====================================================================
AREA: Entry/Exit Procedures CATEGORY: Programming in R:BASE
=====================================================================
In R:BASE 3.x-4.0, you used RECALC in an Entry/exit procedure (EEP)
to force recalculation of form expressions that were dependent on the
EEP field. R:BASE 4.5 has added two new RECALC options for use in
EEPs.
RECALC VARIABLES - forces recalculation of all expressions defined
for the current table and all located variables for that table.
Expressions and variables for other tables are not affected.
RECALC VARIABLES is usually used in a field level EEP.
RECALC TABLES - forces recalculation of expressions and variables on
all lower tables in the form. A RECALC TABLES in an EEP on table 2
of the form, for example, recalculates tables 3, 4 and 5. RECALC
TABLES is usually used in a table level EEP.
Here are examples using the new RECALC options.
RECALC TABLES
=============
No more "No editable data"
-------------------------
The RECALC TABLES gets rid of "no editable data" messages when
editing data in multi-table forms. Lower tables may not yet have any
matching data records with table one in the form. When the user moves
to the lower table, they get a "No editable data in this table"
message on line 24. A new row can be added using the Add row option
off the Edit menu, but this is not intuitive to all users. Use an EEP
placed at the table level, On exit from a row, to automatically add a
row to the lower table if there is no matching data row already.
Users will never again see the "No editable data" message.
Using the Concomp sample database, modify the form custform. Add an
expression to table one of the form, Customer, to place the custid
value into a variable. The value needs to be placed in a variable so
the EEP can reference it. Define vcustid = custid. The variable is
not located.
The EEP counts the number of rows in the Contact table, table two on
the form, that have a matching custid value with the current row in
the Customer table. If no matching rows are found, the EEP inserts
a row into the Contact table and uses RECALC TABLES to ensure that the
form displays that new row.
Placing the EEP as a field level EEP executes only if that particular
field is entered. A user can move between tables in a form from any
field. Placing the EEP at the table level ensures that it will always
execute.
The EEP is placed On exit from a row. Because it will execute anytime
the user leaves the row, you must check for keystrokes. If the user
presses Esc or moves to the menu using the Alt key you don't want to
execute the commands in the EEP.
*(nodata.eep
adds a row of data to lower tables in a form)
SET VAR vkey = (LASTKEY(0))
IF vkey = '[Esc]' OR +
vkey LIKE '[Alt]%' THEN
RETURN
ENDIF
SET VAR vcount INT
SEL COUNT(*) INTO vcount +
FROM contact WHERE custid=.vcustid
IF vcount = 0 THEN
INSERT INTO contact (custid) +
VALUES (.vcustid)
RECALC TABLES
ENDIF
RETURN
Summing rows in a region
-------------------------
RECALC TABLES can also do summing in forms. Create a dummy table -
CREATE TABLE DUMMY (col1 INTEGER) - and add one row of data to the
dummy table. Add the dummy table to the form as a table after the
region you want to sum. Locate a variable, vtotal, for the dummy
table and define an expression for vtotal. The expression calculates
the sum of the region rows. For example, to sum the rows for the
Transdetail table on the form tranform, use this expression:
vtotal =. (SUM(extprice)) +
IN transdetail +
WHERE transid = .vtransid
Vtransid is a variable defined for table Transdetail on the form, the
region table. It is simply vtransid = transid, and places the current
column value into the variable for use by the dummy table expression.
Then, on the region table, Transdetail, place an EEP to execute On
entry into a row. The EEP has just one line in it, RECALC TABLES. As
you enter each row in the region, the expression on the lower table
(dummy table) is recalculated - the sum of the rows in the region
displays.
RECALC VARIABLES
================
Calculate and display many variables
------------------------------------
Use RECALC VARIABLES to display multiple lookup values on a form. You
can replace many form expressions or even a whole table. For example,
in the form tranform in the Concomp sample database, instead of
placing the Customer table on the form, lookup the desired data values
in an EEP and display them using RECALC VARIABLES. This makes tranform
a two table form instead of a three table form and speeds it up.
Define a form variable, vcustid = custid, for the Transmaster table.
Modify the field settings for custid and specify a double column
popup using the custid and company columns from the Customer table.
This makes it easy to select the right customer. Add an Exit EEP on
custid. The EEP will lookup the rest of the customer information and
display it.
The EEP uses a single SELECT command to lookup all the values at
once. Then the RECALC VARIABLES tells the form to redisplay all
located variables and all defined expressions for the Transmaster
table.
*(custlook.eep
Field level EEP to lookup up customer data)
SELECT company, custaddress, +
(custcity + ',' & custstate & custzip), +
custphone INTO +
vcompany ind1, vaddr ind2, vcsz ind3, +
vphone ind4 +
FROM customer WHERE custid=.vcustid
RECALC VARIABLES
Be sure to locate the variables on the form using the same names as
used in the EEP. If the names are different, the new values won't
display on the form. Because the values are now variables instead of
columns, they retain their value from row to row unless reset. Use a
table level EEP on Transmaster, After saving row, to reset the
variables to NULL. It is important to just reset the variables to NULL
and not clear them using the CLEAR VAR command. Clearing located
variables can cause the form to not work correctly.
The EEP to reset the variables to NULL needs just a single SET VAR
command:
*(resetvar.eep
reset located variables to NULL)
SET VAR vcompany=NULL, vcompany=NULL, +
vaddr=NULL, vcsz=NULL, vphone=NULL
This procedure works well for non-editable fields. You are no longer
limited to looking up or calculating variables one at a time. Now you
can have a single EEP do all the calculations and lookups needed for
the table. This provides for easier maintenance of the form and
better performance.