---

Using Powerbase - Part 2

by Derek Haslam

The first article in this series described the construction of a simple database of recorded music. The present article will describe some refinements to the database and go into more detail about querying the database and printing reports.

Powerbase v.8.22 was included on the last Risc World CD. You will find the latest version (8.27) on the present CD in the zip file PB827. This contains important new features so be sure to upgrade your copy and use v.8.27 for the exercises which follow. If you didn't create the example database MusicBase as described in the last article you will find a copy of it on the present disc as a zip file called MUSIC1 and should use it in preference to the one supplied last time.

An alternative to the tool-pane

So far we have used the tool-pane attached to the left-hand edge of the record window. Some people like attached tool-panes, some hate 'em, so we'll start by giving you a choice. Shift/double click on the !Powerbase directory to open it and look inside the Resources subdirectory for a text file called Config. If you've just unzipped v.8.27 and haven't run it yet the file will be missing, in which case copy the Config file from Initial into Resources.

Load Config into your favourite text editor and look for the line which says Tools 2. Change it to Tools 1 and save the file. If you now run Powerbase and open a database you will find that the tool-pane has been replaced by a separate window which looks like this:-

We call this the keypad. It contains more buttons than the tool-pane but the latter has the same functionality. For example, the two outermost buttons on the top row display the first and last records in the database, and the buttons next door to them display the records before and after the one currently viewed. The tool-pane only has the 'last record' and 'next record' buttons, but the functions of the two missing ones are accessed by clicking with ADJUST instead of SELECT. The same applies to the other three mirror-image pairs of buttons on the keypad.

You can reduce screen clutter by clicking the toggle-size icon at top right of the keypad. This restricts it to the most-used buttons which are on the first two rows:-

If you close the keypad when it's not required you can recover it by choosing Show keypad from the main menu, or simply pressing the Tab key.

If you wish to return to the tool-pane edit the Config file to read Tools 2 again. This, and any other changes to Config, should be made to the copy in Resources. The one in Initial should not be altered so that you can always revert to the default settings by deleting Config from Resources then re-running Powerbase.

Now for some changes to MusicBase.

Changing the primary key

When we created our database we allowed Powerbase to choose the primary key structure. This always results in a key consisting of the first four letters of the first editable field. Good enough for some purposes maybe, but not here. Remember that the primary key is our means of calling up a specific record and, whether your music collection is classical or something quite different, it's unlikely that four letters from the Title field specify every recording in a useful way. The ideal situation is where the primary key is unique. Sometimes it is essential that a record be retrievable without any ambiguity and Powerbase can be configured to enforce primary key uniqueness if required. If every record of a database contains something like a customer number or membership number then that might be the ideal choice for a primary key. For MusicBase it probably doesn't matter if the same primary key occurs more than once but we don't want lots of records with the same key. We need to think a little harder.

The problem is particularly evident with a classical collection because the Title field begins with the name of the composer who might have written hundreds of works. The second word in the field is no better: it will quite often be 'Symphony' or 'Piano' or something of the sort. Combining the first two words, giving a key which identifies the composer and goes some way towards specifying the type of work, is an improvement. Let's try that and see where it gets us.

Click MENU over the Powerbase icon on the iconbar and choose New primary key from the Utilities submenu. The key-definition window will appear:-

If you now look at the title-bar of the displayed record you will see that the key now has 7 characters; 4 from the first and 3 from the second word. Note also that the record displayed as first in the database is not the one which qualified as 'first' under the original key structure. Repeatedly clicking the 'next record' button will confirm that all records now have keys based on the new structure. Has it helped at all? We can get a better idea by printing a report showing the titles of all records in the database. The previous article described how to do this but, since report creating is one of the most common operations with Powerbase, I'll repeat the instructions.

• Highlight the Title field for printing by clicking with ADJUST.
• Bring up the Match window by choosing Print from the main menu. (Pressing the Print key on the keyboard has the same effect).
• Select the option button Key. This will cause the primary key to be printed along with the Title field.
• Click on the default action button (Print) or press Return.

