list of chapters    previous chapter   next chapter

Ch 4 - Creating a New Database


Ch 4 - Creating a New Database

Five steps are involved in setting up a new database:

(1) Create the database application shell.

(2) Design the record layout.

(3) Specify the number of records the database is to contain.

(4) Specify the primary key.

(5) Build the empty database.

4.1 Creating the database application shell

You need to have Powerbase installed on the iconbar but with no database open, i.e. "No data" should appear under the icon. Click SELECT over the icon and a Save box will appear containing the default name !Database. Type in the name of your database and drag the icon to a directory window. Remember that, for RISC OS versions prior to 4.00, the name cannot exceed 10 characters, including the initial "!". If you enter more the name will be truncated and you could end up overwriting an existing database with a similar name. You don't actually need to enter the "!"; Powerbase will insert it automatically.

If you are using RISC OS 4.xx on an E+ formatted hard-disc you will be able to use long filenames but, to do so in Powerbase, you will need to make a change in the !Powerbase.Resources.Config file. Look for the token NameLen and alter the number beside it to something bigger than 10. The change will take effect next time you run Powerbase.

If you open the newly-created application directory (Shift double-click) you will find that it contains four files (!Run, !Sprites, !Sprites22 and Data) and six directories (Indexes, Menus, PrintJobs, PrintRes , UserFuncs and ValTables). All databases expect these objects to be present so don't delete any of them.

4.2 Designing the record layout

When you created the application shell you were left with a window on screen, blank apart from a grid of blue lines, and it is here that you must design the database record. This is the lengthiest part of setting up a database, although efforts have been made to render it as easy as possible. If you have closed the window just click SELECT on the Powerbase icon on the iconbar to re-open it.

4.2.1 Simple field creation

Clicking MENU over the window brings up the New database menu on which every item except C reate field and Grid is shaded at this stage. Choosing Create field displays the Field definition window which lets you specify the characteristics of a field. Grid allows you to choose options for the grid used to lay out the fields. This grid appears only in design mode, not in a working database (except when using the Adjust format and New record format features). It may be turned off but you will probably find it a help in getting the field layout as you want it. There are options to choose the colour of the grid, whether it is represented by solid or dotted lines and what the spacing between the lines is. By default the grid has solid, light blue lines spaced 32 OS units apart and fields will "snap" to it at intervals of 4 OS units. If you prefer settings other than the defaults Save choices will ensure that they are preserved next time you load Powerbase. If you make further changes without saving them clicking Load defaults will recover your most recently-saved settings. You can return to Powerbase's original settings by Shift-clicking on Load defaults.

First decide what class of field you want to create. There are eight such classes, selected via radio buttons:

   (1) Editable
   (2) Scrollable list
   (3) Check-box
   (4) External
   (5) Computed
   (6) Stamp
   (7) Tool-pane button (or Keypad button)
   (8) Extra button

For the present we will confine ourselves to the first, which is the default selection. As well as the field class you must decide on the type of field within the class. The default offered is Unrestricted , meaning that it will accept all printable characters. We'll look at other types later.

Decide on a name for the field and enter it in the Descriptor icon. This is the name which will appear on the record window and may be up to 40 characters long. You must also enter a Tag , which is used to identify the field in search formulae and is limited to 4 characters. Next enter the Data length ; the maximum number of characters the field is to hold. Values up to 246 are allowed. Now click on Create and the field will appear on the record window. It's probably not where you want it so drag the white rectangle with SELECT to the position required. When you drop the field in its new position the descriptor falls into place too. If you want the descriptor somewhere other than to the left of the data icon (above it, for example) move it by itself after positioning the data icon. For fine adjustment re-open the Create window by double-clicking on the field and nudge the field into position using the bump icons at the bottom left corner.

To edit an existing field you can either click MENU over the field and choose Edit field or simply double-click SELECT over the field itself as above. You can also display the data for any field by choosing from the Fields Created submenu. This is also available from a menu button on the Field definition window. After making changes click on Update (Create will be shaded).

4.2.2 Deleting, inserting and re-ordering fields.

It is important to understand the difference between the physical position of fields on the screen and the internal numbering of the fields. The former is purely a matter of appearance and you may drag the fields about the screen to your heart's content, but the latter is fundamental to the way the database will function. Each field has a number which corresponds to the order in which it was created. Whenever you invoke the Field Definition window the title-bar shows either the number of the (new) field you are about to create (e.g. "New field 3") or the number of the (existing) field you were pointing at when you opened the menu (e.g. "Modify field 5"). The numbering of fields determines the order in which they will be "visited" by the caret when you are actually using the database and typing Return to get from field to field, i.e. the editing order.

