list of chapters    previous chapter   next chapter

Ch 6 - Performing Calculations


Ch 6 - Performing Calculations

There are four mechanisms for performing calculations in Powerbase:

Computed fields may be of Calculated or Composite types. Calculated fields display a numeric result and Composite fields display a character string. They are readily recognised by moving the mouse pointer over them: on Calculated fields the pointer shape changes to resemble a pocket calculator; on Composite fields it changes to a large blue "+". 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.

Many of the Stamp fields listed at the beginning of section 4.2.12 have exact counterparts among the Computed fields. The relevant type are (c), (d), (e), (f), (g) and (h), all of which are concerned with time and date. They have identical names to their Stamp equivalents but, whereas Stamp fields are entered when the record is created and don't (apart from Last altered) 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 whose tags are No1, No2, No3 and No4, placing the result in a Calculated field called AVGE. The required formula is:

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

If you decide to actually try these examples be sure to make the Calculated field either an integer or fixed-point type, otherwise strings of unwanted decimal places are likely to occur!

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 button 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 button 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).

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 make the correct score appear in PTS.

6.1.4 Calculations involving times

There is an Editable field type called 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)DIV 3

(The use of DIV here, rather than /, ensures that the result is an integer.)

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$ (Note 1) 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. (Note, however that there is a dedicated Composite type for displaying the short-form day of week. See beginning of chapter.)

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.

Each user function to be used by a database should be in the form of a separate Basic file. The files may be called whatever you like, but it is sensible to use names closely related to the actual function names. All the Basic files are stored in a subdirectory of the database called UserFuncs. An entry on the Miscellaneous submenu (keystroke equivalent Ctrl Y) can be used to display the UserFuncs directory and, unless the directory is empty, this menu entry will have its own submenu listing the contents by filename. Choosing an item from this submenu loads the relevant Basic file for editing.

When the database is opened each function is loaded using Basic's LIBRARY command. Powerbase can then 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.

A menu of user functions is available from the Formula window to make them more easily accessible. This is quite different from the menu of filenames referred to above in that it lists the actual function names as given in the Basic DEF FN statement. To enable you to see the number and type of parameters required by the functions these parameters appear in the menu as Basic variable names. When actually using a function in a formula the variable names should be replaced with field tags.

The distribution disc includes a UserFuncs directory containing examples of useful user functions, two of which were 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 for reports only

Computed fields based on other fields have the disadvantage of requiring the user either to foresee at the time the database is designed what calculations will be required or reformat it later in order to include a field which might only be needed for a one-off inclusion in a report. To avoid this, Powerbase lets you specify such calculations without the need for a field to which to attach them. In a sense they resemble the column calculations described in section 6.6 in that they are performed on the fly during the creation of a report.

To define a calculation choose the entry Extra calculations (Ctrl Shift N) from the Print submenu. The window which appears requires you to enter a formula, which has exactly the same format as the formulae attached to Calculated and Composite fields (see 6.1 and 6.2). Since the calculation will not be attached to a field from which to derive a heading, a width, and whether the result is to be numeric or not, you must supply this data yourself. If you omit anything Powerbase will use the formula itself as a heading, assume a Numeric result and assign a width of 15 characters (the maximum space a number can occupy). If you have clicked the radio button to select a String (i.e. non-numeric) result such as would be placed in a Composite field, the width will default to 255 characters. This might seem an enormous value, but unnecessary "white space" will be removed from the report before printing if the output is to the printer. If the report is to be displayed in a window the Shrink list button on the Print options window should be selected. Failing this you may remove the surplus space by choosing Shrink list from the menu over the report window.

It is possible to produce errors by unsuitable combinations of fields and result types (just as it is for Computed fields). The main thing to remember is that Numeric fields will normally produce a numeric result so assigning a String operation to such fields can be expected to produce an error. There are exceptions even to this rule since the Basic STR$() function may be used to convert a number to a string, thus allowing Numeric fields to give a String result. Dates, Times and User Functions may be used in Extra calculations, just as they can in Computed fields.

When you have defined your calculation, click on Include. The displayed calculation number will go from 0 to 1 and the window is set up for entry of another calculation. A total of 10 (numbered 0-9) are allowed and you may return to earlier calculations to edit them by clicking the "bump" icons.

The position of such calculated results in the report is determined by the point at which you define them. If you click ADJUST to highlight a field, then type Ctrl Shift N to bring up the new window and define a calculation, and finally highlight another field, the calculated result will appear between the highlighted fields. The menu of selected fields (see 3.7.2, S) shows these calculations in red so you can easily check on the order in which things will appear on the report. You can choose the red entries from this menu to bring up the calculation window for editing.

All defined calculations are saved as part of a Print Selection and will be restored when the Selection file is subsequently reloaded. The Clear button on the calculation window has the same effect as Clear selection (Ctrl Z) from the Print submenu.

6.5 Calculations from an Evaluate button

The Evaluate button was briefly mentioned along with the Increment button (see 4.2.13). It enters the result of its computation into a data field having the same tag as the button. The formula associated with the button is of exactly the same type as for a Calculated or Composite field and is entered by choosing Formula from the Field submenu obtained by clicking MENU over the button.

Unlike a Calculated or Composite field the data field does not auto-update when one of the fields referred to in the formula is edited: you have to click the Evaluate button to update the field. This method of performing calculations does, however, have two important advantages: the Evaluate button can be added to an existing database without reformatting and the field which receives the result of the computation can be of any Editable type. 

6.6 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 fields of numeric type for printing. These may be editable Numeric fields, Calculated fields or any fields of the Computed or Stamp class which are essentially numeric.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:

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.

To obviate the need to select a lot of check-boxes individually when many numeric fields are highlighted, option buttons are provided which enable all the enabled check-boxes in a column to be selected or deselected with a single mouse click.

Another entry on the Print submenu provides a similar facility for Check-box fields. Instead if six check-boxes for each qualifying field there is just one which, as before, is shaded unless the field is included in a print selection.

For 2-state check-boxes the information printed at the foot of the report consists of the numbers selected and unselected. For 3-state boxes the number in each of the three states is printed. The results are labelled "Yes", "No" and "Neither", except in the case of the F/M/<blank> box where the labelling is "Male", "Female", "Null".

As in the case of numeric fields, an option button allows all the enabled check-boxes in the window to be selected or deseleted.




Notes

1. LEFT$(string$,n) extracts the leftmost n characters from string$. RIGHT$(string$,n) extracts the rightmost n characters. MID$(string$,n,m) extracts m characters starting at the n'th character from the left. (back)



top of page   list of chapters    previous chapter   next chapter