AND, OR, NOT Functions: Combining Conditions

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

Using AND, OR, and NOT Functions

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.
=, !=, <>, <, >, <=, >=

  • When comparing numeric type fields (such as Number fields), you can use all operators.
  • When you compare string type fields (such as Text field), you can only use =, !=, and <>.

For details on the field data types including numeric and string, refer to "Fields That Can Be Referred by Formulas". For details on comparison operators, refer to the description on =, !=, <>, <, >, <=, >= in "List of Operators and Functions". 

Formula Examples

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(Subject>=80,Practical_Skill>=80),"Pass","Retest")

When the scores of both "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(Subject>=80,Practical_Skill>=80),"Pass","Retest")

When either the "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(Subject + Practical_Skill>=160),"Retest","Pass")

When the sum of the "Subject" score and the "Practical Skill" score 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 "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. Click the app setting button Setting button on the upper right of the record view, and select the "Form" tab.

  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 "Subject".
    For the other Number field, change the field name to "Practical Skill" 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 "Results".

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

    • Formula: IF(AND(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 "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(Subject>=80,Practical_Skill>=80),"Pass","Retest")

When either the "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 "Subject" score and the "Practical Skill" score is not (NOT) 160 or more.

  • Formula: IF(NOT(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 tenure flag.

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