Home   Index   list of chapters    previous chapter   next chapter   quick tutorial  
 

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.
top of page

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 or later 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 (see 14.8). 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, Data and History) 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. top of page

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 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
To 14.1.4 (Option buttons: keep descriptor with data)
create/png

Clicking MENU over the window brings up the New database menu on which all but two items are shaded at this stage. Grid allows you to choose options (see 14.3) for the grid used to lay out the fields. This grid appears only in design mode, not in a working database. It may be turned off but you will probably find it a help in getting the field layout as you want it. Choosing Create field displays the Field definition window which lets you specify the characteristics of a field.

First decide what class of field you want to create. There are eight such classes, selected via radio buttons. For the present we will confine ourselves to the first (Editable), 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. Since the tag is used to reference the field the same tag may not be used for more than one data field. Next enter the Data length — the maximum number of characters the field is to hold. Values up to 246 are allowed. (But see Note 1.) 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 Field definition window by double-clicking on the field and nudge the field into position using the bump icons at the bottom left corner of the window.

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 menu. This is also available from a menu button on the Field definition window. After making changes click on Update (Create will be shaded).

If you're creating a lot of similar fields you may find the Dup button useful. This is shaded when you are creating a field but becomes available when you access an existing field. When clicked it creates a copy of the current field, identical in size, type and data-length, but with the descriptor New field 1 and the tag 1XXX. A second click creates New field 2 with tag 2XXX and so on. You can easily alter the tags and descriptors and move the fields to where you want them.top of page

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 with higher numbers than 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 either the number or the tag of the field which you want to follow the new one and you can get that from the Fields created menu. This number or tag is entered in the before icon before you click on Create. Alternatively, you can enter the tag automatically by placing the caret in the before icon then choosing the field from the Fields created menu. The layout of this part of the window should make things clear: "Create before <field>". 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 up the field 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:

top of page

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 in 4.2.8) We have already seen how you can alter the position of a field by dragging with SELECT. By dragging a bottom or right 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, otherwise the field would be off the top of the window.

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

4.2.4 More about tags and descriptors
To Ch 2 (Browsing and Editing)
To 3.5.1 (The construction of search formulae)
rec/png 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 normally no two tags can be the same (but see 4.2.13). 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 the one on the right. 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 or there would be no way of referring to it in a search formula. 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 with no directly associated data — a heading above a column of fields, for example. Since there is no data in them there would be no reason to include them in a query.top of page

4.2.5 Other types of Editable field

To 3.3 (What types of field can be included?)
To 4.2.12 (Stamp fields)
To 14.8 (Config files)
UnrestrictedAny printable character
AlphanumericAll letters and numerals and common punctuation
Upper caseCapital letters, numerals, space and full-stop
Capitalise eachAs for Alpahnumeric but forces first letter of each word to upper case. You may save a comma-separated 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
Capitalise firstAs for Alphanumeric but only first letter of field is forced to upper case
NumericNumerals, +, -, space and decimal point
Yes/No/MaybeY, N and ? only
DateFour formats are available:
dd-mm-yy (e.g. 22-01-06)
dd-mm-yyyy (e.g. 22-01-2006)
dd Mth yyyy (e.g. 22 Jan 2006)
Day,dd Mth yyyy (e.g. Sun,22 Jan 2006)
TimeTime in the form hh:mm:ss up to a value of 23:59:59.
InternetSpecial field for email addresses and web URLs. Double-clicking invokes mail program or browser with the address loaded.
PhoneSpecial field for phone numbers. Accepts numerals and space only. Can be used to tone-dial the number (see 4.2.5.4).
Cross referenceDouble-clicking on a primary key or record number in the field attempts to retrieve referenced record (see 4.2.5.5).
Via button onlyThis type of field cannot be edited directly but is "editable" in the sense that its contents can be changed via an associated user menu or certain other button fields (see 4.2.13).
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 access the various types available. The table lists them along with the type of data they will accept.

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 fields can be used to hold money data and may have £, the Euro symbol, or $ inserted before the number. They should, of course, be fixed to two places when used in this way. 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).

4.2.5.1 Floating-point numbers in reports

