AND Condition

Last updated 6/04/2022

AND Condition in Five

The AND condition allows you to test two or more conditions. It requires that all conditions are met for the record to be included in the result set. 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 AND logical operator, is only executed if all filter criteria specified are met. The AND operator displays a record if all the conditions separated by AND are true.

Example

WHERE Condition_1
AND; Condition_2
AND; Condition_3
...
AND; Condition_4

Create a Query with an AND 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.

Figure 4 - Add fields and criteria

Criteria Examples

> 1000

= 'Brisbane'

info

This AND example will return all patients in the Brisbane suburb that have 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.

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

Figure 6 - Save the query

13. Click the Fields tab.

Figure 7 - Fields tab

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

Figure 8 - Save the form

warning

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

Add an AND Condition with Multiple Tables

When creating AND 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 9 - Add a query

5. Click the Add Table button.

Figure 10 - Add Table button

6. Select a table.

7. Select table two.

Figure 11 - Select table names

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

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

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

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

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

13. In field three, type a condition in the Criteria field.

Figure 12 - Add fields and criteria

Criteria Examples

= 'Brisbane'

= 'Consultation'

> '2022-04-01'

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.

info

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

Figure 13 - Result set

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

Figure 14 - Save the query

15. Click the Fields tab.

Figure 15 - Fields tab

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

Figure 16 - Save the form