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
- Select Forms in the menu.
- Click the Add Item button.
- Type a title in the Title field. For this example, Cars Ready.
- Use the lookup icon the Table field and select the required table. For this example, Cars.
- Type your
WHERE
clause to filter the data. For this example,Ready = 1 AND (Taken = 0 OR Taken IS NULL)
- Click the Pages tab.
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.
- Click the Add Pages button.
- Type a caption in the Caption field. For this example, General.
- Click the Fields tab.
- Click the Add Fields button.
- Add the required fields to the form.
- Click all three Save buttons in the stacked form app bars after adding your final field.
How This Will Look in Your End-User Application
- Select the menu item you created referencing the form.
The list is filtered to only show the records that meet the criteria of the WHERE
clause.