PowerApps Delegation with SharePoint as a Data Source (Part 1)

hello everybody my name is reza durrani and today we will be talking about delegation with power apps and sharepoint as a data source so as you can see in this case I have a power app that I have created based on a SharePoint list of students and if I play this app this is listing out all the students from my SharePoint list and I have a filter on top where I can search based on the name of the student now this app was created directly from the SharePoint list by going to the list of students and then by creating the power app directly by selecting create an app so this is a standalone app that was created now if I go back to my power app obviously I've made a few modifications in the look and feel for the gallery just to make it look a little more nice and as you can see the gallery is listing the name of the student the class the student belongs to the region the student is associated with and of course the enrollment date defined in the SharePoint list now the concept of this demo will be to showcase delegation so if I go to the site contents and if you look at students list we have 295 items in the students list and within the students list if we look at the different types of columns we have we have subjects which is a choice field and if I click on this column we have four subjects here and within these subjects if you notice it's it's a multi select choice field so this is a multi select choice field for subjects there is a column called act nur active that is of type yes/no additionally we have a region column this is a single select drop-down of options it's not multi selects I'm trying to create all different types of columns that are possible another one is called class this is a lookup to another list in the site called class and I'm looking up to the title column of that list okay so again very simple straightforward this is just a very simple lookup and we also have a couple of other fields that I created one is called manager which is a people pick a field and another one called enrolled date which is a date and time field so basically we've covered all the types of fields and as you can see this is my user database title has the name of the student subject has the multi select option so the student can pick multiple subjects active is whether the student is active or not region is defining the region it's a single select choice class is again a single select choice manager is the person or group field single select in this case and enrollment date which is a date and time field now so that's the premise right and we also have a another list called class that I created that just has the list of classes so now if we head back to the app right now what is delegation first of all now as you can see in this case by default the browser Nouri has a has an option you has a has the items property that says filter the list of students where the title property starts with the text value that is entered in the search box so that's the filter criteria that has been defined that's why when I search for the name of a student if you note it was starts with so if I was to so if I was to search for a particular last name for example in this case let's say Drake's right so if I was to search for Drake's you see it won't get me the results because the query says starts with and it does say contain so if I if I was if I was to search for this user for example I would have to give the exact starting letters off the title again case-insensitive but that is something that I would have to do so getting back to just to explain what the query is the default query now to understand delegation first let's understand how many items from the backend data source is this returning so we know we have 295 items in the SharePoint list and if you go to file and if you go to app settings and advanced settings by default if you notice there's this setting right on top which says data rule limit for non-delegable queries and by default this limit is 500 it's set to 500 what this limit specifies is when power Apps is squaring a data source or getting data from a data source does it delegate the job of fetching the information acquiring the information to the data source or does power up has to do it itself if power apps have to do it within the context of the app in that case it can only work of it 500 items that's the default limit if you have a delegation warning based on the query that you have defined okay if you do not have a delegation warning then in that case this delegation limit does not apply and power apps will fetch all the data based on the limitations of the backend data source in this case our back-end data source is SharePoint ok now actually know what my SharePoint list has to 95 items on this delegation limit is above that so what I'm going to do for now is I'm going to change my delegation limit to 200 ok so I've just changed power in fact let's let's change this to 50 so I'm just setting my delegation limit right now to 50 that means if there is any query that power Apps is performing with respect to SharePoint as a data source and if the query is not eligible in that case power apps can only work with 50 records that's it also when I say 50 records in this case if you notice I also put the ID which is the which is the inbuilt ID column in SharePoint if I was to query this data source and if I had a delegation warning and if I say get me details where a student class is class 3 we have a student here that's sitting in ID number 55 if I query this datasource because my delegation limit is 50 and if I get a delegation warning it will power apps will only work with the first 50 records so what power apps does is it first loads all the data and then it will perform the query on it so it's not like 50 is the number of items returned after the query it actually goes and checks it first loads the first set of delegate records that it has and then it works on it and we will see all of this in action right now because I'm going to create scenarios when we see all of this so I've set my delegation limit to 50 again by default it's 500 and you can take this up to 2,000 you cannot go beyond that that's the highest limit you can set so in this case getting it back to 50 will go back so now my delegation is limited is 50 and as you can see I don't have any delegation warning if I go to filter and if I go to formulas I'm sorry if I if I look at if I look at it right here if I have a delegation warning it will show up out here also you will see a little exclamation mark out here highlighting that there is a delegation warning in this case there is no delegation warning okay now now that we have done this right we've we've done this let's first understand that this query right now starts with is a query that's going out to SharePoint as a data source and this is a non that this is not this is a delicate operation now how do I know that this is a delicate operation starts with so if you go to doc start Microsoft comm and if you go to the canvas apps components and if you the canvas apps fees and if you go to a delegation there is this very nice article explaining about delegate data sources and within this if you notice there are the three main data sources your common data service SharePoint and see well this video is all about share points let's just focus on share points I'm going to go to share point and in this case if you notice these are all the functions that are delegate okay and based on the type of the columns as well for example right starts with starts with is delegate with text fields it says yes that means that is Delhi gable that means we won't run into the 50 item delegation limit and complex data types which are lookups and choice fields even those are delegate if I use starts with that's a yes right now let's take another example right if you notice the search function is not here that means if I try to use the search function I will run into our delegation warning and we will look into all of this so that means current currently the app in question which is using starts with to query the title field which is of type text I am NOT going to get a delegation warning and that is true because when I head over to the app I don't get a delegation warning now what I'm going to do is I'm just gonna insert a label out here and this label is going to show us the count of items in this gallery okay so here's browse gallery one I'm gonna do count rows of browse gallery not all items so this will give us a count okay now if you notice I am wearing the entire data source it just says hundred why is it hundred right there is no delegation warning my delegation limit was fifty but the gallery only fetched 100 records why that's because the gallery in power apps is optimized so as you start scrolling it and once you cross 100 it will load the next hundred items so as you can see as I keep scrolling it will keep loading the next set of items and this this number which shows the which shows the item count in the gallery this will keep loading as well this will keep changing and it will keep adding rows to it okay so here's back to the gallery let's let's show the scrollbar and let's play this app so now if you notice if I go right to the Morden right now let's also put the ID column in here so we can so let's say class three as well you can also add the ID of this item dot ID okay and as you can see if I that's and let us sort this by you're gonna sort this by the ID column okay so I've changed the sort sorting it by the ID column and should say you go as you can see it's one all the way through hundred right and you see it loaded the next set as I scroll down it loaded the next set right if you notice here this went to 200 and if I scroll down it will now load the next set of hundred items so you see you see on the top the loader is going say it's loaded all the 295 items so the gallery is very optimized and it's very smart so it loads items in batches of hundreds however this is also a delegate operation so that means even though it has loaded 100 and let's go and reload 100 so what I'm going to do is I'm going to reset the gallery so it goes back and resets again and it loads only a hundred items right right now it's only showing me 100 items from one 200 it's only loaded a hundred items in the context of power apps but suppose let's say I want to search for a student that is sitting at position 140 so let me search for the word Larry lar ye so if I head back here and if I play the app although Larry is sitting at a position that is beyond 100 it because the query is delegate it will go and fetch the data so that's what delegation is it's delegating the work to the backend data source and because I'm not getting a warning I am good even though even though the Delhi gave a limit for my app was set to just fifty right now what we are going to do is we're going to change this so now instead of using starts with title the title starts with this what we are going to do is we want to change this out ok so what we're going to do is we're going to use so we're gonna say search students okay read the column prepare this text which is text box search one door text is in title okay so now what I've done is I've just changed the query to a search query which says search the students list where the text that's that's been entered and search it in the column title so this is like a contains operation okay now the first thing if you notice you have this blue blue line error out here which says delegation warning the search part of this formula might not work correctly on large data sets that's because we have a delegation warning if I go to the app checker and if I go to formulas you see there's a warning out here and clearly says it's a delegation warning it's a warning it is not an error it's just letting you know that our apps will only work with this data source with respect to the delegation now if you notice I have searched for the word Larry right it's the same search the items returned is zero now why is this zero right the student is sitting at position 140 the delegation limit was 50 that means search will only be able to search now in the first 50 items because my delegation limit is 50 if you had set it to 500 500 maximum you can set two thousand that means you cannot work with large data sets data says that span beyond 2000 so now if I was to search for a student within the first 50 items for example at search for tests if I go back if I've searched for tests you will notice it actually pulled it why because power apps is first loading the first 50 records and then doing the search operation on it based on the delegate operation so this is very very important to understand what delegation truly means and how do you get around delegation it's very very important to know which formulas are Delhi gable so it's not just the formulas it's also the formulas and the types of columns that you are dealing with in SharePoint currently number fields are delegated with a lot of operations as you can see texts also a lot of them boolean is also delegate of complex types very recently have been made delegate sir prior to this if you had a choice field or if you had a lookup field those were not delegate an operation with most of the formulas luckily they are very capable now they are not yet Danny gable with sorting so you need to understand which ones are delegate and which ones are not after this I will be creating additional videos to talk about how to handle delegation for the different types of columns so stay tuned for my other video other videos that are going to be coming up on delegation but this is the first video that I wanted to showcase around delegation and highlight what delegation is and why is it so important to understand delegation very very important okay so delegation is what power apps can do if your query is not eligible and that's why this very important limit that you need to be aware of and secondly even after you handle delegation so for example let's say I have a list that has 50,000 records okay I have a list that has 50,000 records and I am using a delegate query right great that means power app should be able to search in all those 50,000 records that is true as long as your data source can handle it right now as most of you guys know SharePoint has another limit which is the 5,000 item limit so let's say in power apps if I have to Collette's say the students list hat was a student database of 50,000 records and if I were to search for the title where the student name starts with a particular name of a student that is sitting at position number 50,000 in this list now if I need to query that because I'm using a delegate will query power apps is fine power apps will delegate the operation to SharePoint but guess what in SharePoint there is also the limit of 5,000 items because SharePoint has its own limitations so how do you work around the 5,000 item limit and share pointers we need to go to list settings and you need to go to column indexing indexed columns okay so if you go to indexed column you can create up to 20 indexes here so if I have a student database that spans beyond 5,000 I know this list is gonna grow right I should be in that in that case what I should be doing is I should be creating indexes for all the different types of columns that I would be filtering or sorting on in Tower apps right so in this case if I go back to my app I have a very simple case where I am searching based on the title property obviously searches not add eligible operation first of all so this is not going to work I have to change this back to starts with right so I need to go to filter I need to do starts with because starts with isn't eligible function and title starts with the text that has been entered in the text box so in this case okay so in this case I don't have a delegate I don't have a delegation issue but but if my student database was beyond 50,000 and if I was to make this query I would run into issues because SharePoint has a delegation limit of 5,000 now the good thing about modern SharePoint is at the backend it automatically creates indexes for you based on the filter queries you are making but if you are building enterprise grade apps with power especially working with large data sets you need to be aware of the limitations of your data so so in case of SharePoint if I know that this list is going to grow beyond 5,000 then in that case what I can do is I can come here and I can make title as an indexed column that ways I have no issues of querying data in SharePoint that spans beyond the 5,000 item limit okay so you need to be very very careful of this - more Pro tips related to indexing in SharePoint as you can see you can only create a maximum of 20 indexes so you need to be very sure of which columns you are filtering and sorting on in power apps that's number one number two once your item count hits twenty thousand you cannot create indexes and you cannot delete indexes so that means let's say I have a list today and I am just bring on a couple of columns and have created two indexes great the list grows goes beyond 20,000 and then I decide who I want to create a third index because I want to filter or sort on a new new column type in that case it will not work because your column indexes has gone beyond I'm sorry because your item count has gone beyond 20,000 so you need to be aware of that fact in that case what you would have to do is write a PowerShell script to move the items out you have to lower your items below 20,000 so you can add indexes or you can delete indexes so that was the first this is my first video now on delegation and I plan to add some future videos that will be coming those will be short ones on delegation for each and every column type in SharePoint ok thank you so much for watching

Channel: Reza Dorrani

Views: 26,905

Rating: 5 out of 5

Keywords: powerapps delegation sharepoint, sharepoint delegation powerapps, powerapps delegation sharepoint list, sharepoint delegation limit, sharepoint delegation, powerapps delegation, powerapps delegation workaround, powerapps delegation warning filter, powerapps delegation warning, powerapps delegable functions, powerapps delegation large data sets, powerapps search delegation, delegation warning the filter part of this formula might not work correctly on large data sets, power apps

Id: gwiErbYtRdA

Length: 20min 35sec (1235 seconds)

Published: Mon Aug 26 2019