Risc World 3/Risc World 3.iso/SOFTWARE/ISSUE4/POWERBASE/DOCS/Ch06-Calcs

Text File | 2001-01-31 | 12.5 KB | 268 lines

==============================
Ch 6 − Performing Calculations
==============================

There are two mechanisms for performing calculations in Powerbase:

•       Self-calculating fields which automatically calculate and display a
        value derived from other fields. These belong to the field class
        called Computed (see 4.2.9).

•       Arithmetic performed on a column in a report.

Computed fields may be of Calculated or Composite types. Calculated fields
display a numeric result derived from other fields in the record. When the
mouse pointer is moved over such a field it changes into a shape resembling
a pocket calculator. Composite fields perform a similar function but display
the result as a character string instead of a number. In this case the mouse
pointer changes into a large “+” sign. The latter do not, strictly, involve
“calculations” at all but because Composite and Calculated fields work in
the same way it is convenient to deal with them together. It isn’t possible
to type data directly into either of these special types of field.

There are also Computed fields for the same types of data as Stamp fields of
types (c), (d), (e), (f), (g) and (h) (see 4.2.10). They have identical
names but, whereas Stamp fields are entered when the record is created and
don’t change thereafter, the Computed types update whenever a record is
displayed

It is also possible to write your own functions in Basic and use them in
Calculated and Composite fields. The system variable TIME$ is useful in this
connection.

6.1     Calculated fields
-------------------------
6.1.1   Simple calculations using Numeric fields

Our first example will be to make a field containing a v.a.t. exclusive
price determine the contents of another field which includes v.a.t. at 17%.
Let the tags of these fields be VEX and VINC respectively. Click MENU over
VINC to bring up the Field submenu and choose  Calculations.  A window
appears with a writable icon which contains:

                                        VINC=

Complete the formula so that it reads:  VINC=VEX*1.17

and click on OK or type Return. From now on the value in VINC will change
whenever you type something in VEX.  Since the v.a.t. rate is liable to
change it would be better to place the current percentage rate in another
Numeric field called RATE and make the formula attached to VINC read:

        VINC=VEX+(VEX*RATE/100)

The second example calculates the average of four fields No1, No2, No3 and
No4, placing the result in a Calculated field AVGE.  The required formula
is:

        AVGE=(No1+No2+No3+No4)/4

You may also enter a tag into a calculation formula by clicking on the
required field with Ctrl held down or by choosing it from the pop-up menu of
fields available from the calculation window. The tag will be entered in the
formula at the caret position. You are strongly recommended to use one or
other of these methods since Powerbase finds it very difficult to decide
whether or not a formula is valid and errors are usually detected only when
the calculation is actually attempted.

6.1.2   Making calculations retrospective

The default setting is such that entering or altering a formula affects only
those records added or altered after the formula entry/change. The changes
can be made retrospective by selecting the Recalculate existing records
switch on the formula entry window. On clicking OK you will be asked to
confirm that you want previous records to be made consistent with the
formula you have just entered. Changes affect the current subfile only, but
can be easily implemented in other subfiles by changing to the required
subfile, calling up the formula entry window, selecting the option button
and clicking OK.

The Preferences window, accessible from the iconbar menu, contains a switch
labelled Recalculate on opening. If this is selected then calculations
involving the system variable TIME$ will be updated automatically for all
records when the database is opened (see also 6.3).  This could be needed
where calculations involve dates, ages, times etc. which might change from
one work session to the next even though no editing of the data has been
done.

                                - 40 -

6.1.3   Calculations using non-numeric fields (!)

This isn’t as daft as it looks! You might think that the process described
in 6.1.1 is only applicable to numeric fields, but a non-numeric field may
be specified in the formula − if it is linked to a validation table. To be
of use there must be  numeric data in the column of the validation table
immediately following the one to which the field is linked (see 5.2).
Suppose, for example, we have fields in a student record for A-level exam
grades, the field tags being GR1, GR2, GR3, GR4. The grades are non-numeric
but they map onto the numeric points system which universities use to
control entry. The relationship of grades to points is as shown below.

Grade   Points
=====   ======
A       10
B        8
C        6
D        4
E        2

A validation table could be set up with the grades in column 0 and the
equivalent points in column 1. Each of fields GR1−GR4 would be linked to
column 0 thus restricting input to the capital letters A-E. A further field
of Calculated type would be created to hold the points score. If we
associate this field (PTS) with the formula:

PTS=GR1+GR2+GR3+GR4

entering or changing the letter grades in GR1-GR4 will cause the correct
points score to appear in PTS.

6.1.4   Calculations involving times

There is a field type Time which will only accept valid 24-hour times in
hh:mm:ss format.  If the tag of such a field is included in a calculation
formula it will be converted into seconds and the result used in the
calculation. Thus a Calculated field DIFF could use two Time fields, TIM1
and TIM2, in the formula:

DIFF=TIM1-TIM2

and Powerbase would keep DIFF updated to show the difference in seconds
between the two times.  Three times could be averaged and the result (in
seconds) placed in a field AVGE using the formula:

AVGE=(TIM1+TIM2+TIM3)/3

6.1.5   Calculations involving dates

