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 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 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 get the next 100 set of data now let's try and also search on my person type column which is assigned to so for my same filter condition i will add an or condition starts with my column name here is assigned to remember this is only delegable with two properties of this column display name and email let's try display name and now what's my text once again coming from that search box let me format text let's preview the app now i can search on both those columns together so let's say i search for reza so here it is only showcasing all those tasks where assign two starts with the text reza or the title starts with the text reza and once again since there are many records that are assigned to reza optimized loading is being performed but i want to also add filters so that the user can also refine the data set i've added a couple of label controls and a drop down which will showcase priority column choices for this control i will set allow empty selection to true and the items would come from since it's a choice column in sharepoint choices of my data source dot priority so this should load all those choices for priority and i have done the same thing for my status choice column as well i have done multiple videos on filtering galleries the links to those is in the description for this video so do check it out so back to my filter condition i will put brackets around my starts with formulas add an ampersand which is my and condition once again in brackets let's put the first condition priority dot value it's a choice column is equal to the drop down control on my screen which is the filter dot selected dot value or if that drop down control is empty this is important to add because in case that is empty i want to get all the records so by using an or condition if it's empty this evaluates to true true or anything is true so it will get all the data and i will add the same and condition but this time for my other column which is my status or my progress column let's go ahead and preview the app so show me all the items from my data source which are assigned to reza priority is high status is completed it's adding all of those filters against my entire data set of more than 2000 records and that is purely because all my queries here that i'm performing against my data source support delegation but i would like to provide searching capabilities to the users so that they can search in any of the columns inside my sharepoint list search is not delegable so the idea here is to provide good filters to the user so that they can get the data set in the gallery within delegate limits the delegation limit of my app is currently set to 500. now i'm going to use that number which is 500 and create a search experience on the onstart function of the app i will set a variable i will call this var delegation limit and i'm setting this to 500. now make sure that this number matches the number that you have right here and since i'm in the edit mode of the app i have to ensure that i right click the app object and run on start so that this variable has a value which in this case is 500 for my filter query i will go ahead and remove starts with so i only have the filters now for these controls that i've added here can also add a reset button and when the user selects this button we will go ahead and reset my drop down controls and also my search box so here if i was to preview the app click reset all the controls are cleared next i will insert a slider control and this control we are going to hide from the user on the screen so the user never sees this the max value for this slider control we will set it to that variable delegation limit and the default value count rows of our gallery control dot all items so if i was to preview the app observe the value shows up here as hundred my total delegation limit of course is 500 and if the gallery was to load more data in memory you can see how that slider control automatically moves ahead now whenever this slider control changes we will set a variable which would be self dot value items property of our gallery control let's go ahead and write the following formula if that data count is less than our delegation limit variable then i'm applying my standard filter criteria now to this i would also like to apply searching capabilities search is not delegable and it only supports text columns but i want to add additional column type so instead of using search there's another function called in in is also not delegable but it can support other column types now this filter criteria here is definitely delegable we will apply another filter on top of this and this is where we will add that in criteria search box control dot text in my title column or is this in my id column let's say or is this in my choice call dot value or my person type column dot display name and so and so forth so as many columns i want to search in and in actually performs a full search for the else condition here i'll just go and apply the original filter criteria which was the one without the searching experience logic here basically implies that if the number of records that we have currently in our gallery if that is less than the delegation limit that definitely means that all that data is now in power apps and memory so if i was to search on that data search would give me accurate results but that is only possible if that result set is less than that delegation limit so the key here is to provide good filters or refiners to our users so that they can first apply filters to get the data set less than the delegation limit and then we can add search now we are getting a delegation warning so will the gallery perform a correct delegateable query because it's throwing a delegation warning well it partially does support delegation how this filter condition has no delegation issues and powerapps when you have multiple unnested formulas it begins from the inside functions and then starts going to the outer functions so because this query is deletable this will work with a large data set and once that result is returned is where it will then try and perform this filter condition on it so that's the concept that we are banking on here and all of our search controls here i'll just go ahead and group them for the visible property we will set it as follows our data count variable if this is less than our variable of the delegation limit now let's preview the app and check this out in action right now my gallery has 500 records why because the entire query is not delegable so it's loading 500 records in memory once the user starts scrolling down to the last item it will not load more than 500 items and you would not want your user to go down scrolling all the way to 500 items and so and so forth you would want to provide good filters or refiners which i have tried to provide right here so let's say i'm interested in all the work tasks that have priority critical so i applied that first filter now observe what happened the moment i applied that filter the gallery count is 259 it's less than the delegate limit that means guaranteed this query has given me all my data why because the inside query or that filter condition that i performed supports delegation so if i actually scroll down you will notice it's picking tasks that are in the numbers two thousand because this number is less than my delegation limit i'm also opening up searching capabilities so let's say i remember that my task had the number 20 somewhere in it so now if i search for 20 this is actually searching in all the tasks that have that number 20 in either the id in this case id has the number 20 in the task here is number 20. now let's try and reset this let's try this once again show me everything where the priority is medium now observe with medium priority i am getting 500 records that's my delegation limit of my app so if you notice the search feature is not yet opened that's because there could be more records in my data set which power apps has not loaded so i've provided additional filters and you can provide more filters here so users can get to a more refined data set if i pick not started there are only 16 records in my entire data set with this combination the search box has opened and i can search for the text against any of my columns in that limited data set if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 328

Rating: 5 out of 5

Keywords: powerapps search and filter, powerapps search and filter combined, powerapps search gallery items, powerapps search gallery, power apps search, powerapps search box, powerapps search function, powerapps search sharepoint list, learn powerapps, powerapps, search, filter, microsoft powerapps, powerapps sharepoint, powerapps search not working, powerapps search bar, powerapps search filter, powerapps filter, power apps, reza dorrani, powerapps search multiple columns, delegation

Id: zxZGaXQlZqo

Length: 19min 40sec (1180 seconds)

Published: Tue Oct 05 2021