Blog

Search - How to filter by multiple fields and return all records when no search term is entered

April 2. 2021

When building search screens, a very common requirement is to enable users to filter records by multiple fields, and to return all records if no filter condition is entered.

Building a search screen

To give an example, let's take a gallery control that displays address details. We want to enable users to filter the gallery control by either the first line of the address, or the city.

To implement this feature, we'll add two text input controls to a screen that contains a gallery control. We'll name these controls txtSearchAddress and txtSearchCity.

To adapt the gallery control so that it filters the contents in the required manner, we set the items property of the gallery control to the following formula:

Filter('[dbo].[Property]',       
      (City = txtSearchCity.Text Or IsBlank(txtSearchCity.Text)),
      (StartsWith(Address1,txtSearchAddress.Text) Or IsBlank(txtSearchAddress.Text))
)

This formula applies the usage of the filter function where we specify a comma separated list of multiple conditions. The filter function filters each condition using the logical 'and' operator.

Taking the 'City' condition here, we specify that a match exists when the City field of a record matches City search text, OR when the user specifies a blank City search value.

To demonstrate the use of an operator rather than the equals operator, we apply the StartsWith function to the Address1 field and Address1 search value.

How this screen appears at runtime

We can now test this screen. When the user enters no criteria, the gallery returns all rows.

When the user enters the city 'London', the gallery returns all records that match the city of 'London'.

When the user enters the city 'london' and address '3', the gallery returns all records that match the city of 'london' and an address that starts with '3'.

Conclusion

When building search screens, a very common requirement is to enable users to filter by multiple fields and to return all records if no search criteria is entered. This post described how to carry out this task.