Home   Index   list of chapters    previous chapter   next chapter  
 

Ch 2 - Browsing and Editing

Opening a database displays the Record window which shows the data for one record at a time. Each item of data in the record occupies a rectangle called a field. Most (but not all) fields will have a descriptor or label which appears next to the field. In addition all fields which contain data will have a tag of up to four characters. Only fields which serve merely as labels and which contain no data may be without tags. Tags are very important — far more so than descriptors — as a means of identifying fields (see 4.2.4). If you click MENU over a field you will see its tag displayed in the menu, e.g. Field: NAME.top of page

2.1 Subfiles, record numbers and keys

Look at the title-bar of the record window. Three pieces of information are displayed; the subfile number (or name, if the subfile has been given one), the record number and the key of the record. Since we will make frequent reference to these terms it will be as well to explain each one at the outset.

2.1.1 Subfiles
Suppose you have a bookcase, capable of holding 1000 books, and sixteen movable partitions, numbered 0-15, which you can use to divide the shelf space into sections. The partitions are, to begin with, all stacked together at the left-hand end of the top shelf. You may leave them like that if you wish and fill up the shelves with an unbroken run of 1000 books. If, however, the books fall into well-defined categories you might prefer to use the partitions to divide the bookcase up into sections. You could also attach labels to the partitions to indicate the type of book which follows each one, e.g. Fiction, History, Science, Cookery, Travel. There are no restrictions on where you can place the partitions, or how many books you put into each section, as long as you don't exceed the total of 1000 books. You might ask why not place each category of book on its own shelf rather than using the movable partitions? That's a less flexible means of organizing the collection because some shelves will have wasted space while others don't have enough. The movable partitions allow categories to be elastic and expand (or contract) as your library develops.

subfile control

In a similar way every Powerbase database is able to use up to sixteen subfiles numbered 0-15. When you open the database you are looking at subfile 0 and you might have no occasion to bother with the others at all, in which case they can be ignored. Like the bookcase divisions subfiles are there for you to use or not use depending on what your requirements are. The database remains the same size whether you use one subfile or all sixteen and the number of records in a subfile is limited only by the defined size of the database. Unwanted subfiles can, however, be disabled. To do so choose Subfile control from the main menu. The window which opens displays information about each subfile in a scrolling pane. Buttons at far left may be used to select a subfile (note that the currently-selected subfile has its number in red) and check-boxes at far right may be used to disable and re-enable subfiles, apart from the current subfile which cannot be disabled. Writable icons allow you to give subfiles meaningful names of up to 16 characters. In this database just three subfiles are enabled and named; the others are disabled and greyed out. Section 2.2.5 suggests instances in which you may find it advantageous to use several subfiles.

The window also gives useful information such as when each subfile was last altered, how big the database is and how much of it is currently used. Settings made will be saved when you close the database; the List button is provided merely for saving a text-file of the window contents for your own use.

2.1.2 Record numbers
Every record in the database has a number which specifies its position in a plain text file called Database. As you step through the records (see 2.2.1) you will probably find that the record numbers shown in the title-bar look quite random. Record numbers reflect the order in which records were originally entered into the database, not the order in which you see them while browsing. The latter is determined by the key.top of page

2.1.3 Keys and indexes
A key is a group of letters or other characters derived from one or more of the data fields in a record and used to identify that record. A record is, of course, uniquely identified by its record number but record numbers are of limited use because they only reflect the order in which the records were entered. Keys, on the other hand, are derived from the actual data and are stored in a special file called an index. The keys in the index are ordered, either alphabetically or numerically and can therefore be used to examine the records in an order which is related to the data. To use the bookcase analogy again, you can specify a book as "the 150th from the start", which is equivalent to using the record number and is obviously not a very convenient way of locating a particular book! Specifying it by title, author or subject is much more useful. A key enables us to do the same for database records.

Remember those card indexes which libraries used to have? Typically there would be, for non-fiction, a subject index and an author index. Both contained the same information but ordered in two different ways. Powerbase can do this (and much more) without duplicating the information because a database may have several indexes built from keys based on different fields in the record. There must be at least one index which we call the primary key index. When you come to create a database of your own you will find that Powerbase insists that you define the primary key (or else determines it for you) before you can use the database. Subsidiary keys may be defined at any time; whether you use them or not is up to you. Only one index and therfore one set of keys is active at any one time. We refer to this as the current key.

You can recognize the key field for the current key by its descriptor (label) which will be red (Note 1). Key fields for unselected keys have blue descriptors. Non-key fields have black descriptors. Look at the key field and at the key itself in the title-bar and you will see that the two are related.

The primary key field is further identified by the background colour of the data field itself which is pale yellow instead of white like the other data fields.top of page

2.2 Moving about the database

tool pane

Attached to the left-hand edge of the record window is the tool-pane which contains buttons for moving around the database, adding and deleting records, searching for records etc. An alternative way of presenting these buttons (the keypad) is described in 14.1.1. It is also possible to place some or all of the buttons on the record window itself and use neither tool-pane nor keypad. 4.2.13 explains how to do this and the sample database Boxes shows such buttons in use.

Descriptions of some of the tool-pane buttons can be accessed quickly by clicking over the represention of the button on the illustration. Speedy return to the illustration can be achieved by clicking a "thumbnail" of the tool-pane which occurs at intervals in the right margin.

Most of the buttons have keystroke equivalents provided by the numbered function keys F1-F11 and experienced users might want to use these instead of the mouse. The relevant keystrokes are given in the text where appropriate but note that these are default values and may be redefined if so desired (see 14.6). Many other actions have equivalent Ctrl sequences which can't be redefined. See Appendix B for a full list.

Powerbase also allows on-screen reports to be browsed, searched and edited (see 2.7).

