---

Using Powerbase - Part 3

Derek Haslam

First of all I must apologise to readers for the long delay between the second article of this series and the present one. I thought I'd missed the deadlines for two issues; I now find that it's three. Thank you Les Chant (Letters, Vol.3 Issue 2) for jogging my memory!

Our on-going Powerbase tutorial continues with some further refinements of the MusicBase database and will then go into more detail about the query language used by the program. The version of MusicBase used is in the zip file MUSIC2 which you will find on the CD. A number of bugs which came to light whilst writing this article have been fixed in Powerbase v.8.32 so do make sure you upgrade to that version, which is supplied on the CD as PB832. We will then move on to the subject of calculations and self-calculating fields for which a different database will be required.

Using validation tables to constrain data-entry

The fields in MusicBase called Title, Artists and Label are all of type Unrestricted; they will allow us to enter any printable character. The user menu associated with the Medium field is also of Unrestricted type but can be made to contain only a limited range of oft-repeated character strings provided we always enter data via the menu. There is, however, nothing to stop us typing into the field directly. Some restrictions can be placed on the type of character a field will accept by redefining it as Uppercase or Numeric but there are many instances where much tighter control over input is needed. This can be achieved by linking the field to a validation table which contains a list of the allowed values for the field. Validation tables have another important use in allowing us to code data. This lets us associate lengthy items of data with short codes of as little as one character. The codes can be entered very quickly whilst reports can be made to include the complete data.

We're going to add a new field called 'Category' to MusicBase. This will be used to classify works as Orchestral, Piano solo, Solo voice, Opera etc. (or, if you prefer, Heavy metal, Rap, Reggae etc.) and will use a validation table to force the data to one of these categories, allowing the category to be typed in as a two-letter code. We can't do this from Utilites=>Alter format on the iconbar menu as we did for the user menu, Print and Exit buttons and for the scrollable list; we need to rebuild the database. Why is that? Look inside the !MusicBase directory and you'll see a file called Database. All data which gets typed into ordinary editable fields (but not scrollable lists) is stored in this file in the form of fixed-length records. The fixed record length makes it possible to find and retrieve a record very quickly but has the disadvantage that if we wish to add or remove a field, or merely alter it to accept more characters, the record length will change and the database file will need rebuilding. Button fields, scrollable lists, and some other types which we'll meet eventually, don't store anything in the Database file and are therefore not subject to such restrictions; they may be added or removed via Alter format.

From the iconbar Utilites submenu choose Rebuild database. The Reformat database window will open:-

Fig.1 The reformat window

It is worth stressing at this point that the Reformat utility builds a completely new database leaving your original database intact. It is therefore a very safe procedure which you need have no qualms about using. The default name supplied is NewFormat and you can, of course change this if you wish. The database icon at the bottom left of the window is then dragged to a suitable directory. If you want the new database to still be called MusicBase you will have to drag it to a different directory from the one containing the present MusicBase, otherwise the reformat will not proceed: you cannot overwrite your old database using this utility. I'm going to call it NewMusic so that it can reside in the same directory as the old MusicBase. (There's no need to include the '!' - Powerbase will add it for you.)

