Hi @TrulsB ,
Could you please share a bit more about the Delegation issue with your formula?
If you want to sort your Gallery Items based on the Choice, LookUp, or Person type column using SortByColums function, I think there is no direct way to achieve your needs.
As an alternative solution, I think the AddColumns function could achieve your needs. Please set the Items property of the Gallerty to following:
SortByColumns(
AddColumns(
Events,
"LocationName", LocationColumn.Value, /* LocationColumn represents the LookUp column in your SP list */
"BranchName", BranchColumn.Value,
"MembershipName", MembershipColumn.Value,
"ManagerName" , 'Event Manager'.FullName
),
"LocationName", Ascending,
"BranchName", Ascending,
"MembershipName", Ascending,
"ManagerName", Ascending
)
Note: Please set the "Data row limit for Non-delegable queries" option within Advanced settings of App settings of your app to maximum value -- 2000.
If the amount of your SP List records is not more than 2000, you could ignore the delegable warning issue within your above formula.
Currently, within PowerApps, the complex type columns (e.g. Choice, LookUp, Person, ... etc) is not delegabled within SortByColumns function or Sort function. Please check the following article:
https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-delegable-functions-and-operations-for-sharepoint
If the amount of your SP list records is more than 2000, please consider take a try with the following workaround:
1. Add a Calculated type column -- 'IndexRow' within your SP list, set the Formula to following:
2. Then you could bulk-load your SP list records into your app based on the 'IndexRow':
Set the OnStart property of the App to following (I assume that there are 10000 records existed in your SP List😞
Concurrent(
Collect(Col1, Filter(Events, Value(IndexRow)>=1 && Value(IndexRow)<=2000)),
Collect(Col2, Filter(Events, Value(IndexRow)>=2001 && Value(IndexRow)<=4000)),
Collect(Col3, Filter(Events, Value(IndexRow)>=4001 && Value(IndexRow)<=6000)),
Collect(Col4, Filter(Events, Value(IndexRow)>=6001 && Value(IndexRow)<=8000)),
Collect(Col5, Filter(Events, Value(IndexRow)>=8001 && Value(IndexRow)<=10000))
);
ClearCollect(MergedCollection, Col1, Col2, Col3, Col4, Col5)
Then modify above Filter formula as below:
SortByColumns(
AddColumns(
MergedCollection,
"LocationName", LocationColumn.Value, /* LocationColumn represents the LookUp column in your SP list */
"BranchName", BranchColumn.Value,
"MembershipName", MembershipColumn.Value,
"ManagerName" , 'Event Manager'.FullName
),
"LocationName", Ascending,
"BranchName", Ascending,
"MembershipName", Ascending,
"ManagerName", Ascending
)
Please consider take a try with above solution, then check if the issue is solved.
Also please check and see if the following thread would help in your scenario:
https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Pulling-in-large-ish-SQL-tables/m-p/243777#M71518
Best regards,
