DOCUMENT #774
===========================================================================
SUMMING ROWS IN A REGION
===========================================================================
PRODUCT: R:BASE VERSION: 4.5+ or Higher
===========================================================================
CATALOG: Forms, Reports & Labels AREA : Forms
===========================================================================
Many users want to see a running total display as rows of data are entered
in a region. This has been doable since R:BASE 3.1 and the introduction of
EEPs to forms. However, now using some of the R:BASE 4.5 Plus! features, it
is so easy anyone can do it. Minimal programming is required, the EEP code
is straightforward and simple.
Prior to R:BASE 4.0 and the addition of the SCREEN RESTORE command a field
had to located on the form to display the calculated total. Because of
region field location rules, it had to be located on every row in the
region, or outside the region and associated with a separate table. Using
SCREEN RESTORE OFF allows variables displayed by a WRITE statement in an
EEP to remain on the screen; the screen is not refreshed when returning to
the form from an EEP.
The summing technique using SCREEN RESTORE OFF calculates the total in the
EEP and writes it to the screen. It is no longer necessary to locate a
variable on the form to hold the total. And R:BASE 4.5 Plus! has added the
SAVEROW EEP only command which provides even more options for the
application developer.
This article shows two different methods for displaying a running total
as new rows are added in a region. They are slightly different, choose
the one that works best for your situation. One uses a field level EEP,
the other a table level EEP. There are pros and cons to each as discussed below.
Some elements are common to both methods. The total is displayed on the
screen using a WRITE command. To determine the correct screen location for
the WRITE command, bring up the form in Create/modify mode. Place the
cursor in the exact place you would like the total to be written. Add 1 to
the row number displayed. The column number to use is where you want the
left side of the value to display. These are the coordinates to use in the
WRITE command. In addition, you need an EEP on the first table in the form,
On entry into a row, to clear the screen location where the total is
displayed. For example,
*(CLS_SCRN.EEP)
CLS FROM 22,35 TO 22,79
SCREEN RESTORE OFF
You must include SCREEN RESTORE OFF in this EEP. It makes sure the screen
is refreshed as you add rows.
Using a table level EEP
The table level EEP is placed On exit from a row on the Table settings
screen for the region. It uses an accumulator variable, vsumprice, to
accumulate the total as rows are added. You define a form variable to
calculate the extended price on each row. This variable passes that row
value to the EEP where it is added into the running total. In this example,
using the sample form, tranform, from the Concomp database, the variable is
already defined on the transdetail table, the variable vextprice.
*(SUM.EEP)
SET ZERO ON
SET MESSAGE OFF
SET ERROR MESSAGE OFF
SET VAR vsumprice CURR = (.vsumprice + .vextprice)
WRITE .vsumprice AT 22,62 YELLOW ON BLUE
SCREEN RESTORE OFF
RETURN
This is a simple, forward processing EEP. It does not take into account a
user moving back to a previous row or a user discarding a row of data. An
incorrect total may display in those situation. Also, this is not a good
choice when editing data. As you move forwards and backwards through the
rows in the region, the EEP executes and keeps adding to itself. These
situations can be corrected by trapping for keystrokes, however. For
example, the following code added to the beginning of the EEP will not
recalculate the total if the user uses the F7 or F8 function keys to move
between rows.
SET VAR vkey = (LASTKEY(0))
IF vkey = '[F7]' OR vkey = '[F8]' THEN
RETURN
ENDIF
There are additional example throughout this issue of the Exchange showing
how to trap for keystrokes in an EEP.
The EEP placed On exit from a row in the Table settings for the Transdetail
table accumulates the total in a variable. The variable that is accumulating
the sum needs to be cleared after the data is saved, before a new row is
added. Add the following command to the EEP used to clear the screen
location where the total is displayed, the EEP placed On entry into a row
for table 1, transmaster, in the form.
SET VAR vsumprice = NULL
This clears the accumulator variable after the data is added to the tables
.
Using a field level EEP
Another way of calculating a sum is to use the SELECT function SUM and
calculate it from the rows of data stored in the table. This is easy to do
now by using the new SAVEROW EEP command. As you leave the field, the row
is saved and the sum calculated. Place the EEP On exit from field on the
last field in the row needed in order to calculate the total.
For example, place it on exit from the vprice field in the transdetail table
on tranform. The row may be saved before all the data for the row is entered
if this is not the last located field on the row. Also, The user can exit
the row before putting data in this field.
Using this method calculates a total for all matching rows in the table,
not just those displayed on the screen. If you have a situation where you
may enter detail records at different times, don't use this method. The
total displayed will not match the amounts entered on the screen.
This technique needs a form expression to put the ID number into a variable
for use by the EEP. For example, vtransid = transid. The expression can be
placed on the transmaster table or the transdetail table.
*(SUM.EEP)
SAVEROW
SELECT SUM(extprice) INTO vtotprice FROM transdetail +
WHERE transid = .vtransid
WRITE .vtotprice AT 22,40 WHITE ON RED
SCREEN RESTORE OFF
Again, this method also uses the EEP to clear the screen location where the
total was displayed. This is a better method to use when editing data as it
retrieves the sum from the table rather than calculating it as data is
added.
Both of these methods are designed for use with a form that is adding new
data to a table. The EEP evaluates totals as the rows are added to the
region. To display a total when editing data, add an EEP to the first
table in the form that executes On entry into a row. Calculate the sum of
matching rows for the region using the SELECT function SUM and display it
using the WRITE command. As you scroll through rows in the first table,
the total will display for the matching rows in the region.
The two methods presented here are not the only ways to display a running
total in your form. Use the ideas presented by these techniques to add a
solution to your application.