You will see that the keys of all 10 records in MusicBase as supplied are identical. But we've hardly begun yet! Beethoven wrote 9 symphonies all of which would have the same key BEETSYM. Worse still, he wrote 32 piano sonatas and 5 piano concertos all of which would have the key BEETPIA! The verdict then is 'Better, but must try harder'. Including the third word would distinguish 'piano sonata' from 'piano concerto' and including the fourth would bring in the number. Notice that, although it would be usual to refer to 'Symphony No.6' or 'Piano concerto No.24' the 'No.' part conveys no useful information and would, in the examples just quoted, occupy 3 of the characters designated for the 3rd and 4th words respectively. If you really wanted to include it you would need to increase the number of characters from these words by 3 in order to include the number of the work.

Go back to the iconbar Utilities menu and call up the key definition window again, filling it in as follows:-

Click Create to rebuild the index. Print a list of Titles and primary keys as before. There are a few points to make about the new keys.

• Keys may be shorter than the defined 12 characters.
• It doesn't matter if the title has fewer than 4 words, or if a word or number is shorter than the specified length. 'Mahler, Symphony 3' has the key MAHLSYM3. There is no fourth word and the number takes up only one of the three characters allowed for the third. Note, however, that if you select Pad with spaces before clicking Create, any such short word would have spaces added to bring it up to the required length. If you rebuild the index again with this option switch selected and look at the key for 'Beethoven, Symphony 6 in F major', you will see that the key has changed from BEETSYM6IN to BEETSYM6 IN. The '6' has been padded to make it 3 characters.
• When searching for a record by means of the primary key you need not type in the whole key. If the 3rd symphony is the only work by Mahler in the database then MAHL by itself will be enough to find it. The fact that some keys end with the superfluous word 'IN' therefore doesn't matter.
• We can improve the Title 'Beethoven, Piano Sonata in B flat, Op.106' by inserting the sonata's number (29) before 'in'. If you do this and then try to move to the next record Powerbase will ask if you really want to change the primary key. Click OK and the key will be changed to BEETPIASON29, allowing other Beethoven piano sonatas to be added without key repetition.

A number of other things should be mentioned before we leave the subject of primary keys:-

• If the database has other kinds of music in it the name of the artist, band etc. might be of equal or greater importance than the album title. The key-definition window allows you to choose words, or fragments of words, from different fields as well as from the same field. For example, one word from each of four fields could provide the key, or two words from each of two fields.
• The characters need not be the leftmost ones in the specified words; you can take characters from the right-hand end of a word by specifying the Pos(ition) as R instead of L. You can even extract characters from the middle of a word by putting the numeric position of the first character in Pos.
• Putting 0 as the word number has a special significance. The entire field is then treated as a single word with all intervening spaces ignored.
• The default situation is for keys to be non-case-specific. All letters are stored in the index as uppercase and whatever you type in when searching by key is converted to uppercase. Thus 'Mahlsym3' will find Mahler's 3rd symphony just as well as MAHLSYM3. You can make the index case-specific by selecting an option button in the key-definition window before clicking Create, but it's not usually a good idea because you would have to type the letters with exactly the right case when searching by key.
• Details of the key currently in use can be viewed (but not altered) by choosing Index=>Show details from the main menu or by typing Ctrl-K.

Adding a user menu

Consider the field 'Medium'. The range of contents for this field is very limited. LP, CD and Cassette are all we have in the sample database. You might want to add DVD, MD (Minidisc) or Video but we're certainly not dealing with more than a handful of 'values' for the field. Wouldn't it be more convenient to pick them off a menu rather than type them in? We will now add a button to the record window which can be used to pop up a user menu.