Floating-point numbers are usually right-aligned in reports, just like integers or fixed-point numbers. This is not always satisfactory, especially when a report is sorted on a column of floating-point numbers. (Try it on the Relative atomic number field of !Elements to see what is meant.) What's really needed is the ability to display such numbers so that all the decimal points are vertically aligned, regardless of how many digits come before or after the point or even when there are none.

To make this possible the user needs to specify the maximum number of decimal places the field is to contain and enter this value in the places icon which is otherwise used to specify decimal places in fixed-point fields. Entering zero or leaving the icon blank causes the resulting data field to behave in the default way, i.e. right-alignment. Some care is necessary in specifying both the number of places and the total field length; if you intend to have data entries of, say, 0.0001, 17.5, 876.22, and 1000 you would enter 4 in the places icon and at least 9 for the field length, thus allowing for the decimal point itself and up to 4 digits both before and after the point. If the preceding values appeared vertically above each other in an on-screen report all the decimal points would line up, including the implied point after 1000. Moreover, sorting the report on such a column would correctly place the numbers in ascending or descending numerical order. For the alignment to work on a hard-copy report you will need to choose a mono-spaced font such as Corpus for the body text. If a proportional-spaced font such as Trinity or Homerton is chosen the decimal points will not line up.

There's nothing to prevent you from typing in more decimal places than you have specified in the field definition. Excess spaces will be trimmed in the report to prevent the decimal points getting out of alignment but the precision of the typed entry is retained in the record. Thus, if your point-aligned field is 9 characters long and you specify a maximum of 4 decimal places (as in the previous paragraph) you could enter 143.96783, which would fit in the field. It would appear as 143.9678 in the report but the record itself would still show 143.96783.href="#top">top of page

4.2.5.2 Date and time fields

Date fields demand some explanation. The separator between the parts of dd-mm-yy and dd-mm-yyyy types need not be a hyphen; you can change it in the Preferences window (see 14.1.2). Powerbase is very tolerant of the way you actually enter these dates and lets you type any non-numeric character as a separator. It can make sense of an entry such as 22/1/6 and convert it to 22-01-06 or 22-01-2006 when you type Return. You can even leave out the separator altogether as long as you include zeros to make up the correct number of digits, thus 220106 is also acceptable and so is 22010006.

Even more usefully the dd Mth yyyy and Day,dd Mth yyyy formats can be entered as if they were the two simpler types! You might care to create a database containing four date fields, one of each type, and entering something like 21/5/43 in each. Powerbase will format the contents of the four fields as 21-05-43, 21-05-1943, 21 May 1943 and Fri,21 May 1943 respectively.

There are times when you want a date field to hold only the month and year, the day being either unknown or irrelevant. References to articles in a monthly magazine, for example, might require a field which can hold such data. Powerbase has no dedicated date fields of this kind but you may omit the day from the date fields in the table above. They will format correctly as mm-yy, mm-yyyy, or Mth yyyy even though they don't fill the field completely. They will also index correctly and will behave properly when sorted in a column of a report.

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 numbers 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 the !Powerbase.Resources.Config file (see 14.8) 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).

4.2.5.3 "Button only" fields

The "Via button only" type of field differs from all the others in the Editable class. You can't place the caret in such a field and change its contents by typing. It is intended for use with an associated button field, i.e. Menu, Increment, Evaluate button, Nudge up or Nudge down (see 4.2.13), where you might want to avoid a writable field so that the user can only alter the field by clicking the button. Used with a Menu button the effect is similar to that of a linked validation table (see 5.2) and might be preferred by some users where the list of permitted strings is short. A question which may arise in connection with this is how do you blank such a field if you've accidentally made the wrong menu selection? This is easily handled by including [blank] as a menu choice. The square brackets must be used. If you set this up for someone else to use with a menu you should set passwords to prevent access to the menu file by ADJUST clicking the menu button.top of page

4.2.5.4 Phone fields

There's nothing to stop you using an ordinary Numeric field, or even an Unrestricted field, to hold phone numbers, but the dedicated Phone field has an advantage. Double-clicking the field will generate the appropriate tone-dialling sounds through the internal loudspeaker. By holding the phone close to the sound source you can make it dial the number for you. You can configure a delay of a few seconds between the double click and the tones by editing DialDelay in the Config file.

4.2.5.5 Cross reference fields

