How to search and filter records in PowerApps (inc delegation)

Posted in Powerapps, by LauraGB View all Posts

This tutorial explores the default search available when you choose the “Start from data” template. Using this template, it’s possible to connect to a SharePoint List (amongst others) and have a “Browse”, “Detail” and “Edit” built for you.

When the three screen PowerApp is created (using a SharePoint list as the data source), it also includes a search box allowing us to perform a straightforward search.

In the screenshot below, you may notice the following:

1. Left – A default search screen showing all results.

2. Middle – a search results screen filtered to results beginning with “Chariot”

Learn Microsoft Flow at our Virtual Summit

3. Right – A search results screen that returns no results based on the word “repair” (more on this later).

3 examples of search

The PowerApps control that allows us to browse the list items, is called the “Gallery”.

The order of the sort (ascending / descending) is determined by the variable “SortDescending1” (see below) which toggles between true and false by clicking the sort icon. Changing the variable instantly changes the gallery, no refresh is required.

default code in the app
By default, the Gallery has a data property called “Items” which includes a “SortByColumns” function allowing us to sort the data. By default, this will sort by the Title. In the code example above, I show you how to sort by the column named “ExpenseDate”.

Now let’s try to improve the search (to filter on “repair”)

To improve the search we need to examine the “Filter” function being used above. This function takes at least 2 parameters, firstly “Expenses”, which is the field data returned from the SharePoint list. The 2nd parameter allows us to set a new filter. The filter we used in our original example was “StartsWith” (which explains why “repair” gave no rows). No rows had a “Title” starting with “Repair”.

SortByColumns(
    Filter(
        Expenses,
        TextSearchBox1.Text in Title
    ),
    "ExpenseDate",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

Changing to use the “in” operative works as expected. We can now search for “Repair” and get all the matching repairs. The filter also only searches the “Title” column. Hence, if we searched for “Travel” we would return no results. However, we can combine different conditions using the or operative which is denoted as a “||”.

SortByColumns(
    Filter(
        Expenses,
        TextSearchBox1.Text in Title || TextSearchBox1.Text in Category
    ),
    "ExpenseDate",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

Another way to solve our problem is to use the “Search” formula which allows multiple columns to be specified and offers more flexibility.

SortByColumns(
    Search(
        Expenses,
        TextSearchBox1.Text,
        "Title",
        "Category"
    ),
    "ExpenseDate",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

Dealing with the warnings in the editor?

You will notice, as soon as you change the function to use “Search” or “In” you will see a blue wavy line under parts of the code along with a warning triangle.

If you click on the triangle or the blue wavy line you will get a warning message concerning “delegation”. The message is telling us that large data sets might not work correctly. By default, PowerApps defines a large recordset as being 500 rows. This effectively means that if your search returns 501 rows, none of the rows will returned. This is for peformance reasons as you don’t want to return 10000s of rows to the client, especially on a mobile connection.

Here’s the warning message that you will see:

Delegation Warning

The example below illustrates the difference between a delegated and non-delegated search:

Comparing results from delegated and not delegated

What is Delegation?

Now that we’ve seen the effects of ‘Delegation’ in PowerApps, let’s examine what it means. Delegation refers to the process where the filter or sort is sent to the backend data source and then it’s the responsibility of the underlying data source to query the data and return the filtered/sorted data. The impact of this means that less data is sent to the PowerApp and the data source which was built to filter and sort takes on the burden of this often expensive processing.

One thing to note, different data sources have different rules regarding which sort and filters can be delegated. This list of rules is constantly expanding and can be found at the following link: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

Scrolling down the list you will notice a list of “Top Level Functions” that can be delegated. It’s also pertinent to mention that SharePoint allows you to sort the results, but you cannot delegate the search. Whereas, SQL Server (as you’d expect) supports all delegatable functions except the predicate – “StartsWith”.

Changing the default 500-row limit

SharePoint LIST
Average No
Filter Yes
LookUp Yes
Max No
Min No
Search No
Sort Yes
SortByColumns Yes
Sum No

Changing the Default

The default number of rows to be returned (via delegation) is 500. This can easily be changed by selecting the File ribbon tab and then selecting “App Settings” and finally “Advanced settings”.

However, you should be aware of the effect of increasing the limit. If you choose a number that’s too high, this can cause major performance issues.

App Advanced Settings

Conclusion

Galleries in PowerApps are really useful, they refresh the data shown really quickly and no refresh code is required. Designers need to be aware of which functions and operatives can be delegated back to the data source and how delegation works.

Let me know your thoughts.

Get the PowerApps Ebook Bundle

Are you looking to hire a Powerapps professional like LauraGB?

Post a job below (even if it's just a 15-minute Skype call), enter a few details and we will get to work on finding you some online help. Alternatively, you can also browse some existing MicroJobs.

Check out some of my MicroJobs...

Download the Building a Hangman Game in PowerApps Companion Ebook Download the Building a Hangman Game in PowerApps Companion Ebook
Download the Building a Hangman Game in PowerApps Companion Ebook
0.0 (0)
10.00 USD
EBook Download: Beginners Guide to PowerApps EBook Download: Beginners Guide to PowerApps
EBook Download: Beginners Guide to PowerApps
4.8 (41)
10.00 USD
I will coach you for 2 hours in creating Approval Flows I will coach you for 2 hours in creating Approval Flows
I will coach you for 2 hours in creating Approval Flows
0.0 (0)
250.00 USD

Want to comment?

>