• From the iconbar Utilities submenu choose Alter format. The record window will display the blue grid which you saw when designing the database.
• Click MENU over the window and choose Create field. The field-definition window will appear.
• Select the radio button Extra button.
• Click on the pop-up menu button at the top right-hand corner and choose Menu.
• You will see that the Tag box contains 'LAB', the same tag as the Label field which is the last of our four data fields. We don't want to associate our menu button with that field, however, but with the Medium field. It's tag is MED, so change the tag of the new button to MED and click Create. (N.B. This is the only circumstance in which the same tag may appear twice.)
• Drag the button so that it sits neatly beside the Medium field as shown below then choose Quit design from the main menu. The record window returns to its normal 'operational' mode with the new button in place.

Naturally, Powerbase cannot guess what items you want your user menu to contain! When you click on the button a text file appears explaining what to do next. Replace Choice 1 etc. with CD, LP and so on, one per line. Delete the explanatory paragraph and be sure to change the word 'Title' to something like 'Medium'. (If Powerbase finds 'Title' as the first word it assumes the menu hasn't been written yet and displays the text file instead.)

Save the menu using the supplied pathname. The file containing the menu definition is called MEDmenu and lives inside a submenu within the database called Menus. You will now find that clicking on the button displays your menu and whatever you choose from it appears in the Medium field alongside. You will also see that whenever the caret enters the Medium field, whether as a result of a keypress or a mouse-click, the menu pops up automatically. If this feature annoys you it can be turned off from the Preferences window (accessible from the iconbar menu) by deselecting the Auto-open user menus button. If you want your database to 'remember' this, or any other, setting in Preferences click on Save choices.

More on-screen buttons?

You might think this is overdoing it a bit, but we can easily add Print and Exit buttons to the record window. The procedure is similar to adding the menu button but simpler because once we've placed the buttons on the window that's the end of it. Follow the same steps as you did for the user menu but choose Print from the Extra buttons menu. These buttons don't require a tag to be entered. After clicking Create repeat the process choosing Exit. Drag the buttons to where you want them and choose Quit design from the main menu. The buttons do exactly what you might expect.

Adding a scrollable list

One of the things you might want to have on your music database is a listing of tracks on the CD, LP etc. You could create, say, 10 fields called Track 1, Track 2 etc. but this isn't very satisfactory because the number of tracks on a disc can vary widely. There are classical CDs with no more than 2 or 3 tracks and most have fewer than 10, but some have 30 or more. You can be quite sure that however many fields you think you need, sooner or later you'll find a disc with one more!

A scrollable list overcomes this problem very neatly by allowing you to add extra rows to the list as and when they're needed. Once again we need Utilities=>Alter format. When you choose Create field and open the field-definition window the Scrollable list radio button will be already selected and extra icons appear at the bottom of the window. Such lists can have up to 4 columns. Let's say we want to store for each track:-

• Track number
• Track title
• Timing

Click the top-right menu button and choose 3 columns. You now need to give the list a tag and also specify the number of rows of the list which are to be actually visible. 4 is a good choice. You also need to enter the number of characters which each column is to display. The track number will occupy 2 characters at most, but the title needs much more room, say 35, and the time (in mins and secs) requires 5 characters. When the above details are entered the window will look something like this:-

You will see that we haven't entered anything in Descriptor. We don't have to; descriptors are, as explained in the first article, optional and merely act as visible explanatory labels. This is, however, a good opportunity to use a descriptor placed somewhere other than its usual position to the left of the field. Enter in the Descriptor box the string 'TitleTime'. Place the caret just before the 'T' of Time and insert as many spaces as the icon will accept. When you click Create the field appears simply as a large white rectangle with the descriptor (only partly visible) on the left. Drag the descriptor to a position above the field rectangle so that 'Title' is about halfway along and 'Time' is about 1cm from the right edge. If you move the field rectangle again the descriptor will jump back to its default position, so first make sure the rectangle is where you want it, then position the descriptor. Now choose Quit design from the main menu. The record window should look something like this:-

Entering data into the scrollable list is straightforward. If the caret is in the preceding field (Label) pressing Return will move it to the first column of the first row. You can, of course, place it there using the mouse. Pressing Return repeatedly moves the caret from cell to cell until it is in the last column of the fourth row. Since the scrollable list is the last editable field in the record another Return will produce a blank record. If, however, you hold down Shift while pressing Return a new row is added to the list. You can do this as many times as you like and the vertical scrollbar will show that only part of the list is actually visible. This is how I store all the details of Handel's 'Messiah' (two CDs of 20 tracks each) at the cost of very little screen 'real estate'. When you add a row as described the list scrolls up so that the new row becomes visible. An alternative way of adding a row is to press Insert. This works wherever the caret is in the list, but the new row is always added to the end: there is, at present, no way of inserting a blank row into the middle of a list.

It's all too easy to add rows to a scrollable list by accident, so how can they be got rid of? A final blank row can be deleted with Shift-Insert. (We can't use Delete because of its normal character-deleting function.) This is quite safe since it only works when the caret is in the last row, and only then if the row is blank. To delete a row containing data, place the caret anywhere in the row and press Ctrl-Shift-Insert. This is a key combination you're hardly likely to use without deliberate intent.