If you enter a comma-separated list of primary keys or record numbers in this type of field, double clicking an item in the list will retrieve the relevant record if it exists. If using record numbers you need to precede them with a hash character. This is a useful way of quickly accessing records which have some connection with each other, e.g. tracks on a CD, members of a family, photographs of a specific place or taken on a certain occasion. Such data might be quite widely scattered in the file because of the type of indexing you've decided to use. Cross-referencing with this specialised field obviates the need to look up each one separately.

If the field contains record numbers you can use it to retrieve the records regardless of the currently active key. Using primary keys is more restrictive in that the currently active key must be the primary key; double clicks are simply ignored when you have a subsidiary index selected.

4.2.6 Scrollable lists
To 6.3 (Calculations on 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 three or four 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 cases — and find even then that an occasional 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. An example of such a field, taken from the !Music database, is shown below. Four tracks are visible but the vertical scroll bar suggests that the list contains many more. There are, in fact, 34 tracks in the field in this record.

scroll/png

list/png When the Scrollable list radio button is selected an extension appears at the bottom of the Field definition window. Ignoring this for the moment, click the menu button at top right and select the number of columns you want your list to have. The configured maximum is 16 but your may increase this by editing the !Powerbase.Resources.Config file (see 14.8). Now turn to the aforementioned window extension and decide how many rows of your list are to be visible on the record window. The default is four which works well enough for most cases but you may increase or decrease this value.

Most of the data defining the Scrollable list is entered in — a scrollable list! Although this contains 16 rows (one for each column definition) only as many are writable as the number of columns you chose from the menu. For each of these enter under Chars the number of characters you want your list column to accommodate. Under Type you need to specify what type of column each is to be and this is easily done by clicking MENU over the icons and choosing the type from it. Note that this menu is exactly the same as the one you use to define ordinary simple editable fields. Order can be ignored for now.

sudoku/png

Rows and columns in a Scrollable list are normally separated by 1-pixel lines. Sometimes a thicker line may be desirable. The option buttons under T may be selected to produce a 2-pixel line at the right-hand side of any column. 2-pixel horizontal lines can be specified every n rows, where n=1,2,3 etc. Finally, you may want to prevent new rows being added to or deleted from the list and option buttons are provided for this. The Sudoku grid shown here makes use of these features. Columns 3 and 6 are followed by a thick vertical line and thick horizontal lines are specified after every 3rd row. Since we don't want to change the number of rows in a Sudoku grid both row addition and row deletion are blocked.

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)top of page

4.2.7 Check-box fields
Cross/tickDisplays a cross initially. A click changes it to a tick.
Null/tickSimilar to Cross/tick but initial state is an empty box.
Null/starSimilar to Null/tick, but second state is a star.
Option buttonExactly like the square option buttons seen on dialogue boxes.
Radio buttonExactly like the round radio buttons seen on dialogue boxes
Null/tick/crossThis is a three state check-box the first state being an empty box. Repeated clicking cycles through tick, cross and back to empty box.
?/tick/cross Another three-state check-box where the first is a question mark.
Null/Male/FemaleYet another three-state box whose states are empty box, M and F.
These are fields whose status changes when clicked on with SELECT. Eight types are possible as shown in the table opposite. 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.

All 2-state check-boxes may, if desired, be grouped in exclusive selection groups. These behave like the groups of radio buttons on dialogue boxes, i.e. selecting one of a group automatically deselects the previously-selected one. There are two differences however. A SELECT click on an already-selected item will deselect it (exactly as with other 2-state check-boxes), and it isn't possible to select more than one member of a group by ADJUST-clicking (which highlights the box for reporting). Check-boxes are placed in an exclusive selection group by entering the group number in the icon which is normally labelled min, but will be seen to be labelled esg when Check boxes are being defined. The group number must be a positive integer. Boxes in the same group are given the same esg number; different groups of boxes have different esg numbers. 
 
 

top of page