2.2.1 Browsing
At the top-left of the tool-pane is the single-step button. This, when clicked with SELECT, steps forward through the records one at a time. Clicking with ADJUST steps back one at a time. The keystroke equivalents for these two actions are F2 and F1 respectively. Beneath the single-step button is what we might call the fast forward/rewind button; it steps through the records in jumps of 10, using SELECT and ADJUST in the way just described. (Keys: Shift F2/F1). You can alter the number of records jumped by entering a different number in the writable icon to the right of the button. At top-right is the ends button; SELECT displays the final record, ADJUST displays the first record (Ctrl F2/F1).

The terms "forward", "back", "final" and "first" in the preceding description refer to the order of records as determined by the keys in the index. The file "wraps around" so after the final record SELECT-clicking the single-step button takes you to the first record. If you display the first record ADJUST-clicking the single-step button displays the final record.

The buttons on the 3rd row resemble the play and stop buttons of a CD player or VCR and it will be convenient to refer to them by these names. Play (F11) causes Powerbase to run through the records, displaying each one briefly, and stop (Shift F11) halts the process. Closing the record window has the same effect as stop and so does the Esc(ape) key. If play is clicked with SELECT records are displayed in forward order. Using ADJUST displays them in reverse order. You may swap the order at any time by a further click on play. The "Fast forward interval" icon (see above) serves another purpose; it alters the time for which each record is displayed. The larger number you type into this icon the longer each record will remain on screen; a value of 100 displays each record for about a second.

At the bottom of the tool-pane are four bookmarks which let you mark specific records for rapid retrieval. Click on one with SELECT and the number of the current record appears in the icon. Clicking with SELECT on a bookmark icon which contains a record number will return you to that record. Click with ADJUST to clear the marker.top of page

2.2.2 Cross-referencing records
NOTE This feature was formerly accessed with Shift/double-click and could be used on any edited field. Powerbase 9.85 changes this behaviour by restricting it to a new field-type called Cross reference, and Shift is no longer required.

If you double-click with SELECT on a field of Cross reference type, and the contents of the field match either:

that record will be displayed. This is a very handy facility for jumping quickly between records which are related in some way. It will also work if a field contains a comma separated list of keys or record numbers. Double-clicking any one of these takes you to the relevant record. You could experiment with this using the Elements database. Look up, say, SODIUM, enter in one of the Notes fields a list of the primary keys of the other Group 1 metals, i.e. LITH, pota, RUBI, Caes, fran (note that case of letters is unimportant!). You can now access the records of these elements from SODIUM's record.

2.2.3 Changing keys
Skip the next three rows of the tool-pane for now and examine the one bearing a blue symbol resembling a key. It is used to cycle through the available indexes using either SELECT (or ADJUST for reverse order as you might expect). Shift F5/F4 have the same effect. Wrap-around again applies. If no subsidiary indexes have been defined the button will have no effect.tools/png

2.2.4 Changing subfiles
Underneath the index-change button is one for changing the subfile being examined. A newly-opened database always displays subfile 0. Assuming all subfiles to be enabled, clicking SELECT on the subfile-change button (F5) causes subfile 1 to be accessed instead. Repeated clicks advance the subfile number by 1 until 15 is reached, after which wrap-around to subfile 0 occurs. ADJUST, as you might guess, accesses the subfiles in reverse order (F4). If some subfiles have been disabled this behavious is modified; disabled subfiles (see 2.1.1) are skipped over and cannot be accessed.

2.2.5 Using more than one subfile
To 2.1.1 (Subfiles)
To 5.2 (Table validation)
As stated earlier a database consists of sixteen subfiles numbered 0-15. You may use subfile 0 (which is the one used by default) and ignore the others if you wish, but there are occasions when users might find multiple subfiles very useful. If, for example, you are maintaining a set of pupil records in a school you could assign one subfile to each year. At the end of a school year the promotion of Year 1 pupils to Year 2, Year 2 to Year 3 etc. can be brought about very easily using the Rotate subfiles feature. You will find the two action buttons for this on the window accessed from Subfile control on the main menu. It actually renumbers the subfiles so that (for "clockwise" rotation) subfile 0 becomes 1, 1 becomes 2 etc and 15 becomes 0. Records which were previously in, for example, subfile 2 will then be found in subfile 3. In an 11-16 school this would put the leavers into subfile 7, from which they could later be removed. You will be asked to confirm that it's what you really want before Powerbase proceeds. Subfile rotation takes account of any disabled subfiles and won't move records into them. Thus if subfiles 0 and 2 are both in use but subfile 1 has been disabled, a single rotation will transfer the contents of subfile 0 to subfile 2, skipping the disabled subfile.

Note also that the Subfile control window provides facilities for selecting two subfiles and swapping them. When you select the subfiles using the up and down arrowheads any disabled subfiles are, of course, skipped.

The features just described are used to alter the dispositions of entire subfiles, but moving individual records from one subfile to another is also possible and suggests another use for more than one subfile: using subfile 0 to hold the in-use records and subfile 1 as a repository for records which you want out of the way but don't want to lose permanently. Powerbase was actually used in this way in a certain Further Education college where the applications file for a course was built up in subfile 0. On enrolment day records were updated to reflect students' subject choices and all the records for applicants who had not appeared were moved into subfile 1. The enrolled students in subfile 0 were then timetabled and class lists were printed. During the first week of the academic year there was always a trickle of late enrolments which simply involved retrieving the required records from subfile 1 and reinstating them in subfile 0.

The facilities for moving single records, or chosen batches of records, from one subfile to another are described in 2.4.2 and 2.5.6. You can, of course, also select a subfile and type new records directly into it. top of page

2.3 Searching for a record

It's all very well being able to move about the database like flipping through the pages of a book but how do we find a specific record? Powerbase provides three ways of doing this; you can search for the record by key, by record number or by using a filter. top of page