The cursor-up and cursor-down keys function differently in a scrollable list from the way they do in other types of field. They normally move the caret to the previous and next fields respectively, 'wrapping round' when the first or last field is reached. This is also the way they work when in the first or last cell of a scrollable list but, when elsewhere in the list, they move the caret up or down staying in the same column. It is also useful to know that Shift-cursor-up moves the caret to the preceding cell, i.e. it has the opposite effect to Return.

More about reports

For the following exercises it is suggested that you use the database MUSIC2 which will be found in the same directory as MUSIC1 and PB827. It uses the format arrived at above and contains a wider variety of data than MUSIC1, including some in the scrollable list.

This is where we need to get to grips with the Print options window:-

There are three ways of accessing this window:-

• Choosing Options from the Print submenu
• Pessing Ctrl-Print on the keyboard
• Clicking the icon to the left of the Cancel button in the Match window

The last of these methods is very handy. If you examine the icon in question you will see that it resembles a blank window. Click on it to open the options window. There are three possible destinations for the output of a report and the one selected is Window. If instead you select Text file or Printer the icon on the Match window changes to reflect the output destination. Note that when Printer is selected the icon on the Match window will be greyed out if no printer driver is loaded so you won't be able to open the options window by clicking on it. You probably also noticed that the large button Lots more, next to the Printer radio button is greyed out unless Printer is selected as the destination. For the present, re-select Window.

There are two formats available for Window (or Text file) output. Horizontal format is so-called because the selected fields are printed so that each record occupies a single horizontal line. The records are aligned so that the fields form columns, right-justified for numeric data, left-justified for everything else. Vertical format places each field on a line by itself, so that the fields selected for printing are stacked vertically. To see the difference between the two formats keep the options window open and click ADJUST on each of the first four fields to highlight them. Click the Print button on the record window and press Return. The output is in Horizontal format, neatly aligned in four columns, with the tags of the fields as headers. Change to Vertical format and print the list again. Each record now occupies four lines labelled at far left with the tags. Look further down the options window for the Headings section and try the effect of selecting descriptors or none instead of tags.

Try changing the Spacer which determines the separator between columns in Horizontal format. '1' means 'one space'. You can increase this number if you wish and can also enter a character string (up to five characters long) to use as a spacer. Try ::, for example. Particularly useful is the vertical bar |, especially when used with a space or two on either side.

The previous article mentioned that a report in a window can be sorted (in Horizontal format) on any column by clicking MENU over the column and choosing Sort. The sort may be in ascending or descending order. The options window also provides the facility to specify such a sort before the report is generated. Select the Sort on button and enter MED in the writable icon. Print the list in vertical format and you will find it pre-sorted by Medium. Click the up-pointing blue arrow next to the writable icon and print again. The report appears sorted in reverse order. You can, if you wish, specify the sort column by number instead by field tag, e.g. entering 3 would have had the same effect as MED.

