Introduction
PowerApps Gallery controls are very useful to show data in different view formats. Unlike a Data Table control that can give only an Excel-like tabular view, Gallery Control allows us to show data in other formats like Horizontal Card Format, Vertical Card Format, etc.
However, it can be difficult to implement some of the common features like Search, Sort, and Group By in Gallery Control.
In this article, I am going to explain how we can achieve all these features in a PowerApps Gallery Control. I will be using a SharePoint List as my Datasource and create a Stand-alone Canvas App. You can choose any other datasource as well, such as an SQL Data, CDS Entity, or even an Excel file.
This article assumes that you are familiar with how to create a Blank Canvas App and connect it with the desired datasource. So, let’s get started by adding controls to the screens. I will be creating two screens: one for Search and Sort and another to explain GroupBy. It will be easier to understand the implementation and limitations.
Search & Sort in Gallery Control
So let’s begin with the first screen that will provide us with the Search and Sort capability in Gallery Control.
- Add a “Blank Vertical” Gallery to the screen:
- Set the “Items” property of the Gallery to the datasource
- Edit the Gallery Control and add labels that will hold values from the datasource. Arrange the labels in a tabular format.
- Now, select the Gallery Control and in the properties panel, for “Fields”, click on Edit and map the labels with desired columns from the datasource.
- Outside the Gallery Control, add the following controls:
- Labels that will act like headers to the columns in Gallery control.
- Sort Icons will be used to trigger the sorting on Gallery data
- A TextBox that will be used as a search criteria text.
- So far, your screen should look something like below:
- Now, we will put some formulas on controls to implement the search and sort functionalities. In the “OnVisible” property of the Screen, set the following two variables that will be later used in other formulas:
- Set(var SortBy,"");
- Set(var SortAsc,false);
- In the “OnSelect” property for each of the Sort Icon control, set the ‘varSortBy’ variable to a Text Identifier and update ‘varSortAsc’ that will allow sorting in Ascending or Descending order. UpdateContext used here is to toggle the value of the Boolean variable.
Ex: for Job Title column
- Set(varSortBy,"Title");
- UpdateContext({varSortAsc: !varSortAsc})
- Finally, let’s update the “Items” property of Gallery control. This property will include a combination of SortbyColumn and Search function. The syntax used for this property will be as follows:
- SortByColumn(Search(“<DataSource>”, “<FilterText>”, “<FilterOnColumns>”), “<SortOnColumn>”, “<Asc/Desc>”)
You would have noticed that I have used the “Switch” statement to identify the value of ‘varSortBy’. This simplifies the overall formula instead of multiple “If” statements. Additionally, the Boolean value of ‘varSortAsc’ determines the sort order, each time when the Sort icon is clicked, it will toggle the sort order from Ascending to Descending or vice-versa.
Note
From my observation, the Search function works with Text column types only. I was not able to make formula work for complex column types like Choice, Lookup, Date and People Picker fields.
Group By in Gallery Control (Nested Galleries)
Now on the second screen, we will implement the GroupBy functionality.
- On a New Screen, add a “Blank Flexible Height” Gallery control
- In the “Items” property of the Gallery Control, set the formula as per the following syntax
- GroupBy(“<DataSource>”, “<GroupByColumn>”, “<CollectionName>”)
- Edit the Gallery Control and add a Checkbox control that will hold values from the data source. It will be automatically mapped to the only column in Collection. No other columns will be available to map in this gallery control.
- Set the properties of the Checkbox Control as follows:
|
Property Name
|
Value
|
|
X
|
0
|
|
Width
|
Parent.Width
|
|
PaddingLeft
|
60
|
|
CheckboxSize
|
0
|
- If you would like to show the number of records for each group, then update the text property of the CheckBox, similar to below:
- Edit the Gallery control and add two Icon controls “Right” and “Down”, that will give the experience of expand and collapse of the group. Ensure that both the icons are placed below the Checkbox control in the tree view.
- Set the “Visible” property of Right Icon to “CheckBox.Value” & Set the “Visible” property of Down Icon to “!CheckBox.Value”
- Edit the Gallery control and add another Gallery Control with the desired template layout
- Set the “Items” property of Child Gallery control as the GroupBy Collection of Parent Gallery
- Set the “Visible” property of Child Gallery control as “If (CheckBox.Value, true, false)”. This will show the child gallery only if the row in the parent gallery is selected. Hence, using the CheckBox control makes it easier to implement the show/hide logic for Child Gallery.
- Add the controls in the Child Gallery as per the requirements.
Note
The column used to GroupBy in Parent gallery will not be available in Child gallery when using the “Items” property of Child gallery as described above.
Closing Comments
You can combine all these functionalities into a single screen as well, but it makes your app more complex. Many times, the app behaves erratically and it becomes difficult to troubleshoot the cause of the issue. Nonetheless, if used correctly, these features make your app user-friendly. I hope you would find these techniques on Gallery control to be simple, useful, and effective. If you have any other ideas on how to extend the functionality of gallery control, then please share your inputs.
Akshay SolankiPosted Jan 6, 2023, 5:06 PM
Hi Pawan, I am trying to use GroupBy and Sort function together, as I try to use GroupBy function, I need to set the order of the items in that table. How can I achieve that?
W CPosted Jan 12, 2021, 2:41 PM
For the groupby, how do you control the position of the checkbox so when the child gallery shows it does not overlap the checkbox below?
Bhargava ChPosted Oct 15, 2020, 6:26 AM
Hi Pawan, I have 10000+ records.. search function is not working on few of the queries because of delgaton. Do you have any solution to resolve that?
prashant anandPosted Oct 6, 2020, 1:59 PM
Could you please help me on one issue in powerapps gallery ranking. The req is we have to show ranking in gallery based on points. but the issue is if 2 or more users have same points(highest -1 rank) the it skips the third user value raning directly to 3. it should be 2.SortByColumns(AddColumns(AddColumns(collAllUsers,"tmpLifeTimePoints",LifetimePoints),"Rank",CountIf(collAllUsers,LifetimePoints>tmpLifeTimePoints)+1),"Rank",Ascending)
Aimee MullinsPosted Sep 3, 2020, 5:51 AM
Hi! Is it possible to use the groupby and search function together? At the moment i have this formula- Search(GroupBy('Knowledge Hub Content', &"Title", "EMPTY"),TextInput1_2.Text,"Title") However there are other fields i want to search for from my sharepoint list but i can only search for the column i have grouped by
shaik ashikPosted Aug 30, 2020, 5:35 AM
This Article confuses me a lot, can you please explain in a simple way??