2.3.1 Searching by key
To 14.6 (Defining the function keys)
search The button with the question mark is the search button (F8). When clicked with SELECT the Search window opens to the right of the tool-pane. Type the key of the required record into the writable icon and click on the Find button or type Return. (To see how the key is constructed from the key field click MENU over the record window and choose Indexes => Show details (Ctrl K). This displays a window describing the structure of the current key.)

If the key exists in the index the record will be found and displayed. If the key doesn't exist Powerbase beeps and displays the nearest matching record and a suitable message. If the database is a small one with keys which differ greatly from record to record then the displayed record may not look to be very near at all. If you typed Return or used SELECT on the Find button the Search window may close after retrieving the record. This will be the case if only one match for the key was found. If there are multiple matches the window stays open. If you want to search repeatedly it is more convenient to have the window stay on screen and this can be achieved by clicking on Find with ADJUST.

Another useful feature of the Search window is seen when you click MENU over the writable icon. A menu shows other searches you have made during the current session. You may repeat a former search by choosing from this menu with SELECT. If the menu becomes too long or gets cluttered up with failed searches you can delete the unwanted menu items by clicking with ADJUST. The modified menu will remain open. This feature also applies to creating reports. (See Ch 3.5). When you close the database the menu contents are saved in the database's PrintRes directory as a Data file called, simply, K. When you next open the database the file is not reloaded automatically (you might not want the old menu) but can be easily loaded (and displayed) by opening PrintRes and dragging the file onto the Search window's writable icon or, alternatively, by typing Ctrl K when the caret is in the writable icon.

If searching on a numeric key you must enter a number of exactly the right value. If, however, the key is alphabetic you may use just the first few letters if they are sufficient to distinguish the key from others. The more you shorten the key entry the more likely you are to get multiple matches. As noted above, multiple matches leave the Search window open, and you will see the number of matches displayed above the Find button. The "bump" icons on either side of the icon showing the number of matches are now "live" and you may click on them to display the relevant records in sequence. Also "live" is the Report button at top right. This may be clicked to produce a report of all the records which match the key. The field selection, report format and report destination are chosen in exactly the same way as you would do when reporting from the Match window (See Ch 3).

You can see that the window tells you which index is being searched. By default this is the current index, but clicking on the up and down "bump" icons (or typing the up and down "arrow" keys) allows you to search an index other than the current one. The index used affects the search only; clicking the browsing buttons on the tool-pane will show you that the current index has not been changed.

A search carried out as described looks only at the current subfile. If you know that the record you want is in a different subfile you can do one of the following:

If you suspect that the record you require might be lurking in some other subfile but aren't sure where you can force all sixteen to be searched. This can be achieved by using Shift-SELECT (Shift F8) on the search button. When the Search window appears you will see that the All subfiles radio button is selected instead of the one labelled with the name of the current subfile. The radio buttons enable you to change your mind after the window has been opened. If an all-subfiles search finds several matches for the key you can retrieve the associated records by clicking the left/right "bump" icons even when the matches aren't all in the same subfile. This feature can be extremely useful if you need to look at or edit a number of records with the same key which are scattered through several subfiles. You can, of course, access each subfile in turn and do a key search but this could be quite laborious.tools/png

There are two other buttons on the Search window. Old restores the last key entered into the writable icon and Cancel simply removes the window from the screen without further action. A further point worth noting is that clicking the search button with ADJUST instead of SELECT opens the Search window without resetting your previous choices of key, index etc.top of page

2.3.1.1 What to do if the key is complex
If the key is a complex one like the primary key in the Music database, which is derived from two fields and a total of four words from which 4,3,3 and 3 characters respectively are extracted, you might prefer an alternative form of entry. Suppose you want to retrieve the record for Mozart's Horn Concerto No.3. The key is MOZARHORCON003 and if you make a mistake when typing this you will probably get the wrong record. You can, however, find the record with: Mozart Horn Concerto 3 (Note the spaces between the words). You may also shorten each word to the length used in the key: Mozar Hor Con 3.

This works in the following way. Powerbase examines your entry for spaces. If any are found it knows you're using the above "long" entry and processes it via the key-generating function. In other words Mozart Horn Concerto 3 is actually converted into MOZARHORCON003 before any attempt is made to search for it in the index. This raises a slight problem; what if you want to locate just any work by Mozart and simply type in Mozart? Powerbase will get confused because there are no spaces to tell it that you're using the "long" form of entry. It will try to find MOZART in the index and fail. Why will it fail? Because only 5 letters of the composer's name (MOZAR) feature in the key and the concluding T ought to be the first letter of a work's title. Since no title of a Mozart work beginning with T occurs in this database the key will be reported as not found and the nearest match will be returned. This will be the 41st symphony whose key is MOZARSYM041. (MOZARS is the nearest to MOZART in this database.) If the letters of the composer's name after the 5th just happen to match a title you could retrieve an unwanted record without even the warning message!

There's a simple way round this difficulty. If you want to search on a single full word which yields the first part of the key, i.e. the composer's name in this case, just add a question mark to the end of the word. This will force the entry to be treated as the "long" form

2.3.2 Searching by record number
You can type a record number, preceded by # (e.g. #1054), in the Search window instead of a key. Provided the record is in use Powerbase will find and display it, regardless of which subfile it is in. Bear in mind that, if the record is found in some other subfile than the current one, that subfile will become the current one. If the record has been deleted or has never been assigned a "Record #xxx is not in use" message is displayed.

2.3.3 Searching by filter
To 3.5 (Specifying which records to include)
To 9.2.2 (Merging the data)

