Home   Index   list of chapters    previous chapter   next chapter  
 

Ch 5 - Input Validation and Validation Tables

Powerbase can apply two kinds of data input validation:
To 14.1.4 (Option buttons: validate input)

5.1 Character 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. 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.

5.2 Table validation

To 2.4.1 (Adding new records)
To 3.10.2 (The rest of the Report options)
To 4.2.5 (Other types of Editable field)
To 6.1.3 (Calculations using non-numeric fields)
To 5.4.1 (The Replace on entry feature)
To 14.6 (Defining the function keys)

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

5.3 Creating a validation table

To 5.4.2 (When to turn off the Exact match button
table/png

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.

tab/png

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

5.4 Linking tables to fields

link/png

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

5.4.1 The Replace on entry feature
To 5.2 (Table validation)
If you select this button before linking the table a third group of icons becomes active, allowing you to choose which column of the table will replace the data which you type in. Suppose, using the Composer table described in 5.2, you select column 2 as the replacement. Entering shost in the linked field and typing Return would cause shost to be replaced with Shostakovich. The linked field needs to be long enough to hold the replacement string, of course; otherwise the string will be truncated. When this feature is in use both the value from the allowed list and the replacement string are acceptable as input but the latter would obviously take longer to enter and you are far more likely to make a mistake, in which case your entry would be rejected.top of page

5.4.2 When to turn off the Exact match button
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 (or on the replacement list if any. See above). 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 that, in the example in 5.3, the strings in column 2 have a comma and a space appended to the end. With Replace on entry selected and Exact match deselected you could type entries such as W7, D42, W13, T75 etc. and they would be accepted because the first part in each case matches an entry in column 1 which is the allowed list. When Return is typed they will be replaced with Widgets, 7; Doodahs, 42; Widgets, 13 and Thingummies, 75. That could save a lot of typing in a stock-taking database.top of page

5.4.3 Conditional validation
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.

tab2/png

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.

link2/png

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.

5.4.4 Write back
wback/png 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. Entering the tags can easily be done by placing the caret in the appropriate icon under Copy to field and then either choosing the tag using the Menu of fields button or Ctrl-clicking the required data field itself. You might not want writeback to occur when Replace on entry is active, in which case select the Not when replaced button.top of page

5.4.5 ...and enhanced Write back!
Write back has an additional twist. Instead of merely entering the string from the table into a field you can process it with a user function (see 6.5) and have the result of the function entered into the field. To use this feature you must provide a user function whose name is identical to that of the validation table with the required column number appended. The function should have just one string parameter and return a string result. If no such function is found the raw data string will be entered. There is an example in the Music database which contains a field for the surname of the composer (COMP) and another for his initials (INIT) The validation table Composer is linked to COMP and column 3 of the table holds the composer's given names from which the initials are derived. The required user function is therefore called FNUComposer3 and the BASIC code for the function is saved as Initials (see Note 3) in the database's UserFuncs directory. It takes the column 3 entry, splits off the initials, placing a fullstop after each, and returns the result to be written back to INIT. Try the effect by displaying a blank record and entering either Mozart or the code mozar in COMP (Replace on entry is in use) and you will see the initials W.A. (from Wolfgang Amadeus) appear in INIT when you type Return.
 top of page

5.5 Displaying validation tables

To 14.1.4 (Option buttons: display linked table data)

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

5.6 The validation table menu

Clicking MENU over a displayed table offers a menu with entries as follows:

top of page

5.7 Loading validation tables

To Note 1

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

5.8 Including validation table data in reports

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

5.9 Testing validation table contents in search formulae

To 3.5.1 (The construction of search formulae)
To 7.3 (Indexes and speed of reporting)

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
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 the Music database. This contains 100 entries of works on CD. We have fields COMP and WORK which hold the composer's surname and the work's title for each record. COMP is linked to column 1 of a validation table which contains 5-letter codes identifying the composer. The table has five additional columns containing further information about the composers.

Can we find out quickly which works (if any) were written by English composers? Yes, by using the formula: COMP/4=England or, alternatively, COMP/country=England, either of which reports the same six works.

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

5.10 Entering validation table data into a record

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.




Notes

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)


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