This means exercising control over what characters a field will accept. RISC OS provides the means to allow writable icons to accept only certain characters, or ranges of characters, and reject others. The disallowed characters are simply ignored so that attempts to type them have no effect. Powerbase exploits this feature to constrain character input into certain types of field. Numeric fields, for example, accept only the numerals 0-9, plus and minus, decimal point and space. Upper case fields accept letters A-Z, numerals 0-9, space and full-stop.
Table validation is used where a record field is only allowed to have certain "values". A manufacturing company, for instance, will have a product code for every item it makes and a database of customers which the firm supplies will make use of such codes to identify the items. It is essential that whoever enters data is prevented from typing a spurious code. This can be achieved by linking the appropriate field to a validation table. Fields linked in this way are recognizable by having their text dark green instead of black. Powerbase will allow you to type invalid data into the field initially but, when you click the mouse over a new field or type Return, the linked validation table is scanned to see whether what you entered in the field is on the allowed list. If it isn't you may be offered the opportunity to add a new entry to the table. A positive response displays the table enabling you to modify it, otherwise the previous contents of the field are restored. (See Note 4.)
It is possible to turn off validation by means of an option button on the Preferences window. It may also be toggled on and off with Ctrl F9. If validation is turned off the Table button on the tool-pane is shaded.
Column | Heading |
1 | Code |
2 | Surname |
3 | First names |
4 | Country |
5 | Born |
6 | Died |
Validation tables have uses beyond mere input vetting. The aforementioned product database can have extra columns containing other data about the products, such as a name and a brief description, and lists can be produced in which this information appears instead of the codes. A very important feature is that this additional data can be queried when creating reports (see 5.9). The Music database uses a 6-column table linked to the Composer field (COMP). The data held in the columns is as shown (right). Column 1 holds a 6-letter code which may be used for rapid entry into the Composer field while columns 5 and 6 contain the year of birth and death respectively. The Replace on entry feature (see 5.4.1) is used replace the typed-in code with a longer, more readable, form (the surname) immediately on entry.
The F.E. college database referred to in 2.2.5 makes extensive use of validation tables for subjects, courses, tutors and schools of origin. Not only does this trap typing errors but the data entry is greatly speeded up when one can type ELL instead of English Language and Literature, yet have the latter printed out on a list by selecting the Expand codes button in the Report options window.
Choose Create table from the Validation tables submenu. Give the table a suitable name and enter the number of rows required. This will be the maximum number of items on the allowed list initially but the number can be increased later if necessary. Next place the caret in the first row of the scrolling list and enter the width (in characters) and the heading (which is optional) for the first column of the validation table.
If the table is to be used only to constrain data entry to an allowed list one column may be all you need. The most common situation, however, is to have two columns, the first holding the allowed list and the second being an expansion or explanation of the former. Use the next row of the scrolling list to enter the column width and heading for the second table column. It is possible to use very large and complex validation tables with all kinds of data associated with each item on the allowed list. There is room to specify 20 columns, but even this can be increased if necessary (see 14.8). The illustration shows the setup for a validation table called Products with three rows and two columns. The first column is headed Code and will hold single characters, the second holds the name of the Product which can be up to 12 characters.
You can if you wish choose the Foreground and Background colours of both the heading and the body of your table by clicking repeatedly with SELECT or ADJUST on the Heading and Data icons. Whether this changes the foreground or background colour depends on which of the two radio buttons is selected. In this example the headings have been made red on yellow and the table body black on cream.
When you are satisfied click Create and the table will be displayed. It is not saved on disc at this stage. You may enter data into it now or later. Entries may be freely altered and overwritten. The table will be saved when you close the database or quit Powerbase but if you want to play safe click Update on the tool-pane. Here is the table defined above with data for the three products and their codes entered. Notice that although Code was specified as the heading for column 1 it has been truncated to C because the column is only one character wide.
To link your newly-created validation table to a field click MENU over the field and choose Link to table from the Field submenu Note 1. This opens the Link window. You can achieve the same result by placing the caret in the field and typing Ctrl L. A pair of bump icons, with the usual pop-up menu alternative, lets you choose the required table, if there is more than one. When you have the name of the table displayed choose the column of the table to which the field is to be linked. It is recommended that column 1 is used to provide the link (and therefore contain the data items for the allowed list) and this is set by default. You may, however, link to any column in the table and another pair of bump icons lets you cycle through the column numbers.
Select Linked to table and click on OK. The foreground colour of the linked field changes from black to green but this may not be easy to see if the field is empty. Place the caret in the field and click on Table on the tool-pane. The validation table will be displayed.You may link a validation table to a scrollable list. The link is to a specified column of the list (make sure the pointer is over the required column when you click MENU) and different list columns may be linked to different tables or to different columns (or even the same column) of one table.
To unlink a field from a validation table click MENU over the field, choose Link to table from the Field submenu to display the Link window, deselect the Linked to table button and click on OK.
For the purpose of providing suitable ancillary courses, A-level students in a Further Education College are categorised according to whether they take all Science/Maths A-levels (code=S), all Arts/Humanities (code=A) or a mixture of both (code=M). These single-letter codes are entered in a field with the tag TYPE and the ancillary courses are entered in a field called ANCL. Some of the ancillary courses are suitable for anybody, others are specific for science or arts students and yet others are especially aimed at the mixed-discipline students. These courses are listed in a validation table which has three columns. Column 1 contains a 4-letter code for rapid entry of the course in ANCL and column 2 contains the full title of the course for printing on class lists, timetables etc. The third column indicates which type of student the course is suitable for. More than one type may be entered by separating the codes with commas. Part of such a table might look like that on the right.
The Link window is now set up as shown. What we are saying here is that entering a course code from column 1 won't necessarily result in an acceptable entry; whether it does or not is conditional upon the contents of TYPE. Thus, if we try to enrol a type A student for a course such as INDC which is only suitable for type S students the entry will be rejected with an explanatory message, even though the course code is valid insofar as it is present in column 1.
Placing the mouse pointer over a linked record field and double-clicking with SELECT makes a small window pop up to the right of the field. This shows all the data which is on the same row of the validation table as the linked item. The item from the allowed list is highlighted in green and the item (if any) to be substituted on entry is shown in red. This feature is very useful if you are examining a database which uses coded data and you encounter an unfamiliar code. You can also make this window appear automatically whenever the caret enters a linked field. To turn the feature on choose Preferences from the iconbar menu and select the Display linked table data button.
When linked to a field in the database record the complete table may be displayed by placing the caret in the linked field and clicking Table button (F9) on the tool-pane. Alternatively, any table present in memory, whether linked to a data field or not, may be displayed by selecting it from the Display table submenu (reached from the Validation tables submenu).
Clicking MENU over a displayed table offers a menu with entries as follows:
A table linked to a field is automatically loaded when the database is opened. If not yet linked, however, the table won't be in memory unless you have just created it. Show files on the Validation tables submenu (Ctrl Q) will display the ValTables directory and the required tables can dragged onto the record window (or simply double-clicked). Even more conveniently, Show files is provided with its own submenu listing the files in ValTables, and tables may be loaded by choosing them from this submenu. Each table is displayed as it loads.
There may be times when you wish to use a validation table to hold some data, but don't want to use it to validate input into a field or to link it to a field at all, but you do want it loaded so that the data it contains is accessible. It is possible to load a validation table once the database is open by using the procedure described in the previous paragraph, but a more convenient solution is to add a "+" character to the end of the table's name (the usual limit of 10 characters applies to the name length). Powerbase will then load the table whether any field is linked to it or not.Validation tables are often used to allow short codes to be entered in records but with a link to a more descriptive entry in another column of the table. If this is all that is required then always put the data which makes up the actual allowed list, i.e. the items which are allowed in the fields of the main record, into column 1 and set the link from the record field to this column. The more detailed expanded entry should go in column 2. Printing with the Expand codes button in the Report options window selected will then print the column 2 instead of the column 1 entry.
You can also include data from other columns of a table by displaying the table and clicking in the required columns with ADJUST, exactly like selecting main record fields for inclusion (see 3.4). The columns selected in all tables are saved as part of a field Selection file and may therefore be retrieved for future use. As well as highlighting the required columns you will also need to select the Expand codes button as described above. Reports will then include all the data from the highlighted columns. You will need to use this method if you want to include columns 1 and 2 of a table rather than column 2 instead of column 1 as described in the previous paragraph.An extension to the search formula syntax enables tests on validated fields to apply to any column of a table linked to the target field. The following trivial example will help to clarify the concept. In the Elements database the field GP is linked to column 1 of a validation table called Group. Column 2 of this table holds the names of the periodic groups, e.g. Halogen for group 7. Consider the search formula GP/2=Halogen. The "/2" suffix to GP tells Powerbase not to test the actual value present in GP but, instead, to find that value in the linked column (column 1) of the table and look at whatever is on the same row in column 2 to see if it matches the string "Halogen". As an alternative to the <tag>/<column number> syntax you can use <tag>/<column heading> which is handier if the column you want actually has a heading (remember column headings are optional). When using this alternative syntax the case of letters doesn't matter and you needn't include all the heading; just enough to identify the column uniquely.
Column | Heading |
1 | Code |
2 | Surname |
3 | First names |
4 | Country |
5 | Born |
6 | Died |
For a more complex example. which composers have a centenary or bicentenary, sesquicentenary etc. in 2006? We can find out with COMP/born,COMP/died=1906,1856,1806,1756 which will, admittedly, list every work by composers who were born, or died, in any of those years so the same composer might make many appearances in the report. Does it really matter? All the works of Mozart, Schumann and Shostakovich are reported. Music by composers called Anton or its near equivalents? Try COMP/first{Anton which produces the works of Anton Bruckner and Antonin Dvorak.
If you place the caret in a database field and then double-click over an entry in an open validation table the table entry will be copied into the data field, if it will fit. Nothing is copied if the item is too long to fit. This also works for entering validation table data into writable icons in dialogue boxes and into Scrollable lists.
In this last case the caret moves to the next cell of the list after the string has been inserted, thus making it very easy to insert several items in quick succession. If, on reaching the last cell in the last row, you hold down Shift while double-clicking in the validation table, a new row is inserted in the Scrollable list.1. If you have closed the database the table won't have been reloaded on opening again. See 5.7 for how to load your table for linking. (back)
2. This feature cannot be used on tables created with early versions of Powerbase and the option will be shaded in such cases. The commonest reason for needing to modify such a table is to increase the number of rows. First export the data as a CSV file then create a new table with the required number of rows and drop the CSV file onto it (see 8.6). (back)
3. Note that it doesn't matter under what filename the user function is saved: it's the actual function name which is important. If the function is to use column 3 of the Composer table it must be called FNUComposer3 but Initials is a good name for the file itself since it suggests what the function actually does. (back)
4. Whether or not additions to the validation table are permitted depends on the setting of the token AddIt in the !Powerbase.Resources.Config file. The default setting is NO but you might want to change it to YES if you are the sole user of the database. "Manager"-level access is, in any case, required so you could allow additions to the table for yourself whilst forbidden them to someone who simply has read/write access. (back)