Search code examples
powerapps-canvaspowerapps-formula

Powerapps Search and Sort Gallery

I have a powerapps application that I have implemented sorting on columns of a gallery, but now the requirement is to also be able to search across the columns in the gallery. How do I add "Search" to the code below?enter image description here

Switch(
locSortColumn,
"DataSource", Sort( Filter(KED_DistributorItems,KED_BrandItemId=Blank()), DataSource, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"MfgrName", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), MfgrName, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"MfgrItemCode", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), MfgrItemCode, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"ProductNumber", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), ProductNumber, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"ProductDescription", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), ProductDescription, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"Created", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), Created, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"LastTrans", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), LastTransactionDate, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
"LastSpend", Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), Last12MonthSpend, If(locSortAscending, SortOrder.Ascending, SortOrder.Descending)),
Sort(Filter(KED_DistributorItems,KED_BrandItemId=Blank()), Created, SortOrder.Descending)
)
Solution

  • Yes, these formulas tend to get messy once you have to implement search-filter-sort.

    Here are some thoughts to help you:

    1. The With() function can help you out in situations when the same long expression is being repeated in your code, like your Filter() expression.
    2. You could also restructure your formula by nesting the Switch() under Sort() function: Sort(Filter(...),Switch(locSortColumn,"Created",Created,...))
    3. Research how delegation works for your data source, since you could do some fine tuning based on that too. For SharePoint for instance, it is mostly preferable to first apply some general filtering to reduce the data size, then do the search and finally sort the results, but there can be different scenarios.