Mastering Delegation in Power Apps: A Comprehensive Guide

look I don't know how else to say it if you don't understand delegation and power apps inside and out it is irresponsible to build a power app I guess I do know how to say it all right so today's show we're going to dive into delegation I'm gonna explain what it is how it works how it's different in different data sources right that's really important it's one of the reasons that people move from SharePoint to something like dataverse or SQL so we're going to kind of compare some of that we're going to talk about some of the workarounds make sure to point you some resources for those and overall just make sure that you are the smartest person in the room when it comes to delegation so you can responsibly build apps I've got a blank PowerApp sitting here waiting on us and before we jump in you're here in SharePoint I created a list today called Mega with 10 0001 items I have no idea why I chose ten thousand one but I did what we're going to do is we're going to add that over here right so data sources add data search for SharePoint click on SharePoint choose my SharePoint connection powerapps videos and then if we search for Mega here here we go choose Mega and then click connect probably under my face but you know where that button is okay so we added Mega to our data source and or to our app whatever that is right so now we want to add a gallery right so let's go a vertical Gallery in here connected to Mega Boop there they go and then now we're going to just set it to be title subtitle and body and as you can see I guess it's probably easier to see over here if we go into Mega I have 10 000 items right they call Item title one through item title ten thousand one right so I incremented those animal color is either blue or red so half or blue or half a red roughly right Powershell random function there and then I have catfish dog zebra there's another animal but I don't remember what it is if there's another animal anyway there should be a nice distribution of those anyway okay so that's we are boy all three of these are text columns so over here we have added it in right it's our gallery now if we want to know how many items in there right one way that we might do this is we do text label and then we could use that new feature where we'd say Gallery one dot all items count so now this says a hundred right what were you expecting to say ten thousand no right so what this does is this shows you the number of items currently in the gallery so keep that in mind if you're ever building logic three takes advantage of all items it might not be all your data it is literally just the records that are currently loaded but the way it works is that when we call our SharePoint list we're like hey give me the SharePoint list sharepoint's like I got a lot powerapps like fine give me the first hundred and so then it just gets the first hundred but if we scroll to the bottom of our Gallery as soon as we hit the bottom it says Hey SharePoint give me the next hundred and now we've got 200 right and if we scroll again we'll get 300 400 Etc right and we can sit here and we can scroll until the cows come home I have done this I think I've gotten in the 2500s hey I'm gonna pause the video see how see how far I get before I get bored okay seriously I'm bored it's 5600 right and it'll keep going in theory if my browser doesn't crash I can scroll to the bottom of ten thousand one right so this is the first thing to know galleries can handle large lists but Shane SharePoint can only have 5 000 items bull right there's 5600 coming out of SharePoint already what you want me to scroll again of course you do look there you go I scroll some more to six thousand right I didn't want you I I just arbitrarily stopped okay so in theory we can scroll to the bottom of ten thousand we are not going to do that today so this is okay right having a very large list in SharePoint with your power app no big deal so where the interesting thing comes in is what if I want to start working with functions like filter right so if I say hey I want to filter Mega where animal color equals red so when I do this it goes and now you can see instead of starting an item one it went from two to six to eight eleven right because they are randomly dispersed but now once again if I set here right it's 100 now it's 200 now it's 300 I could scroll right so this should be roughly 5 000 of these right I should be able to scroll through all the records and find everyone that is at right so in this case once again big list no problem where does the problem come in well if we do something like filter filter is what we call a delegable query so what that means is that when you say filter SharePoint list where animal color equals red powerapps sends that up to SharePoint sharepoints like hey I can do that share points like hey I got a lot of items I got like 5 000 items powerapps like oh hold on give me a hundred and so that is called pagination powerapps and SharePoint are doing it together there's no working around you can't change the number they just talk in chunks of 100 and we have no say in it which is good and bad for some people but it's good right 100 a time it does its thing yay so what's the problem okay this is a delegable query right delegation is good we're delegating the work to SharePoint so what is bad so what if instead of filter we said hey instead of filtering I want to search all right that's another function search Mega and what do you want to search for we're going to search for the text um EB right that's the middle of zebra so search for EB in what column we want to search that in the animal type column right notice right away when I type that we get that blue line over here on the left we got this yellow triangle right what do we do let's close this out okay we did not get an error it worked right we've got results and if you look what do we get here it says hey you got 94 results awesome right no big deal right what are we gonna do play Let's just scroll to the bottom and we notice right we are getting the right things right zebras yes okay let's roll the bottom wait why didn't it reload the reason it didn't reload is because this is a non-delegable query that is what this whole um blue line here this yellow triangle they're trying to tell you if you hover it says it delegation warning the search part of this formula might not work correctly on large data sets okay so what happened so we said hey search e Mega for animal type has EB in it okay we send that to SharePoint SharePoint said nah baby nah I am lazy just like buddy I don't do these things so instead what I'm going to do for you powerapps right this is SharePoint talking still instead what I'm gonna do for you is I'm going to send you the number of Records up to your delegation limits So currently that limit is 500 so not the first 500 matches literally record one to record 500 so it says hey I got these 500 it then got sent across the wire in one big batch now my browser has 500 SharePoint records it then ran some JavaScript yeah and it said okay I found the 94 matches to that query and so it presented those back to us but Shane what about the other 9501 records what about them they are never going to be searched right the search function ignores it my a non-delegable query only searches up to the delegation limit but Shane I know I can change that you're right so if we were to go over here to settings wherever settings are hiding these days right here I don't know why they're hiding from me whatever settings and so if we go and scroll down a little bit data row limits set how many rows are retrieved from server-based connections where delegation is not supported this is the powerapps delegation limit that is where that 500 number I just gave you came from you can set this all the way up to 2000. it is happy right if you try to set it to twenty thousand it'll tell you I gotta be between one and two thousand yeah so if we change this to 2000 okay number doesn't change we'll then go over here to data sources and we will say refresh this will cause it all to reload so now it repeated right it said yo SharePoint do this SharePoint said no powerapps said okay fine SharePoint said here's record one to record two thousand dropped them in it got loaded into memory JavaScript ran and it found that there were 388 um you know zebra related record right and if we scroll down the bottom we'll see that the last number is somewhere close to two thousand so there you go 1988 is the next zebra or the last zebra in the first 2000. the other 8001 records there's nothing we can do about them they're they're just isn't right like I'm not hiding something from you there are some different very tough ways to like do some stuff but if you're looking at pure power apps functionality this is the answer right the search function is only searching the up to the first 2 000 records this though is important because this is a big difference if we go look at dataverse right so in my powerapps training class I am teaching it on Friday or no well you're in the past whatever anyway my live training class we were talking about this and so when we do this right the next thing we'll do and I'm just going to do it for you guys because I like you we're going to go here and add the mega so this is a exact copy of this SharePoint list but in dataverse so we're going to add it in and it's now called megas so there's Mega's so now what we're going to do we're going to throw another Gallery in here we'll connect it to Megas will change its uh view to be titles or image title subtitle and body I clicked the wrong one sorry that one the microphones in my way you can't see it but it's in my way right and so what do we want here we want this one to be the title we want this one here to be the color and then we'll set this one here to be type right so that way they are exactly the same okay and then we're going to put our little label up here and so for this one right what's that one called it's called Gallery two so we're going to do Gallery two dot all items count like that and so then now we'll change the items property of this Gallery remember this is dataverse that's right Mega's is dataverse like probably if I was nicer I would have oh that's not a gallery this is the gallery so then if we do that same formula let's just copy it so I don't have to type again paste and then change this to megas that's not confusing is it I hope it's like hey the name of the column's not animal type over here it is that weird naming so notice when we do the same exact thing in dataverse that we've got a hundred right but if we look at the top we can see they're literally one for one the same but so down here we scroll down to 1988 and so with dataverse every time I scroll to the bottom we get another 100 right and so in theory what is it 10 so it's probably about 2 000 if I had to guess oh we went too far why don't I go so far why is why do I stop right so it was find the bottom so look there's 1988 and so 2004 is the next one that would have matched SharePoint we can't get there dataverse SQL Server they both can delegate right so when we asked uh dataverse to search megas for EB and animal type I said okay cool I got a thousands of records for you and then power up said give me a hundred at a time and they did the 100 game the the pot the powerapps pagination game right so that's what happened if I set up the same thing as SQL we would get the same exact good behavior SQL also can do it SharePoint cannot this is a big thing now one of the things I want you to do is I want you to open up a browser search for powerapps delegation and so this will take you to this page and there'll be a link to this in the description if you don't know where it is but understand delegation canvas apps remember it's irresponsible to build powerapps you don't understand delegation so go read this page it is not as interesting as me it is kind of boring I'll be honest with you but it gets in a lot of nitty-gritty details that you should learn now the main reason I'm sending you here is if you scroll down just a little bit so this says hey these are the four delegable data sources and so if you click on SharePoint this is going to show you hey these are the functions that are delegable against certain column types okay because when you think about delegation it is a combination of your data source so we're using SharePoint SQL dataverse Etc it is also then a combination of what function are you using so like filter or are you trying to use search right because filter delegable search not delegable all right what data source you're using what operators you're using this is where it gets a little tricky right so if you went back over here and you're like hey okay so search doesn't work but we know that filter does right so what if we said something like this filter Mega where EB is in animal type what happens here it didn't fall for it right because yes SharePoint is delegable yes filter is delegable know the N operator is not delicable okay so one of the rules of thumb here is that any time that powerapps tells you something is not delegable it is not delegable it's not lying to you it's not tricking you if you see that blue line whatever you're doing here is causing delegation problems I'm sorry and So speaking of working around them like if you look up there I've got a video that talks about a bunch of different workarounds so we're not going to go through all the workarounds today like if you're looking for workarounds there become things like doing nested functions um you know different types of you know smartness around how you package your data that can be helpful okay so keep that in mind right so it is data source function operator and even column type can fall into right all four of those have to align just perfectly for something to be delegable you're also going to see that you know some functions like if we go back a screen so down here they do talk about um you know some of the things that are does not they're not delegable that you might have assumed you know lower uppers things like that there's a bunch of weird stuff some of their table shaping some non-delegable functions you know there's a lot of those that aren't delegable that you might have thought so reading those is good I find it easier instead of trying to think about what's not delegable this list of what is delegable especially if you're only a SharePoint person that's a pretty tight list filter sort sort by columns and look up starts with and is blank that's it you're also going to see that there's some weird SharePoint right see here it's like hey you can use these operators with a number right can you though so what if I said hey I'm trying to Pare this down trying to be smart so I'm going to say filter where ID equals 12. right that is delegable that would work right and we can prove that that works by saying filter where ID equals 5000. okay that works what if I say filter ID where it is greater than 5000. right why because yes it was filter or uh you know number columns are done but there's a special awareness and I don't know why the weirdness exists but you cannot do greater than or less than against the ID column and SharePoint boom that would fix so many of our problems I I assume that they're just being mean to us no I'm kidding they're not I'm sure it's very technical reason why it doesn't work anyway so just keep that in mind there's a lot of nuance here and So speaking of nuance right inside of one of my training classes we teach all the nuances much better way to go okay anyway so over here let's talk a little bit more about Nuance right so let's just put this back to Mega so a couple other things I want to teach you here right one of them is that you've got to be wary right so I told you the powerapps tells you something's not delegable then it's not delegable right in Story full stop what I didn't mention though is that sometimes it lies to your face right so if we insert a drop down here and so what if we want to use like the distinct function all right so distinct is a way to get the distinct animals out of our data source right so we'd say distinct from Mega and then what do we want we want the distinct animal types and we would close that right so then you're like all right what do we get catfish dog zebra cow all five of them are there yay great right not so fast distinct is not delegable even though it tells you that it is you want to know how the easiest way to prove yourself is not deliber like I could go add something at the end and you would see it didn't show up but a lot of people don't have giant lists to test against so the number one trick when you're struggling with delegation you're trying to avoid it you're not sure right like right here where you think it's lying to your face but you're not sure go back to your friend's settings right people always talk about setting this to 2000 right give me more let's give me less set the delegation limit to one so now any non-delocal query will only return the first result okay if I do this and refresh now let's check our drop down there's only cats right because this is a non-delegable query you just didn't realize it because it didn't tell you unfortunately so you've got to be careful if you're trying to avoid delegation you don't want to have any delegation issues set it to one in your app so when you're building even if you only got five records it's obvious right because this what it was not distinct is non-delocable so it said give me all of the one records it got the first record then it said or were the distinct animal types while it's capped if I had said the limit to two it would have been cat and fish three it would have been catfish and dog four it would have been catfish and dog five would have been catfish and dog right you get the idea it's only checking the records that it gets back up to the delegation limit to create your distinct list yeah how many of you knew that power apps will lie to your face speaking of not knowing right let's go put back in another uh non-delegable query here oh there's a non-deliverable query right nothing return I gotta go set this one let's go set this one back to 500. that's the problem with setting the limit and then we'll refresh again okay so we're back to our 94. why did I want to do this because speaking of not knowing right what here in here we get the blue line we get the yellow triangle hit play what is your user see oh wait they don't see anything they don't get a warning that their data is not here right they just think powerapps is dumb like why is it show me all my information they don't see the yellow triangle they don't see the blue line that onus is on you the maker right this is why it's irresponsible because your users don't know you're not showing them everything what if they're making critical decisions and you forgot to show them all the data because of Delegation had so this is what you have to be careful of right as the App Maker you've got to think this out hey am I doing something that's not delegable what am I doing about it do I put a big warning on the screen and tell users do I you know just be like who cares they don't they're not important to me anyway I don't know it's up to you to solve it but make sure that you're thinking about if you put a non-delegable thing there be ready to solve it one of the ways that so often manifests itself is you also get a phone call like hey I added a new animal type to our data set and all of a sudden it's not showing up on that drop down anymore right well it's because you they're using distinct and they added it outside of your delegation limits oops right those are the type of Clues when you start getting data's not exactly right you know or the new things aren't showing up probably because your app outgrew the delegation limit be smarter the other thing to be smarter with is be cautious with that darn internet right people like me are allowed to put anything they want on the internet like if I want to say Shane's the smartest man alive you can't stop me it's on the internet now it must be true no right but a lot of times I see people put workarounds on the internet that aren't exactly right but they're like but the doubt the Blue Line went away remember powerapps a lot of your face okay while I was editing I decided to cut out an example of one of the crazy things the internet did just so it wasn't there so anyway I took that out just remember if the workaround sounds too good to be true set that delegation limit to one and see if it truly does work around delegation or if it just suppress the delegation message which means it didn't do right so set it to one test it out I'm not going to show you the crazy thing um another thing let's see if it's broken or not I I've given up all the broken ones so what if we do this what if we say what if we put it in a collection so quarter collect coal Mega stuff and then we'll put in Mega oh that's bad right how many items if we press this button do you think we're gonna get are we gonna get all ten thousand items or I did collections make this problem go away insert a label count the rows in my collection womp womp The Collection only got up to the delegation limits right but once again my dear friend powerapps not telling you the truth so don't just assume that you found some magical trick to get around delegation that is my warning to you now if you're thinking but Shane I've seen on the internet like ways to make bigger collections yep they exist I'm not going to teach you them because I don't believe in having giant collections but it is possible there's some things but they're all like I don't want to say French but I'm going to say French like they're they're all they're all out there a little bit right like they're either really complicated and maintain they're building collections that may or may not crash the browser depending how much stuff you put in there there are other Solutions but if you're trying to stick with Core Power apps using your normal functions they're not it right what you're going to see in core power apps is this is usually one of the reasons right honestly the the being able to use the search function is one of the main reasons we see people switch from SharePoint as a data source to dataverse or SQL what are the premium ones right and all the licensing go with that you do what you want but I just want to put that out there like this is it right also just remember if you're as you're getting this as you're understanding this great now if you fully understand this you can responsibly build powerapps go watch that other video I pointed to in the beginning of this one that talks about creative ways that are in the realm of powerapps so using things like starts with using filter now sit inside a search there are creative ways that don't require you to you know get way outside your bubble to do it or if you're comfortable being outside the bubble right then you know some that other stuff could be interesting all right so that's what I got for today make sure you hit that like button I really appreciate likes they help my little video do more with itself you know delegation is a very popular topic I think I meant the first versions video like five years ago like a hundred thousand views so I'm sure this one's gonna have a long life ahead of it too getting questions comments leave them below if you want to sign up from one of my training classes go to training.powerapps911.com and with that I'm gonna say thanks

Channel: Shane Young

Views: 18,452

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, Delegation, SharePoint, Dataverse, Delegable Queries, Non-Delegable Queries, Data Row Limit, Power Apps Performance, Power Apps Tutorial, Power Apps Development, App Building, Large Data Sets, Power Apps Efficiency, Power Apps Functionality, powerapps pagination gallery, powerapps pagination, powerapps paging, powerapps 500 item limit, powerapps 5000 limit, powerapps 500 item limit workaround, power apps delegation

Id: 2FQoTp6RXwc

Length: 23min 13sec (1393 seconds)

Published: Mon Jul 24 2023