Derivation Formula

Automatically calculates a field value and enters it into the field. When you specify a Derivation Formula as part of the Field Definition, the field value is derived by DataEase instead of being entered manually by the user. A Derivation Formula can be used to speed data entry and insure that a value is entered into the field. A derived value is entered in the field when the cursor passes through the field or when the record is saved.

Derivation Formula Components

A Derivation Formula can contain any combination of constant values, Field Names, DQL functions, mathematical operators and/or relational statistical 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 Derivation Formula

For example, the expression:

TOTAL ADULTS * 35.00

is the Derivation Formula in the ADULT FEES field on the MEMBERS form in the Club ParaDEASE application. This formula calculates the membership dues for all the adults entered in a record by multiplying the value in the TOTAL ADULTS field by a constant value of 35.00 dollars.

When you choose Derivation Formula, DataEase displays the Derivation portion of the Field Definition dialog.

The Derivation Formula Dialog

The Derivation Formula dialog displays four list boxes:

Functions

lists all the DQL predefined and custom defined functions and the relational statistical 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 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 on 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 operators, DataEase displays a numeric keypad for entering numbers into a Derivation Formula.

Types of Derivation Formulas

There are four types of Derivation Formulas:

• Default - assigns a constant value to a field. (For example, if you define the Derivation Formula "New York" in the DEPARTURE CITY field on the RESERVATIONS form, DataEase automatically enters New York in the DEPARTURE CITY field in each record).

• Lookup - displays a field value from a related form. (For example, if you define the Derivation Formula lookup "CLUBS" "CLUB NAME" in the CLUB NAME field on the RESERVATIONS form, DataEase finds the related record in the CLUBS form and returns this value in the CLUB NAME field).

• Calculation - uses arithmetic operators (+, -, *, /) and/or DQL relational statistical operators (count of, sum of, mean of, highest of, lowest of) to calculate a field value. (For example, if you define the Derivation Formula sum of "RESERVATION DETAIL" "PRICE" in the ADULT PRICE field on the RESERVATIONS form, DataEase totals the values in the PRICE field in all the related RESERVATION DETAIL records and displays it in the ADULT PRICE field).

• Function - uses any DQL function to generate a field value. (For example, if you define the Derivation Formula (upper(STATE)) in the STATE field on the MEMBERS form, DataEase displays the STATE field value in upper case (e.g., CA), regardless of whether it is entered ca, Ca, or CA.)

NOTE: A relationship between the two forms must exist for the lookup to work.

Any of the four types of Derivation Formulas can be combined in a complex formula.

How to Use a Derivation Formula to Calculate a Field Value

1. Double-click on the Field object.

2. DataEase displays the Field Definition dialog.

3. Click on the Derivation radio button.

4. DataEase opens the Derivation Formula editor.

5. Enter the Derivation 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.

7. DataEase checks 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: Use the numeric keypad to enter numbers into a Derivation 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., highest of MEMBERS ROOMS). Constant text values, like the state abbreviation "CT" 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 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 jointext functions in the expression:

jointext (FIRST NAME, jointext (" ", LAST NAME))

tell DataEase to join the two text strings: the value in the FIRST NAME field and the value in the LAST NAME 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 jointext (" ", LAST NAME) tells DataEase to join a leading space (indicated by the blank space between the quotation marks) to the value in the LAST NAME field. The outer function then joins the first name to the result of the first operation, producing the typical mailing label format, for example:

John Smith

The following example is a Derivation Formula in the DEPARTURE DATE field in the RESERVATIONS form:

if (DEPARTURE DATE < lookup "current" "date", blank, DEPARTURE DATE + (6 - weekday (DEPARTURE DATE)))

The first operation DataEase performs is the weekday (DEPARTURE DATE) function. 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 performs the if function. If the DEPARTURE DATE is before today's date, the field value is blank, otherwise add the result of the second operation to the DEPARTURE DATE that was entered. The final result is a DEPARTURE DATE of the first Saturday following the date entered by the user.

Derivation Formula Length

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

Entering a Derivation Formula Manually

A Derivation 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 displays an error message. You cannot save the field definition until you correct the error.

NOTE: If the Data Entry option on the Field Type dialog is set to Allowed, a Field Derivation Formula can be overridden by the user. If you don't want the user to override the derived value, set the Data Entry option to either Prevented or Virtual. See Chapter 6 in the Designer’s Guide for more information on the Data Entry options.

The Derivation Formula must generate a value that is compatible with the Data Type for the current field. For example, a Derivation Formula of "John" would not be a valid formula in a Date field.