A field may be deleted by bringing it up for editing as described above and clicking on Remove field. Fields which come after the deleted field will then be found to have had their field-numbers reduced by 1. Inserting a field into the middle of the existing field-sequence is obviously a little more complicated because we have to specify where in the sequence the new field goes. You need to know the number of the field which you want to follow the new one. This number is entered in the before icon before you click on Create. The layout of this part of the window should make things clear: "Create before <n>". If you examine fields which come after the insertion you will see that their numbers have increased by 1.

You can change the numbering of a field by removing it and then re-inserting it, but there is a better way. Bring the field up for editing as previously described. We will call this the "current field". Enter the number of another existing field in the same place as was used above to specify the insertion position of a new field. We'll call this the "entered field". You may then do one of the following:

4.2.3 Moving and re-sizing the bounding box

Left to itself Powerbase makes all its field icons the same height and of a suitable length to contain the number of characters specified in Data length. This may not be quite what you want. If the data length is large the field could run off the right edge of the window. You might also want to make the field taller for emphasis. (But not for displaying multiple lines. Only fields of Text block type can be multi-line. These are discussed later.) We have already seen how you can alter the position of a field by dragging with SELECT. By dragging at an edge with ADJUST you can change the size of the bounding box.

You can also specify both size and position by entering the required width and height of the bounding-box in the W and H icons and the co-ordinates of the lower-left corner in the X and Y icons of the Field creation window. All these values are in OS units (the same units as are used for plotting to the screen) but the origin is the top left corner of the record window. This means that the Y values are always negative.

Clicking on Fit automatically sets the bounding-box width to fit the data length.

4.2.4 More about tags and descriptors

Tags are very important in Powerbase. They are used when querying the database to produce reports, export CSV files etc. and also by some internal operations. Every printable field (see 3.3) must have a tag and no two tags can be the same. Descriptors are less important. They are there to provide visible labels for fields and in some cases you may not need one at all. e.g. You might want the record to look like this:

There are 5 fields here but, having given the second one the descriptor ADDRESS, you don't really want descriptors for the remaining 3 (except perhaps POSTCODE for the last). It is quite in order to have null descriptors like this, but your must give each field a tag. Suitable ones might be NAME, ADD1, ADD2, ADD3, CODE. (Remember each must be unique and not more than 4 characters.)

You can omit the tag where the Data length is 0. This allows you to create fields which are simply explanatory labels. Since there is no data in them there would be no reason to include them in a query.

4.2.5 Other types of Editable field

So far we have only used fields of Unrestricted type. Clicking on bump icons to the left of the field type, or on the menu button to the right, lets you cycle through the various types available. These are:

(a) Unrestricted Accepts any printable character.

(b) Alphanumeric Accepts all letters and numerals and common punctuation.

(c) Upper case Accepts capital letters and numerals only.

(d) Capitalise each As for (b) but forces first letter of each word to upper case. You may save a comma-sepatated list in a text file called CaseExcept of words not to be capitalised unless the first word in the field. The file should be placed inside the database directory

(e) Capitalise first As for (b) but only first letter of želd is forced to upper case.

(f) Numeric Accepts numerals, +, - and . (decimal point).

(g) Yes/No/Maybe Accepts Y, N and ? only.

(h) Date Accepts dates in the form dd-mm-yy or dd-mm-yyyy, checking that the date is valid and reporting an error otherwise.

(i) Time Accepts times in the form hh:mm:ss up to a value of 23:59:59. The time is checked for validity and errors are reported.

(j) Internet Special field for email addresses and web URLs. There is no restriction on character input, but double-clicking will call up your mail program or browser (if it has been "seen" by the filer) with the address loaded.

When Numeric is selected, certain icons in the dialogue box which are normally shaded become available. Thus, you can specify floating-point, fixed-point or integer format by means of a group of radio buttons. Fixed-point, 2-place fields may have £, the Euro symbol, or $ inserted before the number. You may also specify a maximum and minimum value for numbers which may be entered in these fields. The Numeric min icon is also used to hold the starting value for Sequence number fields (see 4.2.10).

A Date field should be either 8 or 10 characters long in order to hold the date in one of the two formats specified above. The hyphen separator in these dates may be changed via the Preferences window (see 14.1.2). Powerbase is very tolerant of the way you actually enter a date. You may type any non-numeric character as a separator: Powerbase will make sense of an entry such as 4/5/87, converting it to 04-05-87 when you type Return. Arithmetic may be performed on dates held in this type of field, e.g. you could have a Calculated field subtract the contents of two Date fields and display the difference in days (see 6.1.5).

