In this post, I’ll cover how to sort a gallery by a Dataverse Lookup column. The most straightforward approach is to use the Sort function but SortByColumns can also be used and this is a little more complex. However, be aware of delegation issues! Read on to find out more…
Sorting a Dataverse Lookup Column - Sort Function
For the Region and County tables below, I want to present the 3 columns of the County table in a gallery and sort them by the County_Region Lookup column
The first approach is to use the Sort function as below and apply it to a gallery’s Items property:n
Sort(
Counties,
County_Region.RegionName,
Ascending
)
This works but immediately flags delegation issues. Why is this? The Microsoft documentation states that Sort is delegable with Dataverse as long as only the name of a single column is used and without other operators or functions
A Dataverse Lookup column uses dot.notation to access the related data in another table. Dot.notation is essentially an operator and that is the reason the blue delegation warning appears on screen
A possible workaround would appear to be to transform the dot.notated data into regular text so that it doesn’t use an operator. This can be done using the AddColumns function
Sort(
AddColumns(
Counties,
"Region",
County_Region.RegionName
),
Region,
Ascending
)
However, even though the delegation warning has now disappeared, the code is still not delegable. The output of the AddColumns function is subject to the non-delegation record limit defined in the app’s settings – so don’t be decieved!
Sorting a Dataverse Lookup Column - SortByColumns Function
It”s not possible to use a Lookup column directly in SortByColumns. Instead a new column without the dot.notation has to be created using AddColumns as show below
SortByColumns(
AddColumns(
Counties,
"Region",
County_Region.RegionName
),
"Region",
"County_Name"
)
SortByColumns has the advantage that data can be sorted by more than one column without embedding as required by using Sort. I’ve done just that in the example above where the data is sorted first by Region and then by the CountyName
However, as you will have guessed, the use of AddColumns creates the same delegation issue as previously so while this works as a solution, be sure to use only on small data sets as it is also not delegable
Additional Info
- Hiredgun.tech: Sorting a Dataverse Gallery
- Hiredgun.tech: Dynamically Sort Galleries
- Power Apps Reference: AddColumns
- Power Apps Reference: Sort & SortByColumns
- Power Apps Reference: Understanding Delegation in a Canvas App