Powerbase has several mechanisms for performing calculations but they fall into two main categories:
Computed fields derive their contents from other fields or from the computer's real-time clock. They are not editable. In 4.2.12 there is a table of Stamp fields. Such fields are "stamped" by Powerbase when a record is first created and remain unaltered (and uneditable) thereafter. All the ones listed in bold type, however, have their counterparts among the Computed fields. These have exactly the same names as their Stamp equivalents but they automatically update when a record is displayed. A quick way to see this happening, and to appreciate the difference from a Stamp field, is to create a Stamp field and a Computed field each of the Date and Time type. On displaying a new record the contents of both fields will be identical and remain so as long as the record remains on the screen. You will see that the seconds part (at least) of the Computed field increases whenever you re-display a record while the Stamp field never changes but always shows when the record was first created.
The other types of computed fields are Calculated and Composite. 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 might 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.
For a second example set up a database containing four Numeric fields whose tags are No1, No2, No3 and No4 and a Calculated field AVGE. Click MENU over AVGE, choosing Calculations from the Field submenu as before, and enter the formula AVGE=(No1+No2+No3+No4)/4. Changes to any of the four Numeric fields will cause AVGE to update.
The special functions sum(), mean() etc.for arithmetic on Scrollable lists (see 6.3) are usually attached to a Calculated field.
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 Ctrl-clicking on the required field 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's ability to decide whether or not a formula is valid is somewhat limited and errors are usually detected only when the calculation is actually attempted.
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.5).
A non-numeric field (without the square brackets) may also 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.
A validation table could be set up as shown with the grades in column 1 and the equivalent points in column 2. Each of fields GR1-GR4 would be linked to column 1 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.
See below (6.1.6) for a genuinely useful application of Composite fields to dates.
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.
The special functions word() and chars() (see 3.5.5) may also be used in Composite fields.
Suppose you have a Date field (DATE) and want a Composite field (WEEK) to display a date one week later. You can achieve this with the formula WEEK=DATE+7. Moreover, WEEK will display the result as a date even though it is not itself of type Date! (But see Note 2.) The format of the date in WEEK depends on the number of characters allowed in the field. It needs to be at least 8 to display the date as dd-mm-yy but more space allows longer date formats to be displayed. If you make the length of WEEK the same as that used by DATE the two fields will use the same date format.
There's a further twist to this. Suppose you want another Composite field (WK2) to display a date 7 days later than WEEK. You could, of course, do this quite easily by using WK2=DATE+14 but if you want to use a lot of Composite fields in this way to produce a list of related dates automatically it would be much more convenient to use formulae relating each field to the preceding one, e.g. WK2=WEEK+7, WK3=WK2+7, WK4=WK3+7 etc. Powerbase will refuse to accept such formulae because the field to which we want to add 7 isn't of Date type; it's a Composite. We can, however, force Powerbase to treat it as a date by placing curly brackets round it so that the formulae become: WK2={WEEK}+7, WK3={WK2}+7, WK4={WK3}+7 etc. (This is similar to the use of square brackets to force non-numeric field types to be treated as numbers.) The author has used this method to display dates at 28-day intervals from a given starting date throughout an entire year.
Composite fields may be used in conjunction with Time fields to perform genuine "clock arithmetic", e.g. referring to the above examples involving times (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) (see Note 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.)
The Evaluate button was briefly mentioned along with the Increment button in 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 an important advantage in that the field which receives the result of the computation can be of any Editable type.
sum() | adds up the contents of the cells in the column or row |
mean() | averages the cell contents |
num() | merely counts the cells |
sdev() | calculates the standard deviation of the cell contents |
min() | finds the minimum value in the column |
max() | finds the maximum value |
The functions would normally be attached to a Calculated field and operate on a column of Numeric type (see 4.2.6). If, for example, you have a Scrollable list with the tag LIST whose second column is defined as Numeric, and wish the column total to appear in a Calculated field TOTL you would enter the formula TOTL=sum(LIST,C,2) or, alternatively TOTL=sum(LIST#2).
If column 2 of LIST was defined as of type Time the result would appear as seconds in TOTL. In this case, however, you could use a Composite field for TOTL and the sum would be in hh:mm:ss format. This is consistent with the way ordinary Time fields are treated (see 6.1.4). There is a calculation of this type on the Music database.
Using these functions on a row instead of a column is more restrictive because all the columns would have to be of the same type; either Numeric or Time.
User functions are short section of program code, 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. Powerbase will parse the entered formula and replace the tags by field-dependent array references which BASIC can understand. 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.
A UserFuncs directory containing examples of useful user functions, two of which were kindly submitted by David Lenthall, may be downloaded from our website. 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 formula DATE=FNUnow(TIME$) would cause the field to display the current date at all times. There are also self-updating field-types which will maintain the current date (see 6.1).
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. Since DATE in the above example doesn't depend on any other fields it would never be updated at all. However, the inclusion of TIME$ causes this or any other Composite field to be updated immediately before the record is displayed. If you want a field updating without having to edit the record that's how to do it; include TIME$ as a parameter, even if the function makes no use of it. See 14.1.4 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" could be used and "AGE" is 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).
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 Report 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.
To define a calculation choose Extra calculations (Ctrl Shift N) from the Report submenu. The window which appears requires you to enter a formula, which has exactly the same format as the right hand side of the formulae for Calculated and Composite fields (see 6.1) and may contain anything which can be used in such fields. Pop-up menus are provided to let you enter field tags and user functions into the formula. If using the latter you might have to supply several parameters, in which case commas appear to indicate how many parameters are expected. Since the calculation will not be attached to a field from which to derive a heading or a data width, and whether the result is to be numeric or not, you must supply this data yourself. If you omit these Powerbase will use Calc <n> 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 Report 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.
Note that there is non-editable field labelled Tag adjacent to the Heading field. Powerbase automatically assigns a tag to the calculation based on the first four letters of the heading plus the calculation number. The reason for having a tag is to enable the calculation results to be queried as if they were in a real field. Thus, for the example illustrated, search formulae such as SEAS1>10 are perfectly valid.
When you have defined your calculation, click on Include. The displayed calculation number will go from 1 to 2 and the window is set up for entry of another calculation. A total of 8 (numbered 1-8) are allowed and you may return to and change earlier calculations to edit them by clicking the "bump" icons, the Include button's legend having changed to Modify. Delete calculation will remove the displayed calculation if you decide you don't want it.
Bear in mind that any calculation created with this facility has the same status as a field selected for printing. It will be incorporated in a report if you print it without closing the database but, unless you save the field selection of which it is part, it will be gone next time you open the database, just like the real fields selected with ADJUST. That's why there's a Save selection button which does exactly what the similarly-named entry on the Report menu does.
If you want to experiment with this feature load the Sudoku database, click ADJUST over the Name field to select it then bring up the Extra calculations window and enter something like sum(GRID,C,3) and click Include. Now create a report containing all records (i.e. no search formula) and you will see the column headed Calc 1 at far right. Step through the records and you will see that the numbers in this column are indeed the totals for column 3 of the puzzle grid.
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) 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 Field 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 Report submenu.
2. If you make WEEK a Calculated, rather than a Composite, field the formula WEEK=DATE+7 will, instead of displaying the date a week later, show the number of elapsed days to that date from Jan.1st 1900. You might have a use for that but it doesn't seem likely! (back)
3. TIME$ is a 24-character string containing the current date and time to the nearest second, for example:
Thu,06 Jan 2011.12.20.38
LEFT$(TIME$,3) extracts the leftmost 3 characters producing the day as a string (Thu) and MID$(TIME$,5,2) extracts 2 characters starting at the 5th which gives the day as a number (06). Further uses of MID$ enable you to extract the month, year etc.
(back)