filter The Filter button (Ctrl F8) is just above the bookmarks on the tool-pane. Selecting it brings up the Filter window. The section surrounded by a red border is called the query panel (see 3.7) and appears on several windows (see Note 7). It contains a writable icon into which you can type a search formula (see 3.5.1). If you then click the Apply button or, alternatively, type Return, the buttons used for browsing will only display records which match the search formula, all non-matching records being filtered out. Deselecting the Filter button closes the Filter window and restores normal browsing operations. So does clicking Cancel on the Filter window. The Close button simply closes the Filter window but leaves the filter in effect — useful if you want the window out of the way once you've set up a filter.

Clicking on the single-step button with the filter window open and a filter active causes the number to the left of the Cancel button to increment with every match found until the end of the subfile is reached after which a message is displayed telling you that there are no more matching records. Clicking Count displays the total number of matches in the subfile straight away without the need to step through the records.tools/png

The From start option button is selected by default. This means that when a filter is activated it will always begin with the first match in the current subfile, regardless of what record was on screen when you clicked Apply. If you deselect the option the filter will start from the currently displayed record.

Please note that filters operate on the current subfile only.

2.4 Editing the database - fundamental operations

2.4.1 Adding new records
The button bearing a plus sign is the add record button (F10). Clicking with SELECT displays a blank record for you to fill in. The subfile number or name and the anticipated record number are shown in the title-bar of the record window but there is, of course, no key as yet because there is no data. The title-bar therefore reads "(New)" in the place where the key would normally appear. In most databases you may leave blank any fields except the primary key field (Note 2). Powerbase does, however, allow other fields to be defined as mandatory fields (see 4.2.14). Such fields have red text and a red border. If you try to move to another record, close the database or quit Powerbase without entering something in every mandatory field as well as the primary key field you will find you're unable to do so until you either fill the fields in or say you wish to discard the record. You might also find that on some fields pressing certain keys has no effect. This will happen, for example, if you try to type letters into a field which has been defined as Numeric.

If you type into a field whose text and border are dark green you might get an error message. These fields only accept certain values which are listed in a validation table (see 5.2). To see what these values are make sure the caret is in the relevant field then click on the Table button on the tool-pane. There are times when this data-validation feature can become a nuisance, so it can be turned off. Click Powerbase's iconbar icon with ADJUST; this opens the Preferences window. The seventh option button down on the left is called Validate input. Deselect this button and click on Accept. All validation, including insistence on filling in mandatory fields, will then be disabled. Shift F9 can also be used to toggle validation on and off.

Ctrl D will insert the current date into a field and Ctrl T will insert the time. The fields need not be designated as Date or Time fields for this to work (if so designated they will accept only the date or time) but the data will not be inserted if it is too long for the field.

After entering data into a field you can go to the next field in sequence by typing either the Return or the down-arrow key. To go back one field use the up-arrow key. These arrow keys work in such a way that attempting to go beyond the last field of the record takes you to the first field and attempting to go back from the first field takes you to the last — our old friend wrap-around again. Typing Return on the last field of a record acts rather differently in that it writes the record to disc and brings up a new blank record for editing, i.e. it duplicates the action of the add record button on the tool-pane. You can also place the caret in any editable field by clicking with SELECT.tools/png

When you have entered as much of a record as you wish you can click add record again for another blank record. The record you have just entered is automatically written to the database. The same is true if you click on any other tool-pane button or close the database altogether. You don't need to use the Update (F3) button to tell Powerbase to write the record — its real purpose is to force the saving of indexes and validation tables which are stored in memory. If you do click this button after entering a new record you will see the title-bar update to show the key instead of the word "New". Occasional use of the button during a long working session to ensure that every part of the database is safely on the hard disc is useful to guard against power cuts or system failure. It is possible to automate saving at regular intervals (see 14.1.5)

2.4.2 Deleting records
To 2.2.5 (Using more than one subfile)

The two buttons below the search and add buttons are concerned with record deletion. The second of these, which suggests putting an index card in a dustbin, is obviously a delete button (Ctrl F10) but what about the other? A click with SELECT (F7) normally moves the displayed record into the next subfile (assuming all subfiles are enabled), e.g. if the record is in subfile 0 it will be moved to subfile 1. If it is in subfile 15 it will be moved into subfile 0 (wrap-around again). Clicking with ADJUST (F6) normally moves the record back one subfile. A record in subfile 15 will be moved into subfile 14; a record in subfile 0 will be moved into subfile 15. Normal behaviour is modified, however, if subfiles have been disabled (see 2.1.1). The record is then moved to the next (or previous) enabled subfile.

This button, which we will refer to as the move record button, doesn't delete records at all, except in the sense "delete from the current subfile". We often want to get a record out of our way but keep open the option of bringing it back again and that's where this button is really useful. If you have a lot of records in subfile 0 and want to do a clear-out you can use subfile 1 as a dumping-ground for the unwanted records. You're not really deleting them; just removing them from the subfile you're working in.

Since the record is not lost by using the move record button confirmation is not normally requested. If you'd rather be warned of what is about to happen and given the opportunity to cancel the operation you can do so by altering the ShiftWarn setting in the Config file (see 14.8).

It is sometimes useful when moving a record, to be able to "follow" it into its new subfile. You can do so by holding down Shift while you click on the move record button. e.g. If you use Shift-SELECT to move a record from subfile 0 to 1 you will be left with 1 as the current subfile and the record displayed.

The delete (dustbin) button should only be used when you are sure you want to lose the record permanently. As this is quite a drastic action Powerbase will ask you to confirm it before the record actually disappears. Nervous users may prefer to leave this button strictly alone and do all their "deleting" with move record. If this eventually results in a lot of clutter in the subfile next to the one you're using you can always do an occasional clear-out as described in Section 2.5.6.