Time fields also allow flexibility in how you enter the values. If you enter 3.45;9 it will be reformatted as 03:45:09. The colon separator may be changed via the Preferences window (see 14.1.2). You may also enter incomplete times which are, by default, interpreted as follows. A number entered on its own is treated as hours. Thus, if you enter 6 and type Return it will be reformatted as 06:00:00. Two number separated by a non-numeric character are treated as hours and minutes, e.g. 6/5 would be reformatted as 06:05:00. You may edit Powerbase's Config file to reverse this behaviour so that 6 is formatted as 00:00:06 and 6/5 as 00:05:06. When using this mode of entry you may also suppress the hours part of the display for values less than an hour, e.g. so that 6/5 is formatted as 05:06. (See 14.8 for editing Config.)

Like Date fields, Time fields may be included in calculations, e.g. to obtain the difference in seconds between two times or to average a number of times (see 6.1.4).

4.2.6 Scrollable lists

These are also user-editable but are treated separately because they are like no other editable field. They are intended for record structures which contain closely-related data items whose number might vary widely from one record to another. A music CD, for example, might have only 3 or 4 tracks but it could have 30 or more. If you are cataloguing CDs and including track information you don't really want to define 30 separate fields to allow for just a few extreme records - and find even then that the odd disc has more than 30 tracks! A scrollable list lets you make allowance for a modest number of items, adding new ones as required for individual records. Since the list is scrollable it occupies no more space on the record window no matter how many items are in the list.

When the Scrollable list radio button is selected an extension appears at the bottom of the Field definition window. This contains writable icons to enter the number of rows of the list which will be visible and the number of characters to be accommodated in each column. The list may have from 1-4 columns.

On clicking Create all that will be seen is a grey rectangle. Powerbase has calculated the height of this rectangle so that it will display exactly the number of rows you entered, and the width from an "intelligent guess" at how much room will be needed for the specified numbers of characters when rendered in the desktop font. It's best to leave the size of the rectangle alone, but move it to where you want it, of course. When the working database is created you will see the Scrollable list in all its glory.

The data contained in Scrollable lists is not held in the Database file. Data for each record is in a separate file stored in a special system of subdirectories inside the database directory. In this they resemble External fields (see 4.2.8)

4.2.7 Check-box fields

These are fields whose status changes when clicked on with SELECT. Eight types are defined:

(a) Cross/tick Displays a cross initially. A click changes it to a tick. A second click changes it back to a cross.

(b) Null/tick Similar to (a), but initial state is an empty box.

(c) Null/star Similar to (b), but second state is a star.

(d) Option button Exactly like the square option buttons seen on dialogue boxes.

(e) Radio button Exactly like the round radio buttons seen on dialogue boxes

(f) Null/tick/cross This is a three state check-box the first state being an empty box. Repeated clicking cycles through tick, cross and back to empty box.

(g) ?/tick/cross Another three-state check-box where the first is a question mark.

(h) Null/Male/Female Yet another three-state box whose states are empty box, M and F.

Check-boxes provide the fastest way of entering true/false or yes/no type data and the 3-state types allow for yes/no/undecided situations.

If you examine the file Powerbase.Resources.ValStrings you will find strings associated with each of these five types the latter parts of which read, respectively:

QNo,Yes  Q-,Yes  Q-,*  Q-,Yes  Q-,Yes  QNo,Yes,-  QNo,Yes,?  QFemale,Male,- 

These specify what will actually appear in a print-out when a check-box field is included in a report (see 3.3). You may change them if you wish, but don't omit the initial Q (although this will not appear in the print-out) and take care not to alter other parts of the string.

4.2.8 External fields

External fields allow you to link Powerbase records to pieces of data of a size and type which make them unsuitable for inclusion in an Editable field. Such items are sometimes called "BLOBs" (Binary Large OBjects) in the PC world. The field types in this class and the types of data linked to them are as follows:

(a) Text   Plain text files, such as Edit creates.

(b) Sprite  Sprite files, such as Paint creates.

(c) Draw  Drawings such as Draw creates.

(d) Text block  Plain text files, as for (a).

(e) Picture  Sprite files, as for (b).

(f) Remote  Anything!

When you create a field of type Text, Sprite or Draw it appears on the record window as a button bearing a small version of the icon for Edit , Paint or Draw respectively. Files of the appropriate type may be dropped on these buttons, whereupon the file is copied into a special system of subdirectories within the database application. Unlike fields of the Editable class the data doesn't become part of the Database file within the application (see also 4.2.6); the Text, Sprite or Draw file retains its identity and may be exported for editing in the appropriate application. Clicking on the button in the record window will display the file if the filer knows the whereabouts of the relevant editor (Edit, Paint or Draw).

Text block and Picture fields take things a step further by actually displaying a text or sprite file on the record window. The bounding box of the icon needs to be of suitable size to hold the text or sprite. In the case of a Text block too small a box will cause the text to appear truncated. None is actually lost; it just can't all be displayed. Too small a box for a Picture field will cause the sprite to spread beyond its boundaries. (N.B. To display the same sprite on each record, e.g. a company logo, define the field as of type Logo, not Picture.) The contents of a Text block or Picture field can be loaded into Edit or Paint by double-clicking with SELECT. When the edited text or sprite is saved the Powerbase field will be seen to update.

