Add Criteria

Last updated 6/04/2022

Add a Query Field with a Criteria Condition

The Criteria field is used to filter records. It is used to extract only those records that meet a specified condition. These criteria conditions can be connected through AND and OR conditions. The Criteria field must be used with a selected field.

1. Select Menus in the menu.

2. Click the Add Item button in the list app bar.

3. Type a title in the Title field.

4. Click in the Query field to open the Table and Fields Designer.

Figure 1 - Add a query

5. Click the Add Table button.

Figure 2 - Add Table button

6. Select a table.

Figure 3 - Select table name

7. Double click the field names required for the query.

8. Type a condition in the Criteria field.

Figure 4 - Add fields and criteria

Criteria Example

> 1000

info

This WHERE statement will return all patients with a patient number greater than 1000. Because the first name and last name fields are included in the query, these are also included in the result set.

Figure 5 - Result set

tip

When entering a string it is strongly recommended to use quotes '' around the value to clearly define that it is a string.

9. Click the Save button in the Table and Fields Designer app bar.

Figure 6 - Save the query

10. Click the Fields tab.

Figure 7 - Fields tab

11. Click the Save button in the form app bar.

Figure 8 - Save the form

When using critieria for more than one field in the row, Five will create an AND condition. The records will be returned when all the criterias are met.

When using more than one criteria in multiple columns, Five will create an OR condition. The records will be returned when any one of the criterias are met.

Operators in the Criteria

The following operators can be used in the Criteria field.

Operator	Description
=	Equals	SQL example
>	Greater than	SQL example
<	Less than	SQL example
>=	Greater than or equal	SQL example
<=	Less than or equal	SQL example
<>	Not equal	SQL example
BETWEEN	Between a certain range	SQL example
LIKE	Search for a pattern	SQL example
IN	To specify multiple possible values for a column	SQL example
NOT IN	To specify values are not in a list of values	SQL example

In Five, you do not need to write the full SQL syntax in the Criteria field.

Examples of How to Write Conditions in the Criteria Field

Operator	Example
=	= 'Brisbane'
>	> 4000
<	< 4000
>=	>= 4777
<=	<= 4777
<>	<> 4777
BETWEEN	To be implemented
LIKE	To be implemented
IN	To be implemented
NOT IN	To be implemented