Table of contents:
The Problem: It is not possible in Power Apps to sort by lookup columns
You may have noticed that you cannot sort by Dataverse lookup columns using the Power Apps (Canvas) formula "SortByColumns".
In the example below, our goal is to sort by the title of the "Category" column, which is a Dataverse lookup column.
A try shows that we get the following warning:
"SortByColumns" has some invalid arguments. Cannot sort on expression type.
The reason is that Power Apps can only sort by columns whose value is directly available. Since the title is a column of the lookup column RiskCategory, it is not directly available for sorting.
Workaround with AddColumns (delegation issues)
Now we could make the desired value (title of lookup column) available with an AddColumns formula.
This would then look something like this:
SortByColumns(
AddColumns(Filter(Risks,Client.'Account Name'=VarCurrentClient.'Account Name'),
"SortRiskCategoryTitle", 'Risk category'.Title),
"SortRiskCategoryTitle"
,SortOrder.Ascending)This seems to work, but now we have the problem that we get a delegation issue.
There is not even a delegation warning, but if we set the delegation limit to 2, we see that now only 2 records are displayed.
The Solution: Use a Dataverse formula column
However, there is another, rather simple workaround and that is to use a Dataverse formula column for sorting.
We add a new Dataverse column of the formula type:
Using the same, simple PowerFX formula as for the AddColumns formula, we can add a Dataverse column that always contains the desired value.
Last but not least, we sort by this new column in our gallery. You need to use the database name. In this example: "powerful_sortcategorytitle".
Ta-da! And already we can sort by the desired column and all this without delegation issues.
Do you like this article? Please write a comment or leave a like below.
If you have any questions, I'm happy to help.
Marc Sigrist (Powerfully GmbH, Switzerland)