Filter and Sort Gallery connected to a large SharePoint list by multiple columns

[sunithav]

Hello PowerApps Community,

I'm trying to filter and sort SharePoint list having 9K items and display the result in Gallery control. Filtering works as required without delegation warning.

SortByColumns function is resulting in Delegation warning when a variable is used in the expression. 

SortByColumns(Filter('ListName', 'Column1'= "Value" And 'Column2'="Value2")),varSortByColumnName, Descending)

Could someone please help me have this implemented?

Thanks,

SV

[mdevaney · Accepted solution]

@sunithav 

You will not be able to sort using a variable inside SORTBYCOLUMNS if you want to avoid delegation

SortByColumns(
    Filter('ListName',
        'Column1'= "Value"
        And 'Column2'="Value2")
    ),
    varSortByColumnName,
    Descending
)

Instead you must write out all of the column names inside a SWITCH statement.  Every scenario must have its own branch.

Switch(
    varSortByColumnName,
    "ColumnA", Sort(
                  Filter('ListName', 'Column1'= "Value" And 'Column2'="Value2"),
                  ColumnA,
                  Descending
              ),
    "ColumnB", Sort(
                  Filter('ListName', 'Column1'= "Value" And 'Column2'="Value2"),
                  ColumnB,
                  Descending
              ),
    "ColumnC", Sort(
                  Filter('ListName', 'Column1'= "Value" And 'Column2'="Value2"),
                  ColumnC,
                  Descending
              )
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[sunithav]

@mdevaney , This worked for me. Thank you very much for your help. Need to check how the app is going to perform considering the number of records in the list.

Thanks,

SV