Filter Records in a Menu Selection

Last updated 14/03/2024

In Five, 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

keyword, only the condition. For example, if you had

WHERE OrderNumber > 300

you would only need to add

OrderNumber > 300

in the Data Filter field.

Figure 1 - Data Filter field

For the following example, a data filter will be applied to the Orders menu for the Dispatch log in. Without the data filter, a user with the Dispatch role sees all the Order records on their log in. By applying a data filter and using the Dispatched field, we can make it that only the Order records that have not been dispatched are available in the list.

Figure 2 - All records - no data filter applied

info

A data filter on a menu item will only apply to the record list when the menu item is clicked. If you need the data filter applied to all lists; the record list, a list page, and a lookup list, you will need to use the Data Filter field on the form.

The menu data filter will be combined with the form data filter if they both exist.

Add a Data Filter

1. Select your menu record in the list.

2. Either click the Edit button in the form app bar, or click directly in the Data Filter field.

Figure 3 - Edit the menu record

3. Type your

WHERE

condition in the Data Filter field.

tip

Drop the

WHERE

clause and only add the condition!

Only show order records that have not been dispatched

Dispatched <> 1 OR Dispatched IS NULL

Figure 4 - Add your condition

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

Figure 5 - Save the menu record

info

The records have been filtered to only show the Order records that have not been dispatched.

Figure 6 - Filtered records