2.4.2.1 Copying or moving a record to a different subfile
There might be occasions when you want to move, or even copy, a record to a subfile which is not the one next or previous to the current subfile. This is catered for by an entry on the main menu called Copy/move record to.... It leads to a submenu listing all the enabled subfiles apart from the current one. If you simply choose the required destination subfile from this menu a copy of the record will be created in the chosen subfile, leaving the original record in place. If you hold down Shift while choosing from the menu the record will be moved to the chosen subfile. In either case you will end up viewing either the copy or the moved record in its new subfile.

There's a parallel here with copying and moving files in the filer: drag-and-drop with Shift moves the file, without Shift it copies the file. (But see Note 8!)tools/png

2.4.3 Altering existing records
A displayed record may be altered manually without any restrictions other than those applying to the addition of new records (see 2.4.1). Alterations which would result in a change to the primary key must be confirmed. Normal RISC OS conventions for editing writable icons apply, e.g. Ctrl U clears the icon. As with new record entries you need do nothing to save your changes to disc. The process takes place automatically before a different record is displayed, when a database is closed or on quitting Powerbase.

It is also possible to browse, search and edit records in an on-screen report. This feature is fully described in Section 2.7.top of page

2.4.4 Undoing mistakes
To 2.6.4 (Editing Scrollable lists)
Changes to the record on-screen can be undone provided you haven't clicked on Update (not normally needed anyway) or done anything to cause Powerbase to display a different record. In other words, you can undo changes made to the current record as long as it remains on the screen. You may either undo all the alterations made to the record or only those relating to specific fields. You can undo changes to ordinary writable fields, scrollable lists and check-boxes. You can't restore External fields which is why a warning is normally issued before deleting such fields.

2.5 Editing the database - special features

Section 2.4 described all the editing facilities you actually need in order to maintain your database. The features described in this section are not essential but they can save you time and make your use of the database more efficient.top of page

2.5.1 Using a template
You might want certain fields to be filled in with fixed contents on every new record to save typing the same thing every time and Powerbase provides a means of doing this. Call up the main menu, go to the Miscellaneous submenu and choose Edit template (Ctrl E). A blank record is displayed and the window title says "Enter data which you wish to appear by default on new records". Type in the required fixed data then click any of the usual tool-pane buttons. You will now find that the new record displayed when you click add record is no longer blank but already contains the data entered in your template. This feature affects new records only; it doesn't have a retrospective effect on existing records. You may drop a sprite of your own onto a Picture field to be used instead of the default Sprite icon when a record has no sprite associated with it, and a sprite, JPEG or drawfile may be added as the default image on a Scaled image field.top of page

2.5.2 Copying fields
To 9.2.1 (Preparing the document for merging)
The two buttons below the Change subfile button may be thought of as copy to clipboard (Ctrl C) and paste from clipboard (Ctrl V) respectively. They can save a lot of time when you want to copy data from one field into another. When you click SELECT on copy, the contents of the field which owns the caret is stored in memory. Positioning the caret in another field and clicking on paste will insert the stored data into the new field at the caret. If you want to replace the contents of the new field with the stored data (i.e. completely overwrite the old contents) you can blank the target field by typing Ctrl U, then paste the stored data into it. Using keystrokes instead of the tool-pane button is especially convenient here; just type Ctrl UV. If the data is too long for the field it will be truncated to fit.

Copy and paste are not limited to copying into another field of the same record; you may use them to copy data between records or from a record into a validation table or a dialogue box or, indeed, between any two writable icons within Powerbase. Data remains on the clipboard until you use copy again.

Even more conveniently data can be copied by simply dragging with SELECT. If you place the pointer over a field and hold down SELECT, a text-file icon appears under the pointer. This may be dragged and dropped into another field (provided it is an editable field) or into a writable icon in a dialogue box. It can even be dragged to another database if you have two copies of Powerbase running at once with two databases open. The field contents may also be dragged to a different application such as a wordprocessor, or to a filer window where it will be saved as a file named from the field's tag. See also 8.8.

SELECT-dragging as described above copies the field contents. To move the contents (i.e. copy, then delete from the source field) hold down Shift when dragging. You can also place the caret in the destination field and drag with Alt held down. This will insert the copied data at the caret instead of replacing the previous contents. Shift and Alt may be used together.

The copying and pasting just described make use if the RISC OS Global Clipboard. Other applications such as text editors and wordprocessors also use the global clipboard to copy and paste data from one application to another and data may be transferred to or from Powerbase in the same way.tools/png

Highlight one or more fields with ADJUST as described above and make sure the caret is in the displayed record. It needn't be in one of the highlighted fields; any field will do. Now type Ctrl C. Nothing appears to have happened but the highlighted data is now on the global clipboard. If you open, say, an Impression window, place the caret in it and type Ctrl V the data will be copied to Impression. The same is true for Ovation Pro, EasiWriter and recent versions of Edit (it certainly works with v.2.05). It also works with StrongEd but this uses Ctrl Shift V to paste, because Ctrl V is used differently.

If, on the other hand, you highlight data in Impression, Ovation Pro etc. and type Ctrl C (Ctrl Shift C for StrongEd), then place the caret in a Powerbase record and type Ctrl V successive lines from the source document will be written sequentially to the record fields. You can direct the data to specific fields by highlighting them with ADJUST, in which case the fields are filled in the order in which they were selected and the position of the caret is unimportant.

2.5.3 Copying an entire record
To 14.6 (Defining the function keys)
When a new record contains much of the same information as an existing one you can save time by copying a whole record and editing the copy. Display the record you want to copy and then click the add record button with ADJUST instead of SELECT (Shift F10). No blank record appears when you do this; instead an exact copy of the current record is displayed and a message appears informing you of this.

If you make no changes to the copy Powerbase will not add the new record to the database. (It is assumed that no-one will ever want two absolutely identical records in the same database!) Any alteration, however, leads to the record becoming part of the database.top of page

