======================================================================
SUMMING IN FORMS
======================================================================
PRODUCT: R:BASE VERSION : 4.0
AREA : FORMS CATEGORY: SUMMING DOCUMENT#: 652
======================================================================
Over the years many Exchange articles have explained how to sum the
rows in a region of a form and display the total on the screen. None
of the methods worked really well because of the limitations of regions
in forms and the programming commands available. The desired visual
result was usually something like the following example below, where
you would have the total located once and it would accumulate as each
row was entered into the region.
Until now, this format was next to impossible to achieve because the
total always had to be a located field. The new SCREEN RESTORE command
available in R:BASE 4.0 allows for greater versatility when writing
entry/exit procedures (EEPs). SCREEN RESTORE lets you turn off the
screen refresh when you return from the EEP to your form. Now, values
displayed on the screen (form) using WRITE or SHOW VARIABLE commands
in an EEP stay on the form when you include SCREEN RESTORE OFF in the
EEP. This new command makes easy work of displaying a running total of
rows in a region.
The total is calculated in the EEP much the same way as the Summing in
Forms EEP shown in the May/June 1990 Exchange. The difference is in how
the total is returned to the form. Prior to R:BASE 4.0, you had to
return the value in a variable or through a playback file. In R:BASE
4.0, using SCREEN RESTORE OFF, you can simply write the value to a
location on the screen.
An Example
==========
This example uses a form based on the transmaster and transdetail
tables. Design it as shown below.
+======================================================================+
|| ||
|| Transid: [ ] Date: [ ] ||
|| ||
|| Model Description Units Price Extended Price ||
|| +---------------------------------------------------------------+ ||
|| | [ ] [ ] [ ] [ ] [ ] | ||
|| | [ ] [ ] [ ] [ ] [ ] | ||
|| | [ ] [ ] [ ] [ ] [ ] | ||
|| | [ ] [ ] [ ] [ ] [ ] | ||
|| | [ ] [ ] [ ] [ ] [ ] | ||
|| +---------------------------------------------------------------+ ||
|| Invoice Total: $ [ ] ||
|| ||
+======================================================================+
The transdetail table is a region and has these expressions defined
for the table:
1. INTEGER : vtransid = transid
2. INTEGER : vdetailnum = detailnum
3. TEXT : vextprice = (price*units)
4. INTEGER : vunits = units
5. CURRENCY: vprice = price
You still need to define expressions to pass values to an EEP; that's
what these expressions (except for vextprice) are doing. Locate the
columns from the transdetail table in the region; you don't need to
locate variables except for the variable vextprice.
Place sum1.eep (listed below) as an entry procedure on the first field
of table 1 in the form (the transid column of table transmaster). When
you add data with the form, this EEP displays $0.00 in the total until
detail records are added. When you edit data with the form, this EEP
displays the correct total for the detail records while moving through
the rows in transmaster.
*( SUM1.EEP )
SET MESSAGES OFF
SET ERROR MESSAGES OFF
SET VAR vsum CURRENCY,vtemp CURRENCY=NULL
SELECT SUM(price * units) INTO vsum FROM transdetail +
WHERE transid = .vtransid
SET VAR vtemp =.vsum
WRITE .vtemp AT 19,54 USING '99,999,999.00' black ON cyan
CLEAR VAR vsum,vtemp
SCREEN RESTORE OFF
RETURN
The EEP uses the SELECT command to compute the total and the SCREEN
RESTORE command to not clear the screen when the form is redisplayed.
This leaves the total displayed with the WRITE command on the screen.
Note that the WRITE command uses a picture format and colors to display
the total.
Place SUM2.EEP (listed below) as an exit procedure on the field to
total; in this example it's placed as an exit procedure on the price
column even though it is actually totaling extprice (a non-editable
field).
*( SUM2.EEP )
SET MESSAGES OFF
SET ERROR MESSAGES OFF
SET VAR vp CURRENCY, vu INTEGER, vsum CURRENCY
SELECT SUM(price * units) INTO vsum FROM transdetail +
WHERE transid = .vtransid
SELECT price,units INTO vp,vu FROM transdetail +
WHERE transid=.vtransid AND detailnum=.vdetailnum
IF (.vp*.vu) < > (.vprice*.vunits) THEN
SET V vtemp = (.vsum + (.vprice * .vunits) - (.vp * .vu))
ELSE
SET VAR vtemp=.vsum
ENDIF
WRITE .vtemp AT 19,54 USING '99,999,999.00' black ON cyan
CLEAR VAR vp,vu,vsum,vtemp
SCREEN RESTORE OFF
RETURN
The EEP uses the SELECT command to compute the total. By subtracting
VP and VU, the EEP allows for the possibility that the region might
have only one row or that you have moved up and down to different rows
in the region. Also, because the EEP is evaluated every time you leave
the field, you need to make sure that it doesn't re-add values and make
the total larger than it should be. The IF statement checks to see
whether the price or units fields have been changed; if so, a new total
is calculated using the variables from the form.
Note the use of the SCREEN RESTORE command in the EEP to not clear the
screen when the form is redisplayed. This leaves the total displayed
with the WRITE command on the screen when you return to the form.
To figure out the correct screen coordinates for the WRITE command,
modify the form and position your cursor at the location where you
want the total to display. The lower right corner of the screen shows
the current row and column location of the cursor. Add 1 to the first
coordinate (the row), and use those two numbers as the coordinates in
the AT section of the WRITE command.
This form works well for both Add data (ENTER) and Edit data (EDIT
USING). Although your database might not fit exactly into this struc-
ture, you can adapt these ideas using the SCREEN RESTORE feature to
display totals and other information on your forms easily from within
an EEP.