and today's show we're going to up our apps delegation you know that pesky yellow triangle group yeah that's right the blue squiggly that thing that drives you crazy but you probably don't really know what means so we're gonna do is we're gonna talk about the basics what is it then we're gonna talk about some different data sources and functions and how you can maybe work around some delegation stuff I'm gonna try and sprinkle in some advanced stuff and just other little tidbits I've learned from all my years of fighting delegation but first here's our intro hi my name is Shane young with power epsilon11 those guys and today's topic is power apps delegation it's a nasty little topic that a lot of people try to ignore so where I thought we'd do is we're gonna break down and talk about what it is right we're gonna make sure all on the same page we understand what it is what causes it all those fun things we will start with us basics and then from there we'll kind of talk about maybe some of the variety of it you know because really what it matters is it's a function of what is your data source but also what functions you're using but even what operators you're using so there's a lot of pieces that kind of go into the puzzle so we're gonna talk about that there's about 500 versus 2,000 we're gonna you know sprinkle on some sharepoint some sequel some common data service and just try and make sure that we kind of get an all up you know overview of what delegation is so you guys can be better equipped to handle it so even if you got a decent handle on it I still hope there'll be some things you'll learn a little bit later in the video cuz I'm gonna try and you know bring all the knowledge that I have on this lovely topic all right that's enough blah blah blah because there'll be plenty of blah blah blah so what's the switch over to my desktop and get started ok so over here on my desktop I've got a framework of an app altogether so I've already added a sequel table so as your sequel it's a really large table a bunch of data and I've added a common data service entity that has a whole bunch of data because what I want to do is well we're going to primarily teach from share points I want to be able to compare and contrast those two so I just do those over here you may not a little screen and so we can jump over to these galleries if we need along the way but so let's jump over here to SharePoint and so the first thing you're going to do is you're going to add a gallery so insert gallery I'll do vertical and so then here we're going to search for a friend SharePoint and so then here on the shame team site there is a list called giant right here connect and so this list has about 25,000 items and so I made all those with PowerShell and so I made this list with 25,000 items and then I went over here and I went to the common data service and I imported or made a common entity from SharePoint data to grab those same 25,000 items so these two have the same exact data the sequel table does not if you're wondering how I made a SharePoint list with 25,000 items the way I did that was with the PNP PowerShell it's a bunch of other stuff that was on this channel before I did power wraps if you got questions about that leave me comments or if you just want to see me make a video that showed you how to make a bunch of sample data leave a comment maybe I'll do that one day anyway okay so now we've got our SharePoint list and as you can see it's happy right now the first thing I want you guys to understand so if you throw a label over here right this is just some stuff you need to kind of know all in the way that helps you you can use the count rows function I'm gonna say hey count rows I want you to use gallery one dot all items right so this is a way to know how many items in your gallery now if you look at this short wait Shane you just said this list has 25,000 items in it why is the gallery only showing 100 items well the trick to this is that power apps and SharePoint got together today you know Shane doesn't really want to see our 25,000 ants we can technically show them to him but we don't want to but if you scroll the bottom you hit the bottom of the hundred what happens is it loads the next hundred oh and he hits next hundred and so boom boom and then finally boom so it's loading them five hundred at a time now what's interesting was it actually went and got 500 it only shows a hundred in increments but when I hit the bottom of the 500 it'll then go to 600 and so if we sat here long enough I will not do it I will not make you watch me do it but I've scrolled all the way the bottom it will scroll to the bottom 25,000 make sure browse is really mad because then all sudden it's got literally 25,000 things going on so don't do it but all the items are technically here and that's because when I did this request right SharePoint power apps work together to get me my data so that's the first step in this is understanding how much data you're seeing because that's gonna help you as you start to drag prayer delegation so then if you go in here let's just talk about delegation so if you say I want to filter the giant list how about where color equals red all right so my sample data has three different colors so that's actually going to go out and go to SharePoint and get the data and so what you need to understand here is this is what it's called aid eligible query so what that means is that in the grand scheme of things I don't don't get offended but perhaps is lazy all right power amps tries to do as little work as possible so when I wrote this query filter giant where the color equals red you know said hey power up show it to me power it up says alright hold on one second power up says hey SharePoint this Shang guy wants to see all the records where the color is red go do that so power apps delegated the work to sharepoint sharepoint goes and does all the processing appears oh okay here's the list of all the items where it's red and so then it starts returning those back to power apps in small chunks so we can then go through right so then if we hit play once again we could scroll down here so there's you know 200 300 400 500 right and the reason I'm going to the 500 because we know that's part of the delegation story so you can see that we could scroll the bottom and because of the way my data set up it's about 8,000 records are there where the filter colors read so that is aid eligible query delegation delegating the work to the data source it's a good thing in power apps so we've done something positive here and we've made the work do and the reason we want to do that is because we don't really want you know the opposite right imagine if we had to say hey I want to filter this list okay SharePoint download all 25,000 items in my browser and then of my browser process all 25,000 items and find the 8,000 that matched the color red right that is terribly inefficient and if I was on my fast computer connection that's great but unlike a mobile phone my mobile phone would probably catch on fire from that right so what they've done is they delegated all that we're to SharePoint so then that way the amount of data that comes across the water is just the data that matches the query instead of the whole data set so it's making your app faster and more efficient yeah for delegation so this is good so what if we though then say okay well we filtered that's fine but what if we go up here and we change this instead of you know what I would do a search I want to search the giant list for the text research latex red in the field color you know and I prescient use word color but that's how you do it so right away look I've got this blue line and this yellow triangle and what's real interest this is why I put this over here look at the count rose it's 247 which should make that bigger that's a small number that's a bad number we want to see it 24 all right we've got 247 items in my gallery so what happened here well the yellow thinks is a delegation warning the search post for me might not work correctly and large data sets so what happened behind the scenes was I typed in this query write power apps today SharePoint same want you to do a search sheriff was like nah baby nah I don't do that so instead what happened was SharePoint returned the first 500 results okay not the first five higher results that match this query literally ID item 1 2 item 500 sim Center now the browser right so download all 500 items and then once it had those 500 items then locally in my browser it went and processed those 500 items and found all the ones were the word read or the text read was in the column color and so that's where it found 247 matches whoa so but what happened to item number 501 225 thousand nothing literally nothing happens right those that data was completely ignored right if we scroll to the bottom here I get to the bottom we stopped at 500 which is just dumb luck but so item 501 through 25,000 they were completely ignored another really important thing to note right so when we were building the app we saw the yellow triangle right I really like this triangle white motion I don't know why I've been watching twitch football or something or triangle press whatever you don't care anyway so you see the yellow triangle and we sold them blue squiggly but as a user that is running your app what indication do I have that I have missing data here nothing nothing right your users do not know that they're not getting all their search results back so the entire onus of dealing with delegation falls on your shoulders because you're the one that will no no no because it's like wait a minute I just put something in the system and it's not showing up you're going complaints like that but the users don't don't have this so in power apps what determines whether or not a query is delegate is it really comes down for me I think there's three pieces there is the data source right so what data source you're using are you using SharePoint or using sequel or using CTS you're using some third other data source I'm not thinking of right so the data source is one piece of it secondly the function right so in this case you saw that like our simple filter was able to be delegated off to SharePoint but our search function wasn't right and so for example if we take away so search giant red color so if we jump over here to our sequel on CD s let's go to common data service so if we go over here and run that same thing we say hey we want to search giant's where red red is in the column color right there look at this you do boom we get all of them and if we were to sit here and scroll for long enough we would get to the bottom right let's do I suppose to scroll all right so let's scroll down here so there's two 18 remember this is the same exact data set oh there you go we're in the five 40s far enough so the function search can be delegate well it's dela the will to sequence delegate or common data service but it's not eligible to SharePoint so the function matters the third piece this puzzle is what we refer to as the operator so let's go back to our friend filter all right so if we go back to filter so filter giant right so SharePoint list we're animal equals zebra all right so this is a Delaware II and so we should get some results back here in a second boom-shaka-laka but if we then go in here what if we're doing and right people asked me to make sure I carry this so we can say and color equals red so still a delicate bowl query right so now only in the red hits and we're only getting a hundred at a time so we know that once again we could scroll the bottom and we're gonna get the whole dataset right because SharePoint is doing all the work so power apps doesn't care how many results you have they're just gonna work together to bring those back in small bits so that's this piece but would you have to be careful about as you add or as you go in here some operators are eligible some are not so for example how about instead of animal equals that what if we wanted to use the end what if we wanted to say zebra in animal right so that's you know that fuzzy sequel syntax but what happened the in operator is not delicate bore to SharePoint so now all the sudden we're only out 124 results back right because the only wouldn't process the first 500 so you have to watch out for your operators because now they took a delegate query which was filter and made it unde eligible or indelible or I don't know something and it's also I always always just I always used to say delegate able turns out eligible I've learned a new English word when I was writing the Microsoft documentation for this so anyway really weird scenario so so you have to watch out for your operator because now it took and broke a previously working function but what happens if we go let's do the same thing a scored a sequel and see I don't even remember I think sequel can do this though so if we go over here is a hey sequel I want to filter your table where this oh that's not the name of the column what's the name to call so there's no say or do 0 1 and we cross her fingers boom Shakalaka so in her filter is delegate all force equal so there's this like nasty matrices of you know what is and isn't eligible right it's really way I think of it right datasource function and then operator and that's kind of how you put the story together so you have to kind of go and I'll be honest I don't have all of them memorized right I really didn't remember in worked or not there I thought I did but I wasn't sure but the most important thing to understand is if you see the yellow triangle delegation is biting you in the butt so that's when you need to care and back alright well well this was a dumb way to write this anyway I really meant to say animal animal equals zebra oh my goodness typing is hard people I don't know and so there now we're right back into delegation and we're getting all of our data back boom right you see the hundred which tells us we're getting it now I keep telling you let's actually undo let's go back to our broken one alright so I keep telling you that this only process is the first five hundred but if you've ever looked at delegation for more than thirty seconds right what change changing there's a button you're right so if you go over here and click file and then you go to settings in the Advanced Settings this data delegation limit is set to 500 by default so you can change this all the way up to thousand is the maximum as soon as I change that you see the dots going my app is running you're gonna see we're gonna get well now we get four hundred sixty six because now we're processing item one to item two thousand but what about item 2001 to twenty five thousand they're still skipped so you can increase your delegation limits up to two thousand and so if you have a data set you know that you know fits in that's okay because sometimes that happens sometimes you're like you know what I'm never gonna have more than six hundred items in this list so I don't care that the queries not delegate almost up the limit to a thousand and then work through it that's okay right it's not ideal I prefer you found a way not to delegate but if you have you know a smaller data set you know it's never going to be any bigger then that's okay some of my customers we've done things like that than I put in my little checks and we'll talk about how to do that a second but we are putting a little checks to see if they actually exceeded that number they told me they would never exceed because you know what always happens when the customer tells me they're never going to exceed a number they see the number so so the big thing here is I want you to think about when you're looking at slow guy though is when you see this yellow triangle okay the way that I do this this is the reverse of the u.s. criminal system right it is guilty until proven innocent if you've got a yellow triangle in your app and I've reviewed hundreds of apps at this point if you've got yellow triangles in your app you need to tell me why you can't say oh I don't really feel like this way we're all answer that yellow triangle is only allowed to say after like oh yeah it's delegating but my datasets only got 17 items and I don't really care perfect I accept that if you're like yellow triangle eyes haven't gotten to it yet I'm gonna give you that mean book so keep that in mind yellow is bad another weird thing I guess we should talk about here so you can be like zebra in animal and how about we're just gonna do some way that's red in color oh look they fixed it okay so this is actually nose kind of so what it's doing here is it's saying hey I've got multiple things that are causing delegation a lot of times power apps has a hard time if you have multiple things causing delegation right because we know this in is also but it's not blue so typically speaking when I try to tell people is if your are seeing the blue you know work left to right because you might fix this one right because if I fix this one like all right fine I really meant to do animal equals zebra all right oh sorry animal equals zebra I haven't been drinking yet I can't type but see so then now it lights up more blue things so fix left to right because you might have more than one thing that are causing delegations and complex queries okay all right next important thing for you guys to understand here is let's look at another thing that happens sometimes with our friend filter so here we're like a animal equals that so instead another thing that comes up with sharepoint more than other data sources become can see it too is that the column type can matter so for example you know your SharePoint list has a created by column right so you can do hey I can do created by dot display name equals the glorious Shane Young and so this is going to filter all the data where the disappointing McCrea by was me which I created all the records that's why you're seeing all the records right that's a delegate will query yay created by claims oh the claims property is not delica below all right so it doesn't matter that's not my claim my claim is Shane to power apps nine one that doesn't matter what he's trying to tell me here is that this column or this field in that column this field is not delegate also you've got to be real careful when you start using SharePoint cuz complex columns they can cause additional delegation issues okay that was another one another thing that comes up a lot and this is not just SharePoint this is also any of your data sources but so if I go here I'm okay I want to do created by display name we know that my display name right let's just throw another label on here I said though another way oh not there wait delete let's try skin don't you hate we actually only add them to galleries I know I do so if we do user dot full name right we know this is going to show you my full name I hope there I am so what you'll typically do right I do some apps all times I mean I want to filter this data so the user only sees their own records so I'm going here and I'm going to do this womp-womp what happened well the blue showed up so it's not delegate so what do I do well the reason this is not double is because SharePoint does not know how to run the user full name function her point the hello I don't know do-it-yourself power apps so but what you can do here is you can just store this in a variable right so come back over here to Apple and start for example but hey set bar username and then is that right click and run your own start Boop come over here and so then now I can just say hey you I want you to be var username notice I over capitalized the s not on purpose but that's alright we know what I'm gonna tell time typing but so now I got the same data I'm now filtering by the logged in user but it's not or it is delegate also no warning so that's the what this thing I need you guys to work on right is to when you find delegation is there another way to do it okay that's the big thing I want you guys to be thinking about as you're working through this okay here my cheat sheet real quick so we talked about search not working with SharePoint right but one of the things you can do with SharePoint is it has a function called starts with so you can say I want to find out if the animal column or no how about the title column title is way down here if the title column starts with item title oh my goodness title 12 I don't know I'm just making up stuff boom boom so that is a way to do search like right so because search is a full text index thing right it goes I want to do all the portions of the field you're searching on but starts with at least let you get very similar functionality right because now I'm getting item title 112 121 21 out of SharePoint what's interesting is I don't think that function works with the other data sources let's go see I don't know so you click over here I guess I gotta do it like this we'll say starts with my key field starts with 0 0 0 alright which would really be all of them but I don't care close it close it and so it looks like Oh sequel does support it so guess where sequel used to not support that one see once again I don't have them all memorized I just come over here trying to do my thing and see whether I see a blue or not right that's the key all right where's if we go over here who knows let's try this one this is CTS it's gonna say filter that is not what I just did starts with animal starts with a Z oh yeah this one's gonna work too I think boom all right say yes starts with words normal man I swear it used not to you know doesn't matter the blue triangle wasn't there or blue triangle the yellow triangle wasn't there that's all that mattered okay so now it was kind of you know now we've talked about starch with and how you can use that to get some search right functionality so sell some other functions probably the biggest one I want to talk about notice here I kept doing count rows gallery wand at all items right so that's looking at my galleries all the items in the gallery and Kanyon number rows in there but count rows is not eligible so if I did this if I sit in or just do count rows how many you know items do I have in my SharePoint list well it's gonna tell me and what are we gonna get back ruining it mm right because it says a SharePoint count the rows and giant SharePoint says I don't know how to do that here's the first two thousand items power app said oh I can count those items 1 2 3 4 5 mm who knew right so this is one of those things that I will this is that circuit breaker I talked about so my apps for customers tells me hey instead of the 2000 we'll never have more than that what I do is I like have this function and then I will just throw like a warning triangle where is my triangle don't worry I'll just I'll just be here scrolling way down here all right so I'll throw a warning triangle on the screen and I'll be okay you're you're visible okay yes so then I'll say you know you're visible is equal to you know if that turns out to be equals mm then I know that you know we've ended up with too many items right so and then that way and then I'll have some text here says hey tell Darrel right it's one of my customers who did this for tell Darrell he got too many items in the list and you're not seeing all the data right literally says that coming Doric whatever so that's one of the ways I use this but we keep in mind count rows is not eligible it's only going to get the first 2,000 items so that's important another function that comes up a lot that is not delegate is the collect right so if I say would you quit collects but clerk elect collect they do the same thing if I say quitter collect test video collection that's a really long name and then I say just you know go get filter of the giant list where color equals red boom if I press that button now what's really tough here is that there is no indication that this is not a double query right there is no blue triangle I don't tell you the triangles never blue there's no yellow triangle either there's no warning that this is going to happen but if you use my old friend count rows you say hey count rows count where did I call that test video collection mm that seems suspicious to me right anytime it's mm like that I know that something bad happened so when you need to understand okay right you gotta put your thinking cap on like it took me like an hour like go watch fit or traces to make sure I understood exactly what happens here what's going to happen is this is going to be ran right this is eligible so it's going to say hey SharePoint go filter the records but what it's going to do is it's going to a pin to it a only returned the top mm so SharePoint went and looked through all 25,000 and it got what did we say earlier like 8,000 hits right it got 8,000 matches but power app said hey I only want the first 2,000 so only 2,000 of those 8,000 came back and went in his collection right so there was 6,000 more items where the cover equals red that sure boy knows about but power apps does not okay but you did not get a warning that this was not delica more so you've got to be super careful here when you're using collections and delegation and basically this is where I do this circuit breaker thing right if that collection has 2,000 items in it I bet something I bet I'm over the limit okay but where I was really confused for a long time is it if we went and looked at this collection all right we can do that we just click on this right here Boop all of these are red so it did filter the data so we didn't get item 1 to item 2000 and then just get the red ones out of that we looked at all 25,000 but when we got back the first 2000 matches hey this was hard for me to wrap my head around so now I will tell you that there are clever people in the internet who have made content and told you how to go and get the first 2,000 and then get the next 2,000 and your collection a collection can technically be as big as you want it right if I throw another button on here I'm like hey I want button where's that but now there is right I can say hey button I want you to collect into test video collection boom title equals dumb very very descriptive of my titles where is that button there it is if I press this right my collection will get bigger you can have right it's 2008 now you can it's not 2020 anyway you can have as many items as you want in collections so people have made videos to talk about how to go take 2000 then at 2000 more 2000 more until you got to all 8000 of the matches but remember if you have 8000 items in your collection that means your browser has 8000 items holding in memory right you complain about your machine running out of RAM all the time having collections of 8000 items is not a good idea and it might work great I have the world's largest supercomputer here 2000 gigs of RAM right this computer is awesome it can deal with it what about Suzy down the hall who's using a burner phone to run my app imagine if I tell her a little burner phone to collect 8000 items over a 3G connection right we're going to burn up a cell tower we're going to burn up your phone right we're probably going to break things and cause fires I don't want to Fyers so do not do the thing where you make collections that become giant okay all right so other things I want you guys to know a couple of little quick tips so remember if you're using these other premium data sources sequel or the common data service which I've been doing a lot with common data service lately right it's big keeps moving up my list of things I like both of them have the ability to do views so I could build a common data service view that was already had some crazy complex filter applied to it so then that way I see all the expense reports where you know there is a trip or there there's the bar tab was included and Johnny did it and while on vacation right I I could build this really complex crazy filter that power apps can't do without delegation but I can go build that query into a sequel or CVS view and then just point my app to use those views and not have to make power apps do any of the filtering or surgery so keep that in mind it is possible to use views I think that was a great tip you know one of the things I did I was working on this video was I reached out to my friends on Twitter and said my friends on Twitter kind of gave me a whole bunch of ideas I'll post a link to that Twitter thread down below because I think there's a lot of neat tips there that I not covered in this video I'll be honest there's also a great video by a guy named Reza a friend of mine he did like a five-part series on delegation in different share point columns and kind of how to work around them and do that so I thought that was good but yeah I think I don't I feel like I've been talking too long so I'm gonna stop but that's that's at least your primer that guy get you guys going with delegation right so if you have delegation showing up in your app right you open up your app you're like hey there's that stupid yellow triangle or if you click on the little stethoscope and over here in your formulas you'll see the warning here right if you've got delegation remember it is guilty until proven innocent you've got to justify to me not really to me but to yourself why you're letting delegation stay in your app because I don't know what you guys with that triangle creeps me out so I got to get that out of and you know there's no there's no perfect answer fists right I think I'm Twitter people are hey make sure you tell us how to get work around all delegations there's no one answer right what you have to figure out is you know what is your data sets and you know so where's your data source what is your function you want to use and then what is your your operators what one of those is causing delegation and then figure out how to work with that particular one so sometimes we use collections sometimes we use views sometimes we realize that oh we didn't want to do that right distinct is another one that's not delegate ball count rows collect those are probably the three most common non-delegable ones that you have to figure out oh did I really want to do it that way and then you just have to figure out I figured out like seven times what do you do so I will also remind you guys right if you go over here power outs delegation so I'm a firm believer that you all should read this page it is not nearly as fun and as witty as I am I hope umm but if you read through this it's going to talk about and say all these same things I did but in a much more professional manner so be sure to check that out and then what's nice here is also you're going to see that you know you can go in here you can click on the common data service and it'll be ok down here you can see here are the different functions and are they'd eligible or not and it's notice like for example um the sort function is not delegate born option set is that a problem I don't know but but now you know so you can make sure you're kind of playing for and accounting for that in your solution so but I've never I don't try to memorize all these I just know I build my app I do my thing if I get the blue the blue oh the blue line or the yellow triangle then I deal with it right because and the reason that we have delegation right the reason it comes up so much is right it power apps is lazy it and chewy like to sleep on the couch all day and so it's always trying to push work off to someone else and so that's what's going to happen here so anyway check out these right so make sure you do that I'm going to link to the Twitter thread below there's a lot of great tips on there hopefully you're interacting with me on Twitter so you can provide your feedback too and also keep in mind that this keeps evolving so SharePoint you know keeps adding more functions that are delegate alright common datasource keeps adding more functions that are available so keep trying to make this a better and better story but it's a story that you've got to understand to be responsible power-ups okay I've been talking way too long so with that I'm gonna say thanks and have a great day before you go be sure to click on the subscribe button over here that way be notified when new videos come out if you need any help or you want work together whether your problem is big or small check us out at power apps 9-1-1 we do it all i ron't or if you're looking for more formal training offerings we have those that link to appear somewhere so check them out thanks and have a great day