2.5.4 Choosing the field where editing starts
To 14.1.6 (Start editing at)
When an existing or blank record is displayed Powerbase places the caret in the first (i.e. lowest-numbered) writable field of the record. (This might not necessarily look like the first if the fields have been placed in out-of-sequence positions in the window.) When going through a database making changes to one or two fields somewhere in the middle of a large number of records it might be convenient to have the caret start somewhere else. Click MENU over the field where you wish the caret to start, go to the Field submenu and choose Start editing. The same result can be achieved by placing the caret in the required field and typing Ctrl @. Whenever a record is displayed the caret will be placed in the chosen field. The normal starting position is restored by the same method. top of page

2.5.5 Changing many records at once
To 3.5 (Specifying which records to include)
To 3.5.3 (Using wild-cards in search formulae)
To 12.5 (Commands used mainly for editing [Script])
To 14.1.3 (Wild-cards)

global/png To alter a given field in a set of records point the mouse at the field and click MENU. Go to the Field submenu and choose Global changes. In the first writable icon (Replace) enter what you want changing and in the second (with) enter what you want the first string changing to. In the third you may enter a search formula (see 3.5.1) specifying which records are to be changed. Finally click on Change or type Return. You will be asked for confirmation before the changes are allowed to take place. Note that:

It should be apparent from the above that leaving both Replace and the Search formula blank will place the with string in all records of the selected subfiles.

The characters * and # may be used as "wild-card" characters in the Replace and with strings (Note 3). The first of these is used to represent a group of characters which don't need to be matched. If you enter: Replace *Street with *Road the substitution will be performed on any record which matches the search formula and where the target field ends with "Street". The ending will be changed to "Road" leaving the rest of the string unchanged.

Another example of use is: Replace 47* with 1047*. If the target field begins with "47", the beginning will be changed to "1047", the remainder left unaltered: Replace *MPUT* with *NFUS* would change "MPUT" occurring anywhere in the target field into "NFUS", e.g. "COMPUTER" would become "CONFUSER"(!). You may, of course, use * in the middle of a word: Replace re*ment with dis*d would cause a string in the target field beginning with "re" and ending with "ment" to have its head and tail replaced with "dis" and "d", leaving the middle unchanged. e.g. "replacement" would become "displaced".

The # character is used to represent a single wild-card character and can replace individual letters in specific positions. The Replace and with strings here must be the same length and should have # characters in the same positions. The characters in the remaining positions are the ones which get replaced, but only if they occur in the corresponding positions of the existing string.

[U] Make all letters uppercase
[L] Make all letters lowercase
[UL] Make first letter of field uppercase, the rest lowercase
[ULUL] Make first letter of each word in field uppercase, the rest lowercase
You may also use Global changes to perform simple calculations e.g. "+12" in with will add 12 to the current value in the field on all the matching records. "*5-13" will multiply the current value by 5 and then subtract 13. (But see Note 6.) It is operations like these which are most likely to produce values which won't fit in the field, especially if floating-point numbers are involved. The operation isn't limited to Numeric fields, or even to other types of field consisting wholly of numerals; Powerbase will find the number embedded in an entry such as "ABC45XYZ" and the operation "+12" would convert it to "ABC57XYZ".

It is also possible to change the case of letters in a field, without any other change to the data, by placing special strings in the with field. These strings are distinguished by being placed in square brackets as shown in the table above.

top of page

2.5.6 Moving or deleting many records at once
To 3.5 (Specifying which records to include)
To 2.2.5 (Using more than one subfile)

move/png Choose Move/delete from the Miscellaneous submenu. This opens a window which gives you the choice of three types of operation:

The default is to Move records from the current subfile to the next in sequence, but any source or destination subfile may be specified by clicking on the bump-icons. Type in a search formula (see 3.5.1) and click the default action button or type Return. All matching records will be moved or deleted as specified. Operations may be undone by clicking on the Undo button, but only for the last operation carried out.


2.5.7 Hiding sensitive data
Your database might contain information which you don't want just anyone to read. A field may be hidden in the following way:

  1. From the iconbar Utilities submenu choose Alter format. This puts Powerbase into design mode. No data is displayed; just the empty fields over a blue grid.
  2. Double-click with SELECT over the field you wish to hide.
  3. In the Field design window select the option button Hide and click Update field.
  4. From the main menu choose Quit design. This returns you to normal editing mode.
The field you've just "doctored" will now have vanished. Typing Ctrl Shift F11 makes it reappear, but with its text in mid-grey to show that there's something special about it. Typing Ctrl Shift F11 a second time will hide it again, i.e. there is a toggle action. Even if you close the database with the field visible it will always be hidden when the database is re-opened. The field can only be returned to normal using the procedure detailed above, deselecting the Hide button.

Whilst in the invisible state a field cannot be selected for including in a report (see 3.4). ADJUST has no effect and you can't sneak round the protection by using Ctrl A to select all fields or by choosing a range containing the hidden field (i.e. by clicking with SELECT on an earlier field then double-clicking with ADJUST on a later one). Its tag doesn't appear in the Field entry of the main menu when you click MENU in the relevant place, nor does the field appear in the field list produced by typing Ctrl F. It is also excluded from the Database details report accessed from the iconbar Utilities submenu. As soon as you reveal the data by typing Ctrl Shift F11 it can be selected.

If you intend to use this facility you are strongly urged to password-protect your database (see Ch 11). Only a user with Manager access rights will then be able to use Ctrl Shift F11 or access Alter format to remove the protection. If you leave the database unprotected then anyone can use the key combinations and might easily discover them by chance (or by reading this manual!).top of page

2.5.8 Protecting data against deletion
If you follow steps 1 and 2 above you will see beneath the Hide button another option button called Lock. Selecting this button and returning to editing mode causes the field to be locked against alterations. The Return and arrow keys skip over the field and an attempt to place the caret in it directly results in a message telling the user the field is locked against changes. Locked fields are easily recognizable by their light green text.top of page

