Even more tips on using Galleries to find a specific records in a data table! In this article, I’ll explain how to search across multiple columns. Once we have a reduced set of data, SortByColumns can be used to dynamically sort Galleries
Table of Contents
Searching Multiple Columns of Data
Below we have the data table of Continents and Countries that I’ve used in previous posts. The demo shows how a Dropdown box is used to seach a column. I’ve demonstrated this before but this time there are also additional Text Input boxes to specify Min and Max values for the size of the country. All three input boxes are used together to update the records displayed in the Gallery
The records in the Gallery can be sorted using the sort Icons – we’ll come on to this later
The Dropdown box contains all the distinct Continents sorted alphabetically taken from the ‘Country’ Table. To do this the items property is set to:
Items: SortByColumns(Distinct(Country,Continent),"Result")
Much more detail on how this works together with lots of other options to configure Dropdown boxes available here: Hiredgun.tech: Power Apps Dropdown Boxes. The Dropdown box has the AllowEmptySelection property set to true so the Dropdown is empty by default
The two Text Input boxes that define the Min and Max surface are size have their Default set to 0 and 10 respectively. Other than that their properties are just as they are ‘out of the box’
The filtering of the Gallery from the selections is performed by the code below in the Items property of the Gallery
If the length of the value in the Dropdown box is zero then this indicates that a selection has not been made. When this is the case, the source for the Gallery is the full Country table so all records are displayed. I’ve chosen to show all the records because their total number is relatively small. If the table of data is much larger then a better option could be to not display any data till an option has been selected. Incidently, the IsBlank function could have been used here in place of the Len function
Once a selection has been made from the Dropdown box and therefore the length of the value selected is greater than zero. The table is immediately filtered on both the Continent and Area columns
If(
Len(Dropdown_Continent.Selected.Result) = 0,
Country,
Filter(
Country,
Continent = Dropdown_Continent.Selected.Result And
Area > Value(TextInput_MinArea.Text) And
Area < Value(TextInput_MaxArea.Text)
)
)
Alternatively, the ‘And’ operators can be replaced by comma as below:
If(
Len(Dropdown_Continent.Selected.Result) = 0,
Country,
Filter(
Country,
Continent = Dropdown_Continent.Selected.Result,
Area > Value(TextInput_MinArea.Text),
Area < Value(TextInput_MaxArea.Text)
)
)
Dynamically Sort Galleries
You can see that I added a couple of sort Icons to the App, one against the Continent column and the other against the Country column
Selecting either Icon sets the value of two variables. The first variable is called varColumnOrder and is set to 1 if the Continent sort Icon is selected and 2 if the Country sort Icon is selected. The other variable is a Boolean variable called varSortOrder that simply toggles between True and False
The OnSelect property of the Icons are shown below:
Continent:
Set(varColumnOrder,1);
Set(varSortOrder,!varSortOrder)
Country:
Set(varColumnOrder,2);
Set(varSortOrder,!varSortOrder)
Now we’ll apply the above variable values to the Items property of the Gallery. We’ll use a Switch function within a SortByColumns function, though alternatively we could use an If statement instead of Switch and Sort instead of SortByColumns. The Switch function will define the column used to sort the data and also whether the sort is by Ascending or Decending order. We also embed the existing If statement inside the SortByColumns function too
Once we’ve done that, the Items propery of the Gallery will be:
SortByColumns(
If(
Len(Dropdown_Continent.Selected.Result) = 0,
Country,
Filter(
Country,
Continent = Dropdown_Continent.Selected.Result And
Area > Value(TextInput_MinArea.Text) And
Area < Value(TextInput_MaxArea.Text)
)
),
Switch(
varColumnOrder,
1,
"Continent",
2,
"Title"
),
Switch(
varSortOrder,
true,
"Ascending",
false,
"Descending"
)
)
Now the selection of the records from the Dropdown and the Text Input boxes work together with the sort Icons inside the SortByColumns function
Both Icons share the varSortOrder Boolean variable that simply toggles between True and False to define whether the data is sorted in Ascending or Descending value
Summary
As well as showing how to filter and sort the records in a Gallery, I hope this example also helps demonstrate that when using Power Apps, there is often more than one way to get the desired result. We have used SortByColumns and could equally have used Sort. I’ve used Len to identify whether an entry is blank but IsBlank could also have been used. Also If and Switch can also often be used interchangeably. There is often no clear right or wrong answer on which function to use other than a combination of personal preference, the particular cirumstances and striving to make the code as efficient as possible
More Info
- Hiredgun.Tech: Filter and StartsWith
- Hiredgun.tech: Multiple Search Options
- Hiredgun.tech: Power Apps Dropdown Galleries
- Hiredgun.tech: Input Boxes, Galleries & Collections
- Hiredgun.tech: Filter Galleries – 3 Great Tips and Tricks
- Power Apps Reference: Filter & Search
- Power Apps Reference: SortByColumns