Sorting is essential for any app’s good navigation, but most of the time, we want to define a column to be the “default” sorting column. SortByColumns Function helps us on that. We can define a column within a table, and that table is automatically sorted.
Important things to keep in mind:
- If your column names have spaces, like most Excel columns and SharePoint list columns, you have to include “x0020” instead of the space. For example, “My Awesome Column” should be ”My x0020 Awesome x0020 Column”.
- Delegation of columns. I’ll detail below the limitations, but keep in mind that some data sources won’t support delegation.
Usage
It follows a simple pattern.
- Table
- Column Name
- Sorting order(s). You can have multiple based on multiple columns.
Examples
Let’s look at a simple department list of people.
SortByColumns(Organization, "Department", SortOrder.Ascending)
Now let’s check this example:
SortByColumns(Organization, "Department_x0020_Responsible", SortOrder.Ascending)
Notice the “x0020”.
Final example. Let’s sat what you want to organize by Department and by name:
SortByColumns(Organization, "Department_x0020_Responsible".Ascending, "Name".Ascending)
You can add multiple sorting orders based on your data. There’s no difference in the case above, but you’ll see if you had multiple people in the same department.
Limitations
The biggest limitation is delegation. If you’re not aware of what delegation is, I explain it in detail here, but it’s when Power Apps can “delegate” to the data source the operation. For example, if the data source can sort the columns, Power Apps won’t need to do it since the data comes from the source already sorted. Here’s a reference of all delegable data sources and functions.
Recommendations:
- Don’t be scared of putting multiple fields. It will logically present your data to the user, and it won’t break delegation.
- Don’t nest it. There’s no real need to nest since you can add multiple sorting parameters. If you nest, the last “SortByColumns” will prevail since it will sort the first dataset and then sort again based on the last criteria. If you’re nesting “SortByColumns,” please double-check your logic because you’re probably doing something wrong.
Localization
- Please note that formulas may have localization-based differences. For example, you should write “aaa” with “separating each of the arguments, but if your regional settings are set to Portugal, you should use “;” instead.
Sources:
Back to the Power Apps Function Reference
Photo by Sophie Elvis on Unsplash
What if there are multiple sort columns, but the user can select one, some, or all? How can the column names be dynamically supplied to the function. (For example: Country, State, City)