Power Apps Gallery Sort Controls

Power Apps Gallery Sort Controls

Adding sort controls to a Power Apps gallery helps users find the information they are looking for more quickly. It ensures the most relevant results are displayed at the top of the gallery and the data can be browsed in an organized way. If you want to deliver an awesome user experience you must include this ability.


I will show you a simple method to create sort controls for every column displayed in a gallery.


Vehicle Sales Information App

The Vehicle Sales Information App allows salespeople at a car dealership to lookup the price of previously sold cars. Salespeople scroll through the gallery to find the car they are searching for and can click on the sort controls to arrange any column in ascending or descending order.




Make a new SharePoint list called ‘Car Inventory‘ with 5 columns: Year (number), Make (text), Model (text), PurchaseDate (date), Price (number). To populate the list with 3,000 vehicles I used a free fake data generator called Mockaroo. Here’s a sample of the 1st few rows:

YearMakeModelPurchaseDatePrice
2009MazdaMX-510/21/202013,014
1985HondaAccord5/2/201816,725
2001FordWindstar10/22/201917,198
1994MitsubishiEclipse2/16/201915,617
2003LamborghiniGallardo11/9/202014,831



Open Power Apps and create a connection to the Car Inventory list. Then create a gallery and insert labels showing all columns in the SharePoint list.



Place a label above the gallery with a purple fill to act as a table header. Write each column name in the text property of the label and position them to match the data columns inside the gallery.



Creating Sort Controls

A salesperson should be able to click on an arrow icon beside each column name to sort the gallery. Insert a white chevron down icon beside the year column name like this.



When the salesperson clicks on the icon we need to capture which column should be sorted and which direction to sort. The first click should sort by year in ascending order (lowest-to-highest) and a second click should sort in descending order (highest-to-lowest).



Put this code in the OnSelect property of the icon to capture the column name and the sort direction

UpdateContext(
    {
        locSortColumn: "Year",
        locSortAscending: locSortColumn<>"Year" Or !locSortAscending
    }
)



Then put this code in the Icon property of the icon so it changes to match the sort direction in locSortColumn.

If(
    locSortColumn<>"Year" 
    Or locSortColumn="Year" And !locSortAscending,
    Icon.ChevronDown, Icon.ChevronUp
)



The icon must be highlighted yellow when it is clicked to indicate which column is actively being sorted.



Use this code in the Color property of the icon to change the color

If(locSortColumn="Year", Yellow, White)


Now that we have created the sort control for Year follow the same pattern to create sort controls for Make, Model, Purchase Date and Price.



Finally, we must update the Items code in the gallery to produce the desired sort order. I have chosen to use a switch function combined with a sort function for each possible value of locSortColumn. This ensures the formula can rely on delegation to perform sort operations and return all the rows in ‘Car Inventory’.

Switch(
    locSortColumn,
    "Year", Sort('Car Inventory', Year, If(locSortAscending, Ascending, Descending)),
    "Make", Sort('Car Inventory', Make, If(locSortAscending, Ascending, Descending)),
    "Model", Sort('Car Inventory', Model, If(locSortAscending, Ascending, Descending)),
    "Purchase Date", Sort('Car Inventory', PurchaseDate, If(locSortAscending, Ascending, Descending)),
    "Price", Sort('Car Inventory', Price, If(locSortAscending, Ascending, Descending)),
    'Car Inventory'
)



The gallery now has sort controls for each column.



Combining Sort and Filter functions

All galleries used to browse large datasets have both sort and filter controls to help the user find what they are looking for. In the gallery shown below I’ve added filters for Year, Make and Model.



To filter the gallery we would need to write this code in the Items property of the gallery. But how can we combine it with our previous code to sort the items as well?

Filter(
    'Car Inventory',
    Year=drp_Year.Selected.Value,
    Make=drp_Make.Selected.Value,
    Model=drp_Model.Selected.Value
)



We will have to re-use the same filter code 5 times within the switch function: once for each possible switch case. The Items property code is quite lengthy but its quick to write if you copy and paste the repeating section. Most importantly, it follows delegation rules and returns the entire dataset.

Switch(
    locSortColumn,
    "Year", 
    Sort(
        Filter('Car Inventory', 
            Year=drp_Year.Selected.Value, 
            Make=drp_Make.Selected.Value, 
            Model=drp_Model.Selected.Value
        ),
        Year,
        If(locSortAscending, Ascending, Descending)
    ),
    "Make", 
    Sort(
        Filter('Car Inventory', 
            Year=drp_Year.Selected.Value, 
            Make=drp_Make.Selected.Value, 
            Model=drp_Model.Selected.Value
        ),
        Make, 
        If(locSortAscending, Ascending, Descending)),
    "Model",
    Sort(
        Filter('Car Inventory', 
            Year=drp_Year.Selected.Value, 
            Make=drp_Make.Selected.Value, 
            Model=drp_Model.Selected.Value
        ),
        Model, 
        If(locSortAscending, Ascending, Descending)),
    "Purchase Date",
    Sort(
        Filter('Car Inventory', 
            Year=drp_Year.Selected.Value, 
            Make=drp_Make.Selected.Value, 
            Model=drp_Model.Selected.Value
        ),
        PurchaseDate,
        If(locSortAscending, Ascending, Descending)),
    "Price",
    Sort(
        Filter('Car Inventory', 
            Year=drp_Year.Selected.Value, 
            Make=drp_Make.Selected.Value, 
            Model=drp_Model.Selected.Value
        ),
        Price, 
        If(locSortAscending, Ascending, Descending)),
    'Car Inventory'
)