When you drop the database icon in the directory the now-familiar record-design screen with the blue grid appears. Choose Create field from the main menu. The field class we want is Editable (already selected) and from the pop-up menu we will choose Uppercase. (We don't have to but it demonstrates the other form of input-control — restricting the range of characters — referred to earlier.) The Descriptor can be 'Category', the Tag 'CAT' and the Data length is 2. Click Create and drag the field to a convenient place, then choose Quit design from the main menu.

Brief disc activity, during which a small grey window tells you what's going on, eventually leaves you with the new database open:-

Fig.2 The modified database NewMusic

The new field will only accept uppercase letters, numerals and the space and full-stop characters. Try it out: notice that the Caps Lock light on the keyboard comes on whenever the caret enters this field and goes off again when it is moved to another field. You can turn Caps Lock off from the keyboard but you still won't be able to enter any other characters than the ones listed above.

The next step is to construct the validation table which will further restrict data in this field to the values on a defined list. From the main menu choose Validation=>Create table. The table design window (see Fig.3) is displayed. We can call the table anything we like, provided it's not more than 10 letters, but 'Category' will do. 10 rows should be enough and we need to define two columns, one for the code and one for the longer description of the category. The first of these is two characters wide and 20 should be enough for the second. Column headings are optional and, since the heading isn't allowed to be wider than the column, it seems hardly worthwhile to give the code a heading. 'Description' will do for the second column. We can change the colours of the headings and table body by selecting Foreground or Background and clicking on the Heading and Data icons which will cycle through the 16 standard desktop colours. The window might then look like this:-

Fig.3 Specifying the design for a validation table

Click Create. The blank table will appear and you can start entering data right away. Here it is with some data entered:-

Fig.4 How the completed table might look

The final step is to link the table to the Category field. Click MENU over the field and choose Link to table from the Field submenu. The Link window is displayed:-

Fig.5 Linking the table to a database field

The table name Category is already displayed (since it's the only table present) and the column to link to is the first, which is numbered 0, so all we need do is select the Linked to table option button and click OK.

You will see that the foreground colour of the Category field is now green: this is Powerbase's way of indicating that a field is subject to table validation. Try typing two-letter codes into the field. If the code exists in the first column of the validation table all is well. If it doesn't exist the code will appear initially to be accepted, but any attempt to move the caret to another field, or to display a different record, results in the message '[input] is not on Category validation list'. When OK is clicked the old contents of the field are restored. Because of this you should normally leave at least one row of your validation table blank so that the linked data field can be left blank if desired.

New users sometimes get into a tangle with validation tables so that they can't change the contents of the validated field, move to a new field or record or, apparently do anything at all except keep on producing the 'Not on validation list' message! If you always include a blank entry in the table column containing the validation list you can get control of the situation by deleting whatever offending entry is in the field. If you do get stuck you can go to the iconbar menu and display the Preferences window. One of the option buttons down the left-hand side is called Validate input and you can deselect this and click Accept to temporarily turn validation off. You can then either add the missing entry to the table or click MENU over the field, go to the Link window, and unlink the field from the validation table by deselecting Linked to field and clicking OK.

With a valid code in the field, try double-clicking on it with SELECT. A small window is displayed which shows not only the code but the associated data in the other column of the table. You can make this window pop up automatically (like the user menu) if you wish by selecting Display linked table data in the Preferences window. There's another useful feature: place the caret in the Category field and click Table (if using the tool-pane) or Show values (if using the keypad). The entire table is displayed. It can also be displayed by choosing the table name from the Validation submenu.

Now make a print selection which includes Category and print a report. Only the codes of the latter field are printed. If, however, you go to the Print options window and select Expand codes then print the report again you will find that the descriptions from the second column of the validation table are printed instead of the codes. This is exceedingly useful! In the Further Education college where I used to teach we made extensive use of validation tables and coded data-entry for such things as courses, subjects studied, names of tutors and schools of origin. The people entering the data soon became familiar with the codes so that data entry was rapid and trapped against (most!) errors. Those who actually used reports based on the data were not necessarily familiar with the codes but could be given lists in which the information was printed in full.

Also useful is the Replace on entry feature. To illustrate this we will construct another validation table to be linked to the Label field. Call this one 'Label'. Once again we require two columns, of widths 2 and 20 characters respectively, and 10 rows. The first column contains coded data for the record label, the second contains the full name of the label. When the table is filled in it might look something like this:-

Fig.6 The new table with data entered

Click MENU over the Label field in the database and bring up the Link window as you did for the Category field. The displayed table name will be 'Category' so change it to 'Label' by clicking the down arrow or by choosing from the pop-up menu. The link, as before, is to column 0 but this time select the Replace on entry button. Column 1 is offered as the default replacement and this is the one we want so just select Linked to table and click OK:-

Fig.7 The Link window should look like this

The Label field will now be green. When you enter a code from the first column of the table you should find that pressing Return or clicking the mouse in another field causes the code to be replaced by the corresponding text in the second column, eg. entering 'DG' followed by Return results in 'DG' being replaced by 'Deutsche Grammofon'. You could, in fact type the latter in full and the field would accept it: both the code or the replacement string are valid entries.

A database's validation tables are stored in a subdirectory within the database called ValTables and will be automatically saved there when the database is closed. When a database is opened, validation tables are automatically loaded if they are linked to data fields. Since a table has to be in memory in order to link it to a field what do you do if, having created the table, you close the database without establishing the link? There's an easy answer to this. Look on the Validation submenu and you'll see an entry Show files which will open the ValTables directory. The table can then be loaded by double-clicking on the file with SELECT.

More could be said about validation tables. They are one of the features of Powerbase which set it apart from other non-relational databases. They can be sorted on any column, printed, saved for transferring to another database, modified etc. However, we must move on! Interested readers are referred to Chapter 5 of the main manual, or to the file Ch05-Valid in the DOCS archive.

More about queries

So far we have said little about the method of querying the database to get at all records which match a particular criterion or set of criteria; in fact we haven't provided an example since the first article. Since this is what database users probably do more of than anything else it's high time the omission was remedied. Just to remind you of the general idea bring up the main menu and choose Print (or press the Print key on the keyboard) and type MED=CD into the writable icon of the Match window:-

Fig.8 Entering a search formula, or query, into the Match window

Clicking Print then produces a list of all records which match the search formula, i.e. all which have 'CD' in the Medium field (whose tag is MED). Oh, you forgot to choose which fields to print? You click ADJUST over them, remember? If you fail to do that Powerbase will list the primary key field for each matching record as a sensible default. Suppose we want the report to include all CDs and LPs? We could do that by entering:-

The five elements printed all have names ending in IUM and exactly the same list is produced if the group numbers are swapped over.

Performing calculations

To explore this aspect of Powerbase we need a different database. We will use a very simple example which records the names of school students and their percentage marks in examinations in English, Maths and Science. The construction of this database (and a quick method of getting some data into it) will now be described to give you further practice but, if you're feeling lazy, you can use the completed example MARKS.

Constructing the Marks database

We need 4 fields:

            Descriptor       Tag         Type           Data length

            Name             NAME        Unrestricted       20
            English          ENG         Numeric             3
            Maths            MAT         Numeric             3
            Science          SCI         Numeric             3

• Close any open database
• Click on Powerbase icon, enter name "!Marks" and drag to a suitable directory.
• Click MENU over the main window and choose Create field.
• Enter the descriptor, tag and field length for the first field and click Create.
• Create the other three fields in the same way, making sure you choose type 'Numeric' before clicking Create. (If you forget, just double-click on the field to bring up the dialogue box again, change the field type and click Update field.)
• Arrange the fields in an aesthetically-pleasing way.
• Choose Default database from the main menu.

Data entry - the lazy way

Now to get some data into the fields without a lot of tedious typing. The zip file RESULTS contains data in the form of a CSV file called DATA. CSV stands for "Comma Separated Values" and the reason will become obvious if you load the file DATA into an editor. CSV files are widely used to transfer data to and from databases and to import data into wordprocessors and spreadsheets. With the newly-created Marks database open, drag the CSV file onto the record window. The CSV import window will appear:-

Fig.9 CSV import window

This gives us a great deal of control over how the imported data will be handled but, in the present case, we can simply accept all the defaults and click Import. The result will be a functioning database containing 12 records. Note that the names are in alphabetical order in the database even though they aren't in the CSV file. In fact, for reasons we need not go into just now, it's best if the source file doesn't present the records in primary key order: the more jumbled up they are the better, especially if a large number of records is being imported.

Select all four fields (Ctrl-A provides a quick way of doing this) and print all records. What else would we like to see in this report? Why, the average for each subject, of course! Close the report window then, with the fields still selected, choose Print=>Numeric fields from the main menu. The Numeric fields window:-

Fig.10 The numeric fields window

consists mainly of Check (tick) boxes which only become "live" when the associated data fields are selected for printing. There is a row of 6 boxes for each numeric field and they may be selected in order to include certain information at the foot of the report. Since we want averages for each of the three columns of numbers we click on Mean for each of the three fields and then Close the window. Now print the report again and see the difference. You might want the standard deviation of the sets of marks also, in which case tick the boxes under StDev. Max and Min could also be ticked to tell us the highest and lowest marks. You might care to confirm these last two with the aid of Powerbase's sort function. Click MENU over a column of marks in the report and choose Sort. The sort may be in either ascending or descending order. Compare the first and last numbers in the column with the printed Max and Min.

What about the average of the 3 marks for each student? If we'd had the forethought to put a self-calculating field on the record those averages would have been automatically worked out for us but, oh dear, we didn't did we? We'll remedy that in a little while but for the moment let's see how to print the required averages even though there's no field for them. From the main menu choose Print=>Extra calculations. The resulting window, with the required information entered, is shown below:-

Fig.11 The extra calculations window

The formula has been set up to add the 3 marks and perform integer division by 3. If you don't enter a heading the formula itself will appear as a heading. That's usually undesirable because the formula is too long and not always self-explanatory. Make sure all the fields are still selected and click on Include. When you reprint the report it should look like this:-

Fig.12 Report with extra column giving personal averages

To end this article we will add a self-calculating field to the database so that the above calculation will be performed automatically. This involves rebuilding the database. We did that with the Music database at the beginning of the article and you might need to refer back to the procedure. Only the essential differences are detailed here:-

• The field class required is Computed and the individual type is Calculated
• Enter 'Average', 'AVGE' and 3 as the Descriptor, Tag and Length and click Create
• Choose Quit design from the main menu.

The calculation formula must now be entered into the new field. When you move the pointer over this field it changes into a "pocket calculator". Click MENU and choose Field=>Formula to display the Calculation window:-

Fig.13 The calculation window with the formula entered

Note that the calculation is exactly the same as the one used previously. To make the calculation retrospective you should select Recalculate existing records before clicking OK, otherwise the field will only be updated for new records or when a change is made to one of the marks in an existing record. You can't place the caret in the calculated field to edit it directly but you can include it in a print selection. Select all four fields and print a report and you will see the same averages as before in the final column. Moreover, the new field is itself of numeric type and therefore has its own row of check-boxes in the Numeric fields window. If these are ticked you can include the same data in the report for the Calculated field as for the three individual mark fields.

Next time

• Associating string operations and user functions with Computed fields
• Using subsidiary indexes
• Attaching external files to database records

Derek Haslam