An InfoMaker expression is a combination of data, operators, and functions that, when evaluated, results in a value. An expression can include column names, operators, functions, and constants such as numbers and text strings.
For information about functions that you can use in expressions, see Using DataWindow expression and InfoMaker functions, or look up the function you want in online help.
In painters, you use expressions in these ways:
|
In this painter |
Expressions are used in |
|---|---|
|
Report painter |
Computed fields Conditional expressions for property values Filters Sorting Series and values in graphs Columns, rows, and values in crosstabs |
|
Form painter |
Computed fields Conditional expressions for property values Validation rules |
|
Database painter |
Validation rules |
Other types of expressions you use
You also use expressions in Quick Select, SQL Select, and the Query painter to specify selection criteria, and in SQL Select and the Query painter to create computed columns. In these painters you are using SQL operators and DBMS-specific functions, not operators and functions, to create expressions.
Some of the specific places where you use expressions are described here.
In computed fields
Expressions for computed fields can evaluate to any value. The datatype of the expression becomes the datatype of the computed field:
|
Expression |
Description |
|---|---|
|
Today ( ) |
Displays the date using the Today function |
|
Salary/12 |
Computes the monthly salary |
|
Sum (Salary for group 1) |
Computes the salary for the first group using the Sum aggregate function |
|
Price*Quantity |
Computes the total cost |
Expressions for graphs and crosstabs
You can use similar expressions for series and values in graphs and for columns, rows, and values in crosstabs.
In filters
Filter expressions are boolean expressions that must evaluate to true or false:
|
Expression |
Description |
|---|---|
|
Academics = "*****" AND Cost = "$$$" |
Displays data only for colleges with both a 5-star academic rating and a $$$ cost rating |
|
Emp_sal < 50000 |
Displays data for employees with salaries less than $50,000 |
|
Salary > 50000 AND Dept_id BETWEEN 400 AND 700 |
Displays data for employees in departments 400, 500, 600, and 700 with salaries greater than $50,000 |
|
Month(Bdate) = 9 OR Month(Bdate) = 2 |
Displays data for people with birth dates in September or February |
|
Match ( Lname, "[ ^ABC ]" ) |
Displays data for people whose last name begins with A, B, or C |
In validation rules for table columns
Validation rules are boolean expressions that compare column data with values and that use relational and logical operators. When the validation rule evaluates to false, the data in the column is rejected.
In the Form painter
When you specify a validation rule in the Form painter, you should validate the newly entered value. To refer to the newly entered value, use the GetText function. Because GetText returns a string, you also need a data conversion function (such as Integer or Real) if you compare the value to other types of data.
If you include the column name in the expression, you get the value that already exists for the column instead of the newly entered value that needs validating.
In the Database painter
When you specify the validation rule in the Database painter, you are defining a general rule that can be applied to any column. Use @placeholder to stand for the newly entered value. The name you use for @placeholder is irrelevant. You can assign the rule to any column that has a datatype appropriate for the comparison.
When you define a form, a validation rule assigned to a column is brought into the form and converted to form syntax. @placeholder is converted to GetText and the appropriate datatype conversion function.
Other columns in the rule
You can refer to values in other columns for the current row by specifying their names in the validation rule:
|
Expression in Database painter |
Expression in Form painter |
Description |
|---|---|---|
|
@column >= 10000 |
Integer(GetText())>= 10000 |
If a user enters a salary below $10,000, an error message displays. |
|
@column IN (100, 200, 300) |
Integer(GetText()) IN (100, 200, 300) |
If a user does not enter a department ID of 100, 200, or 300, an error message displays. |
|
@salary > 0 |
Long(GetText()) > 0 |
If a user does not enter a positive number, an error message displays. |
|
Match(@disc_price, "^[0-9]+$") and @disc_price < Full_Price |
Match(GetText( ), "^[0-9]+$") and Real(GetText()) < Full_Price |
If a user enters any characters other than digits, or the resulting number is greater than or equal to the value in the Full_Price column, an error message displays. |
