top of page
  • Writer's pictureMarc Sigrist

Power Apps: Sort by Dataverse lookup column, without delegation.

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.

Power Apps Gallery

A try shows that we get the following warning:

"SortByColumns" has some invalid arguments. Cannot sort on expression type.

Power Apps SortByColumns

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.

Power Apps Delegation








Power Apps Delegation issue



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:


Microsoft Dataverse Formula Column























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.

Power Apps Sorting by lookup column

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








Marc Sigrist (Powerfully GmbH, Switzerland)

1 Comment


Guest
Nov 21, 2023

worked for me! thanks for the idea

Like
bottom of page