2.6 External fields

The data in External fields (see 4.2.8) is not stored within the Database file but in separate files, one for each record. With the exception of Referenced fields these files are stored inside the database's application directory. They may be Text, Sprite or Draw files and can be linked to buttons on the record window. Clicking on these buttons loads the External file into Edit, Paint or Draw provided that the relevant program has been "seen" by the filer. For Referenced fields the file objects can be of any type whatsoever including applications and directories and clicking on the button performs the appropriate action, e.g. an application is run, a directory is opened, an Obey or BASIC file is executed, other files are loaded into the appropriate editor if the filer knows where it is. Referenced fields are discussed fully in 4.2.9.

It is also possible to link Text files to a Text block field which will actually display the text (with certain limitations) within a large icon. Sprites may be similarly displayed in a Picture field.top of page

2.6.1 Linking files to the fields
To link a file with an External field drag the file's icon onto the record window and drop it on the appropriate field. For example, if the record includes a field represented by a Text button, i.e. one bearing a small version of the Edit icon, you may drop a Text file onto it. The button's icon changes to the small version of the Text file icon to indicate that the file has been linked. Sprite and Draw files are linked in the same way and Powerbase will not allow the wrong type of file to be linked. A Text block field also may have a Text file dropped onto it (Note 4). The text (or as much of it as will fit inside the icon) is then displayed on the record window. The formatting will not be the same as it is when you examine the text in Edit; lines are word-wrapped and centred within the display icon. (This is a feature of multi-line RISC OS text icons and is out of the control of Powerbase.) You can't edit the text directly but it is possible to export the file for editing (see below). Sprite files may be dropped onto and displayed in a Picture field. Any new file dropped onto an External field which is already linked to a file simply replaces the old file with the new. As noted above, any type of object may be linked to a Referenced field. Scaled image fields are a more specialised type of Referenced field which can display sprites, JPEG images and drawfiles.top of page

2.6.2 Editing External fields
To 3.3 (What types of field can be included?)

You can't edit an External field directly, only via an appropriate editing program such as Edit, Paint or Draw. As long as the editor has been "seen" by the filer clicking on the on-screen button with SELECT will load the file into the editor and display it. You can edit the file and re-save it without altering its name and it will remain associated with the same field of the record. The contents of Text block and Picture fields are loaded into their editors by a double click.top of page

2.6.3 Clearing and exporting field contents
To 14.1.4 (Option buttons: warn of external deletion)
Clicking MENU over an External field makes available certain entries on the Field submenu which are shaded for other types of field. Remove external lets you delete the linked file from the database application (Note 5). As deleting a file is irreversible you would probably like to be warned when this is about to happen so the message "Delete object? Are you sure?" normally appears. Finally, Save contents on the Field submenu lets you export the linked file to a filer window or to another application.top of page

2.6.4 Editing scrollable lists
To 8.4.3 (Importing into a scrollable list)
Entering or altering data in a scrollable list is not very different from doing so in other types of field, but some keys do behave differently and there are extra keystrokes to access the special features of such lists. Scrollable lists may also be sorted on any column. Click MENU over the required column and choose Sort column <n> from the Field submenu. By default an ascending sort is used and sorting is case-sensitive, but you may choose a descending sort and also have case ignored. These options are chosen from a submenu identical to that used when sorting reports.

See also 2.4.4 for selectively undoing changes to Scrollable lists

2.6.4.1 Blanking whole list or selected columns
Data may be removed from a scrollable list by clicking MENU over the list and choosing Blank list from the Field submenu. Cofirmation is requested, after which the whole list will be blanked. If you wish to blank only certain columns first select them by clicking with adjust then proceed as above.top of page

2.6.4.2 Exporting and importing data
When MENU is clicked over a scrollable list two choices become available on the Field submenu in addition to Blank list noted above: top of page

2.7 Browsing and editing on-screen reports

To 2.2 (Moving about the database)
To 3.1.3 (Calling up records from the report window)

The generating of reports (lists) is fully described in Ch3 Creating reports from the database. Readers of the present chapter will, however, almost certainly have seen an on-screen report in the default (Columns) format in which each record occupies a single line. Attached to the bottom of the report window is a small pane which carries two buttons; an option button labelled Browse and an action button (shaded initially) called Edit. The functions associated with these buttons are described below.top of page

2.7.1 Browsing and searching
When the Browse button is selected the first record of the report is highlighted in light blue and the report window gains the input focus. Clicking SELECT over any line in the report moves the blue highlighting to that line. Buttons at the top of the tool-pane which are normally used to view records in the Record window now move the highlighting in the report instead. Thus single step moves the highlighting up or down by one record. Ends highlights the first and last records in the report. Fast forward/rewind steps through the report 10 records at a time. You can also search the report for an individual record by clicking the Search button and entering a key or record number in the usual way. If the required record is included in the report it will be highlighted. Automatic scrolling of the report window ensures that the highlighted line is always visible.

Function keys which duplicate the actions of the aforementioned buttons also work on the report. Moreover, when browsing a list in this way it seems natural to reach for the up and down arrow keys so these have been co-opted as additional browsing keys. Alone they operate as single step keys, with Shift they duplicate fast forward/rewind and with Ctrl they move to the start and end of the report.

While browsing a report you might find it convenient to highlight certain lines and have them stay highlighted when the aforementioned blue bar moves to another line. If you point at a line and click SELECT the line is highlighted in blue as noted above, but if you keep the SELECT button down briefly the highlighting changes to green (or red: see below) and the line stays green when you move to another record. Repeating this process removes the green highlighting. It's worth pointing out that this feature works whether the Browse button is selected or not. Combined with the Mark feature as described below it provides a useful way of creating an on-screen report, whittling it down to a subset of the report, then producing a new report comprising only the highlighted records.