To break the link between the button and the External object choose Unlink file from the Field submenu.

4.2.9 Remote fields

These demand a special section to themselves. They allow any type of filer object (file, directory or application) to be linked to a button on the record window. They differ from Text, Sprite, and Draw buttons in that the linked object is not copied into the database directory: the object remains in its original position in the filing system and only its pathname is stored to provide a link between database record and object. Hence the term "Remote". This has both advantages and disadvantages. Because no copy is made disc space is saved and the database stays a manageable size; an important factor where large sprites or JPEGs are involved.

A Remote button normally shows a large, down-pointing arrow inviting the user to drop an object onto it. It will accept files of any type, ordinary directories or applications. Once a link is established the button shows the appropriate file icon or a blue folder or the default application icon. A single click has the same effect as double-clicking the object in a filer window: directories are opened, applications are run, files are loaded into their applications, Obey files and BASIC programs are executed. If the linked object can't be found Powerbase will be aware that something used to be there and the button will display a question mark.

Pathnames of Remote objects

The stored pathnames may be either absolute or relative , that is relative to the database. Which is used depends upon a setting in the Config file, which may be the file in !Powerbase.Resources or a "private" Config file stored inside the database directory. Look for the token Pathlen , beside which is a number (normally 255) followed by a letter: either A or R. If the letter is omitted altogether R (for R elative pathnames) is assumed. Relative pathnames are only possible when the files are on the same disc as the database: otherwise Absolute pathnames are used even when not configured.

Consider the file structure below:

The directory called Pictures holds a collection of JPEG images (sub-divided into several categories), each image being associated via a Remote button with a record in the !MyPhotos database. The full pathname of the JPEG Horse is:

ADFS::HardDisc4.$.FredsData.Pictures.Animals.Horse

If you drop the JPEG file onto the Remote button and either the database or Powerbase itself is configured for A bsolute pathnames, this is what will be stored. Everything is fine as long as the JPEG remains in the same directory on the same hard disc.

But what happens if you decide to transfer your photo collection to CD ROM? The old pathname of the JPEG is no longer valid! Even if you transfer the whole of the above structure from the root directory down, the pathname will be different because the data is no longer on an ADFS disc called HardDisc4. On opening the !Photos database you will find the Remote button sporting a question mark instead of the JPEG icon. Clicking on the button is without effect: Powerbase can't find the file on your CD because it still thinks the pathname should be the one beginning:

ADFS::HardDisc4.$.FredsData.

There is, however, a way of making this transfer to a different medium quite painless and that is to use R elative pathnames. On the original hard disc the absolute pathname of the database was:

ADFS::HardDisc4.$.FredsData.Databases.!MyPhotos

If you compare this with the original pathname for Horse you'll see that the first sections of both pathnames, as far as and including FredsData , are identical and this enables us to locate Horse without using its full pathname. Powerbase does, of course, know the full pathname for !MyPhotos when the database is open so it can find Horse by, in effect, saying: go up two levels in the disc tree from the database, then use:

Pictures.Animals.Horse

But how do we let Powerbase know it needs to go up two levels? The character '^', included as an element in a pathname, is an instruction to go up one level. What needs to be stored for the example given above therefore is:

^.^.Pictures.Animals.Horse

which is the pathname relative to !MyPhotos. When Powerbase reads this all it needs to do is prefix it with the already-known pathname for !MyPhotos and the JPEG Horse can be retrieved. If the directories Databases (containing !MyPhotos) and Pictures (containing the subdirectory Animals) were transferred to the root directory of a CD ROM named PICLIB the pathnames of Horse and !MyPhotos would be:

CDFS::PICLIB.$.Pictures.Animals.Horse

and:
CDFS::PICLIB.$.Databases.!MyPhotos

If the relative pathname has been stored, as described above, it will still be valid: it's still a matter of going up two directories (thus reaching the root) and then working down from there. You don't, of course, have to work out the relative paths yourself! Powerbase does it all for you when you drop a file on a Remote button provided R elative paths are configured.

So which should you use; absolute or relative pathnames? There are advantages and disadvantages to both. Absolute pathnames are appropriate if the Remote objects are unlikely to be moved and, especially, if the same database is associated with objects on several different discs or filing systems. The database itself can be moved anywhere you like without affecting Powerbase's ability to retrieve the data. Relative pathnames are more appropriate if the Remote objects are organised in a closely-related set of directories on one disc, especially if you intend to move the whole collection. It is, however, important to maintain the same relative positions between the objects and the database at all times.