Questions?

If you have any questions or feedback about Power Apps Gallery Sort Controls forms please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

33 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Femke
Femke
3 years ago

Thanks for the thorough walkthrough. It works great until I try to sort and then filter by more than one category. I’m using the same data with the same column types and I use a distinct function for the dropdown items (Distinct(source,column)).
When I only add the code for the Year dropdown (to gellery items) it still works, but as soon as I add the code for the Make dropdown the gallery shows no data anymore. Would you please tell me how you populate your dropdowns? Maybe my distinct function causes problems?

Florin
Florin
2 years ago

Hi. can you give an advice to make the printing command work in Desktop PowerApps, Print() does nothing ezcept if app is open in edge browser?
Any sugestions?

Don
Don
2 years ago

Thanks for these great instructions, Matthew! I have the sorts working beautifully on my gallery, but I haven’t been able to get the combo box or the text input box to work with sorting. The following two snippets work individually:

Sort:

Switch(
  locSortColumn,
  “New Employee Name”, Sort(‘Admin_Employee_List’, FullName, If(locSortAscending, Ascending, Descending)),
  “Onboarding Status”, Sort(‘Admin_Employee_List’, OnboardingStatusText, If(locSortAscending, Ascending, Descending)),
  Sort(Admin_Employee_List,ID,Descending)
)

String search on names:

Sort(Filter(Admin_Employee_List,StartsWith(FullName,tbSearchGallery.Text)),ID,Descending)

Ryn
Ryn
2 years ago

Thanks much for this method! How would you do ascending-descending alphabetical sort on a field that looks up to another table (i.e. a field whose values are UIDs but the text of which is displaying the title/description from another table)?

Deb A
Deb A
1 year ago

Lifesaver, thank you. I’m new to PowerApps and could not figure out how to sort my gallery columns on select.

Matthew
Matthew
1 year ago

Hello, thank you for this walkthrough! I have managed to make everything work, however one thing I have not been able to work out is simultaneously running the Switch() function under “Items” of my gallery while also using a Sort() function under “Items” to sort my ID column by Descending as its default (which I do not plan to add a sort button onto). If you could provide a solution, that would be greatly appreciated!

Richard
Richard
1 year ago

Thanks for this Matthew, it was helpful and easy to follow. I am new to PowerApps, really just evaluating it as a dev tool for modernizing some of our older systems. I was surprised to learn that galleries don’t ship with sorting by columns already wired in with perhaps a property that sets whether and which columns are sortable and which are not. In fact I was surprised that if one wishes to have columns heading for a gallery, one has to create them manually using labels. I am wondering if these features will be added in future versions of PowerApps.

Marco
Marco
1 year ago

Hello Matthew,
thanks for your valuable advice.

I use the formula:

Switch(locSortColumn; “Created”; Sort(Filter([@Sentinel];(ricercaSentinel.Text in Title)||(ricercaSentinel.Text in Details)); 
‘Created’; If(locSortAscending; Ascending; Descending)); 
[@Sentinel]) 

But I would like to add the combobox
Filter([@Sentinel]; 
Categoria.Value = DropdownSentinel.Selected.Value) 

how to combine the two formulas?

Marco
Marco
1 year ago

Hi Matthew, with this format I have 2 problems:

  1. Not all results are returned. I loaded a row in the list on 12/08/2022 and it is returned only if I use the dropdown filter. Without filters, the last line is from 03/08/2022 (example) .
  2. locSortAscending sorting doesn’t work

Forgive my incompetence, thank you!

Ilya
Ilya
1 year ago

Hey Matt, thanks for the post! Always mega helpful!

“We will have to re-use the same FILTER code 5 times within the SWITCH function”

I can suggest WITH function, so you don’t have to re-use it 5 times:

