Validation Formula

lets you verify a field entry by establishing a range of acceptable values. When you enter a value in the field, DataEase checks the value to make sure that it falls within the specified range. If the value you entered falls outside the established range, DataEase first displays an information message dialog followed by an alert message dialog.

DataEase does not let the cursor exit the field until the value has been changed to conform to the validation check.

Validation Formula Components

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.

Sample Validation Formula

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 the Club ParaDEASE application to set a lower limit of 600.00 and an upper limit of 2200.00. If you enter a value lower than 600.00 or higher than 2200.00, DataEase displays an error message and restricts cursor movement until the error is corrected.

NOTE: When you create a Validation Formula, you can establish either a lower limit, an upper limit, or both.

The Validation Formula Dialog

The Validation Formula dialog displays four list boxes:

Functions

lists all the DQL predefined and custom defined functions and the relational operators. 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 such as the current time, current date, current user name, current computername, and current user level. Use the scroll bar to view all the relationships.

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 view 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 comparison operators, DataEase displays a numeric key pad for entering numbers into a Validation Formula.

How to Enter a Validation Formula

1. Double-click on the Field object.

2. DataEase displays the Field Definition dialog.

3. Click on the Validation radio button.

4. DataEase opens the Validation Formula editor.

5. Enter the Validation Formula manually, or click on a selection from the four list boxes and the numerical keypad to enter field names, relationships, functions, operators, and numbers in the formula box.

6. When you finish entering the formula, click OK. DataEase check the spelling and syntax. If DataEase finds an error, it positions the cursor at the start of the invalid word or expression. If the formula is valid, DataEase saves it as part of the field definition.

NOTE: Constant values (and any associated quotation marks) must be entered into the formula manually. If a Field Name or a relationship name is more than one word long, it must be enclosed in quotation marks. A constant text value, like the last name "Smith" must also be entered in quotation marks.

Formula Syntax

A constant value must follow the rules listed below, so DataEase can distinguish constants from Field Names and differentiate different types of constants.

Using Parentheses in a Formula

You can use parentheses to control the order in which the operations are performed. When DataEase calculates a value, the operations in a Validation 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 + 2) 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 in the interior-most set of parentheses is performed first. In this 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 to 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.

Validation Formula Length

DataEase allows you to enter a Validation Formula up to 4,000 characters long. If the formula you enter exceeds the area of the Validation Formula box, the formula scrolls, allowing you to continue typing.

Entering a Validation Formula Manually

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.

NOTE: 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 lower limit of "z" is not a valid formula in a Date field.