Relative pathnames may be globally converted to absolute ones using Miscellaneous=> Convert paths. Where files are on the same disc as the database it is also possible to convert absolute to relative pathnames. Any which cannot be converted will be recorded in a file NoCanDo in PrintJobs along with the record numbers. A window is displayed showing the configured setting (which is not changed by the conversion) and radio buttons to select the type of conversion required. The Query panel is also present enabling you to target specific groups of records and select the subfile(s) to be searched. Confirmation is required before conversion proceeds.

A Remote button may be highlighted with ADJUST in order to include the stored pathnames in a report. You can examine the pathname of an object by clicking Shift-ADJUST on the button. 

4.2.10 Auto-displaying External files

The Field definition window has a Display button which is shaded for all except External fields. If you select this, and if an application for loading and displaying the file is known to the filer, then every time a record is displayed the associated External file will be displayed without the need to click on the button. This makes it possible, for example, to run a "slide show" of linked images by clicking the Play button on the tool-pane. Thomas Olsson's PD application EasyView is very fast at rendering JPEGs and doesn't open separate windows for successive JPEGs. This makes it very suitable for this type of application.

You needn't decide at the field-creation stage whether you want auto-display or not: you can always set the option later. The feature may also be toggled on and off by clicking the button with Ctrl-ADJUST.

4.2.11 Computed fields

Discussion of Computed fields is postponed to Ch 6 - Performing Calculations.

4.2.12 Stamp fields

Stamp fields resemble Computed fields in that you cannot edit them; Powerbase "stamps" the fields with the appropriate contents automatically. The following types are available:

(a) Sequence number Unique value numbered from a base value set by the user.

(b) Record number Database record number.

(c) Time Time at which record was created.

(d) Date Date on which record was created.

(e) Date and time Date and time of record creation.

(f) Day Day (of week, month or year) on which record was created.

(g) Month Month in which record was created.

(h) Year Year when record was created.

(i) Last altered Records the date and time record is first created and updates it only if the record is subsequently altered. Merely displaying the record does not cause updating.

(j) Logo Allows a sprite to be included as a logo on every record.

(k) Frame Used to enclose groups of fields to clarify layout.

These fields are stamped by Powerbase when a record is first entered and thereafter, with the exception of (i), stay fixed. (But see Ch 6 for similar types of field which auto-update.)

Sequence numbers
The base value from which sequence numbers begin is entered in the Numeric min box. Every time a new record is added the Sequence number is incremented. Be sure to set the field-length so that the biggest number envisaged can be accommodated. When a record containing such a field is deleted the sequence number is not normally re-used; a new record is given a new sequence number. This leads to gaps in the numeric sequence and you might want to reassign the numbers so that the sequence is continuous. You can do so by means of Compact sequence from the Field submenu. Before this can be used the sequence number field must be indexed and selected as the current index.

The base value of sequence numbers may be reassigned by choosing Set base value from the Field submenu. The new value must be larger than the largest sequence number currently in use.

Record number requires no further comment.

Time is in hh:mm:ss format. The colon separator may be changed in Preferences.

Dates can be in one of three formats:

Date and time uses the format: Tue, 10 Aug 1999.21:18:36 (identical to the Real-time clock).

Day The day of week may be expressed as a numeral (1-7) or textually, either in short form (Mon, Tue etc.) or in full (Monday, Tuesday etc.). Day of month and Day of year are always numbers.

Month may be a numeral (1-12), in short form (Jan, Feb etc.) or in full (January, February) etc.

Year is always a 4-digit number.

Last altered is displayed in the same format as Date-and-time

Logos require the name of the sprite to be entered as the tag of the field to be used as a logo. This means that the sprite name is limited to four characters. The icon for a Logo field (like that of a Picture field) must be large enough to hold the intended sprite. You may have several logo fields on your record, all the required sprites being included in a sprite file called UsrSprites which is placed inside the database directory.

Frames may be of either Channel or Ridge type, both of which are familiar objects on RISC OS dialogue boxes. Be careful how you use them. You should define the frame before defining the Editable or Button fields which it encloses, otherwise you won't be able to get at these because they are "behind" the frame even though the latter is transparent. If a frame is to be given a heading this too needs to be defined after the frame or the channel/ridge will run through the middle of the text. Use a field of Unrestricted type with a descriptor only and a zero data length. This can be dragged to a position where it overlays part of the top of the frame. (You can't simply give the frame itself a descriptor and drag that into position because the descriptor has an earlier icon number than the frame and the frame will cut across it as described above.)

4.2.13 Button fields

Any or all of the control buttons on the Powerbase tool-pane or keypad may be made to appear on the record window itself. They are chosen from the Tool-pane button menu and have exactly the same functions as they do on the tool-pane. These button fields let you build a customised database which allows the user to use only the features you want him/her to have access to since the tool-pane/keypad and menus can then be suppressed (see 11.2). There is also a group of Extra buttons which can only appear on the record window:

