Combine the AND and OR Conditions

Last updated 8/04/2022

Combine the AND and OR Conditions in Five

Criteria can be a combination of AND and OR clauses. Combining the two enables you to perform complex filtering. AND conditions will return rows matching all the specified conditions, while OR conditions will perform the opposite and only retrieve rows that match either conditions.

Example

WHERE Condition_1
AND; Condition_2
...
OR; Condition_4

Create a Query with an AND and OR Condition

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. Click outside of the Table Selector dialog box to close the window.

8. Select the table and drag to the desired position on the Designer.

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

10. In field one, type a condition in the Criteria field.

11. In field two, type a condition in the Criteria field.

12. Click the Add Or button.

Figure 4 - Add an AND condition

13. In the new row, type an OR condition in a Criteria field.

Figure 5 - Add an OR condition

Criteria Examples

> 1000

= 'Brisbane'

= 'Aspley'

info

This AND OR example will return all patients in the Brisbane suburb that have a patient number greater than 1000, or, all patients in the Aspley suburb.

Figure 6 - Result set

tip

The AND and OR conditions can be set for any field that makes logical sense in the Query Builder.

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

Figure 7 - Save the query

15. Click the Fields tab.

Figure 8 - Fields tab

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

Figure 9 - Save the form

warning

Ensure to click the Fields tab to have all your query fields saved before committing the form.

Add an AND and OR Condition with Multiple Tables

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 10 - Add a query

5. Click the Add Table button.

Figure 11 - Add Table button

6. Select a table.

7. Select table two.

Figure 12 - Select table names

8. Click outside the Table Selector dialog box to close the window.

9. Select each table and drag to the desired position on the Designer.

10. In table one, double click the field names required for the query.

11. In table two, double click the field names required for the query.

12. In table one, type a condition in the Criteria field for a field.

Figure 13 - Select fields and criteria

Criteria Example

< 1000

info

This WHERE statement will return all patients that have a patient number less than 1000. Because the first name, last name, suburb, date, time, and details fields are included in the query, these are also included in the result set.

Figure 14 - Result set

13. In table two, type a condition in the Criteria field for a field.

14. Click the Add Or button.

Figure 15 - Add an AND condition across multiple tables

Criteria Examples

< 1000

> '12:00PM'

info

This AND statement will return all patients that have a patient number less than 1000 and an appointment after 12:00 PM. Because the first name, last name, suburb, date, and details fields are included in the query, these are also included in the result set.

Five will automatically create the JOIN between these two tables.

Figure 16 - Result set

You will notice Five has prefixed all fields with their table name, for example, Patient.Details. This is required to eliminate any ambiguity as to which field is being referenced, as the same field name may exist in both of the tables.

15. In the new row, type an OR condition in a Criteria field.

Figure 17 - Add an OR condition

Criteria Examples

< 1000

> '12:00 PM'

= 'Brisbne'

info

This AND OR statement will return all patients that have a patient number less than 1000 and have an appointment after 12:00 PM or in the Brisbane suburb. Because the first name, last name, date, and details fields are included in the query, these are also included in the result set.

Figure 18 - Result set

tip

You can create as many AND and OR combinations across multiple tables that are required for your result set.

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

Figure 19 - Save the query

17. Click the Fields tab.

Figure 20 - Fields tab

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

Figure 21 - Save the form