Powerbase can apply two kinds of data input validation:
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.
If you Shift/double-click on the Powerbase icon the contents of the application directory will be displayed. In it you will see an ordinary directory called Resources and inside Resources you will find a file called ValStrings. You are recommended not to alter this file unless you understand exactly what you are doing and to keep a copy of the original. Examining it can, however, give you an insight into the validation process. Look, for example, at the entry beginning "03 Numeric:E:". This refers to field type 3 (Numeric) and "E" tells us that this field type is Editable. The rest of the line is the validation string of the writable icon which makes up a numeric field. It looks like this:The initial "A" is a command which signifies "Allow" and is the part of the validation string which especially concerns us. It determines what characters the field will allow you to type. In this case they are the numerals 0-9, the decimal point, plus and minus signs and <space>. Why the back-slash before the minus sign? Because "-" is a special character in an icon validation string (used, as in the 0-9 part of this example, to specify a range of characters) and so are the semicolon, tilde (~) and the backslash itself. To include any of these four characters in the validation string you must precede it with a backslash. The next character is a semicolon which indicates that a new command follows.
The "P" which comes next is the command "pointer". "ptr_write" is the name of the sprite used to represent the mouse pointer when it is over this type (and many other types) of field. You will have noticed that the usual RISC OS arrow changes to a blue vertical bar, somewhat resembling the caret, when over a writable field. "4,4" specifies the "active point" of the pointer. A couple of further examples should be sufficient. "01 Alphanumeric:E:" uses the same pointer type but the characters accepted are <space> (immediately after the "A" for "allow"), the upper-case letters A-Z, the lower-case letters a-z, the numerals 0-9, some common punctuation and the "½" character. You might want to add to this list. "06 Calculated:C:" uses a different pointer sprite (resembling a pocket calculator) and has no "Allow" command at all. (You can't type into this type of field; its value is calculated from other fields.) The "C" indicates that this is a Computed field.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 Powerbase will restore the previous contents of the field and print an error message.
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 is shaded. Validation tables have other uses too. They can have extra columns containing other data about the products, such as a name and a brief description, and lists can be printed in which this more informative data appears instead of the codes. You can also have Powerbase replace the typed-in code with a longer, more readable, form immediately on entry. Provided the substitute data will fit in the field, replacement occurs when you type Return or click the mouse in another field. If the replacement is too long then the typed-in data will be unchanged. This can be a great timesaver when a lot of data has to be entered.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 button in the Print options window.
Choose Create table from the Validation 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. (It is possible to increase the number later if necessary.) Next place the caret in the first row (row 0) of the scrolling list and enter the width (in characters) and the heading (optional) for the first column of the validation table.
If the table is to be used 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). 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. When you are satisfied with the data click Create and the table will be created and 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.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. A pair of bump icons, with the usual pop-up menu alternative, lets you cycle through the tables in memory. When you have the name of the required table displayed choose the column of the table to which the field is to be linked. It is recommended that column 0 normally 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. You will see that the foreground colour of the linked field has now changed from black to green. 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, at present, only one column may be so linked: you can't link two different validation tables to two columns of the same scrollable list.If you select this button before linking the table a third pair of bump icons becomes active, allowing you to choose which column of the table will replace the data which you type in. In the F.E. college database referred to in 2.2.5 the short subject codes (e.g. CHE) would be in the first column and the longer names (e.g. Chemistry) in the second. On typing CHE<Return> Powerbase would replace it with Chemistry. In such cases both the entries in the allowed list proper and those in the replacement list are considered equally valid and either may be entered. You could actually type Chemistry if you wished, instead of just CHE, but the former would obviously take longer to enter and you are far more likely to make a mistake, in which case Powerbase would object.
The Exact match button is normally selected to indicate that the only user inputs which will be accepted are those which exactly match an entry on the allowed list. Deselecting the button allows you to type inputs which are longer, but not shorter, than the entries on the allowed list so long as the leftmost part of the input matches such an entry. This feature is intended for use with the Replace on entry facility. The unmatched "tail" of your typed entry will be attached to the end of the replacement string. Suppose your database records numbers of items called Widgets, Doodahs and Thingummies. You decide to identify these names with the codes W, D and T you would normally put the codes in the first column of the table and the names in the second and select Replace on entry. You can then type W<Return> and it will be replaced with Widgets. What you might want, however, is to type W7 and have it replaced with Widgets, 7. Deselecting Exact match allows you to do just that since the W in W7 matches an entry in the validation list. The "expanded" entry in the second column is made to read "Widgets, " (note the comma and space) and Powerbase tacks the unmatched part of the entry onto the end of this so that W7 is replaced with Widgets, 7.
There are times when the validity of a data item in one field might depend on the contents of another field. In other words, entering a value in the first field which matches an item in the allowed list of the table isn't good enough; there must be a specific value in the second field as well. This sounds a bit complicated and a hypothetical example might make it clearer.
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 0 contains a 3-letter code for rapid entry of the course in ANCL and column 1 contains the full title of the course for printing on class lists, timetables etc.
The final column (2) indicates which type of student the course is suitable for. More than one type may be entered by separating the codes with commas. Thus, a course on the history of science might be aimed at science students and mixed discipline students and would have S,M in column 2. A course suitable for everybody would have A,S,M in column 2.
In the Link window we link ANCL to column 0 of the table, thus ensuring that only courses which are actually in the table are acceptable. We also select the option button to the left of the writable icon, enter TYPE in the writable icon, and click the associated bump icons until the matching column is 2. What we are saying here is that merely entering any old course from column 0 won't necessarily result in a valid entry; whether it does or not is conditional upon the contents of TYPE. Thus, if we try to enrol a type H student for a course 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 0.
When you select the Write back button the Link window grows an extension downward, displaying the numbers and headings of the columns in the currently-selected validation table. For each column there is a writable icon in which the tag of a data field may be entered. After completing the link you will find that entering data into the validated field and either typing Return or simply clicking the caret in another field causes the fields whose tags were entered as described above to be filled in with the relevant data from other columns in the validation table.
This feature partially overcomes a limitation of Powerbase in that whilst it is possible to include validation table data (from any column or columns) in reports (see 5.8), it is not possible to target such data in a search formula ˜ to be the subject of a query the data has to be in a record želd. By taking advatage of Write back you can at least have this data inserted into record želds automatically instead of having to type it yourself.
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 submenu).
Clicking MENU over a displayed table offers a menu with entries as follows:
Clear removes all data from the table, leaving it blank. Since wiping out a table in this way is pretty drastic you will be asked to confirm the operation before it actually takes place. It is possible to recover the table using Undo all provided the table has not been closed. When you close a database all the validation tables in memory are written to the disc so you will over-write your disc copy with the blank table.
Modify Note 2 brings up the same window as you used to create the table. You may then increase the number of rows, add extra columns, alter the order of existing columns or, indeed, do anything sensible. Be careful about renaming columns; combining this with shuffling the columns about is apt to cause confusion: Powerbase isn't a mind-reader! When you have made the required changes click on Modify.
Print outputs the contents of a validation table in the same format as a report. The output appears in a window from which it may be saved as a text file (see 3.1.1).
Sort <column> will sort the table on whichever column the mouse pointer was over when you clicked MENU on the table.
Undo change will restore the specific item which you were pointing at when you clicked MENU to the state it was in when the table was displayed. You cannot undo changes if you have closed the table then re-displayed it.
Undo all restores the entire table to the state it was in when first displayed, even if Clear has been used. You cannot undo changes if you have closed the table then re-displayed it.
Save leads to a Save box in which the default pathname points to a directory called ValTables inside the database application directory. Since that's where Powerbase expects to find the tables for a database you should normally accept this pathname by clicking on Save or typing Return. Only if you are transferring tables from one database to another should you need to drag the icon to a filer window.
All validation tables in memory are, in any case, saved to the ValTables directory of the database when you close the database or quit Powerbase and you can also make this happen at any time by clicking Update on the tool-paneSave as CSV leads to a Save box from which the table contents can be saved as a CSV file. The default pathname points to the database's PrintJobs directory. The file can be loaded into a blank validation table of appropriate format by dropping the file onto the open table (see 8.6 for further details).
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 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 wishes 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. 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 it 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 the first column of the table (column 0) and set the link from the record field to this column. The more detailed "expanded" entry should go in column 1. Printing with the Expand button in the Print options window selected will then print the column 1 instead of the column 0 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 printing (see 3.4). It is immaterial which row you click on; only the column matters and the highlighting to show which columns are selected always appears in the first row. The columns selected in all tables are saved as part of a print 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 button as described above. Printed reports will then include all the data from the highlighted columns. You will need to use this method if you want to print columns 0 and 1 of a table instead of printing column 1 instead of column 0 as described in the previous paragraph.v.9.20 supports an extension to the search formula syntax which enables tests on validated fields to operate on table columns other than the one linked to the field. The following, trivial example will help to clarify the concept. In the Elements database the field GP is linked to column 0 of a validation table called Group. Column 1 of this table holds the names of the periodic groups, e.g. "Halogen" for group 7. Consider the search formula:
The "/1" 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 0) of the table and look at whatever is on the same row in column 1 to see if it matches the string "Halogen".
A report produced using the above formula would contain exactly the same records as one produced simply with GP=7 because there‘s a straightforward one-to-one relationship between the two columns; neither contains any duplicated entries. That‘s why the example is trivial. For a non-trivial use consider a database of classical music in which we have fields COMP and WORK which hold the composer's name and the work's title for each record. Suppose COMP is linked to column 0 of a validation table which has three additional columns containing Nationality, and Birth and Death dates for each composer. Each of columns 1-3 would contain entries repeated several times - probably many times in the case of Nationality. Can we find out quickly which works (if any) were written by French composers born in 1880? Yes, by using the formula:
And which composers have a centenary or bicentenary in 2004?
which will, admittedly, list every work by composers who were born, or died, in 1804 or 1904 so the same composer might make many appearances in the report. Does it really matter?
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)