Exit duplicates the action of Close database on the iconbar menu.

Quit duplicates the action of Quit on the iconbar menu.

Menu buttons are of two types. The first associates a specific data field with a pop-up menu. Clicking the button with SELECT and choosing a character string from the menu enters it into the data field, overwriting the previous contents. To associate the menu button and data field they are given the same tag: one of the very few cases where this is allowed.

The menu data is stored in a text file whose name is the tag of the associated data field plus the word "Menu". When you click on the Menu button for the first time the text file is loaded for editing. The first line of the file reads "UserMenu" and you should change this to whatever title you want to use. If you leave it as UserMenu you will always be presented with the text file instead of an actual menu when you click SELECT on the button. Each menu choice must be placed on a separate line after the title. Save the file.

The second type of menu button is not linked to a specific field and must be given a unique tag. It is a general menu button which may be used to insert strings into any Editable or Scrollable list field and is distinguished from the previously-described field menu button by being darker grey. In use, it inserts data into the field which contains the caret. Moreover, the data is inserted at the caret without overwriting whatever is in the field already.

All user-menu files are stored in the Menus subdirectory inside the database directory. To modify a menu just click on the Menu button with ADJUST, which loads the menu file for editing.

User menus of the "field" type pop up automatically when the caret enters the associated data field. This behaviour can be disabled from the Preferences window.

Directory In its initial state this button displays the small directory icon. Dropping a directory onto it changes this to a large directory icon and clicking on the button opens a filer window on the directory. This type of button does not enable you to associate a different directory with each record: if you want to do that you need a Remote field. A Directory button is merely a convenient way of having a frequently-used directory always available, regardless of what record is displayed.

The pathname of the directory may be absolute or relative and is determined by the same Config option as for Remote fields (see 4.2.9). Two other Config options are relevant. DirOpts determines how the directory is displayed, the default being with small icons and alphabetic sorting. ButtonAtts enables the directory name to appear beneath the button. These features are fully described in 14.6.The link between button and directory may be broken in the way already described for External fields.

Run file This type of button displays an icon made up of four different filetype icons. When you drop a file onto the button the icon changes to that of the relevant file and subsequent clicks on the button will run the file. Text files are treated as Powerbase scripts (see Ch 12), other types of file (e.g. Obey files) have their normal Run action. As in the case of the Directory button, this does not permit different files to be linked to different records; it's always the same file whatever record is displayed. Filenames are, again, either absolute or relative and the ButtonAtts option in Config applies to this button too. Unlinking the file is performed exactly as for the Directory button.

Increment The simple task of increasing or decreasing a number in a field by a fixed amount can be a bit of nuisance to do manually, especially if several records are involved, so a button to do it with a single click is provided. Like the Menu button, the link to the data field (which must be Editable but need not be defined as Numeric) is supplied by giving it the same tag. Whatever you type in the Descriptor icon will appear as a legend on the button and, if you leave Descriptor blank, the default legend "Increment" will be used. The default increment is +1 and may be altered via Increment size on the Field submenu.

Evaluate is set up in much the same way as Increment, i.e it is given the same tag as an Editable field and the descriptor is used as a legend (default is "Evaluate"). The button is used to enter the result of a calculation into its associated data field and further discussion is postponed until Ch 6.

4.2.14 Mandatory fields

This isn't yet another class of fields. A mandatory field is one which must be filled in before you can access another record or close the database. It has already been noted that at least one of the primary key fields must not be blank (see 2.4.1), but any Editable field (but not Scrollable list) can be made mandatory by selecting the Must not be blank option button when designing the field. Such fields appear on a working database with red as the foreground colour. (This may be altered via the Colours window; see 14.5)

4.3 A short-cut to a working database

The third item on the New database menu is called Default database. Choosing this is by far the quickest way of getting a database up and running. Its action is to create three files inside the application directory. These are called Form (which holds the record design), PrimaryKey and Database (which will ultimately contain the entered records). The number of records in the database is set to 100, with 25 as the amount by which this should increase when the database becomes full. The primary key is defined as the first four characters of the first Editable field. The database is opened and a blank record displayed ready for data entry. Since you can alter all the above characteristics later, you might wish to use these defaults while you experiment with the database. If you want to choose the database size and primary key structure yourself follow the procedure in Sections 4.4 and 4.5

4.4 Specifying the database size

This involves two steps:

(1)  Save the Form file (which contains all the field data). The Save form file menu entry leads to a standard Save box but, since the pathname is correctly set for saving the file inside your database application, all you actually need do is click on the menu item itself.

(2)  You will now see that the Database size choice is no longer shaded and may be used to reach the Size window in which you specify the number of records in the database and the increment for expanding the database when it becomes full.

4.5 Specifying the primary key

4.5.1 General procedure