You may also include date fields in a calculation in a similar manner to the
use of Time fields (see 6.1.4). A Calculated field DIFF could be used to
show the number of days between two dates using the formula:

DIFF=DAT1-DAT2

6.2     Composite fields
------------------------
To enter the formula for a Composite field follow the same procedure as for
a Calculated field. Note that the relevant entry on the Field submenu now
says Combine fields. Formula entry is similar to that for Calculated fields.
The result of the “calculation” is a character string and is usually result
of string operations. One of the most frequently used will be “+” which
allows fields to be joined together.  Suppose your database contains fields
for surname (SNAM) and forename (FNAM) and you want to be able to print
names in the format forename-surname. Define a Composite field called NAME
and attach to it the formula:

NAME=FNAM+“ ”+SNAM

Note the quoted space separating the names.  You might want the NAME field
to show only an initial plus the surname. This could be extracted using the
Basic function LEFT$ * to produce the formula:

NAME=LEFT$(FNAM,1)+“. ”+SNAM

Composite fields may be used in conjunction with Time fields to perform
genuine “clock arithmetic”, e.g. referring to the examples in 6.1.4, if we
attached the formulae to Composite fields instead of Calculated fields they
would display the difference and average respectively in hh:mm:ss format
instead of in seconds.

        As with Calculated fields updating occurs after editing a field
whose tag appears in the attached formula. Thus NAME would be updated after
changes to FNAM or SNAM and DIFF after changes to TIM1 or TIM2. You can,
however, have Composite fields which make use of the Basic system variable
TIME$. Thus a field DAY could be linked to the formula:

DAY=LEFT$(TIME$,3)

to make it show today as Mon, Tue etc. No field tags are referred to in the
formula so DAY gets updated immediately before displaying the record so that
the information is correct at that time.

                                - 41 -

6.3     User functions
----------------------
These are functions, written in Basic, which accept field tags as parameters
and can be included in the calculation formulae of self-calculating fields.
All such functions must return only their principal value: RETURN variables
in the parameter list aren’t allowed. The name of each function must begin
with an upper-case “U”, e.g. FNUaverage. This avoids duplication of
function-names which occur in Powerbase.

        All the user functions to be used by a database must be included in
a Basic program called UserFuncs which is stored in the database directory
(not in the !Powerbase directory). When the database is opened UserFuncs
will be loaded as a library and Powerbase can call the user functions just
as readily as its own functions. When a user function appears in a
calculation formula the tags of any fields on which the function operates
are included as parameters to the function. Editing any such field makes the
Computed field update.

        The distribution disc includes a UserFuncs file containing two
examples of user functions kindly submitted by David Lenthall. Users are
invited to submit other user functions for possible inclusion in the
function library. FNUnow operates on the value of TIME$ from the real-time
clock and returns a string containing the current date in form DD-MM-YY. A
record design could include a field of Composite type, at least eight
characters long and tagged as, for example, DATE. Associating the field with
the following formula would cause the field to display the current date at
all times:

DATE=FNUnow(TIME$)

If the function always operates on TIME$ and never on any other string, why
do we bother including TIME$ as a parameter to the function?  A Calculated
or Composite field is normally only updated when you edit a field on which
its value depends.  The field DATE in the above example doesn’t depend on
any other fields and would therefore never be updated!  The inclusion of
TIME$ causes the Composite field to be updated immediately before the record
is displayed.  The same trick of using TIME$ as a parameter, even if the
function makes no use of it at all, can be used for any user function
associated with a field which you want updating without having to edit the
record, but note that updating will only occur if you call up the record for
display. See 14.4.3 for how to make all records in the current subfile
update on opening the database.

        The second function, FNUageinyrs accepts two parameters, each of
which should a date in DD-MM-YY format, and returns the difference between
the dates to the nearest year. The first date should be the earlier of the
two. If your record design has a field for Date of Birth (let’s call its tag
DOB) you can use this function together with FNUnow (which returns today’s
date in the required format) to make a Composite field (AGE) display a
person’s age in years by using the formula:

AGE=FNUageinyrs(DOB,FNUnow(TIME$))

Considerable care is needed in constructing user functions as it is very
easy to make Powerbase generate errors. In particular you must avoid giving
a function a name which is the same as a field tag − or even one which
contains a field tag as a substring. The two functions described could not,
for instance, be used in a database which had a field tagged as “now” or
“age”, although “NOW” and “AGE” could be used.  To avoid this problem users
are recommended to form the habit of giving tags names in upper case and
user functions names in lower case (apart from the “U”, of course).

6.4     Calculations on a column of a report
--------------------------------------------
There is an option on the Print submenu called Numeric fields which is
normally shaded. It only becomes available when you highlight one or more
Numeric or Calculated fields for printing. You can then access a window
listing all the fields (if any) in the record which are of these two types.
Associated with each field are six check-boxes which can be selected to
include the following in the report:

•       Count (number of values in column)
•       Sum
•       Average
•       Standard deviation
•       Maximum value in column
•       Minimum value in column

The check-boxes are shaded until a field is included in a print selection.
They then become “live” and any or all of them may be chosen by clicking
with SELECT. The information requested is then added to the report footer
when printing takes place.

                                - 42 -