Multiple Search Criteria
– If you want to have multiple criteria to filter the contents of a gallery in a Canvas App, here is the best way to achieve it!
My user case is that I want to be able to filter a Dataverse table on many criteria simultaneously, and if any control doesn’t have a value, then I want that particular criteria to be ignored and omitted from the filter
This could be done using embedded If statements, as I wrote about here: Multiple Search Options. This works fine for a smallish number of search criteria, but for multiple criteria it gets complicated. I want a solution that is scalable
To demonstrate this, I’ll use 4 criteria to show a better technique to filter a table that can be easily scaled
The 4 filter criteria to search the ‘myTable’ Dataverse table and display the results in a gallery are:
- check if ColumnA (which is a text column) starts with the string entered in TextInput1
- check if ColumnB (also a text column) contains the string entered in TextInput2
- check if ColumnC (a date column) is more recent than the date in DatePicker1
- check if ColumnD (a text column) is an exact match to the selection in Dropdown1
The code for the Items property of the gallery is shown below
Filter(
myTable,
(If(IsBlank(TextInput1.Text),true,false) || StartsWith(ColumnA,TextInput1.Text))
&&
(If(IsBlank(TextInput2.Text),true,false) || TextInput2.Text in ColumnB)
&&
(If(IsBlank(DatePicker1.SelectedDate),true,false) || ColumnC > DatePicker1.SelectedDate)
&&
(If(IsBlank(Dropdown1.Selected),true,false) || ColumnD = Dropdown1.Selected.Value)
)
Although each of the 4 sections of code is slightly different due to the different types of controls and search types being carried out, the structure is always the same for each section
The first element of each sections contains an If statement which returns false if the control has a value and true if it is empty. true will return all records, false will return none
The second element of each section always executes but doesn’t return any records if the control is empty because it is matching against a null, and returns matching records when the control has a value
Because the two elements are connected via an ‘or’ operator, if the control is empty then all records are returned via the first element returning true, and if it contains a value then only matching records are returned from the second element
Each section is joined by an ‘and’ operator so the 4 sections are cumulative within the Filter function meaning only those controls that have a value entered are actioned by the Filter function
This big advantage of this technique is that many more search criteria can be added without adding much additional complexity!
Happy (low) coding!