AND, OR, and NOT Functions: Combine Conditions

Article Number:040496

You can use AND, OR, and NOT functions to determine if multiple conditions are met.
The evaluation of these functions can also be used together with the IF function.

Function syntax

The AND function returns TRUE if all of the specified conditions are TRUE. Otherwise, it returns FALSE.

The content has been copied.
AND(condition1,condition2, ...)

The OR function returns TRUE if any of the specified conditions are TRUE. If all of the conditions are FALSE, it returns FALSE.

The content has been copied.
OR(condition1,condition2, ...)

The NOT function returns TRUE if the specified condition is FALSE. It returns FALSE if the condition is TRUE.

The content has been copied.
NOT(condition1)

Operators that can be used in conditions

The following operators can be used to specify conditions.

  • =
  • !=
  • <>
  • <
  • >
  • <=
  • >=

When comparing values of numeric type fields (such as "Number" fields), you can use all of the operators above.
When comparing values of string type fields (such as "Text" fields), you can only use the following operators: = != <>

For information on field data types, refer to Data Types of Fields.

Formula examples

When specifying a field in a formula, make sure to specify the field code as the argument (and not the field name).
In the examples below, the field code is set for each field as the field name with underscores instead of spaces (e.g., the field code for the "Practical Skill" field is "Practical_Skill").

Formula to display "Pass" if two items both score 80 or higher

The following formula displays "Pass" if both the "Written" and "Practical Skill" fields contain scores of 80 or higher, and "Retest" if not.

The content has been copied.
IF(AND(Written>=80,Practical_Skill>=80),"Pass","Retest")

Screenshot: "Pass" is automatically displayed because both the "Written" and "Practical Skill" fields have scores of 80 or higher

The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.

Formula to display "Pass" if at least one of two items score 80 or higher

The following formula displays "Pass" if either the "Written" or "Practical Skill" field contains a score of 80 or higher, and "Retest" if not.

The content has been copied.
IF(OR(Written>=80,Practical_Skill>=80),"Pass","Retest")

Screenshot: "Pass" is automatically displayed because the "Written" field has a score of 80 or higher

The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.

Formula to display "Retest" if the sum of the scores of two items is not 160 or higher

The following formula displays "Retest" if the sum score of the "Written" and "Practical Skill" fields is not 160 or higher, and "Pass" if it is.

The content has been copied.
IF(NOT(Written+Practical_Skill>=160),"Retest","Pass")

Screenshot: "Retest" is automatically displayed because the sum score of the "Written" and "Practical Skill" fields is not 160 or higher

The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.

Formula to display "1" in the "Employment Status" field for current employees

The following formula checks if a person is currently employed at a company by referring to the "Hire Date" and "Termination Date", and displays "1" in the "Employment Status" field for current employees.

This formula is set in such a way as to return "1" if the "Hire Date" field is populated and the "Termination Date" field is blank, and "0" otherwise.
Because we want to use an empty value as a condition, the following is specified as the condition: field_code=""

The content has been copied.
IF(AND(Hire_Date!="",Termination_Date=""),1,0)

Screenshot: "1" is automatically displayed in the "Employment Status" field because a value is entered only in the "Hire Date" field

If you want the calculation result to be a numeric value, set the formula in a "Calculated" field. If you want the calculation result to be a string value, set the formula in a "Text" field.
For information on field data types, refer to Data Types of Fields.