Five steps are involved in setting up a new database:
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.
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.
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.
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:
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.
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.
Unrestricted | Any printable character |
Alphanumeric | All letters and numerals and common punctuation |
Upper case | Capital letters, numerals, space and full-stop |
Capitalise each | As 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 first | As for Alphanumeric but only first letter of field is forced to upper case |
Numeric | Numerals, +, -, space and decimal point |
Yes/No/Maybe | Y, N and ? only |
Date | Four 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) |
Time | Time in the form hh:mm:ss up to a value of 23:59:59. |
Internet | Special field for email addresses and web URLs. Double-clicking invokes mail program or browser with the address loaded. |
Phone | Special field for phone numbers. Accepts numerals and space only. Can be used to tone-dial the number (see 4.2.5.4). |
Cross reference | Double-clicking on a primary key or record number in the field attempts to retrieve referenced record (see 4.2.5.5). |
Via button only | This 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). |
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).
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">
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).
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.
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.
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.
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.
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)
Cross/tick | Displays a cross initially. A click changes it to a tick. |
Null/tick | Similar to Cross/tick but initial state is an empty box. |
Null/star | Similar to Null/tick, but second state is a star. |
Option button | Exactly like the square option buttons seen on dialogue boxes. |
Radio button | Exactly like the round radio buttons seen on dialogue boxes |
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. |
?/tick/cross | Another three-state check-box where the first is a question mark. |
Null/Male/Female | Yet another three-state box whose states are empty box, M and F. |
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.
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. |
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.
None |
Fit field |
Fit but don't magnify |
Fill field |
Fill but don't magnify |
Don't show image |
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.
Referenced | Anything! |
Scaled referenced image | Sprites, JPEGs and Drawfiles. |
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.
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.
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 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.
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.
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 |
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.
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 |
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).
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.
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.
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:
This involves two steps:
After entering this information click on Create. Powerbase will then set up the database and open it ready for you to start entering records.
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 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 | *** |
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:
Row | Field | Chars | Word | Pos |
1 | SNAM | 4 | 1 | L |
2 | FNAM | 1 | 1 | L |
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).
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 |
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.
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.
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.
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)