The feature described above also connects with the record marking feature described in 3.8.1. The menu over the report window has an entry called Mark which only becomes available when one or more records has been highlighted as described. Choosing Mark then causes the highlighted records to be marked as if you had retrieved each one and selected the Mark record button on the Mark pane. There is also a submenu which lets you choose between marking the records for inclusion or exclusion. When Include is chosen the highlighting is in green; choosing Exclude causes the highlighting to be in red.

This highlighting feature also works when report-browsing is not enabled. It differs from the grey highlighting produced by a SELECT double-click (see 3.1.3) in that it does not retrieve the record and display it in the record window. top of page

2.7.2 Editing
You might use the above facility simply as a place-marker when checking a report, or as a handy way of moving about a long list. When doing so, however, one often notices mistakes in the data. One way of dealing with a mistake is to double-click the line with SELECT to retrieve the record (that's another useful attribute of the Report window!) and make the correction in the Record window, but there is a much nicer way.

Click the Edit button (which is no longer shaded). The button legend changes to Update and something interesting happens to the highlighted line; the background changes from grey to white and the familiar caret appears to the right of the first data item allowing it to be edited. You can also enter this mode with the keypress Ctrl-E. Only data derived from Editable and Check-box fields will be affected; you can't (unfortunately) edit data from Scrolling lists in this way and other field types such as Stamps and Computed fields aren't alterable by the user anyway.

While in this List edit mode the up and down arrow keys move the caret cyclically through the editable items of the line. The Return key also moves the caret from item to item. When you've finished editing either click the Update button at the foot of the report or press Ctrl-E again.(don't confuse this with the Update button on the tool-pane). The edited data is written to the database, the line reverts to its previous blue highlighted state and the Update button becomes the Edit button again. If you type Return when the caret is in the last editable field you won't need to click Update or press Ctrl-E. If the main window is visible you will see that the record has been retrieved and modified according to the changes you have made in the report. If you change your mind while still in List edit mode, press Escape and the line will be unaffected.

Apart from correcting odd errors in the data this feature could be immensely valuable if you need to alter one or more fields in a particular group of records but can't do it with the Global changes facility (see 2.5.5) because the alteration isn't the same for all the targetted records. Now you can create a report containing the relevant fields for the records which need altering and simply get on with the job by browsing and editing the report itself. Note also that this method works even when the report contains records from several subfiles.top of page

Restoring normal browsing and editing
Deselecting the Browse button returns all buttons and keys to their normal function, the Edit button is shaded once more and the record displayed in the main window is the one which was last highlighted in the report. Note that this could mean that the subfile has changed from what it was before you began report-editing.top of page

2.7.3 Limitations to list editing
There aren't many apart from the inability to edit Scrollable list data. Two relatively minor ones should, however, be mentioned.
Further points
When reports are saved in a database's PrintJobs directory using the Save as text choice on the report menu, they may be reloaded into Powerbase by choosing from a submenu attached to the Report=>Show reports submenu. A report saved and reloaded in this way retains all the features and functionality it had when first created. The colours are the same, lines which have been double-clicked to retrieve records show in light grey. If the report was saved with Browse selected and, therefore, with a line highlighted in blue, Browse will be selected on reloading the report and the same line will be highlighted. Browsing and editing as described above are possible but you must, of course, be careful in case the report is out of date due to deletion and insertion of records or alterations which have been made subsequent to saving the report.top of page

Turning browsing/list editing on and off
If you don't wish to use these facilities you can suppress the relevant buttons by editing the !Powerbase.Resources.Config file. Look for the token ListEdit and change the following word from YES to NO, then save the file.

Even when turned off in this way the pane may be toggled on and off with Ctrl-Shift-E. (i.e. it behaves like the Mark pane on the main window, which may also be suppressed by editing an entry in Config but can still be toggled on/off with Ctrl-M).top of page

2.8 Dragging objects onto the record window

There are numerous instances in which files and other objects might be dragged and dropped on the record window, not all of which are concerned with editing, and they are described in other chapters. What we are concerned with here is dragging a file, directory or application onto an Editable field whilst either Shift or Ctrl is held down. A Shift-drag enters the full pathname of the object into the field. This is just like Shift-dragging an object into an Edit or StrongEd window. If the field cannot hold the full name it will be left-truncated so that at least the leafname can be read. Ctrl-drag enters the leafname only into the field.

It is also worth knowing that Ctrl-double-click with SELECT over a field containing a full pathname will, if the leaf is a file, open the directory containing the file or, if the leaf is a directory, open that directory.



Notes

1. Colours referred to above which are used to identify key fields are the default colours but it is possible for the user to change them (see 14.5). In some databases you might be surprised to find more than one field with a red descriptor and yellow background. This is because a key can be derived from up to four separate fields. (back)

2. If the primary key is derived from two or more fields you needn't fill in all of them provided the result isn't a null key. (back)

3. The characters used to represent single and multiple wild-card characters may be changed via the Preferences window (see 14.1.3). (back)

4. If you drop a text file in the wrong place in the record window Powerbase will think you want to treat the file as CSV file and import data from it (see 8.4) and the Import text file window will appear. This is rather startling but harmless enough as long as you don't click on Import! Click on Cancel to get rid of the window. (back)

5. Unlinking objects from Referenced, Run file and Directory button fields is also done this way (see 4.2.8 and 4.2.13). (back)

6. If a global change involves multiplication you will need to temporarily change the multiple wild-card character to something other than * to avoid confusion with the multiplication operator, which is the same character. The change is made as described in Note 3 and $ is suggested as the alternative wild-card character. Failure to make such a change will result in the intended calculation being ignored. (back)

7. The list of windows featuring the Query panel is:

(back)

8. "Windows" does it the opposite way round! Grrr! Surely the more drastic operation—where the original is deleted—should be the one requiring the extra action?) (back)



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