Using Oracle FastFormula for Validation
You can use Oracle FastFormula to validate user entries into the element input values, and to user tables that you define. In both cases, you must write and validate the formula before you define the element or table, so that you can select the formula from a list in the Element window or Columns window.
In the Formulas window, select formula type Element Input Validation or User Table Validation.
When writing either type of formula, you must observe the following rules:
- There must be one input value, of type text, and it must be called entry_value.
- The formula must set and return a local variable giving the status of the validation (success or error). This variable must be called formula_status and have the value 's' (success) or 'e' (error).
- Optionally, the formula can also return a text variable giving an explanatory message. The returned message variable must be called formula_message and can contain any text. It can be returned with both successful and unsuccessful statuses.
- The formula must not return any other results.
For an element input value validation formula, you must also observe the following rules:
- You cannot use the element's other pay and input values in the formula.
- You cannot return a value to another pay or input value.
All entry values are stored in the database as text items. Therefore, if you want to validate an entry value as a date or number, you must use Oracle FastFormula's conversion function to convert the text into a date or number type variable. For example:
TO_NUM (entry_value)
TO_DATE(entry_value,'DD-MON-YYYY')
Examples
Checking an Element Entry
The formula below checks that the entry value of the Salary element does not exceed 200 000.
/* Formula Name: Salary Range */
/* Formula Type: Element Input Validation */
INPUTS ARE entry_value (text)
IF TO_NUM(entry_value) > 200000
THEN
(
formula_status = 'e'
formula_message = 'Too much money . . . try again!'
)
ELSE
(
formula_status = 's'
formula_message = 'Fine'
)
RETURN formula_status, formula_message
Checking a User Table Entry
The formula below checks that the deduction entered in the Union A column of the Union Dues table is between 10.00 and 20.00.
/* Formula Name: Union A Dues Validation */
/* Formula Type: User Table Validation */
INPUTS ARE entry_value (text)
IF TO_NUMBER(entry_value) < 10.00 OR
TO_NUMBER(entry_value) > 20.00
THEN
(
formula_status = 'e'
formula_message = 'Error: Union A dues must be between
$10.00 and $20.00.'
)
ELSE
(
formula_status = 's'
formula_message = ' '
)
RETURN formula_status, formula_message
See Also
Defining an Element
Defining an Element's Input Values
Setting Up User Tables
