Using Powerbase - Part 3
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:-
Note the spaces on either side of OR; they are essential. This method of querying is fine when only a couple of 'targets' are to be matched but it can quickly become clumsy for three or more:-
See what I mean? There is, however, a shorter way. The search formula:-
produces the same result as the longer version. Suppose now that we want to extract all opera recordings on LP. We can do that with:-
In other words, the only records which will appear in the report are those which have 'CD' in the Medium field and 'OP' in the Category field: simply matching one or the other isn't good enough. OK, how do we combine both OR and AND in a search formula to list all opera recordings which are on either CD or LP? It looks straightforward; how about:-
Well, this sort of thing might give you the correct selection of records but you shouldn't depend on it! Read the above carefully or, better still, ask someone else to read it and ask whether the logic is clear. This is one of the cases where the resulting report won't be what you want. Each OR and AND is interpreted as it is encountered so what you would actually get from the above formula is a list of all CDs (whether operatic or not) and all operatic LPs. In other words the first criterion (MED=CD) gets matched and, since the next item in the formula is an OR, Powerbase prints the record regardless. When the next part of the query (MED=LP) is matched, Powerbase sees the AND, knows there's another criterion to be met, and only prints the record if CAT=OP. To make the logic clear we need parentheses:-
You might like to explore the matter further using the sample database Elements. If you don't already have this you will find a copy in the zip file EXAMP on this disc. Select the fields 'Name' and 'Group' and enter the search formula:-
The last part of this formula may look a little strange: the $ is a multi-character "wild card" and the search criterion implied is "any name ending in IUM". The six records printed include one (BORON) which doesn't have such an ending. Try again with the groups reversed:-
(Tip: Make sure the record window has the input focus and type Ctrl-O. This retrieves the old search formula which you can then edit.) This time nine records are printed, three of which don't end in IUM (CARBON, LEAD and TIN), and BORON is not included! Now try:-
The five elements printed all have names ending in IUM and exactly the same list is produced if the group numbers are swapped over.
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 3So it's:-
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 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 timeIn the next article we will look at:-