When you click on Validation Formula, DataEase lets you specify a Validation Formula, which checks whether a field entry falls within a range of acceptable values. If the value falls outside the established range, DataEase displays a dialog informing the user of the error, and does not let the cursor exit the field until the value has been changed to conform to the validation check.
A Validation Formula can contain any combination of constant values, field values, lookup formulas, relational statistical operators, and any of the following comparison operators.
A constant value is a text, date, time, numeric string, or number value that does not change.
A DQL Function returns a value in a different format.
A relationship name refers to another table that the current form's underlying table is related to.
A relational statistical operator lets you extract and summarize data that is stored in a related table.
For example, the expression:
PRICE between 600.00 to 2200.00
..could be used as a Validation Formula in the PRICE field on the PRICES form in Club ParaDEASE., in order to set a lower limit of 600 and a higher limit of 2200. If an operator tried to enter a value outside this range, DataEase reports an error and prevents the cursor from leaving the field until the error is corrected.
Note: When you create a Validation Formula, you can establish either a lower limit, an upper limit, or both.
Functions lists all the DQL predefined and custom-defined functions and the relational statistics operators. You can use the scroll bar to browse through the list of available functions.
Relationships displays all the relationships for the active form, including the current relationship which provides access to system information (current time, current date, current user name, current computername, and current user level). Use the scroll bar to view all the column names.
Columns displays all the field names for the active form. If a relationship is selected from the Relationships list, the columns list displays the names of the fields in the related form. Use the scroll bar to see all the column names.
Operators lists comparison operators, punctuation marks, and wild card characters. Use the scroll bar to view all the operators. Next to the list box of operators, DataEase displays a Numeric Keypad for entering numbers into a Derivation Formula (The numbers at the top of your keyboard also work - the Keypad is for extra convenience).
A Validation Formula can also be entered manually. When you click the OK button to save the formula, DataEase performs a spelling and syntax check. If any errors are found, DataEase beeps and positions the cursor at the start of the invalid word or phrase. You cannot save the field definition until you correct the error.
DataEase allows you to enter a Validation Formula up to 4000 characters long. If the formula you enter exceeds the area of the Validation Formula box, the formula scrolls, allowing you to continue typing.
The Validation Formula must test for a value that is compatible with the Data Type for the current field. For example, a Validation Formula that sets a lower limit of "a" and a higher limit of "z" is not a valid formula for a Date field.
Use the numeric keypad to enter numbers into a Validation Formula. Constant values and the associated quotation marks must be entered into a formula manually. If a Field Name or a relationship name contains more than one word, it must be enclosed in quotation marks (e.g. count of "FAMILY MEMBERS" "Last Name"). Otherwise, you can omit the quotation marks (e.g. count of MEMBERS Rooms). Constant text values, like the last name "Smith" must also be entered in quotation marks.
A constant value must follow the rules listed below, so DataEase can distinguish constants from Field Names, and differentiate different types of constants.
You can use parentheses to control the order in which mathematical operations are performed. When DataEase calculates a value, the operations in a Derivation Formula are performed from left to right. Multiplication and division take precedence over addition and subtraction. For example, the parentheses in the expression:
(6 + 12) * 2
..tell DataEase to perform the addition operation (6 + 12) and then multiply the result by 2.
Parentheses are also used around any expression that follows a DQL function. For example, the weekday function in the expression:
DEPARTURE DATE >= DEPARTURE DATE + (6-weekday(DEPARTURE DATE))
..tells DataEase to determine the day of the week using the value from the DEPARTURE DATE field.
When multiple sets of parentheses appear in an expression, the operation of the innermost set of parentheses is performed first. In the above example, the inner function;
Weekday(DEPARTURE DATE)
..tells DataEase to determine the day of the week from the value in the DEPARTURE DATE field. The result is a number from 1-7. The second operation is to subtract the result of the first operation from 6. The new result is a number from -1 to 5. In the final operation, DataEase adds the result to the date in the DEPARTURE DATE field.
The final result is a Saturday that is greater than or equal to the date entered by the user. This formula is used to verify that the date entered into the DEPARTURE DATE field is always a Saturday.
The table below contains some sample Validation Formulas.