Experiment with the option buttons on the right of the window. Some will have no apparent effect for this report however. The three icons labelled Colours may be used to select different colours for Headers and footers, Body text and Rules. Clicking on any of these icons with SELECT cycles through the 16 standard desktop colours; ADJUST cycles in the reverse order. Only foreground colours may be specified since these options are meant mainly for use when printing on paper which will (usually) be white.

So far we've avoided including the scrollable list in our reports. It's probably best to cancel the existing print selection (use Ctrl-Z) and select just the Title and scrollable list fields for what follows. Bear in mind that, appearances to the contrary, a scrollable list is a single field and we can't choose to print just parts of it; it has to be all or nothing.

The default option is to print the entire list as a single row. Items from the same row of the list are separated from each other in the report by a comma plus a space, and the final item from each row is followed by a semicolon plus two spaces. These may be replaced by character strings of your own choice by editing the Sep and Row end icons in the options window. If Shrink row is not selected each item from the list will be padded to the maximum defined column width, e.g. anything from the Title column will be made up to 35 characters. This can make for extremely wide reports and would not normally be used. Even with Shrink list selected the report can take up a lot of horizontal space, which is where the alternative format, printing As columns, comes into its own. In this format each row of the list appears as a row in the report. The Row end string is not used and its icon is greyed out and one or two spaces would be more appropriate than the comma plus space for the Separator. A very neat layout is produced, with the columns of the scrollable list accurately reflected in the report, but printing is slower than in the Single row format and the report can occupy a good deal of vertical space. You pays yer money and takes yer choice!

Using the printer

Selecting Printer as the output destination gives us, as remarked earlier, Lots more. Clicking on this button displays the Printer setup window:-

All the reporting options already described for Window output may also be used for hard copy, but we now have, in addition, a choice of fonts and sizesfor headers/footers and for body text, the option of printing the report sideways ('landscape' mode) and the ability to print in up to four columns with a user definable 'gutter' between them; very handy when the report contains a large number of lines but the lines are all short. We can choose our margins or accept the printer's defaults by selecting As printer. If you choose a narrower margin than the printer is capable of it will be overridden by the printer's own minimum value. This is most likely to occur when using inkjet printers, many of which are only able to print to within 15mm of the bottom of the paper.

When using Window output there is no restriction on the width of the report; it can be far wider than the screen and you can still examine it all by horizontal scrolling. Paper, however, doesn't stretch to fit the output! Powerbase will determine whether or not the report will fit between the side margins. If it won't you will be warned that certain fields will be truncated or omitted altogether and given the choice of proceeding regardless or aborting the print job. Powerbase does try to suggest a smaller point-size at which the whole report might be printed but, if this comes out to be less than 6pt, it will tell you that the report can't be printed at a useful size. Problems like this are almost certain to occur if you try to print scrollable lists in single-row format.

We also have two additional formats: Table and Label. Neither is available for Window output because there is no point to these formats unless the report is put on paper. Table format resembles Horizontal format in that each record forms a row and the fields align in columns, but the data is enclosed in a ruled grid which may have a user-defined number of additional blank rows and blank columns of specified width. This feature makes it very suitable for applications such as printing mark-lists in a school, where names are pre-printed and marks are to be written in by hand. The table can seem a little cramped using the default line-spacing of 120% and column separator of one space; a 150% line-spacing and 2 or 3 space separator are recommended.

Label format supports special label stationery from '2-up' to '4-up' and variable label sizes which may be specified in inches, points or (most likely) mm. When one uses sheets of labels there always seems to be a partly-used sheet in the box and we don't want to waste it. To make use of it, put the partly-used sheet in the printer first but get it the right way up! The used part, i.e. the top edge where there's just backing paper, should be the edge inserted first into the sheet feeder. Count from this top edge to find the number of the first remaining label and enter the value in the Start at label icon. Once the first sheet has been printed printing will automatically start from the first label on subsequent sheets. Do experiment with ordinary paper before committing yourself to expensive label stationery!

Next time

In the next article we will look at:-

• Special types of field for special purposes
• Controlling data input by means of a validation table

Derek Haslam