|
It is very important to make sure that the correct data is entered into a database. You can help reduce many errors by adding rules to your database, then database will not accept data that does not match the rules.
This is called Data Validation and the rules are called Validation Rules.
Simply setting a field type to be something other than text is a form of simple data validation. For example, you would not be able to enter an 'O' (the letter) instead of a '0' (the number) into a number field.
In DATAPOWER, most
Data Validation is done by selecting:
Field => Options => Check or <shift> <ctrl>
<c> and then entering the check in the
box.
You must be careful how you enter these checks or you will get error messages.
Add your validation checks one at a time before you enter any data. Use the following procedure:
- Create a field if the correct type (text, number etc.)
- Add the validation check
- Enter some dummy data to check it works and the error messages make sense (see bottom of page)
- Delete all the dummy data (Edit => Delete all records is the quickest way to do this)
Syntax rules:
- Any text should be enclosed in double quotes such as "blue"
- Any fieldnames should be enclosed in single quotes such as 'surname' (If you want to check the name of a field, select it and then use Field => Name to view or alter it)
- You can use a ? or a * as wildcards:
- A ? represents one character of text. i.e. b???? could be brown or black but not blue
- A * represents text characters of any length. i.e. b* could be brown, black or blue
| Possible validation checks you could use | Possible validation
rule
using DataPower
|
Data that would be accepted | Data that would be rejected | How you would use the validation |
| Limiting what text can be entered |
LIKE "red" OR "blue" | Blue | Green or Blu |
To make sure only certain text is entered (can be combined with a 'drop down list') |
| Limiting what numbers can be entered | >5 AND <=10 | 7 | 3 or 11 | To check that nonsense numbers are not entered |
| Limiting text to an exact length and format | LIKE "??-???" | DR-567 | J 453 | To limit a text entry to a certain format and length (each ? represents a single letter or number) |
| Limiting the length of text | LEN ('fieldname')<=5 | xdfr | sdffsd | Check the length of a text entry |
| Limiting text to an exact format | LIKE "BB*" | BB206 | B206 | Match an entry to a certain format (the * represents text characters of any length) |
| Checking the number data in one field against the data in another | 'max' > 'min' | Field 'max' contains 45 and field 'min' contains 50 | Field 'max' contains 50 and field 'min' contains 45 | Use this to check the data in one field against the data in another (note the use of single quotes when referring to a field name) |
Improving the error message:
The checks
above will only produce a very simple error message like the one shown on the
right.
It is better to make the error message more meaningful by using checks like the examples below:
- IF Len('fieldname')<=5 THEN TRUE ELSE ERROR ("Only 5 characters allowed")
- IF LIKE "red" OR LIKE "blue" THEN TRUE ELSE ERROR ("Only red and blue allowed")
- IF LIKE "??-???" THEN TRUE ELSE ERROR ("Your entry must be in the format ??-??? where ? represents a single character")
- IF <1000 THEN TRUE ELSE ERROR ("Number must be below 1000")
- IF >=10 and <=100 THEN TRUE ELSE ERROR ("Number must be between 10 and 100")
- IF 'max' > 'min' THEN TRUE ELSE ERROR ("The max value must be greater than the min value you previously entered")
- IF >10 OR NULL THEN TRUE ELSE ERROR ("If you enter a value it must be greater than 10")
| 1b Coursework Index | Tutorials Index |