Click MENU and choose Primary key. This opens the Key Structure window. The primary key (or any other key) is derived from one or more record fields called key fields. Up to four key fields may be used to define a key but we will begin by using just one. Four pieces of information need to be specified:

(1) Choose the field , either by clicking on the bump icons or choosing from the associated pop-up menu. The default is the first Editable field in the record.

(2) Enter the word within the field from which characters are to be taken to make up the key. The default is word 1. If 0 is entered word boundaries are ignored (see 4.5.2).

(3) Enter the position within the word from which characters are to be taken. L (default) means from start of word, R means take from end. A number (n) means start at the n th character.

(4) Enter the number of characters to be taken from the word. The initially-set value is 4 but thereafter the default is the defined field length.

After entering this information click on Create and the empty Database and PrimaryKey files will be created, after which the database will open ready for you to start entering records.

4.5.2 Some illustrative examples

Key fields should be chosen with care. An ideal key field is one whose contents would never be repeated in another record. Powerbase lets you enforce this condition if you wish (see 11.2.1) but the default setting allows key duplication. Occasional repetitions may not be serious, but a field which can have only a few "values" is usually a poor choice. A customer number or membership number is the sort of thing we are looking for but your database may not contain anything like that. In a database of school pupils the pupil's name would be a good choice of key, but the form teacher's name would not, since only a small number of names would be involved, each appearing on the record of many pupils.

Suppose you decide to use a person's name, stored surname first, in a single field whose tag is NAME, as a key. You could use the whole name but it would probably be too long. The first four letters would be more appropriate but, as this is the start of the surname, you might get a lot of duplication. e.g. PRESTON and PRESCOTT would both have the key PRES; and this is before we even consider people whose surnames are identical. To get round this problem, Powerbase lets you construct an alphanumeric key from characters taken from up to four successive words. You will seldom need to go so far. In the present case a five-letter key made up from the first four letters of the surname and one letter of the forename would be good enough for most purposes.

Set up the Key Structure window as follows:


          Field  Chars     Word      Position
1st row:  NAME     4         1          L
2nd row:  NAME     1         2          L

This works quite well. Smith Peter and Smith Janet would have the keys SMITP and SMITJ. Duplication can still occur, but not often enough to be a serious problem.

Further examples illustrate the use of letters from different parts of words in the key field. The following settings of word, position and characters would produce the keys shown from the name Herring Albert. (a)-(d) use the Surname only, (e) and (f) use the Forename only, the rest use both names:

          From Surname                 From Forename
      Chars     Word   Pos        Chars     Word   Pos     Key
(a)     5        1      L           -        -      -      HERRI
(b)     4        1      R           -        -      -      RING
(c)     3        1      2           -        -      -      ERR
(d)     6        1      4           -        -      -      RING    (stops at word end)
(e)     -        -      -           3        2      L      ALB
(f)     -        -      -           4        2      R      BERT
(g)     4        1      L           4        2      L      HERRALBE
(h)     3        2      L           3        1      R      ALBING
(i)     3        1      4           2        2      4      RINER
(j)     7        0      R           -        -      -      GALBERT (ignores breaks)
(k)     6        -      4           -        -      -      RINGAL  (ignores breaks)

Take particular note of what happens if the word number is entered as 0 (or left blank). All breaks between words are then ignored. The field is treated as if the blanks between words had been removed leaving a single long word which is then subjected to the process specified by the position and characters icons. When, on the other hand, the word number is 1 or greater the scanning for characters stops when the end of the word is reached so that the key might be shorter than the character length specified. Example (d) illustrates this.

4.5.3 Using more than one field in a key

You might, of course, want to store surname and forename in different fields. Let's call their tags SNAM and FNAM. To produce the five-letter keys given in our first example the key definition would be:


          Field  Chars     Word      Position
1st row:  SNAME    4         1          L
2nd row:  SNAME    1         1          L

and the keys generated will be exactly the same as they would be for the single field NAME.

There are databases where no single field is suitable for constructing the primary key. In a database of classical music, for example, there would probably be one field for the composer's name and one for the name of the work. Neither is much use individually; the former could contain many occurrences of Mozart or Beethoven and the latter many repetitions of Symphony No. 5 or String Quartet in D minor.

This is the kind of situation where you might want to use two fields and as many as four words, taking one word from the Composer field and three from the Work field using, say, 4, 3, 3 and 2 characters respectively from them. The following work would then yield the key shown below:

 BEETHOVEN Symphony No. 5    BEETSYMNO.5

This is less than ideal: part of the key (NO.) conveys no useful information. The problem is even more acute in the following case:

 MOZART Piano concerto No. 23    MOZAPIACONNO

