OR Condition

Last updated 6/04/2022

OR Condition in Five

The OR condition is used to test two or more conditions where records are returned when any one of the conditions are met. The Criteria field is used to filter records. It is used to extract only those records that meet a specified condition. The criteria condition used together with the OR logical operator, is executed when one or more conditions are met.

Example

WHERE Condition_1
OR; Condition_2
OR; Condition_3
...
OR; Condition_4

Create a Query with an 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. Type a condition in the Criteria field for a field.

11. Click the Add Or button.

Figure 4 - Add fields and criteria

12. Type an OR condition in a Criteria field for the new row.

Figure 5 - Add an OR condition

Criteria Examples

= 'Brisbane'

= 'Aspley'

info

This OR example will return all patients in the Brisbane or the Aspley suburbs. Because the first name and the last name fields are included in the query, these are also included in the result set.

Figure 6 - Result set

Tips

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

• The OR condition can be set for any field that makes logical sense in the Query Builder.

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

Figure 7 - Save the query

14. Click the Fields tab.

Figure 8 - Fields tab

15. 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 OR Condition with Multiple Tables

When creating OR conditions with multiple tables, Five will automatically create the JOIN for tables that have relationships defined.

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 name

8. Click outside of 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.

13. Click the Add Or button.

Figure 13 - Add fields and criteria

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

Figure 14 - Add criteria

Criteria Examples

= 'Brisbane'

> '2022-04-01'

info

This OR example will return all patients in Brisbane OR patients with an appointment date after the 2022-04-01. Because the first name, last name, patient number, and details fields are included in the query, these are also included in the result set.

Figure 15 - Result set

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

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

Figure 16 - Save the query

16. Click the Fields tab.

Figure 17 - Fields tab

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

Figure 18 - Save the form