AND, OR, NOT Functions: Combining Conditions

You can use AND, OR, and NOT functions to facilitate the creation of IF conditional expressions.

AND(Conditional expression1,Conditional expression2, ...)
When all conditional expressions are true, it returns True. Otherwise, it returns False.
OR(conditional expression1,conditional expression2, ...)
When any conditional expression is true, it returns True. When all conditional expressions are false, it returns False.
NOT(conditional expression)
When the conditional expression is false, it returns True. When the conditional expression is true, it returns False.

You can use following operators in your conditional expressions.
=, !=, <>, <, >, <=, >=

  • You can use all operators when comparing numerical types.
  • You can use only =, !=, <> when comparing string types.
    Text, Drop-down, Radio Button, and Lookup (when the source field is string type) fields are string type fields.

For details, see the list of operators and functions.

Example Formula

This section describes example formulas that can be used for the Calculated field or the Text field.
Field codes are specified as conditions in these formulas.
IF function can be nested. You can wrap lines or insert line feeds to make long formulas easy to read.

Formula: IF(AND(Academic subject>=80,Practical skill>=80),"Pass","Retest")

When the scores of both "Academic subject" and "Practical skill" are 80 or more, this formula returns "Pass". Otherwise, it returns "Retest".
You must enclose strings in double quotation marks ("").
Since the result will be returned in text, you must enter the formula in the Text field.
Formula: IF(OR(Academic subject>=80,Practical skill>=80),"Pas","Retest")

When either the "Academic subject" score or the "Practical skill" score is 80 or more, this formula returns "Pass". Otherwise, it returns "Retest".
You must enclose strings in double quotation marks ("").
Since the result will be returned in text, you must enter the formula in the Text field.
Formula: IF(NOT(Academic subject + Practical skill>=160),"Retest","Pass")

When the sum of the "Academic subject" score and the "Practical skill" sore is not (NOT) 160 or more, this formula returns "Retest".
You must enclose strings in double quotation marks ("").
Since the result will be returned in text, you must enter the formula in the Text field.

Procedure to Set AND Function

This section describes the way to set a formula, using an example of a Certificate Examination Result app.
If you want the app to return "Pass" when both the "Academic subject" score and the "Practical skill" score are 80 or more, and return "Retest" for other cases, you must use the AND function.

Image of the completed app:

  1. Open the app settings page.
    Opening App Settings Page

  2. From the parts list shown in the left side, drag and drop two Number fields.

  3. In the field setting screen, change the field name and the field code to "Academic subject".
    For the other Number field, change the field name and the field code to "Practical skill".

  4. Since we display the result in text in this example, place the Text field.

  5. Open the screen to set the Text field, and change the field name and the field code to "Result".

  6. Select "Calculate automatically", and enter the formula that returns "Pass" when both the "Academic subject" score and the "Practical skill" score are 80 or more, and returns "Retest" for other cases.

    • Formula: IF(AND(Academic subject>=80,Practical skill>=80),"Pass","Retest")
  7. Click Save on the screen to set the Text field.

  8. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen.

Now, the formula returns "Pass" when both scores are 80 or more.

When either of the scores is less than 80, the formula returns "Retest".

Procedure to Set OR Function

If you want the app to return "Pass" when either the "Academic subject" score or the "Practical skill" score is 80 or more, and return "Retest" for other cases, you must use the OR function.

  • Formula: IF(OR(Academic subject>=80,Practical skill>=80),"Pas","Retest")

When either the "Academic subject" score or the "Practical skill" score is 80 or more, the formula returns "Pass".

Procedure to Set NOT Function

NOT function determines whether the condition is not met.

In this example, the formula returns "Retest" when the sum of the "Academic subject" score and the "Practical skill" score is not (NOT) 160 or more.

  • Formula: IF(NOT(Academic subject + Practical skill>=160),"Retest","Pass")

When the sum of the scores is less than 160, the formula returns "Retest".

Example of Combining Conditions in the Calculated Field

If the result is returned using numbers, you must set the AND, OR, or NOT function for the Calculated field.
This section describes an example of setting employment status flag in a Company Directory app.
Image of the completed app:

Place two Date fields and one Calculated field in the screen to set forms.
Label the Date fields as "Hire Date" and "Retirement Date".

In the Calculated field, enter a formula that returns '1' when the Hire Date is filled and the Retirement Date is empty, and returns '0' for other cases.
If you want to set a condition to determine whether the fields are empty or not, specify Field Code = "".

  • Formula: IF(AND(Hire Date<>"",Retirement Date=""),1,0)

If the user's hire date has been entered but the retirement date is empty, the formula returns '1' as the employment status flag.

For details on operators and functions, see the list of operators and functions.