Acorn RISC PD-CD 1

home *** CD-ROM | disk | FTP | other *** search

/ Acorn RISC PD-CD 1 / Acorn RISC PD-CD 1.iso / utilities / powerbase / pbasedoc / calculate < prev next >

Wrap

Text File | 1993-08-30 | 14.0 KB | 262 lines

Performing Calculations ======================= There are two mechanisms for performing calculations in Powerbase:- (1) Fields which automatically calculate and display a value derived from other fields. (2) Calculations performed on a column in a report. Self-calculating fields ======================= Fields defined to be of 'Calculated' type 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. Fields of 'Composite' type perform a similar function but display a textual "value" instead of a number. In this case the mouse pointer changes into a large "plus" sign. It isn't possible to type data directly into either of these special types of field. 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 is possible to write your own functions in Basic and make use of them in 'Calculated' and 'Composite' fields. The system variable TIME$ is also useful in this connection. 'Calculated' fields ------------------- Click MENU over the field to bring up the 'Field' submenu and move right over the fourth choice ('Calculations'). You must have 'Manager' access rights in order to do this, otherwise the menu option is shaded. A dialogue box appears into which you can type the formula to be used to calculate the value of the field. Suppose we have four normal numeric fields whose tags are No1, No2, No3 and No4 and a Calculated field called AVGE. The dialogue box will initially show: AVGE= Complete the formula so that it reads: AVGE=(No1+No2+No3+No4)/4 and click on 'OK' or type RETURN. From now on, whenever any of the numbers in No1, No2, No3 and No4 are entered or altered the contents of AVGE will be updated in accordance with the formula. To make formula entry easier, the dialogue box works like the Match window i.e. clicking on a field in the main window whilst the dialogue box is open causes the tag of the field to be copied into the formula. The default setting is such that entering or altering a formula affects only those records which are added or altered after the formula entry/change. The changes can be made retrospective by setting the option button on the formula entry window. On clicking OK you will then 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. You might think that this process is only applicable to numeric fields, but you would be wrong! A non-numeric field may be specified in the formula IF it is linked to a validation table. To be of any use there must be numeric data in the column of the validation table immediately following the one to which the field is linked. (See 'Validation' for a description of validation tables and field-linking.) Suppose, for example, we have fields in a student record for A-level exam grades, the field tags being A1, A2, A3, A4. 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 follows:- 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 A1-A4 would be linked to column 0. A further field of type 6 (Calculated) would be created to hold the points score. Let's suppose this has the tag PTS. Use the method given above to associate this field with the formula PTS=A1+A2+A3+A4. From now on entering or changing the letter grades in A1-A5 will cause the points score to be updated in PTS. A further feature of Calculated fields is their use with Time fields (by which we mean editable Time fields in the 'Data' class, not the current time of day field provided in the 'Stamp' class (see below). If the tag of such a field is included in a calculation formula it will be converted into seconds. 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. 'Composite' fields ------------------ Click MENU over the field to bring up the 'Field' submenu and move right over the fifth choice ('Combine fields'). You must have 'Manager' access rights in order to do this, otherwise the menu option is shaded. A dialogue box appears into which you can type the formula to be used to "calculate" the value of the field. Formula entry is similar to that for 'Calculated' fields except that only string operations are allowed. The one most frequently used will be '+' but you may use the BASIC statements LEFT$, RIGHT$ and MID$, as well as literal strings. As an example of use you might have three fields in a database called SNAM, FNAM and MNAM for surname, forename and middle name. Addressing letters requires another field containing just the first two of these, but in the reverse order. You could therefore define a 'Composite' field called NAME and enter the formula: NAME=FNAM+" "+SNAM Note the use of a literal string to place a space between the names. Composite fields, like Calculated fields, may be used in conjunction with Time fields to perform genuine "clock arithmetic". Referring to the example above, using DIFF=TIM1-TIM2, if we attached the formula to a Composite field the latter would show the difference in hours, minutes and seconds. The method can be extended to jobs such as averaging times, e.g. with a formula such as AVE=(TIM1+TIM2+TIM3)/3. 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 fied DAY could be linked to the formula DAY=LEFT$(TIME$,3) to make it show Mon, Tue etc. No field tags are referred to in the formula so when does DAY get updated? The answer for this, or any other 'Composite' field using TIME$, is that updating occurs immediately before displaying the record so that the information is correct at that time. 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 is to avoid duplication of function names which already appear 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 on its own functions. 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 (which takes no parameters) 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 would cause the field to display the current date at all times. The second function, FNUageinyrs accepts two parameters, each of which should be the tag of a field which holds 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 to make a 'Composite' field (AGE) display a person's age in years by using the formula AGE=FNUageinyrs(DOB,FNUnow). Considerable care is needed in constructing user functions since 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. 'Calculated' and 'Composite' fields are normally updated when changes are made to the fields whose tags appear in their calculation formulae. If a formula contains a user function then the field will be updated after changes to those fields whose tags appear in the parameter list of the function. The previous discussion of 'Composite' fields referred to the fact that they are updated after altering fields whose tags appear in the calculation formula or, in the case of those using TIME$, immediatly before display. There is a slight problem in that user functions may use TIME$ without it appearing in the parameter list of the function - FNUnow is an example of this. The field AGE in the example given above will only be updated when DOB is edited. In other words it will show the person's age at the time you enter his/her date of birth but won't automatically change it a year later. To make the change automatic you could introduce TIME$ as a parameter in FNUnow, i.e. make it FNUnow(TIME$) even though the function doesn't need it. As soon as Powerbase sees TIME$ in the formula it will update AGE. The same trick of introducing TIME$ as a "dummy" parameter can be used for any user function associated with a field which you want updating immediately. 'Stamp' fields -------------- Although not used to perform calculations or string operations mention will be made here of this special class of field since, like 'Calculated' and 'Composite' fields, they are non-editable and Powerbase maintains their contents automatically and distinguishes them by changing the pointer to a "#" symbol. The contents of such a field may be any of the following:- (1) the record number in the database file (2) a unique number for each record obtained by counting upwards from a user-specified base value (3) the time of day when a record was first entered (4) the date on which a record was first entered. This may be in any of the following forms: (a) Sun,01 Aug 1993 (b) 01-08-93 (c) 01-08-1993 (5) the date and time at which a record was first entered. (6) the day on which a record was first entered. This may be either day of week (e.g. Tue) or of month (e.g. 24). (7) the month in which a record was first entered. This may be either a string (e.g. Oct) or a number (e.g. 10). (8) the year in which a record was first entered (9) the date and time at which a record was last altered (10) a sprite such as a company logo which appears on every record When creating a field of type (2) you will observe that the fill-in field labelled 'Numeric min' becomes active. This is to allow you to enter the base address for the sequence number. Suppose, for example, you want each record to have a number from, say, 4000 upwards which remains attached only to the record to which it was originally assigned. To achieve this enter 4000 in the 'Numeric min' box when creating the field. The first record entered in the new database will then be given sequence number 4000, the next will be assigned 4001. Deleting the second record and inserting a new one gives the new record the number 4002 and leaves the database without a record with sequence number 4001. Thus the contents of such a field always indicate the order in which records were initially entered. Fields which display the record number (1) will also indicate the order of entry until you delete a record and insert a new one. When a record is deleted (i.e. with the "dustbin" button, not merely moved to another subfile) its record number is released and will be reused by the next new record. Displaying a logo on each record -------------------------------- The sprite for the logo should have a name not more than four letters long and should be included in a sprite file called 'UsrSprites'. This file is placed inside the database directory. When designing the screen select the 'Logo' type from the 'Stamp' class of fields and use the name of the required sprite as the tag. When the database is in use the sprite will appear on every record, provided the UsrSprites file is in place. If not then the field will appear as a rectangle which is empty apart from the name of the missing sprite. You may use several logos on the screen at the same time. All the sprites must be present in UsrSprites. All 'Stamp' fields (except 'Logo') may be included in a report. They may also be indexed. Calculations on a column of a report ==================================== There is an option on the 'Print' submenu called 'Numeric fields' which leads to a dialogue box listing all the fields (if any) in the record which are of type 3 ('Number') or 6 ('Calculated'). Associated with each field are four check boxes headed 'Count', 'Sum', ''Average' and 'Standard deviation'. These 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 list footer when printing takes place.