The number is the 5th "word" and forms no part of the key: all Mozart's piano concertos would have the same key! What we really want to do is ignore insignificant words like "No.", "A", "The" when constructing keys. We can do so by entering a list of the words to be ignored in the Ignore icon on the Key Structure window, using commas to separate the words. If we enter No.,A,The,the,of in the Ignore field the works mentioned above would be given the keys:

 BEETSYM5  and  MOZAPIACON23

An entry such as MAHLER The Song of the Earth would yield the key MAHLSONEAR, and BRITTEN A Ceremony of Carols would give BRITCERCAR.

Additions and alterations to the Ignore list and Split characters list (see below) can be made at any time by choosing Index=>Show details (Ctrl K) from the main menu. Click Modify after making changes.

4.5.4 Further refinements

We can do even better. It is more natural to write "No.23" (no space) than "No. 23" but, so far, we have regarded only the space character as a word-separator. If we can make the field split at characters such as full-stop, hyphen, comma, semicolon and colon the key structure will be much more versatile. We can achieve this by entering the required Split characters as a string, i.e. .-,;:

Where numerals form part of the key, but the key isn't wholly numeric Note 1 there can be a problem with the ordering in the index and hence the order of records in a report. The problem would be particularly bad if we were cataloguing all Haydn's 104 symphonies. Using the key structure devised earlier this would involve keys such as: HAYDSYM102, HAYDSYM17, HAYDSYM45, HAYDSYM6

Surely that's not the order they should be in? It isn't, but that's the order we'd get! The reason is that the computer doesn't regard numerals in a character string as anything special. They are merely characters, no different from letters and punctuation. Each character is represented internally by a number called the ASCII code and the ordering of characters is determined by these codes. Any string of numerals beginning with "1" therefore comes before one beginning with "2", even though the strings might be 1000 and 20. There is a way round this problem and that is to pad all our numbers to the same length by adding spaces to the left of the string. Powerbase will do that for you in keys if you select Justify numbers (not Pad with spaces ! That adds spaces to the end.) The four keys shown above will then come out as follows:

               HAYDSYM  6
               HAYDSYM 17
               HAYDSYM 45
               HAYDSYM102

and will appear in the correct order because the ASCII code for a space is less than that of any numeral. Corpus font is used so that the justification of the numbers is clearly visible.)

4.5.5 Other matters concerning keys 

You will have observed that a word shorter than the number of characters specified is used just as it is, resulting in a short key. There might be times when you want short words padding out with spaces to give a key of full length and selecting Pad with spaces in the Key Structure window will do so by adding the requisite number of spaces to each section of the key. Don't confuse this with Justify numbers (see 4.5.4).

Another factor to be decided is whether the indexing is to pay attention to the case of letters, i.e. if they are capitals or small letters By default indexing is not case-sensitive. Thus if a record has the word "Horse" as the contents of a key field and the first four letters are used as the key then the entry in the index will be "HORS" and you may search for it by entering "HORS", "hors", "Hors", "hOrS" etc.

This may not be what you want. If you require indexing to be case sensitive select the button labelled Case. Forcing to upper case as described above does not then take place: keys are inserted and strings are searched for "as is". The field containing "Horse" will be indexed as "Hors" and only that precise combination of upper and lower case letters will successfully find it.

Alphanumeric keys will be ordered according to the ASCII Note 2values of the letters. Since lower-case letters come later in the ASCII table than upper-case ones a record containing "dog" would appear after one containing "Horse", whereas one containing "Dog" would come before "Horse".

Omit null keys is shaded at this stage since null primary keys are not allowed. Only if you create a subsidiary key do you have to decide whether to include nulls in the index or not.

4.6 Building the empty database

Once you are satisfied click Create and blank Database and PrimaryKey files will be created. The record window is redrawn and you may start entering data at once.

4.7 Renaming a database

Use the Rename database option on the Miscellaneous submenu to do this. If you rename via the filer your database will lose its 'PB' icon and revert to the default application icon. Rename database is duplicated on the iconbar menu so that you can rename at the record-design stage.




Notes

1. It is not usually sensible to build up keys derived from Numeric fields out of separate bits in this way and, indeed, Powerbase won't allow you to do so. Numeric keys should use one key-field only, the key being the actual numeric value of the field contents. (back)

2. ASCII stands for American Standard Code for Information Interchange. Each uppercase letter, lowercase letter, numeral, punctuation mark etc. corresponds to a number in the range 32-255. (Numbers below 32 are non-printing "control" characters.) e.g. the numerals 0-9 have consecutive ASCII values from 48-57, the uppercase letters have consecutive values 65-90 and the lowercase letters from 97-122. The numbers are used to store the characters within the computer and on disc. Ordering strings according to their ASCII codes means that those beginning with a numeral will come before those beginning with an uppercase letter and the latter will come before those beginning with a lowercase letter. (back)



list of chapters    previous chapter   next chapter