With({Table:Filter(‘Car Inventory’, 
      Year=drp_Year.Selected.Value, 
      Make=drp_Make.Selected.Value, 
      Model=drp_Model.Selected.Value
    )},

Switch(
  locSortColumn,
  “Year”, 
  Sort(Table,Year,If(locSortAscending, Ascending, Descending)),
  “Make”, 
  Sort(Table,Make, If(locSortAscending, Ascending, Descending)),
  “Model”,
  Sort(Table,Model,If(locSortAscending, Ascending, Descending)),
  “Purchase Date”,
  Sort(Table,PurchaseDate,If(locSortAscending, Ascending, Descending)),
  “Price”,
  Sort(Table,Price, If(locSortAscending, Ascending, Descending)),
  ‘Car Inventory’
))

Robert
Robert
1 year ago

This worked great but I dont get any values back until a sort is selected. How would I set a default sort value to return when the app is started? My gallery values are blank until I hit one of the chevrons.

Dorinda Reyes
Dorinda Reyes
1 year ago

Matthew,

I need to incorporate this code below into your function and I am a bit confused on the correct syntax any guidance would be appreciated

SortByColumns(
    Search(
        AddColumns(
            Filter(
                'Auto Transformer',
                IsBlank(ComboBox_Substation.Selected.Result) || (Substation in ComboBox_Substation.SelectedItems.Result) 
            ),
            "id2",
            Text(Station)
        ),
        TextInput6_9.Text,
        "AssetLocation",
        "Substation",
        "LTCManufacturer",
        "Stencil",
        "LTCModelNumber",
        "Status",
        "Manufacturer",
        "Stencil",
        "Crew",
        "Compliance",
        "System",
        "Model",
        "id2"
    ),
    "Substation",
    Ascending)
Jyothi
Jyothi
8 months ago

Hi, I am also used this method now as per my requirement on a common button click need to clear any sort is ascending

Tom E
Tom E
8 months ago

Thanks for the great article. I am trying to add sort functionality to the 1st column of my gallery named “DataSource”. However, it gives an error in the If statement that Ascending and Descending are not recognized. I am very new to Power Apps, any guidance is much appreciated.

Switch(
  locSortColumn,
  “DataSource”, Sort(‘KED_DistributorItems’, DataSource, If(locSortAscending, Ascending, Descending)),
  KED_DistributorItems
  )

Tom E.
Tom E.
8 months ago

I solved my issue I have to put an explicit SortOrder in the If statement.

Switch(
  locSortColumn,
  “DataSource”, Sort(‘KED_DistributorItems’, DataSource, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
  “MfgrName”, Sort(‘KED_DistributorItems’, MfgrName, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
  KED_DistributorItems
  )

Tom E.
Tom E.
8 months ago

Thank you for the excellent write up. I have a question about default sort order as descending for one column. How would I achieve that on the initial gallery load?

Mike
Mike
7 months ago

Greetings,
I’m having trouble finding info on where PowerApp sorts it’s data that it brings in. Does PA bring in a copy of the data set and then sort it locally, or are the sorts pushed down to the database and refreshed from a new data pull? TIA! 🙂

Kevin
Kevin
7 months ago

Matt, thank you this solution worked great. One question, do you know how to incorporate a search function within this code? In your example let’s say you wanted to give a user the option to search on Make in your car inventory from the gallery you created?

Scott
Scott
2 months ago

Is there any way to make this work for SQL Datasource?

I need to Sort my SQL Datasource (viewAcctInterface_….), but I keep having delegation problems if I use a variable value for what column to sort by (if I use a text value for the column to sort by, there are no delegation issues).

I’ve attached an image to explain more clearly.
When using the method from the article, using Switch() causes delegation problems.

This is evidenced by WontWork, which should just return the entire DataSource with no modifications (this is the most stripped-down example I could create. Ultimately, I would like to add Sort or SortByColumns to this, but it doesn’t matter if I can’t get past Switch limiting my rows). The returned sum value is 333.

Conversely, within the same With statement, I’ve got rowCount which performs a Search and a Filter (but no Switch () ) on the DataSource, and it returns the (expected) sum of 2698.

Code shown below just in case:

With(

	{

		    rowCount:  // Returns 2698, which is the correct sum of JEL_Sage_Code values

      Sum(

        Search(

          Filter(       

              viewAcctInterface_TransactionsIndividual,

              vet_total_hours_day >= Value(cboTimeReview_Filter_DayHrs_1.Selected.Value)

          ),

           

          // _Search

          txtSearch_galTimeAgg_1.Text, "emp_all_cname", "emp_all_employee_no_var"

        ),

         

        JEL_Sage_Code

      ),










    WontWork:  // Returns 333, due to delegation limits (500 Record max)

      Sum(

        Switch(

          "tpt_employee_no",

          "tpt_employee_no",

          viewAcctInterface_TransactionsIndividual

        ),

        JEL_Sage_Code

      )



	},

	...

) // Close With() statement
TKAcct_SS_240117.jpg
Apolo
Apolo
11 hours ago
Awaiting for approval

Hello:
In the event that each gallery element had a button to access the details, how can I make sure that when I return to the gallery window the gallery element I selected remains selected to see its details?

Thanks