The if function evaluates a specified condition and returns one of two specifiedvaluesbased on whether the condition is true or false.
if(CONDITION, TRUE VALUE, FALSE VALUE)
The truevalueif the specified condition is true. The falsevalueif the specified condition is false.
In a field Derivation formula, you can use the if function to:
In a Validation formula, you can use the if function to:
When you use an if function in a Derivation or Validation formula, the truevalueand falsevalueparameters must be of the same data type as the field being derived or validated.
In ascript, you can use the if function to:
assignone of two specifiedvaluesto a field orvariablebased on whether the condition is true or false (see Example3).
hide or show a field, calculatedvalue, or text string in a procedure'soutputdepending on whether a condition is true or false (see Example 4).
Ascript, Derivation formula, or Validation formula may have multiple if function statements nested within one another.
Using the if function in a Derivation formula:
The following example shows the iffunction used to derive thevaluein a Text field. The function returns one of two textvaluesdepending 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 avaluefor a Text field, the truevalue("in the morning") and the falsevalue("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 DISCOUNTvaluesdepending on thevaluein another field, TOTAL DUE.
if(TOTAL DUE>=500, TOTAL DUE*0.15, TOTAL DUE*0.03)
This formula evaluates thevaluein the TOTAL DUE field. If thatvalueis greater than or equal to $500 (making the condition true), the formula sets thevalueof DISCOUNT to TOTAL DUE * 0.15 If thevalueof TOTAL DUE is less than $500 (making the condition false), the formula sets thevalueof DISCOUNT to TOTAL DUE * 0.03.
By nesting if statements inside one another, you candefinea Derivation formula that evaluates multiple conditions and returns one of severalvalues. The example below shows how one if statement can be nested inside another to return one of three DISCOUNTvaluesdepending on thevaluein 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 thevaluein the TOTAL DUE field. If thatvalueis greater than or equal to $500 (making the condition true), the formula sets thevalueof DISCOUNT to the TOTAL DUE multiplied by 0.15. If thevalueis less than $500 (making the condition false), a second if statement evaluates TOTAL DUE to determine if itsvalueis greater than or equal to $200. If this second condition is true, thevalueof DISCOUNT is set to the TOTAL DUE multiplied by 0.08. Otherwise DISCOUNT is set to TOTAL DUE * 0.03.
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 thevaluein a field named OVERDUE DAYS (this field tracks a customer's past-due invoices). If thatvalueis less than 90 (meaning the customer has no invoices over 90 days past due), then anyvalueup to $5000 is valid in the CREDIT PURCHASES field. If thevalueof OVERDUE DAYS exceeds 90, then only up to $50 can be saved in the CREDIT PURCHASES field.
Using the if function toassignavaluein a DQLscript:
The examples below show how to use the if function in a DQLscriptto conditionallyassignavalueto a field orvariabledepending on whether a condition is true or false.
modify recordsinRESERVATION AGENTS
BONUS:=if(SALES>80000, 1000, 0).
Thisscriptassigns avalueto the BONUS field in all RESERVATION AGENTS records. If an employee generated more than $80,000 in sales last quarter, thevaluein the BONUS field is set to 1000. Otherwise thevalueof BONUS is set to zero.
The following line shows the if function used toassignavalueto atemporaryvariablein a DQLscript:
assigntempBONUS:=if(SALES>80000, 1000, 0).
You can use the if function to conditionally display fields, calculatedvalues, or text strings in theoutputof a DQL procedure. Thescriptbelow uses the if function to display the SALARY field in a procedure's printedoutputonly if the procedure is run by a specific user:
LAST NAMEin order;
if(current user name="Moe", SALARY, BLANK).
Thisscripttells DataEase: (1) For each record in the EMPLOYEES table, list the LAST NAME, FIRST NAME, and JOB TITLE, (2) evaluate the current user name systemvariable, 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.