Filter Records Displayed in a List

Last updated 24/04/2024

The Data Filter field can be used to filter the records that are displayed in the list. There maybe scenarios when you do not want to show all the records in the list. In Five, you can filter the data and only show the records based on your query (filter).

Filtering enables you to return only the results that you want by providing specific criteria that the records must meet in order to be returned. When using the Data Filter field on a form, records will be filtered in all lists; the record list, list pages, and lookup lists unlike when using a menu data filter.

You can use an SQL

WHERE

clause in the Data Filter field to filter the records in the list. The

WHERE

clause is used to extract only the records that fulfill your specified condition.

You do not need to write the

WHERE

key word, only the condition. For example, if your clause was

WHERE = Ready = 1 AND (Taken = 0 OR Taken IS NULL)

this becomes

Ready = 1 AND (Taken = 0 OR Taken IS NULL)

The

WHERE

clause provides a way of specifying the requirements that a record must meet. If the record does not satisfy all of the conditions, Five will not show the record in any lists.

For this example, we are going to use a customer form, and on the customer form you can see a list of cars that belong to the customer.

In the below image, Harper Green has two cars currently being worked on. One car is ready and one is not.

Figure 1 - Cars list page

A car can be flagged if it is ready to be picked up.

Figure 2 - Ready switch

In the below image, May Smith also has a car that is ready to be picked up.

Figure 3 - Cars list page

The Data Filter field can help us in this scenario. We can add a Cars Ready form and query the data in the Car table using the Data Filter field to only show us the entries for cars that are ready to be picked up, so that staff members can contact the customers.

Add a Data Filter

1. Select your form record in the list.

2. Click in the Data Filter field and type your

WHERE

condition.

tip

Drop the

WHERE

clause and only add the condition!

Only show cars that are ready to be picked up

Ready = 1 AND (Taken = 0 OR Taken IS NULL)

Figure 4 - Data Filter field

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

Figure 5 - Save button

info

The records have been filtered to only show the cars that are ready to be picked up.

Figure 6 - Cars Ready form

If I was to now add this as a list page to the Customer form, only the cars that are ready for each customer are listed.

Figure 7 - Cars Ready list page

tip

The data filter applied will also remove the cars from the list once the Taken switch has been flagged!