4.2.8 External fields
To 2.6 (External fields)
To Ch 2, Note 5
To 4.2.3 (Moving and re-sizing the bounding box)
Text Plain text files, such as Edit creates.
Sprite Sprite files, such as Paint creates.
Draw Vector graphics files, such as Draw creates.
JPEG JPEG files; especially photographs from scans or a digital camera.
Text block Plain text files.
Picture Sprite files.
Scaled image Sprites, JPEGs and Drawfiles.
With the exception of Scrollable lists, all fieldtypes discussed so far store their data in a large text file called Database. The fieldtypes listed at right are called External because the data is stored in files external to Database, albeit inside the database application directory. 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. The field types in this class and the types of data linked to them are as shown in the table.

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 displaying/editing the file, i.e. the Edit, Paint and Draw icons respectively. (We don't edit JPEGs and, since these are more often than not photographs, the button displays a camera.) Files of the appropriate type may be dropped on these buttons, whereupon the button icon changes to show the appropriate file-type and the file is copied into a special system of subdirectories within the database application. The 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 a program able to load it.

Text block, Picture and Scaled image fields take things a step further by actually displaying the file on the record window. The bounding box of the field 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. For this reason the Picture field should be avoided and the Scaled image used instead. This will always fit the image within the bounding box. Picture fields are retained only to accommodate older databases. (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 clicking with SELECT. When the edited text or sprite is saved the Powerbase field will be seen to update.

4.2.8.1 Scaled image fields
Scaled image fields are supported by Powerbase from v.9.6 on. They can display JPEGs, sprites and drawfiles on the record window, scaled to fit within the field. The attached image is copied into the same type of directory structure as the other six External field types. Scaled sprites and drawfiles can be displayed with all versions of RISC OS but JPEGs require RISC OS 3.6 or later. If your version of RISC OS is older than that you can still attach JPEGs to a Scaled image field but they will not be displayed. If the database is transferred to a machine with a later operating-system the images will then be seen.
None
Fit field
Fit but don't magnify
Fill field
Fill but don't magnify
Don't show image
A field of appropriate size is created to display the image and a JPEG, sprite file or drawfile is linked to the field by dragging and dropping in the same way as for other types. The image is displayed within the limits of the field. The type of scaling performed is selected from a submenu when creating the field. There are five scaling options, all of which centre the image in the field and preserve the aspect ratio; i.e. the image may be cropped but is never distorted. The options are as shown in the table on the right.

None calls for little comment. The image is displayed at full size so unless it is smaller than the field you will only be able to see the middle part of it.

Fit field scales the image so that all of it falls within the field. Since the aspect-ratio of the image is unlikely to match that of the field there will usually be white strips either above and below or to right and left of the image. If the image is smaller than the field it will be magnified until it touches the horizontal or vertical borders of the field, whichever is reached first. This is fine for drawfiles but can look ugly and "pixellated" for sprites and JPEGs. You might therefore prefer the third option which will leave the image unscaled if it fits wholly inside the field. This option — fit where appropriate but don't magnify — is the default setting.

Fill field normally scales the image so that no white strips can be seen. Different aspect ratios of image and field will cause the image to be cropped at either the vertical or horizontal edges (but not both). As with the Fit option, magnifying a sprite or JPEG until it fills the field can spoil its appearance, hence the final option which leaves small images un-scaled.

Don't show image is self-explanatory. You might want to choose this setting whilst develping a database if the associated image files are large and updating the record screen is inconveniently slow.

Scaled image fields supersede the older Picture type, although the latter is retained to support older databases. The fact that they don't allow sprites to "leak" beyond the field boundary is reason enough to prefer them.

4.2.8.2 Mouse-clicks over External fields
The following mouse actions are possible on External fields: If you have trouble remembering these actions don't forget Iteractive Help! Turn it on and move the mouse over a field and it will remind you of the possibilities.href="#top">top of page

4.2.9 Referenced fields
To 2.6 (External fields)
To 4.2.13 (Button fields - Directory button)
To 12.1.1 (Filenames in script commands)
Referenced Anything!
Scaled referenced image Sprites, JPEGs and Drawfiles.
These demand a special section to themselves. The first appears as a large button to which any type of filer object (file, directory or application) may be attached. They differ from Text, Sprite, Draw and JPEG buttons in that the attached 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 "Referenced" — such fields are even more "external" than the types previously described. 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.

Scaled referenced image fields look exactly like the External Scaled image fields already described (see 4.2.8.1) except that they have a green border to distinguish them from the non-referenced type. They respond to mouse-clicks in the same way too and, since the field stores the pathname, which is searchable text, it may be selected for printing with ADJUST and its tag inserted into a search formula with Ctrl-SELECT. Other mouse-clicks are similar to those described earlier for the other External fields.

When defining a Referenced field you should give careful consideration to the space allowed for the pathnames. This is specified in the Data length icon on the field definition window. Powerbase sets the value to 255 but you may reduce this if desired. (Set it to 0, however, and Powerbase will put it back to 255!)

A Referenced 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.

Pathnames of Referenced 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 RemPaths, beside which is a letter: either R or A. top of page Relative pathnames are only possible when the files are on the same disc as the database; otherwise absolute pathnames are used even when the Config setting is R.

Consider the file structure illustrated. The directory called Pictures holds a collection of JPEG images sub-divided into several categories, each image being associated via a Referenced button with a record in the !MyPhotos database. The full, or absolute, pathname of the JPEG Horse is: ADFS::HardDisc4.$.FredsData.Pictures.Animals.Horse. If you drop the JPEG file onto the Referenced button and either the database or Powerbase itself is configured for absolute 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 !MyPhotos database and retrieving the record to which the Horse JPEG should be linked you will find the Referenced 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 relative 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: start at MyPhotos, go up two levels in the disc tree to FredsData, then use: Pictures.Animals.Horse from there.

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 from !MyPhotos 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 Referenced button provided the configured pathname type is relative.

So which should you use; absolute or relative pathnames? There are advantages and disadvantages to both. Absolute pathnames are appropriate if the Referenced 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 Referenced 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.top of page


conv/png Relative pathnames may be selectively converted to absolute ones. Absolute pathnames may also be made relative where Referenced files are on the same disc as the database. Click MENU over the Referenced button and choose Convert paths from the Field submenu. The displayed window shows the setting in the Config file. The standard Query panel allows subfiles to be chosen and a search formula entered. Scan pathnames examines the currently-stored pathnames in the targeted records and produces a report listing them together with the following information:

It is a good idea to perform this scan and examine the resulting report (which may be saved as a text file or printed out (see 3.1.1) before attempting to convert pathnames to the alternative type. Confirmation is then required before further action. Any pathnames which cannot be converted will be recorded in a file NoCanDo in PrintJobs along with the record numbers and the reason why conversion was not possible. There are several reasons why pathnames might not be convertible: In the rare instance of a Referenced field being indexed the index will be invalid after the conversion and you will be warned to rebuild it.top of page

4.2.9.1 Moving the database or object directory
Suppose you have a directory of files linked to a Referenced field and want to move either the database or the directory so that the new relative positions of the two will have changed. Moving the database will make no difference provided the stored pathnames are Absolute, but Relative pathnames will no longer be valid. Moving the directory is even worse since it alters the absolute pathnames of all the objects within it thus rendering both Absolute and Relative pathnames invalid! All is not lost, however. Powerbase knows from the stored pathnames what the directory and object name are, and these have not changed. All we need is some way of telling the program where the directory resides and it will be able to rectify the pathnames. Select Rename for moved directory, drag the directory to the Convert pathnames window and drop it on the icon with the large down-pointing arrow. The pathname of the dragged directory will be displayed. Click the default action button (which now says "Rename"). The pathnames will be updated and Powerbase will once more be able to find the linked objects. This feature works equally well if it is the database which has been moved.

It might occur to you that there's a possible snag with this facility. Suppose the objects are now in the root directory of a disc? This could easily happen if they aren't on your hard disc but on a removeable medium such as a CD ROM or a USB memory stick. They're in a directory all right, but there's no directory icon for you to drag to the Convert pathnames window! A solution is provided, however; simply drag and drop any object from the disc. The two "bump" arrows to the right of the target icon then enable you to step up the directory tree all the way to the root - or step back down if necessary.

4.2.9.2 Finding a "lost" object
It's all very well to have a means of re-linking objects when the whole directory has been moved but what happens if individual files have been shifted about within a directory tree? You might have, in the People subdirectory, a picture of Fred throwing a stick for the dog. You decide that the dog, not Fred, is the real interest in the picture and move it into Animals. Neither a relative nor abosolute pathname will then be valid. OK, this is a fairly trivial example and easily rectified by manually re-linking the photo, but you might do quite a lot of this shifting about between subdirectories and forget to re-link all the moved pictures. Later on you're scanning through the database and up comes a record for which the picture can't be found. Where is it? Where did you move it to? Can't remember! That's where Search for lost object comes in. You know it's somewhere in a certain directory tree so you drop that on the icon with the down-pointing arrow and click the default action button (now "Find lost"). The programme will search the entire directory tree. If it finds the errant object it will display the pathname and open the relevant subdirectory so that you can easily re-link the object. If you're searching the root directory of your hard disc the search could take quite a while. Some time is saved, however, since Powerbase assumes you wouldn't have moved an object into !Boot or into an application and it will ignore these.

4.2.9.3 Auto-linking objects to Referenced fields
There's one further facility provided by this window. Your record design might include a field whose contents exactly match the names of the files linked to the Referenced field. It would, for instance, be quite reasonable for a database classifying animal photographs to have a field in which you could enter Horse for the record linked to the picture of a horse etc. It would be tedious to retrieve each record and drag the relevant picture onto the Referenced button so Powerbase lets you link them all automatically. Make sure the names are all present in the field to be matched and enter the tag of that field in the writable icon. Drag and drop the picture directory as described above, select Auto-link files with matching names and click the default action button (now "Auto link").

4.2.10 Auto-displaying External files
To 4.2.5 (Other types of Editable field)
To 12.7 (Other commands [Script])
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.top of page

4.2.11 Stamp fields
To 6.1 (Computed fields)
To 14.1.4 (Option buttons: recalculate on opening)
Sequence number Unique value numbered from a base value set by the user
Record number Database record number
Time hh:mm:ss
Date Four formats, as in 4.2.5
Date & time Day,dd Mth yyyy.hh:mm:ss
Day of week Can be 3-letter abbreviation (e.g. Sun) or full name
Month Can be 3-letter abbreviation (e.g. Jan) or full name
Day in week (num) 1-7, 1 being Sunday
Day in month 2-digit number
Day in year 3-digit number
Month (num) 2-digit number
Year 4-digit number
Last altered Date and time at which the record was last edited
Logo A sprite which may be made to appear on every record
Frame Used to enclose groups of fields to clarify layout. Two types: channel and ridge.
Plinth Used in a similar way to frames
None of these fields may be edited by the user. They are "stamped" by Powerbase when the record is first created and, with one exception, do not change thereafter. The exception is Last altered which will automatically update if any part of the record is changed. Merely displaying the record or including it in a report does not make the field update. However, those whose names are in bold type have exact equivalents in the Computed field class and those will update when required. (see 6.1).

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.

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. For the heading use a field of Unrestricted type with a descriptor only (no tag) 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.)

Plinths, like Frames, are used to clarify and enhance the appearance of the record window by providing a chamfered panel on which data fields or buttons can be placed. Like Frames they must be created before the fields which are to be displayed on them.top of page

4.2.12 Button fields
To 2.2 (Moving about the database)
To Ch 2, Note 5
To 4.2.5 (Other types of Editable field)
To 12.1.1 (Filenames in script commands)
To 14.7 (Overall control of the database)
To 14.8 (Config files)
Exit Duplicates the action of Close database on the iconbar menu
Quit duplicates the action of Quit on the iconbar menu
Menu (field) Enters data from a pop-up menu into an associated field
Menu (general) As above, but data is entered at the caret, wherever this may be
Directory Opens a linked directory
Run file Executes a linked file
Increment Adds a predetermined number to the contents of an associated field
Evaluate Performs a specified calculation and enters the result into an associated field
Nudge up Adds 1 to an associated field
Nudge down Subtracts 1 from an associated field
Any or all of the control buttons on the tool-pane 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. These are listed in the adjoining table.

Five of the above descriptions refer to an associated field. This means a data field to which the button is tied so that its action affects that data field and no other. How do we "associate" a button and a field? By giving them the same tag. It has been stated previously that all field must have different tags and that is true as far as fields which contain data are concerned. Buttons which act on an associated field are exceptions to this rule. The first type of Menu button provides a good example (see below).

Menu buttons
(See also Note 5.)
There are numerous cases where only a few values are ever used in a data field. In a database of recorded music there might be a field for Medium (tag MED) which never contains anything other than one of: CD, cassette, LP, DVD. You could put these items on a user menu which opens when you click a menu button next to the field. The menu button would also have the tag MED and would be placed next to the field. Choosing an item from the menu would enter it into the data field, overwriting anything already present.User menus of this type can be made to pop up automatically when the caret enters the associated data field by selecting Auto-open user menus from the Preferences window.

The second type of menu button is not associated with 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 having a red "pointer" instead of a blue one. It inserts data into the field which contains the caret and the data is inserted at the caret without overwriting whatever is in the field already.

If you hold down Shift while choosing from one of these menus the first word in the chosen string will have its initial letter capitalised.

Both these types of menu button are used on the sample database Music. Data for both types of button is stored in a text file whose name is the tag of the button plus the word "Menu" and all such files are stored in the Menus subdirectory of the database. 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. 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. If passwords have been set you need a Manager-level password to access the menu file in this way.

Directory button
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 Referenced 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 Referenced fields (see 12.1). 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.8. The link between button and directory may be broken by clicking MENU over the field and choosing Unlink file from the Field submenu.

Run file
This type of button initially 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, Nudge up and Nudge down
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 the Increment button is provided to do it with a single click. When defining the button 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 to any positive or negative value via Increment size on the Field submenu. Nudge up and Nudge down perform a similar function but only increase or decrease the field value by one. They are identical in appearance to  bump  icons and may be conveniently used as a pair, in which case both buttons and the associated data field have the same tag — the only case in which a tag may appear three times.

Evaluate
As with Increment the descriptor is used as a legend, the default being "Evaluate". The button is used to enter the result of a calculation into its associated data field and further discussion is postponed until 6.2.top of page

4.2.13 Mandatory fields
This isn't yet another class of field. 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.2.14 Computed fields
Discussion of Computed fields is postponed to Ch 6 - Performing Calculations.

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. It creates the database with the following settings:

All these features can be changed later so there is nothing irrevocable about using the Default database option. It opens the database and displays a blank record allowing you to experiment with the database immediately. If, however, you want to choose the database size and primary key structure yourself follow the procedure in the next two sections.top of page

4.4 Specifying the database size

size/png

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

To 7.1 (Indexing a field)

keydef/png

4.5.1 General procedure
Click MENU and choose Primary key. This opens the Key structure definition 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 number 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). You may also enter F (for Final) to specify the last word in the field regardless of number.
  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.
If you make a mistake or change your mind, choosing <clear> from the top of the menu of fields will blank the field name and other details.

After entering this information click on Create. Powerbase will then set up the database and open it ready for you to start entering records.top of page

4.5.2 Some illustrative examples
To 11.2.1 (Duplicate primary keys)
To 12.5 (Commands used mainly for editing [Script])
In the following illustrations it is assumed the option buttons are set as shown above, these being the default settings. Note that Case specific is deselected but Preserve case is selected. This means that the letters in the key retain their case but the ordering within the index takes no account of case. (If Preserve case is deselected all letters in the keys — but not, of course, in the source fields — are forced to uppercase.) Remove accents is selected causing letters bearing diacritical marks (accents, umlauts, tildes etc.) to be replaced by unaccented equivalents. This makes record-searching by key easier because only the regular keyboard letter-keys need be used. The changes are applied only to the keys not, of course, to the record fields from which the keys are derived.

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
Row Field Chars Word Pos
1 NAME 4 1 L
2 NAME 1 2 L
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 use all of these. 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 shown in the table.

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.

From Surname
Einstein
From Forename
Albert
   
Chars Word Pos Chars Word Pos Key  
5 1 L       Einst  
4 1 R       tein  
3 1 2       ins  
7 1 4       stein *
      3 2 L Alb  
      4 2 R bert  
4 1 L 4 2 L EinsAlbe  
3 2 L 3 1 R Albein  
1 1 4 6 2 3 sbert *
5 F L 3 1 R Alberein **
8 0 R       inAlbert ***
7   4       steinAl ***
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 Albert Einstein stored surname first in a single field.

The first four examples derive the key from the Surname only; the next two from the Forename only. The rest use both names. Special attention is directed to the following cases:

top of page

4.5.3 Using more than one field in a key
To 7.1 (Indexing a field)
Row Field Chars Word Pos
1 SNAM 4 1 L
2 FNAM 1 1 L
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 as shown here 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.

Row Field Chars Word Pos
1 COMP 4 1 L
2 WORK 3 1 L
3 WORK 3 2 L
4 WORK 2 3 L

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 as shown on the right. Beethoven Symphony No. 5 would then yield the key BeetSymNo.5

This is less than ideal. Part of the key (No.) conveys no useful information. The problem is even more acute in the case of Mozart Piano Concerto No. 23 which would yield the key 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 definition window, using commas to separate the words. If we enter No.,A,The,of in the Ignore field (see also 4.5.5) the works mentioned above would have the keys BeetSym5 and MozaPiaCon23 which is much more useful. An entry such as Mahler The Song of the Earth would yield the key MahlSonEar, and Britten A Ceremony of Carols would give BritCerCar.

With Remove accents selected the key for Berlioz Nuits d'été would be BerlNuid'e (or BerlNuidet if the apostrophe has been defined as a "split" character as described below in 4.5.4).top of page

4.5.4 Further refinements
To 7.1 (Indexing a field)
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 a string of the required characters in Split at, i.e. .-,;:' Multiple split characters, such as a double space or a full-stop followed by a space, are treated as if there was only a single split character. Thus the key for Arnold Scottish Dances Op.59 according to the Composer-plus-work key structure detailed above will be ArnoScoDan59 (assuming "Op" or "op" to be on the Ignore list) irrespective of whether the WORK field is exactly as shown or has a comma after "Dances" or an extra space after "Op.". In other word Powerbase doesn't count zero-length words.

Additions and alterations to the Ignore and Split at lists can be made at any time by choosing Index=>Show details (Ctrl K) from the main menu to bring up the key-definition window. Click Modify after making changes. Take note, however, that changes to these lists might invalidate some keys in the relevant index. Suppose that in the previous example the record had been entered before placing "Op" or "op" on the Ignore list. The stored primary key would be ArnoScoDanOp instead of ArnoScoDan59. This won't affect browsing or printing, nor will it prevent you from retrieving the record by typing the key ArnoScoDanOp, although a unique shorter form such as ArnoScoDan would work. If you enter ArnoScoDan59 Powerbase will report the key not found even though this would be the correct key taking into account the modified Ignore list. (You might still retrieve the right record despite the error as the "nearest match".) To ensure that an index complies with changes made to the Ignore or Split at list you might want to rebuild it.

Where numerals form part of the key, but the key isn't wholly numeric Note 2 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 result in keys such as HaydSym102, HaydSym17, HaydSym45, HaydSym6
HaydSym006
HaydSym017
HaydSym045
HaydSym102
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 with leading zeros. Powerbase will do that for you in keys if you select Justify numbers The four keys shown above will then come out as shown in the table and will appear in the correct order.top of page

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 definition window will do so by adding the requisite number of spaces to each section of the key.

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 although the case of letters is preserved when constructing keys. 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" but you may search for it by entering "HORS", "hors", "Hors", "hOrS" etc. It should also be noted that if the index isn't case-sensitive the words in the Ignore list (see 4.5.3) aren't either; there's no need to include both "The" and "the", or "An" and "an" in the list.

This may not be what you want. If you require indexing to be case sensitive select the button labelled Case specific before creating the primary key. 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 3) values 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".

When looking at keys in the title-bar or including them in a report a mixture of upper and lowercase (as in the original fields) is more readable than un-relieved uppercase. This is where the Preserve case option button comes in useful. If selected at the time the index is created it preserves the case of the letters without sacrificing non-case-sensitive searching. This button is shaded when Case specific is selected since case is necessarily preserved. All the foregoing examples of keys are shown as they would appear in the title bar if Preserve case is selected

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.top of page

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 special 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. For certain types of field (e.g. Dates and Times) you will find that Data length is shaded and therefore uneditable. This is normal behaviour since such fields have a fixed length which is determined by Powerbase itself. (back)

2. 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)

3. 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)

4. The harmlessness of trying to convert Relative to longer Absolute pathnames for which there is insufficient room applies only to Powerbase versions from 9.52 onward. Earlier versions are likely to make a mess of the database unless you edit the designated field length to a suitably safe value first! (back)

5. A third type of Menu button, Menu (scripts), is no longer supported because the record window doesn't seem like the best place for such a facility. Its function (to run stored scripts by choosing their names from the resulting pop-up menu) is now provided by a submenu attached to the Scripts entry on the Report menu. (back)



top of page Home   Index   list of chapters    previous chapter   next chapter