Power Apps Search and Filter Functions on Large SharePoint Lists

Power Apps Search and Filter Functions on Large SharePoint Lists Hello everyone reza here today's video is around a very important topic that every app maker needs to know about how to search and filter on large data sources in power apps.

Power Apps Search and Filter Functions on Large SharePoint Lists

Here i will show you how to add a search box add multiple filters and all of this by taking care of delegation so let's get started with the video i have a power app that is connected to.

A sharepoint list as a data source the powerapps has a gallery control that is connected to that sharepoint list the list is called work progress tracker and the list has over 2000 items in it.

With a wide variety of column types choice dates person column and more for the gallery control for the items property when i associated with that sharepoint list which has over.

2000 records how much data does the gallery truly load in memory powerapps is designed keeping the mobile experience in mind.

And there is a concept known as delegation and power apps wherein powerapps delegates the work of performing the queries to the underlying data source if that data source supports.

Delegation as well as if the queries that we execute against that data source support delegation in that case the query is performed server side at the data source and then.

The result is sent back to power apps in an optimized manner now here i have a label control that is showcasing the count of the number of items in my gallery control and the.

Gallery control is directly connected to my sharepoint list now this is also a query the query here is get me all the data from my work progress tracker list.

Which has over 2000 items if i was to play this app you will observe that the gallery count right now is a hundred what power apps does is because this.

Query supports delegation the query is performed in sharepoint sharepoint returns the data back in an optimized manner in batches of 100. that's why we see hundred.

Now what if i want the user to see more than 100 records well for that the user would have to scroll to the end of the gallery to see the next set of records.

Posts Related:

    So here if i was to scroll to the bottom

    Of this gallery what happened right now is there was another query performed against the data source which is sharepoint to fetch the.

    Next 100 records in memory and as the user keeps scrolling at the bottom it would keep loading the next 100 records in memory so in reality powerapps is working with.

    The entire data set just that it is presenting that data set in an optimized manner in fact even in modern sharepoint if you go to your modern list or libraries.

    Here i have 2 000 records but sharepoint does not load all of that in memory as i start scrolling to the bottom you will note how it starts loading the next set of records.

    Now since delegation is such an important concept it is very important for us to follow the documentation currently there are only four data sources that support delegation.

    My demo here today is focused on sharepoint but similar concepts can be applied to the other data sources as well now if i head over to sharepoint here we.

    Have a table that lists out the delegable functions and operations related to sharepoint so when you're querying sharepoint from powerapps there are various factors that.

    Drive the concept of delegation the type of my column makes a difference the type of formula that i use makes a difference let's try and build a search box.

    Experience so i've just used a button control here and there's something known as border radius i'll give this a border radius of 20 so it creates this curved edges right.

    Here

    And this button control i'm going to make it view only i will insert a text input control position it right inside this button.

    Control set the default text to empty i'll set the border radius here to 10 so it creates that curved edge again i will also go ahead and insert.

    The search icon position it right here and as the user types because i want to start typing from a few pixels from the left for my search box.

    Padding left 40 pixels let's preview and here as the user type we can see how the typing begins with the left padding of the pixels that we specified i have renamed my text box control to.

    Txt search now in power apps we have a function called search so search my data source next it's asking for the text the text.

    Will come from my text box that i added which is called txt search dot text and i can specify the column i would like to search in so i will say the.

    Title column i can specify additional columns here as well for now let's just close this function there are some key things to observe.

    I get this blue lines underneath that formula that says that there's a delegation warning so this formula might not work with large data sets on my screen itself i get this hazard.

    Symbol which gives that same delegation warning and if we go right here to the app checker and go to formulas we have a warning for delegation now in this case if i was to preview the.

    App observe how the gallery count is showcasing as 500 directly that means it's not undergone that optimized loading feature for us because it's just loaded 500 items in memory.

    I'm searching on the title column which i've called task here so let's say i search for data this will apply that search criteria on my result set of 500 records only.

    It will not search on the entire data set you will observe that each of the tasks i have a number associated with it so let's say i want to search for task.

    Number 600 so if i just search for 600 i do not get the result but in my back end i do have that data now why don't i see that record the reason is because of delegation.

    And powerapps will load only 500 records in memory if the query is not delegable now can we increase this number 500 we can go to settings general and right here is that limit now by.

    Default it is 500 the maximum you can set is 2 000 you cannot go one beyond 2 000. bear in mind increasing this limit to 2000 can also cause performance issues.

    Purely because powerapps will have to load 2000 records in memory and then perform the query i've seen folks use a workaround wherein they create collections and start.

    Loading data into a collection what's the limit for a collection there is no limit you can load as much data as you want but where is all that data loading it is loading in memory in the.

    App i would recommend to stick to the base limit which is 500. the search function is just not delegable with sharepoint.

    The search function itself is only supported for a certain set of column types so in my data set i have a choice column called progress.

    If i wanted to search in this column if i type that column name here it will give me an error because the only supported data type here is text now at sharepoint there is another.

    Function called starts with and this function is delegable with text columns and complex type columns here it says complex one so if i head over to the first point here.

    The only complex type column that is supported is the person type column and in there the email address of the person and the display name of the person are delegate.

    Instead of using search i will use the function filter filter my data source my logical test would be the startswith function.

    The name of my column here i'll use the title column and then what's the string that we need to check it will come from my search box which is.

    Txt search dot text now the formula is complete i don't see that blue warning for delegation and if you were to preview the app you.

    Will observe that the gallery count now is back down to 100 because it's performing optimized query try and search for that same task 600 now here i'm using starts with so the.

    Text has to start with the text that i specify there and search so here let's try this task 600 observe how it was able to perform the.

    Query against my entire data set let's try something else let's try task one here once again the gallery count is a.

    Hundred i have all of these tasks that start with that text but i have more than 100 so powerapps does optimize loading once again if i scroll to the bottom it will.

    DISCLAIMER: In this description contains affiliate links, which means that if you click on one of the product links, I'll receive a small commission. This helps support the channel and allows us to continuetomake videos like this. All Content Responsibility lies with the Channel Producer. For Download, see The Author's channel. The content of this Post was transcribed from the Channel: https://www.youtube.com/watch?v=zxZGaXQlZqo
Previous Post Next Post