Filter Data

Last updated 15/11/2021

How to Filter Data to Reduce Records in the List

This documentation will explain how to filter the data on a form. There may be scenarios when you do not want to show all the records in the list. In Five, you can filter the data and only show records with a certain status. The Data Filter field will filter the data as per the required condition.

Filtering allows you to return only the results that you're interested in by providing specific criteria that the records must match. By using an optional clause in the Data Filter field you can receive only the records that meet your criteria.

The Data Filter field requires an SQL WHERE clause without providing the WHERE key word. For Example,

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

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 to match the query. If the record does not satisfy all of the conditions specified, Five will not show the record in the list.

Example

We presently have a full list of cars on the Motor Dealers form in the Production application, and we can flag if the car is ready to be picked up and filter the data to only display these cars in the list.

The Data Filter field can help us in this scenario. We can create a 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 motor dealers.

Post Requisite

Create a menu item for the form.

Steps

1. Select Forms in the menu.
2. Click the Add Item button.
3. Type a title in the Title field. For this example, Cars Ready.
4. Use the lookup icon the Table field and select the required table. For this example, Cars.
5. Type your WHERE clause to filter the data. For this example, Ready = 1 AND (Taken = 0 OR Taken IS NULL)
6. Click the Pages tab.

Figure 1 - Add a data filter clause

info

The filter is checking if the Ready field is true and the Taken field is false or is NULL The records that match this query will be shown.

7. Click the Add Pages button.

Figure 2 - Add Pages button

8. Type a caption in the Caption field. For this example, General.
9. Click the Fields tab.

Figure 3 - Add a page to the form

10. Click the Add Fields button.

Figure 4 - Add Fields button

10. Add the required fields to the form.
11. Click all three Save buttons in the stacked form app bars after adding your final field.

Figure 5 - Save the Form

How This Will Look in Your End-User Application

1. Select the menu item you created referencing the form.

info

The list is filtered to only show the records that meet the criteria of the WHERE clause.

Figure 6 - Filtered list in end user application