Type
Conditional Function

Purpose
The if function evaluates a specified condition and returns one of two specified values based on whether the condition is true or false.

Syntax
if( CONDITION, TRUE VALUE, FALSE VALUE)

Returns
The true value if the specified condition is true. The false value if the specified condition is false.

Usage
In a field Derivation formula, you can use the if function to:
derive a field value based on one of two specified values or expressions depending on whether a condition is true or false (see Example 1).
In a Validation formula, you can use the if function to:
check a field's validity based on one of two specified expressions depending on whether a condition is true or false (see Example 2).

When you use an if function in a Derivation or Validation formula, the true value and false value parameters must be of the same data type as the field being derived or validated.
In a script, you can use the if function to:
assign one of two specified values to a field or variable based on whether the condition is true or false (see Example3).
hide or show a field, calculated value, or text string in a procedure's output depending on whether a condition is true or false (see Example 4).

A script, Derivation formula, or Validation formula may have multiple if function statements nested within one another.
Example 1
Using the if function in a Derivation formula:
The following example shows the iffunction used to derive the value in a Text field. The function returns one of two text values depending on the current time in the computer's system clock.

if( ampm( current time) = "AM" , " in the morning " ,
"in the afternoon" )

Because this Derivation formula returns a value for a Text field, the true value ("in the morning") and the false value ("in the afternoon") are both text expressions.
The following example shows the iffunction used in a Derivation formula for a Number field named DISCOUNT. The formula returns one of two DISCOUNT values depending on the value in another field, TOTAL DUE.

if( TOTAL DUE >= 500, TOTAL DUE * 0.15, TOTAL DUE * 0.03)

This formula evaluates the value in the TOTAL DUE field. If that value is greater than or equal to $500 (making the condition true), the formula sets the value of DISCOUNT to TOTAL DUE * 0.15 If the value of TOTAL DUE is less than $500 (making the condition false), the formula sets the value of DISCOUNT to TOTAL DUE * 0.03.
By nesting if statements inside one another, you can define a Derivation formula that evaluates multiple conditions and returns one of several values. The example below shows how one if statement can be nested inside another to return one of three DISCOUNT values depending on the value in the TOTAL DUE field:

if( TOTAL DUE >= 500, TOTAL DUE * 0.15,
if( TOTAL DUE >= 200, TOTAL DUE * 0.08, TOTAL DUE * 0.03) )

This formula evaluates the value in the TOTAL DUE field. If that value is greater than or equal to $500 (making the condition true), the formula sets the value of DISCOUNT to the TOTAL DUE multiplied by 0.15. If the value is less than $500 (making the condition false), a second if statement evaluates TOTAL DUE to determine if its value is greater than or equal to $200. If this second condition is true, the value of DISCOUNT is set to the TOTAL DUE multiplied by 0.08. Otherwise DISCOUNT is set to TOTAL DUE * 0.03.

Example 2
Using the if function in a Validation formula:
The example below shows how the if function can be used in a Validation formula to conditionally apply one of two validation criteria to a field named CREDIT PURCHASES.

if( OVERDUE DAYS < 90, <=5000, <50)

This Validation formula evaluates the value in a field named OVERDUE DAYS (this field tracks a customer's past-due invoices). If that value is less than 90 (meaning the customer has no invoices over 90 days past due), then any value up to $5000 is valid in the CREDIT PURCHASES field. If the value of OVERDUE DAYS exceeds 90, then only up to $50 can be saved in the CREDIT PURCHASES field.

Example 3
Using the if function to assign a value in a DQL script:
The examples below show how to use the if function in a DQL script to conditionally assign a value to a field or variable depending on whether a condition is true or false.

modify records in RESERVATION AGENTS
BONUS := if( SALES > 80000, 1000, 0) .

This script assigns a value to the BONUS field in all RESERVATION AGENTS records. If an employee generated more than $80,000 in sales last quarter, the value in the BONUS field is set to 1000. Otherwise the value of BONUS is set to zero.
The following line shows the if function used to assign a value to a temporary variable in a DQL script:

assign temp BONUS := if( SALES > 80000, 1000, 0) .

Example 4
You can use the if function to conditionally display fields, calculated values, or text strings in the output of a DQL procedure. The script below uses the if function to display the SALARY field in a procedure's printed output only if the procedure is run by a specific user:

for EMPLOYEES ;
list records
LAST NAME in order ;
FIRST NAME ;
JOB TITLE ;
if( current user name = "Moe" , SALARY, BLANK) .
end

This script tells DataEase: (1) For each record in the EMPLOYEES table, list the LAST NAME, FIRST NAME, and JOB TITLE, (2) evaluate the current user name system variable, and if the current user is Moe, list each employee's SALARY along with his/her other data, (3) otherwise, do not list the SALARY field.