20 Functions to Master Before Becoming a Power Apps Master

so today what we're going to do is we're going to go over the 20 the powerapps functions that uh you really do need to master to wrap your mind around and start using in order to get to the point where you can master powerapps so these are the functions I like to run through today if we could um we'll probably at least go um at least an hour maybe two hours but I'd like to jump and touch on each of these um and if you're not familiar with all these so hopefully by the end you're going to feel like you've got uh a pretty good feeling for when you would use which of these functions in which situation right so let's go ahead and jump in I'm so glad that you guys can uh hear me now um okay so I've brought up powerapps here now whenever you write a sample app or or start doing any type of demoing and using powerapps functions you almost need a good uh set of data to work against right so what I'm going to do is I'm going to direct you guys attention to a video we probably did like three weeks ago and it's called powerapps beginner topics and uh what we did is we went out to that website uh macaroo and we got ourselves a comma separated value file I think we've got maybe a thousand records and uh I just wanted to demonstrate how to bring that into dataverse uh initially before we can start uh tackling a lot of these things because a lot of these functions do have to do with data so we want to make sure we have some good data in there to use to use these functions against okay so um here I'm on the home screen Within powerapps and um what I want to do is I'm going to bring up yet another tab here for powerapps and instead of going to the apps what I'm going to do is I'm going to go down to tables okay and what I want to do is import that student data that we had a few weeks ago okay so um if you click on tables this is the dataverse end of things within powerapps okay so we want to import now what I like to do even if it's from Excel I like to click on import data because I feel like I've got a lot more options here look at all these options of course the first one is Excel which we'll be using that there's all kinds of stuff we can import from okay so I'm going to click on Excel workbook okay then I am going to browse my OneDrive we have here at the very bottom um I do have a mock data that we created uh in that previous session I'm going to select that um Excel document select and let's see what happens so this is a wizard so we're going to have a few next buttons a few screens to step through so I'm going to click on next and um you know Excel the Excel data source is not a good data source we at least want to bring it into SharePoint dataverse or something like SQL Server a good relational database management system here okay so I remember uh we did have a student's worksheet in there and we formatted it as a table and called it students okay look at this here's all of our data okay so we can look at these things and see what we're about to import I'm going to go ahead and click on next which should take us to a similar type of screen where it's going to show us the data it's going to show us the columns and everything and what I want to do here is I can sort of transform this data so for example if I have this powerapps ID which is what powerapps likes to create for Excel data sources um I'm going to go ahead and let's see myself here okay very good um what you can do here is you can right click on these and say something like remove columns you can rename them you can change the data type so if you're going to do any of that type of stuff this is the screen to do it on and I'm okay with a lot of how it has things set up here okay so I'm going to go ahead and move forward I'm going to click next and we're going to load this data into a new table what's the new table name well I'm going to call it my students or my students because uh dataverse will try to create a plural version of it hey my students um okay so if we look around here we've got uh the source column the destination column so we got the city email first name gender we have an ID last name phone a state and zip I think that's a great first step um we've got all this data here I'm going to click on next okay refresh manually um now if you wanted it to refresh any data source that we have um that's added to that Excel spreadsheet over time uh we could set this up to refresh automatically okay we could do that this is just a One-Shot import that I want to do here so I'm just going to click on publish and of course creating objects within the dataverse it's going to take a few moments uh here and there to actually uh bring this stuff in here so I'm going to click on custom to see um my new table that is creating so I call it like my students and I don't see that yet so I might give it just a little bit more time and as I'm waiting I'm going to hit the refresh button here so as we're waiting I'm gonna keep an eye on the comments here um it looks like Andre's in here uh sounds like he's excited to be here uh data's King good morning everyone and um see okay very good so thanks for joining us uh today uh you know this morning today guys um and I am going to hit refresh and one more time and hopefully it's there okay see there we have my students now if I click on that table here is a question from Andre when should you consider switching from lists and I assume you're you're talking about a SharePoint list to dataverse um I would move over to dataverse as soon as you can if you have the licensing for it uh to use dataverse I would say use it right away right away now if a lot of your company data is in SharePoint list and that would be a huge undertaking and getting all those lists all that data over into dataverse um and training people on on how to use something else instead of just the their SharePoint sites well you might need to plan that out but absolutely I would recommend taking advantage of your premium licensing if you have it and start using dataverse as soon as possible all right so it did take a little while for this uh for these rows to come in here and you see down here at the bottom let me um move us down a little bit here it says 989 additional rows that are there that could be populated Okay so we have the data in there now I want to create a new powerapps application and I'm going to create it from blank so I'm going to click on create here blank app and we'll do a canvas app and I'll call this my students and I'll also say learning 20 functions okay here we go all right so let me go back over to our functions list here so the first thing that I'd like to go over is the IF function okay the IF function so there is um an app object here that we can look at and the app object has uh several properties that we can see here okay now one of them is the onstart screen the onstart property and you can just give it a name of a screen there okay now if you leave that blank it's always going to bring up the very first screen that's at the top of this I'm going to create a screen for myself new screen and I'll call this the boss screen okay it could be developer screen or something like that and then for screen one I'm going to bring over a little label and inside this label I'm going to use that app object and I'm going to say active screen dot name okay so it's going to dynamically pull in the name of the screen there okay now I'm going to select that um maybe give it a little bit of a nice background okay and I'm going to select that I'm going to copy go over to the Box screen paste there and you see it says boss screen and now what I would like to do for this app onstart is I'm going to use this if function okay so an IF function expects two or three parameters okay so I'm going to type f with two parentheses there now the very first parameter is expecting a true or false value okay so what I want to do is I want to see if it's me or not okay so I'll say user email that equals parent at Power Labs at us okay then I want the screen that it goes to be the boss screen okay I'm going to open this up a little bit okay so we have two or three parameters here for the if so this will this equal sign is what's called an operator it takes what's on the right side and the left side almost as parameters okay and it Compares those two things and it's going to return to true or false value okay and if it is true it's going to return this otherwise if that's not me I want it just to go to screen one okay so I'm going to save this is the first time I might save this so um publish now I'll bring up a new tab power apps over here and what I'm going to hope is that I I see my project there sometimes it takes a few moments for those new projects to show up now I'm just going to click on this Ellipsis these three dots and I'm going to hit play okay now it says screen one so it looks like we've got a little bit of an error here okay so um what should have happened here I don't know sometimes we gotta figure out when code goes wrong because um a lot of times code doesn't go right right a bigger screen I can't see that yeah okay so let me oh there you go look at that yeah look at that look at that so um now I wonder if I spelled everything powerlabs.us okay now what I can do is I can select this user email and it says text okay so let's just do some um testing and troubleshooting here and what I can do is move us to the bottom I'll make things a little bigger there what I want to do is I want to create a few more labels here so there's one here's another so for this first one what I want to do is a user with parentheses and I'll say email okay so during the power lab Zone there are some cases when I just don't type things in right I'm going to control C and you know over here in the app should be a exactly the same okay um so the boss screen should say boss screen and then um what mouse I'll do is I'll I'll look at this are these two things the same that's that's true all right so I'm gonna hit save and I'm going to republish sometimes there is a delay guys whenever you publish an app it might take a few minutes for a user if they were to go out right when you hit publish chances are they're not seeing the absolute latest version okay so I'll just give it a moment here and I'll go back out to yeah I actually that happened to me just a little while ago um I was working on some nasty code and not not near as hard as the stuff Luke was working on the other day but it was still nasty and I made some changes and I knew that it was right I'm look I'm like there's nothing wrong with this code and it would not take it it was redlining all over the place and then I left and came back in and it was just beautiful so it's just really funny how sometimes it just doesn't register you know when you're putting that stuff in yeah you know yeah so it's uh I'm gonna go ahead and click refresh and let's see if it uh oh look at this you're using an old version of the app hit refresh to the latest version okay so I'm gonna hit refresh yeah and that's you know the reason why that's happening is because we are literally working live over a cloud out we're working this is cloud development this is 100 Cloud development this is not um it's it's really an amazing thing that that we're doing here but that's one of the one of the consequences of that is we've gotta we've got to wait for a second sometimes yeah so I I was having a brain fart here guys it's it's not uh on start that's that's where you put your code that you want to have executed whenever the app starts up they created a start screen I thought I was on start screen so so thank you Sid that was a brain fart on on uh on me my bad so absolutely yeah that should be in fact Microsoft made a change it was probably maybe a year year and a half ago where you could not put a navigate function you cannot use navigate function within onstart so they created the start screen and for some reason I thought I was using the start screen so I'm going to go ahead and hit uh save it's early it is early I don't think my caffeine is kicked in just yet back here I'm gonna hit refresh there is a newer version okay okay there we go the boss screen Okay so going back to our function list so f is a really good function to really Master if you want to master the powerapps platform okay so there as these functions I've listed off there are some that have to do with each other okay so switch is another way of uh doing what's called branching which is what if does so if if this condition is true or false then do this otherwise go do this and it's a way of branching code okay so switch allows you to do the same thing but if you have let's say you could have nested ifs or you've got a lot of different conditions to check not just one but several then you might want to use the switch function okay yeah I just want to I just want to say you guys that if it's not it's important it's you have to do it there's no programming without conditional branching you've got to make conditions so that is like the number one thing to learn is that if if function it really is I can't do it yeah so it looks like uh Amber's trying to get in let me make sure so I'm going to share this link here I want to share so right now just the accelerator students are able to come on as panelists so um here is the link to that so the accelerator students would have access to to actually view that link there um so Amber yeah please click on that link and there's a link to join the live stream okay so very good it'd be great to have you on okay so let's go ahead and move over to switch okay so while we're looking at this we might have something like an app admin screen okay so I'll create a new one here and I will there you go MRI I see gone if you actually want to be brought on to the uh the stream and be able to talk just show your video and I'll I'll bring you on okay all right very good so uh this one here I'm going to call this app admin screen and then what I want to do in the app start screen okay now what we could do is do well if email email equals okay then what we could do is uh say app admin screen then do this screen one okay so that's a way if you have multiple things to you can Nest your if statements okay um so probably a better way of doing this I'm going to copy that and have it down here I want to delete your code that you spent uh so much time to create right so I'm going to go up here and I'm going to use switch okay and this the first parameter switch is what we're what is going to be compared okay so we're just going to look at the user email do you think when you get done looking at the switch could we go back and look at that nest and let's talk about that nest with the if let's slow that down and talk about that break it down a little bit you think that might be good for these guys I think yeah absolutely yeah let's do it yeah um yeah just let me finish the switch here so we've got um we're going to look at that user email now if it is Darren okay then we want the screen plus screen now string literal should be in double quotes so make sure I get some double quotes in here and that's another topic string literals strings and string literals we are going to be doing so much work with strings at some point yeah so I'm going to hover over this red screen switch is unknown or supported function um it probably would help if I spelled it right right yeah yeah very good all right so if it is Darren's going to go here if it's Kurt it's going to go at app admin otherwise this is like the else if none of these other conditions are met and compared to these uh this email then I'm just going to go to the screen one okay so now I'm going to save this and I'll publish it should work exactly the same way now new version app is coming we'll let you know when it's available okay well this is an improvement as Microsoft is adding and improving power apps this is the great a great thing that they're actually doing this yeah um yeah because I've had to implement little features in there like little version numbers at the bottom if they don't see the latest version and they know they don't have it so it seems to be working the same way um so um so we've got that okay so any questions on switch before we go back to the IF function and talk about nesting and all that type of stuff yeah you know I would like to talk a little bit about good programming practices within that switch I don't know if this is a good place to do it or not I don't want to confuse people within it um yeah yeah it just takes like a minute or two I say we jump into it go ahead yeah well especially when it comes to switch you know in every language I don't care what language we're working with you've got some sort version of it you've got it on case or or in this case it's a Swiss where you've got a selection of a list of conditional things that you could go down you know like like Darren just showed right there so one of the things that I was taught early on is to be in total control of your programs and so that else that when he has that otherwise you'll notice he's got three options there he's got screen that the first option on the switch is Darren else it's going to be Kurt and then you've got uh otherwise in other words if there's anything else if nothing else fits go there well then when you have that that's that that's a hint that you don't have control of your program because that means that something else could be going in there that is not part of that that condition so we should never in my world this is Kurt's world and we should never use the else in that there should not be another uh we should make a range in other words before you go into that switch you have a condition before you even go into the switch to make sure that you're within a range that's good don't even go in there unless you have the the items you want in other words in this case it would be if it's not Darren or Kurt you don't even go in there don't even try to go in there you know because we don't want to have a a situation where it might be somebody else so that's that's just something I kind of remember and think about when you're writing your programs have total control of your system you know I'm trying to think of a good example here this might be a good example so this switch statement you might have dozens or you know more different switch comparisons in here so something I remember occur you taught me is well let's let's go ahead and check to see if this little user email has anything in it there beautiful that's perfect so we could say is blank make sure it's not empty before you even go right in there and we'll say like the bang operator or we could say not yep okay now now you guys but he's saying not there's two different versions you you can use in in some cases like power apps they're trying to help you make it so that whatever you're used to dealing with so that's why you have two different ways of saying not if you can use the bang the bang operator in some of the old programming languages that's that's the representation of not or if you come from like a Visual Basic or an Excel background that not you spell it out you know so that's that's what's going on there but I just created a screen called something went wrong and I'm going to copy the name of that screen okay so as long as they have an email that's returned then it's going to go ahead and look at that otherwise I'm just going to send them over to something went wrong screen look at that that's beautiful you like that that's a good slick programming practice right there according to Curt you just you could have saved yourself quite a bit of processing there just to check just to sort of validate like should we even be going through all these switch evaluations so you guys pick up on that people pay big money for this and there's one other thing about that you could be writing this program for General Motors I like to use General Motors because they they just got they're huge right so you you might be processing 5 million records you know you might not just three or four records so think about that if you're going if your program is going through five million records and you're going through a bunch of stuff that's not in there well now you're you're using a lot of computer time that you don't need to be using and I know computers are fast and I know processes are fast but so are so is the world the world's getting bigger and fast so you need all that time you need that them them processing slices so don't waste don't waste processor time don't waste processor resources on bad data right and I have a question in that situation so I mean you could you could you shorten that down to where they would just look as like for instance you know General Motors or whatever that it had that you know at generalmotors.com and that's the only way you could get into the app yeah to keep it from keep other Outsiders from being able to use it is that where you're kind of going with that or oh no no what I was talking about was like okay so so let's just say you're you're processing these two people and against five million records let's just say that there's some kind of input maybe we're not doing an email or maybe we're sending out emails I don't know um but we're processing again that against five million maybe we're checking 5 million records within that maybe we have to do something maybe we're not just sending out an email maybe we're actually looking at the data and we're updating data tables before we send the email out you know I you know I'm just trying to let you guys know that you might be writing a program that could be hitting that database a lot and and if you don't if you've got um a lot of erroneous data in there then you're wasting a lot of resources kind of what I'm trying to say in that but you're right in your case and that's another way you know that's your your thinking is exactly right Lou you want to you want to narrow down your scope so that you don't even touch those 5 million records you're just gonna talk about Darren or Kurt or you're or you're not going to go into General Motors unless you're part of that operation you know so that's right and that Nick says over the private chat uh that's very good Kurt yeah so we we do need to keep moving on we have uh 18 other functions to cover and we're already 30 minutes in so um I would like to sort of move more um now Kurt maybe what we can do is we have a whole video whole session on let's just call it branching where we like really delve into because I think there was more stuff you want to talk about the if and nesting and we could sit here and probably talk at least a whole hour on this but um I wanted to keep this sort of light we touch on each one so people are exposed to all 20 of these functions yeah and like they're at least on their road to uh to getting power apps down you know yeah good call good call yeah all right so let's go back and look at our functionalists we've talked about if we talked about switch what about navigate well chances are you guys have probably already uh have started to use navigate if you do a search for darrenise navigate I've got a really good video on that but here is something that we might want to do here in this application as we've been building this um is if I go to the boss screen perhaps I might want to have a button that goes back to you navigate as the name of the screen so screen one okay so the navigate function takes uh three parameters okay now the first one is required that's the name of the screen that you want to navigate to now if the screen does have spaces and really your screens should have spaces because it needs to be very human readable especially readable by um you know uh screen readers okay so if you don't have spaces in there it's going to have a hard time understanding how to pronounce pronounce uh those words so um so you see we have app admin screen something went wrong they've got spaces in there you'll need to put single quotes around them okay if I go here to app admin he's got the single quotes in there so I'm gonna hit the back um and of course the the undo doesn't work very well does it I'm gonna try to redo okay and uh type in here screen one you know that undo I've done that a lot I'll hit that undo and it won't do anything and it's like what are you doing but if you if you're patient a lot of times it'll actually undo it it just takes time it's got I don't know it's like it's slow sometimes you know yeah yeah in this case I went from screen one to the app admin and then I hit back in order just to go back to screen one but it went like back twice so um I don't think I hit it twice so yeah it's just a little frustrating so if I were to click on this button it's going to take me back okay and then of course something you should always learn while talking about the navigate um it's a very simple function which is back that will navigate back so I'm gonna double click on this button and say back so it's properly labeled there look how hard this is guys it's just so hard isn't it I I mean that in the old days that would have taken eight hours [Laughter] back and that works okay now well since we're talking about the navigate and we want you guys to master these functions so um the first parameter is required that's the name of the screen the second parameter is the transition now you used to be my favorite one is just fade I thought I always thought that looked nice but look at this here is screen transition.fade now used to be um and it looks like uh someone here in the chest has lost can you guys still hear me okay just want to make sure yeah yeah here you go okay very good so um I'm if I just leave that as fade it's it's like I don't know what that is you have to give that enumerated value or a prequel or fully qualify what it is you're trying to say screen transition dot fade and that's another topic we'll have to bring up for a while is enumerated values yeah you guys are getting confused by that we're going to talk about that yeah and then uh Chris uh buffer here I'm looking for you Chris and the panelists I don't see us all I'm just going to answer your questions here on the screen how does back differ from navigate so navigate will take you to a specific screen that you name and back will always take you back one one screen so that's the difference sort of like going forward and going back in a way so it's like you're it's like your little left carrot Arrow um on your phone right where you can it just goes back one level right yeah the backwards using Android yeah yeah absolutely so Chris did that answer your question okay if not please um maybe clarify uh how we could uh answer that better there okay so um so going back here so we got fade now if I click off of this it just says fade but if I click in here um it puts that screen transition.fade and then the third parameter is almost like doing an update context for the screen you're going to so you you have these early braces there where you can specify these values all these um local variables for that new screen okay so you could say um okay we're going to screen one and I could say Loke this is gonna be a local variable is uh what is boss and I'll set that to true okay so there's a lot there's a lot more to navigate than just having it go to a particular screen so if I go over here to screen and um I'll make a copy of this label here and I'll say is a boss and I'll splice in local variable is boss because on some other screen we have a navigate and we name off a local variable that actually creates that local variable here on this screen isn't that interesting okay so right here it says um I'm not a boss but if I save this over here to boss and I were to click on this oh it is true because I came from that other screen and it set that up to be true uh and data's King says back remembers how many screens back I I do not have that answer um I think so I think it is recursive I think it's recursive how many times I think he's asking how many screens back so if we have 100 screens and we go from screen one two three four five it would go all the way a hundred if each screen has a back button will it take us all the way back to one it seems like it does it see when I when I tried that when I was trying to learn how to do that I did that I believe it is recursive which means it no it remembers where it came from so it's going to go back to back to back to back yeah um it's easy enough to test it though you know well what I found here is when you are just clicking around the different screens that isn't necessarily keeping track of what screen you came from last okay so if I go here I click on this button to go to screen one right okay right now if I click on yeah go ahead but when you use the navigate I think it does um when you're using navigate I think it does keep track of that though but yeah I don't think so when you're clicking around I don't think it's going to so if I click back right now and I was on the third and the fourth screen it took me back to the second screen okay so if you just click around it's not going to keep track of that but if I well let's add a let's add another button here say home okay this one will be you can say app admin of course do you see what happened I hit Tab and it blinked everything I'm gonna control Z hopefully I get all my stuff back nope control Z is not working isn't that fun foreign let's try this again so I give it a space before and after and I'll say app admin screen keywords man I guess they changed it again to uh whatever I don't need to pass parameters I just wanted to go the next screen that's fine app admin yeah and then um bring these two buttons right here and this one will go and start here I'll run it I'll go home and I'll go back I'm gonna go to app admin and then I'll go to this wrong screen so I've gone to all of them so I'll get back back I don't have a back screen here that's what's great about having a navigational system so you're not copying and pasting random buttons all over the place to get around right right now my test now my test if you guys were um wondering about that side is if let's say you have four levels deep of screens screen one went to screen two that went to screen three to went to screen four if you hit back it'll remember that going backwards too it'll it'll take that all the way deep all the way back just by hitting the back yeah and Chris says amazing very good okay so let's go back to our function list so we've done navigate and now that we're getting into these other functions I'd almost like to pull up some data here and um let's go I'm just going to create a new screen so I've got something new to work with and I'll just call this data B1 create a new button here double click on that button and type in data navigate that is not a good screen name we should probably call it data screen and someone might be asking okay now that you renamed that screen and you go back home in this little data button did it rename it and it did that's the beauty of powerapps or sometimes you don't get that in traditional uh the software development of the languages you know that's so nice too because when you start doing complex programs and you've got that in about 40 or 50 different places that's nice you don't miss it then yeah so here on this screen here it's the fifth screen data screen I want to make sure I've got some data in here so let's add data and it's going to be um the one that we imported and I think I called it my um customers or my students something along those lines if I'm not mistaken uh let's see here yeah my students there we go it's going to pull that in we all know that galleries are a lot better than data tables right what I find interesting is this data table has been out there for a long time and still under preview it's still on the preview uh is Gmail still in it's beta I remember Gino was in its beta for like years um I always go with blank ones uh typically the the vertical ones the ones that go down Okay so let's pull in some data here and I'm going to use up my students data okay of course we don't have any controls yet so I'm going to maximize this uh or almost maximize it now the reason why you'd like to go down that uh you use the blank one is because if you don't it use it it populates it with a lot of stuff right and you want to just say look I don't want to be messing with all that taking all that out to put my own stuff in right yeah so if I said uh Gallery vertical gallery yeah see how it puts that stuff in there so let's say well I don't need an image in my gallery and you take that out okay and it's like well I don't want this or I don't want that right um now they just recently changed it but okay so here's a perfect example why I don't like to do that okay now we have an error what what do I do here you know so if you're a beginner trying to bring over a gallery and you don't want certain things so now you're like more lost rather than if you had just started with a blank slate here and it gives you directions add an item so if you click on this add an item boom there is what's in the name column okay I'm looking at the the private chat over here from Amber Darren you want to use responsive screen design in this if so it's going to be looking lit fire so we did Cover responsive design uh last Saturday with Matthew devaney uh if you want to go uh check that out guys but yeah I'm not going to try to mix in responsive design uh here with what I'm doing do you guys hear any Thunder uh over my mic at all there is an absolute horrible storm going on right now just wondering if you guys are experiencing that yeah um just making sure it's not over overtaking my microphone here but yeah it's pretty loud Okay so and you notice here on the gallery uh you can always change everything on what's being repeated in the gallery with um what you change here in the very first one okay so a lot of times I do like to give a little border to these labels here inside the gallery so I can see what's going on more I think this Gallery object is the biggest magic for for powerapps out of all of it you know this is this is amazing yeah you can't live without that yeah it's fantastic all right so what I'm going to do is I'm going to have um I don't really care about the ID but I am going to say first name and I'm going to make this 250 and say this item dot last name same we got people's names in there I'm gonna copy and paste what other type of stuff do we have in here we do have their um phone yeah we've got their phone that may not be uh need to be so wide so let's do something like 200 there I think and uh let's do a copy and paste or other type of good stuff do we have there that around okay let's go look at our data just momentarily I don't want to know when it's created on a city email first name gender last name phone state and I think we've got ZIP down there those are all the columns that uh we sort of created there okay so I want to move this up on my other screen so I can see it okay let's do email and uh email probably needs to be a little bit longer okay sometimes people's emails are really long um let's also go throw in um gender okay there we go um and phone got the email maybe we'll add in um that might be good for now okay so we can actually look we have some good data here and if we add in let's say a label here and um how many records do we have in here so we could say count rows and I suppose we only have zero rows does that seem right to you guys a little odd my students count Rose okay that's a little strange um it's currency um let's all just leave that there for a second and see if that updates hopefully soon um otherwise if I just go in here and refresh let's see what that does for me if that helps us out at all okay so let's look at the functions here so text um let's look at text so text will take a value and turn it into text okay so um if you have a number and you want to treat it as if it's a string you can use the text function to do that okay so um another thing that you can do is you can change the format so sometimes uh you might think it's a format a function it can be used as that so if I take a look at created on and there is a date time string or a date time value and if we say text we're going to do is we're going to convert that to a string now visually it really didn't do anything to it okay but what we could do um is use a second parameter which is the format so if we all we don't we're not interested in the time we're only interested in the year we could say or lives that will give us the year and we could do our own little format here so maybe a dash month month Dash okay we could do that um and of course that data was just imported earlier today um let's let's see some uh variant information here because it's all the same for the the date let's say our hour minute minute second second okay then there's also a millisecond that you can use with the FF doesn't look like it's giving us anything there and again you guys this is a whole another topic um this text function is so important and it's so big that we could talk for maybe two weeks on this you know this is a really big topic here yep yeah um so that's go ahead I was gonna say you are you going to show them some of the time format stuff that you're going to date formats you can use on that the enumerated uh features sure which one yeah which one do you want to use here well you could use like you could you could talk about the short um the short date you know the date time format okay short date yeah let's see how you got date time format you got short date short top date time all those yeah short date time you see that it actually brings in the uh Am Pm yeah thing yeah um so something that um if you liked what I had in there before maybe you had your own format so let's say our hour minute minute second second if you say Am Pm it should actually say either a.m p.m for you I never knew that that's good stuff yeah that's kind of cool so any questions about the the text function um I mean we could look up the documentation and talk about all the different variations that you can have in text but if you want to format text a certain way or what's called typecasting and other programming languages so you want to take one type say a number turn it into a string or to text yeah use that text function what's the opposite from text let's say you've got a phone number and you just want the numeric values out of it does anybody know the the function to use for that I don't have it on my list here but it is related to that one okay so that is value so I wonder what were to happen if I were to say value to this okay it doesn't like it because it has all those other uh yeah too much stuff too much stuff yeah so um if we did have a number here um without that now I could do substitute and substitute all those little the plus signs of the parentheses and and everything out and then uh use value on that if I wanted to but uh yeah value is the way to turn the string back into a numeric value okay right so moving on here so we got text what about split you might know what split does that's one that I always have trouble with I'm glad you're going to be talking about this one yeah so let's say you've got a bunch of um text string and you want to split things out and um let's say you might want to create an array based on that okay so for example I'm looking at this email and I don't care about the their domain I just want to grab their username okay so typically people's usernames when they log on to a network it's their email address okay or the part right before the at sign okay yeah so let me go in here and actually utilize this function so here's an email and I want to split this thing up we're going to split it up the very first parameter is the text that you want to split up second parameter is the separator well I want to separate it based on an at sign okay now we're going to get an error here because a label is going to display uh one string that's what it's expecting and pretty much we gave it a you see this data type a table it's giving us a table value a single column table um now what I'm interested in so it's going to give us let's say my email address Darren at powerlabs.us okay and it's probably not even gonna have that at sign in the middle it's gonna be Darren and powerlabs.us will be the two items within that table and I'm only interested in that first part well how do we get at the first part we can just use the function first give me the first item out of that see if if that works at all okay so what that's going to do is going to give us the very first record out of that table and now that we have that first record we say dot value so a lot of times when power apps gives you a single column table that column name will be value sometimes result okay so there you go we got all their usernames there now if we wanted the the other one we could have said last let's say we're only interested in like what organization or what website they're coming from we could have done that we could have just got their their domain name and that's sort of cool there and then also what we could do we could use um a function uh called launch it's a it's a little similar to navigate but it allows you to launch to something outside the the application so this is maybe a cool little exercise on the click event of this thing on the on select what we could do is say launch and we could launch out to self dot text I wonder if that would work out if you want to make it look like a link what you're going to do is say blue okay go so now if I run it I want to make sure I save it just in case that opens up in the current window there uh click on it look at that Studio Express nice very nice Isn't that cool that's some stuff people would pay big money for right there I think that's good stuff man yeah so I'm keeping an eye on the comments here yeah data's King says splits a string uh Andre says I I use split lots and power automate for splitting and reformatting dates okay very good yeah in what instance have you used power automate from powerapps oh this is a great topic um that was covered this last Thursday um in the accelerator program so we had a whole two hour crash course on a power automate from our very own Mary that was with the uh the accelerator students that we did yeah from the accelerator program yeah yeah I might actually uh release like a slim down edited version of that just to give people a taste for for what we did there on Thursday but um the main reasons I use power automate is when I I can't do something in powerapps so if I need something kicked off in the middle of the night nobody's in my app to click on a button you know fire something off or some event that I can respond to um you almost have to use a power automate flow to do that also if you want to create files um you know and work with uh OneDrive create files and and process things and um it's great for that as well so just keeping an eye on on everything here you know back back to the functions that we're working on here today too uh for for you uh Power accelerator students um we have we have an overview we have three videos out right now an overview where we go into the a lot of the details of of many of these functions and we're going to be creating more detailed videos on each and every function that we have in powerapps so you know this today today is just kind of an overview of things it's kind of like just getting you guys um you know what I want to call exposed to these these these functions and we'll talk a little bit about it but for you power accelerator students we're going to be getting very deep into the Weeds on these functions why how are you how you use these things you know so just wanted to uh take a moment to talk about that yeah so that's how you split things out and then um concat actually does the opposite okay so it takes uh several things and turns it into a string so for example if I were to have a label on here um and maybe it would be a long label okay so we could do something in here like uh we want to display maybe the first 20 people's names in there just to let uh people know I don't maybe there's a reason to to do that but you could take a table of value so let me let me go ahead and type in that concat okay and we want to grab okay here's a gallery one I'm just going to use that gallery gallery one dot all items okay um and perhaps we only want to use the first 20 well there's a function called first in that interesting first in and we'll say we'll just give me the first 20 items okay that's how you would do that okay so we're going to go through those items and then the expression will be what do we want to do with this stuff okay well um I'm looking for first name we have a first name in the data source and um let's go ahead and splice in last name in I can tell you what would be a good example of doing this let's say you're going to send an email to the first 20 people okay yeah email and then we want to concatenate a little semicolon well if you have a bunch of email addresses separated by semicolons then um you know an email a provider a service will understand okay this is a these are separated emails so we need to send it off to all these people uh between the the semicolons there okay so concatening concatenate all those together separated with um semicolons and there they all are you know I I distinctly remember this um problem happening about a week ago with somebody yeah where they were trying to splice names in to an email Nick might remember that too I think this is a way to have this is a way to handle that yeah good now if you if you're looking at this output and you're like uh well I just want one line at a time you know if I want to do something instead of those semicolons well there is a function called Char and you can give it an ASCII character here of 13 and it's going to give you a little carriage return look at that isn't that neat so there's an ASCII code for if you just do a Google search on ASCII codes so there's an ASCII code for pretty much anything you can type on the keyboard okay um and 13 just happens to be a carriage return I think 10 is a character turned in 13 is a line feed if I'm not mistaken no uh 10 was the Line Feed and 13 is the carriage return that's right you were right and and I wanna what I I guess I just want to explain that just a little bit um flush that out just a little bit so so so people that get confused with that so whatever you know computers operate on numeric values right so but we work off of in the text we work off of you know letters and so what they've done is they've they've represented each letter with and special characters like semicolons um with with a numeric value so that the computer can recognize that and you can actually use that for uh every for different languages as well you know so the computer represents that in numbers so it like he just said the carriage return is 13. they gave it a the uh they gave that a numeric value so the computer recognizes when you give it a value of 13 that's going to be a carriage return or if you like if you type in the capital uh cap 65 if you put in the letter 65 in there it's going to give you a capital a if you subtract 32 from that it's going to give you a lowercase a and so it's it's it's really it's um it's a great way to manipulate or ordinal data you know with that ASCII and again that's something that's a it's a big long discussion we can have some some time down the road but um it's an important one for you guys to remember what's really happening underneath the surface of these these apps when you start putting those things in there you know especially when you're working with strings yeah all right so um let's move on to uh we have collections and um patch collected clear to collect all kinds of stuff to to cover now the first thing I want to do is cover patch just real quick here that patch now I have a whole video on this um if you do a search for Danny's patch it'll come right up um and I take 30 minutes on the patch function I think it's one of the most important functions to master um so you guys are probably already familiar with this but I'm going to go over here so I'll say patch and then we've got my students okay so the very first parameter is expecting that data source okay the second parameter will be a record that we want to patch well let's just create a new record and we do that by using a function called defaults and the parameter that you give defaults is the name of the data source so again we're going to have my students in there so it's going to go out to the data store and say hey give me all the default values for a new record so that's essentially what it's doing and then as the third parameter for the patch we're going to name off all the The Columns that we want to specify and throw in the values for those okay so let's say uh first name let's create a um John and we'll say last name oh do that and let's go ahead and run it and see if that works okay get out I don't see any red squigglies that's good so the question is is it there easy way we could do that is we can go over here to edit data and let me do a search for filter by um hey there's John where's the where's the last name there's John Doe so it did work okay very good so the patch is working now I wanted to touch on patch which is number nine before I talked about collect and clear collect Okay so what is a collection before we start talking about collecting clear collect we need to understand what a collection is it's a table that's in memory in your powerapps application that um isn't necessarily bound to a data source so right here we've got a data source in dataverse it's got the data there that's where the data resides and we can query it we can bring it over uh if we change something it changes the data source but a collection is we could get a whole collection ever of everyone and then we could actually patch that collection but that doesn't necessarily mean the data source is going to be updated it's an in-memory uh variable that you use okay so let me um create another button here any questions about this patch that I did here before I move on to collect and clear collect okay so um switcher's patch I'm going to say what now the only difference between collect and clear collect collectors that uh you're going to collect one or more things into a table into that collection at once okay now if you say clear collect it's going to do the same thing but it's going to clear it out or delete it before it starts adding it and then of course there is another function out there called clear and you give it a name of a collection it'll just clear it out here's collect and what I want to do here is um it and collect this a little bit like using a variable the very first parameter is the collection name so if this is the first time that you use collector in the application you're you're sort of defining that collection you know how you set first time you set you're creating a variable and you're defining it at the same time so you only start your collections with col okay and I'll call this new students okay so that's that now you can provide your own uh values here you could say um first name equals new test person and then for the last name new uh now if I double click on this collection it should show me what's in there you see it says data type table okay now if I hit that downward Chevron there's no data in there okay so if I were to just hold down the ALT key on my keyboard sort of runs the app right here where it's at I'm going to click on that collect once twice three times okay I'm gonna release my ALT key open up the code window and double click on that student that little variable there look at this new test person new last name so just inserted it three times for us what's great about using collections along with patch is that we could hatch or update the data source with those three records uh all at once okay so what I'm going to do is I'm going to use this patch on my copy and paste that patch button here and I'm going to say patch action this is a place where I always struggle with I could never get it to work right where I'm wanting to just patch in to a data source from The Collection what you're doing right here I always struggle with this one yeah I do see a question right here it says uh iTube hertzberg is there a limit to the number of Records in the collection um no so you do have a limit of bringing over 2 000 records from a data source so that is a hard and fast uh limit Now by default it's 500 within a project so if you go over here and look at settings for a project you'll notice here it's by default it's 500 you could set that at Max to 2 000 okay so sometimes people they want to work with more than 2 000 records at a time and what they'll do is they'll go do a query and maybe get the first two thousand records and then they'll have another uh thing or maybe an iteration of a loop or I'll go get the other 2 000 until they get they load up maybe all ten thousand I've never heard of a limit to the collection I think really what the limit is is how much memory uh do you have there that your browser can can utilize so that might be a good little test of like you just going and getting 2 000 records at a time uh or actually just starting to create your own collection now it also might have to do with how wide your collection is so if you've got a hundred different columns and you've got data for each of those columns um and if your limit is actually the memory You're Gonna Fill Up the memory faster the fatter or the more um wider the The Collection is with all with all the different columns yeah if you've got if you've got pictures in there that's going to go it's going to eat up some memory really fast yeah yeah absolutely um Okay so what I can do here what's what's what's so beautiful about the patch is I can patch that and then what I could do is I could just use that little collection that we have hold new students okay and what is it telling me here hasn't been valid arguments you cannot use a non-record value in this context um and I wonder if uh they have changed how this works this is this is where I always have trouble with right here yeah I hope you can fix it fix it Darren okay um and Andre says I'm assuming SQL is the most efficient solution to that um well SQL um is a a different topic that that maybe we'll we'll get into a different session uh I know I plan on having a whole series of stuff on Azure on creating a SQL Server database and actually jumping to start using some structured query language that's going to be really exciting but I'm really excited about that I'm real excited about that okay so um I'm used to this working so this collection there's an error in this from a try revising the formula and running it again okay well I'm wondering is if I click on this second one here do I still have the data there and I do okay um and I think maybe in dataverse it really is requiring the name you know how SharePoint has that column called title and always likes that title to be used I bet you're right I bet that's gonna do it I didn't think about that yeah so um I'm gonna put in here I think it's a numeric value in this one okay so what I'm going to do I'm going to want to see if I can do this do a Max on that um not the collection but I want to say my students and the column that I want to look at is the name um it doesn't seem to like that either so you know something we could do of course it does need a comma here maybe it actually would now whenever you see these double lines it's a delegation issue um and perhaps probably the better way to do it say well just give me a just give me a new grid you know quit there okay so see there's a grid so at least there's something in name and I don't have to worry about what it should be so now what I'm going to do is I'm going to make a copy of this and I'm going to call this here there's that collection yeah because the collect the collect uh function is just unrelenting if you use collect it's going to just put something in there every time and if you don't do it clear anywhere it's just going to keep adding to that thing yeah so now that we've got that now if I click on this I cleared it out and then I redid it so if I double click on this you see that we have something in name yeah and these are all unique of course the first and last name are are not unique okay so if I go back over here let's see uh what the issue is here okay and maybe powerapps has changed us either that or I'm I'm having a brain fart here being live here uh so I'm wanting to patch the data source and provide that now alternatively what you can do I'm going to comment this out just momentarily and what we could do is um now if you look at our function list we're eventually going to get over to where is for all it's number 15 okay so we're actually going to use for all right now okay if I get back to my my project here I'll do it for all now for all is a function you should Master because um if you ever need a loop through things this is the way that you Loop in powerapps is by using this for all okay what do you want to Loop through well we want to Loop through a table of values or an array of values and we do have this collection here okay each of those and I always like to use a little Alias for the for all the as my um and if you don't use that Alias by using the as keyword you can access the current record that you're going through repeatedly by using a keyword called This Record every once in a while I've got a problem with this record it's like it's it's um confused so what I do is I create my own little keyword my to get at that so the first parameter in the for all is what we're going to Loop through and the second parameter is what we're going to do each time around okay so we're going to use that patch function okay and the data source is my students and we want to have a new record so we'll say defaults then what do we want to put in there well we've got name what do we want to put a name well let's use our little Alias my um now I did forget to put curly braces in here so the third parameter for Patch always needs curly braces curly braces signify hey this is a record so we're gonna have name value pairs so like how we do in Json if you guys have ever worked with Json JavaScript object notation is what that stands for okay so name we want to say my DOT uh it's not good it's called my DOT name and it will hold a grid okay so then we got the first name we'll say my DOT first name last name I got last name okay that should work let's uh let's try this out batch it's gonna go through those three and put those in there okay now if I go in and try to edit this data and let's go do a search for um filter what was the name that we used there new test person let's make sure there's at least three records there they are all right so we've talked about collect clear collect we've also thrown in another function called clear you see patch okay and we just did an example of using for all so we're just sort of trucking right along now do you guys have any questions about anything that we've covered so far now we'll just keep trucking here all right so we got all that the next one is uh filter okay so let's say we only want to filter based on something okay so that's something might be let's bring over a uh drop down list now I'll go with what the data says here okay so for the data source for the items for this drop down I'm going to say distinct okay and uh let's look at the function list over here just real quick they're distinct well do we have a distinct here number 14. okay distinct will give us a single column table um of what what's in that that data source okay so here I'm going to say my students okay and then for the second parameter this is going to be the field name well the field name is uh either gender or sex there there we go gender now if I were to run this we've got a male female and a lot of others here okay so what we could do is we could allow the user to click on one of these and then filter the results down here based on that drop down list okay so I'm going to say DRP which is what we should prefix our drop down list with DRP and we'll call this uh gen genders okay so I am going to copy that on the clipboard and then here let's filter this thing down so we only want to look at ones that line up with what was selected in the drop down list okay um and I I am seeing an item what uh comment says what is a grid from Andre what is a grid a grid stands for globally unique identifier okay probably unique identifier so if you do a Google search on gwid um it'll come up and sort of explain what that is it's supposed to be unique a unique uh value there okay so um yeah so we're going to filter this and we're going to say uh gender equals drop down list dot selected text Dot value okay and let's see what comes up here okay so we'll run it and because we have mail selected we have all the males there we have female there's all the females oh yeah look at that but our little uh concat thing is is uh sort of dynamically moving around here so agender so we've got some good data to to filter on uh does anybody have any questions about how filtering works chances are if you if you've been writing any type of apps with with power apps you're probably very familiar with uh with Filter it's a it's a very um commonly used function um so the first parameter is always the data source the second parameter is the criteria yeah yeah all right so I'm going to go back over here so we just used distinct any any questions on distinct at all so we we cover distinct in a filter and then the next function should be lookup okay so let's do a lookup so we'll go back here now look up [Music] um we'll go and get a record now it can get a specific value out of that record so a record has got many columns or Fields right um so you go get a record and then um let's say there's two records that have the same criteria okay for example over here in this collection we created um three people with this new test person okay new test person I'm gonna copy that and then what I'm going to do I'm going to make this a little smaller here I'm going to insert a label look up say my students and the second one will be um first name okay that will give us the record itself but a label isn't going to display a record a label is going to display single value out of there okay so we need to provide a third parameter okay so in this case I want to look at the grid because that is something that I know is different between those uh but it's not good it's actually in the name field okay so look at this I'm going to expand this little label and what it did here was it when and looked at did a lookup based on this new test person and this is the grid this is the name value out of the ones that came back okay so if I were to go in here the lookup and the filter work very similarly copy that I could go down here in this uh gallery and I could use instead of look up I could change this lookup okay and we would say filter and we and filter doesn't need a third parameter there okay so you can see there's three of them here if you will put another little label on here and this one will say thank you okay we could do that so you see we've got this 30b so we got four records and it went and it brought back the lookup brought back the very first record uh even though these other three matched the lookup is always going to find the first record there we've used to look up any questions comments about lookup so let's take a look at search now I'm going to change the data source back search let's add over a text box typically we want to do a search you want to text box for people can um uh uh I do see a question here Nick Nick says uh can you add last to look up and um you could but it's not going to work the way that you want to um so for example so this went this lookup finds the very first record so if you say first or last there is a table of only one value there one record so if you say first or last there's only one in there so it's going to give you the same thing now if you want the last of things that match use filter okay that will give you them all and then you could say last out of that okay there's actually another function called index so if you know you get used first to get the first one or the the last for the last one you can use first n for the first so many or last in for the last so many or you can use index to get at a specific one out of a table yeah all right yeah the lookup kind of works like a filter but it's just a dynamic filter right like you're just wanting if you're if you're not sure where where it's at in there it's going to give you one one it's going to filter it down to one record just like the last we'll filter it down to one record but it's always going to be the last position first we'll always be the first position lookup will be a different position depending on where it's at in the in the table where it finds it at yeah yeah the very first one that comes up against yeah yeah so um let's look at search so let's add in a text input okay and what we'll do here we'll call this txt search I won't have any text input in there okay and what I want to do is the stuff around here and um let's search now we could do one of two things we can either search the filtered value based on that the gender drop down we could do that okay oh you just have the full thing the my students it really doesn't matter which one you use just realize that you can Nest them like I am right now so this is the data source the next thing is like okay what do you wanna what is it you want to search for whatever they type in the txt search text input not text that's what that's what we're looking for now the third parameter all the columns that you want to look in Okay so we've got first name and look at that it wants us to use this one that's included in double quotes it's got the cr833 okay why do they do that there you know why that is yeah so if it wants something inside of Double quotes like this because it can be like really dying it could be dynamic okay so you know the difference between sort and then you've got another function called sort by columns and the sort by comms allows you to sort on multiple columns in a certain way yeah sort by Collins expects a string of names for the for the columns that you want to sort on the same thing with search it wants a string of the column names uh and because of that it's gonna want if you're working with dataverse you're gonna have to include all this stuff or just pick it from the intellisense yeah okay so yeah typically if you if you need the column names in there and they they need to be in double quotes you're going to have to give it the fully qualified ugly name there okay yep okay good stuff I never knew why that was the case yeah and then you just keep on adding more and more columns here okay so we got last name oh look at that there's the there's the last name so we'll search for uh first and last name and um now because I have mail selected it's only going to look in males let's see if we have any Johns in here no Johns what about um let's go look at I know we have new test person let's just you say person let's see if that works okay um now they don't have any gender so that's gender hasn't been set so if I select a blank one it'll bring them up you see that um something that might be good for us to do is we could do something like this if there's anything in the txt search how do we check to see if there's something in it I use the function called Len for links okay if the length is greater than zero and yeah let's go ahead and do a search okay so it's all just have that otherwise let's just do what we were doing before which is this filter so now this is a really good example of of having total control of your program that's another good example of that yeah you're doing a search for John you know it's like just show me everybody you know um you don't want to have to search for John and then then select um you know you know other genders or whatever you probably just searching for anyone with the name John okay so let me do a thing here uh here we go there's John Doe and what about Darren here's a Darren okay so we are using search now whenever I show people search I always like to show them you know you're always going to use a little text input here you might want in the hint text say search first or last name just let them know what they can do in there and then also there's another property here called a clear button a clear button so if you turn that on and run this you start to type like I don't want that so instead of making them hit backspace backspace or select everything and hit delete they can just click on that X and takes it right out so we just use the search function yeah so um so Andre asks are delegation issues just to do with the 2000 record limit um that's typically what people are talking about when they're talking about the delegation issues now these functions sometimes um depending upon the data source are delegables some of them are not delegable okay so what my experience has been with dataverse when I do account rows with SharePoint it has to bring over the data and then count them and with dataverse what my experience is is that it can figure out what the count is even if it's over 2000 and will give me that higher number okay so just sort of keep that in mind is whenever you're using these functions and you're worried about delegation um is that the delegate how delegable a function is depends upon what the data source that you're working with okay um yeah so data's King says I wish I had a clear button for the drop down yeah I wish also if you like rounded um you know controls like this text box you know that looks nice right well what about for a drop down list why can I do a rounded corner for a drop down list maybe eventually they'll they'll do that as well right right so let's go back to our function list here so we got search what about sort sort's an easy one okay so if you look at our results set you notice they're not really sorted in any fashion right now so um what I like about sort is you see I've got an if statement here we do a search here we just do a filter well I like to put search on the very outward uh portion of all the nesting of functions when you're talking about a data source for something like a gallery okay so what I'll do is I'll sort and I love the sort functions very nice simple typically you're just want to sort on a single column sometimes you want to do more than one but um I'm just going to name off what column I want to sort on so in this case let's say last name and you notice that because I can just reference the the um as I want to sort on the column without double quotes well I don't have to mess with all this CR 80 33 stuff and keeping an eye on the comments here um Amer says we talk about that power apps can only deal with two thousand rows of data from a data source I guess we can add more than one data source in powerapps and use the data source on another screen so now we have four thousand rows is that possible just asking out there um that wouldn't be yeah you could do that you could um the thing is you you want to be able to bring back specifically what you're wanting to show or not show in that result set of two thousand uh amber so what's great about the collections is you don't have that so if you want to display typically I don't think that's a good type of uh user interface for the user to experience um very rarely if ever have I ever had a need to display more than 500 a thousand two thousand records on a screen all at once so a lot of times if people hear about that that limitation of powerapps like what can only handle two thousand records like I've gotten I've got 20 million records you know so whatever on powerapps right um and Amber says but ultimately will slow down the application yeah if you're working with thousands and thousands of Records um from the same data source phone it all into one collection or different data sources Yeah the more data you bring over and you load up in memory it's going to slow down your application yeah absolutely what you want to do is if you if you want to process a lot of data you know powerapps really wasn't designed or created to be a data processing engine I would uh if you're going to be doing a lot of that type of stuff um maybe a power automate flow but um if you're going to be working with lots of data I would recommend you something like SQL Server you can create a database object called a stored procedure and data base operations that happen on the SQL server side in a store procedure what happened uh much much more quickly than they could happen inside of a power automate flow so something to keep in mind there yeah it's important to remember that that powerapps is designed to be a front-end designing tool it's not meant to be like you said a mass data processor you know data's King says we also have a 500 row limit on drop downs we have over 600 employees okay that's good to know um and then also galleries will only load up 100 records at a time unless you can display more than 100 then it will try to load up more but um yeah just trying to be as sufficient as possible try to help you along um all right so we've done sort now what we could do there's another function called sort by columns okay so if you want to sort on let's say you've got a lot of people with last name of Smith well you might want to start sorting on their first name so right here last name it doesn't like last name there okay it wants to include it in double quotes there's last name if you want to sort of by first name there start typing first name and um hoping it was going to give me this thing right here so I'm going to copy the first name as well okay there we go so delegation is a big topic perhaps we could have a whole uh lesson or a whole series of lessons on delegation dealing with delegation um and designing your database in a way so you don't have any delegation issues okay so um so somebody here in the comments uh Elias I got a delegation with sort with no delegation warning I had to split the sword use the sort twice instead of combined yeah so if it's going to sort a result set and it doesn't have all the records to sort by then yeah um so for example we have 10 000 records of students out there in that database table and we try to sort descendingly we want to start with the Z's and it went and got the first 2000 records so it started with the A's so if you have hundreds of people with A's and B's that their name start with and then you try to sort it you're going to start with the B's or the C's or the B's first and then get down to the age you're not going to start with the Z's so yeah absolutely yeah you really need to understand at what point do these functions like sort of kick in and start working you know so some of these functions are delegable and some are not and it's it's it can change based on the data source okay all right so let's move on here um something I should mention about this sort it's it's a little important here um so if you I I just mentioned it actually so if you want the last name to be sorted be sending okay and you would actually type it here um yeah see it says column and then order and I think there's an enumerated value for that now I can't just say descending okay maybe it's called sort order dot descending something along those lines um the sort of um but yeah I'm used to just typing descending and it gives me that keep an eye on the comments here the way that I was able to show all of the employees I used to I use the check box to separate the drop down list of non-exempt employee from exempt employees ain't very good um yeah it's called sort order that ascending you could use these let's just see if this is trying to um force it to be the very last parameter not so yeah you should be I'm just looking at the documentation here guys I'm going to put the documentation link over here in the chat just in case you want to look at it yeah so there's my sword order right that a Cindy maybe he just wants me to type it all okay so it's giving me uh red squigglies here we go does not exist now what were to happen if I were to close down this project and reopen it it would probably work fine right unless you guys see something that I'm not seeing here otherwise I'm a little inclined just uh just to move on here so let me go back to just a regular sort and I'm gonna say uh last name okay see there there's working fine but it should also work on the the um sort by columns as well so collapse this up a little more here it's a little too come on here we go hang that up a little bit there we go yeah I agree with um as a commenter here the viewer delegation is not a big problem if you have some experience you can solve 99 of the problems um and and that's been my experience so let's say if we're looking at a big we're trying to connect to data and there's thousands upon thousands of rows um what I what I try to do is I try to slim things down so it's manageable there's no reason to display thousands and thousands of Records on the screen what you want to do is sort of pre-qualify what it is you want to see in a lot of cases um the stakeholders the the people that are specking out the application they only want to see the last 90 days worth of data they don't want to look at the 20 year old history of the data so if you Nest your filters in a way to the most delegable to the least delegable functions uh typically you can as as they said here you can solve 99 of the delegation issues by structuring and nesting those filter functions in a particular way okay so oh wow so also powerapps is still developing things and as they develop I'm noticing that they are becoming um they're they're bringing in uh they're creating more versatility Within These functions so that there's more delegate Bill delegability Within These functions right that's a hard word to say three times delegability yeah absolutely um so I'm looking through all these okay look up search sort distinct for all okay now we're in the the last stretch here what about group by hmm that's interesting group by um you guys ever worked with Group by before okay so what I want to do here is um I don't have a column for country but what I would like to do is um create a column for the country code so in a phone number if it's got like plus one I know that's for the United States so I'm going to use a culmination of a lot of these functions I'm going to create a new column and I'm going to populate it okay and what I'm going to do is I'm going to go over here and um I can close down some of these things here I don't know that I need all this stuff okay here is my table okay my students I'm going to create a uh a new column and I'm gonna call this country phone code a single line of text will be fine and I'll save it okay so that's going to be out there it's going to be a blank uh string for now and uh so this is the city maybe we can sort of make some of these things a little smaller here so we've got more space to work with here I'm gonna try to make my screen bigger so you guys can spread this um okay so for this label here what I want to do is get at this phone okay now what I want to get at is I don't want the Plus I just want the number right after the plus and before the space that's what I want to get at so um there are a few ways to do this okay so what I want to do is see some of them the codes are three and some of them are two so what I'm going to do is use some of these functions that were already worked with so let's do a split we'll do a split on space okay now I want to look at the first one okay and then I'm going to say dot value there we go but we don't want the plus sign so we could I do a lot of times is substitute okay so there's what we're working with from the get-go we're going to substitute any plus signs with an empty string and also do we have any spaces in here at all I think I'll put a little star and doesn't look like we have any spaces after the numbers so that's good otherwise we can do a trim and trim out any spaces some reading and trailing spaces okay now the use of these functions here that I just did does that all make sense is all that clear I didn't want to clip over that without fully explaining things you know right so we've got that now how do I put that into my database for that new column there okay well one thing that we could do is um we could create and now we've got we've got about a thousand records here okay now I could write a loop that will Loop through everything and update the um the code for each one okay we could do that um the more efficient way is to patch them all at once I try to do a patch The Collection before to remember this thing done here I commented out because it wasn't working what I want to do is I'm going to retry that and see if I can do it all in one shot okay so what I'm gonna do is [Music] um update country code let's make that a little bigger there okay so inside of this what I want to do is do a clear collect collection is going to be call temp students okay and then we'll go get my students all the students and put them there inside of that collection okay now what we could do we should probably refresh the data source right since we added um yeah since we added that new column I want to make sure that's in there so I'll do a refresh here so we haven't yet populated this now what I want to do is um I'll be one step The Next Step will be um for all and that's going to work on this collection here that we just populated and then inside for each record that we go through there we can patch and patching in memory collections is so much faster than trying to patch something that's actually tied to the data so it's sort of good that we're doing it this way here so we're going to patch I remember that for all I always use that little Alias as and get at a specific record I'm gonna patch collection it allows me to do that because um it may not allow me to patch something inside of a loop if we're using that item to loop at the top so I'll try and see if it works so we're going to patch that and then we're going to be using the my which should be that record there and then we've got uh some type of a country phone code okay got country phone code right here so let's copy that okay but instead of this item which is what you use inside of a gallery let's use my my phone okay now what is that wrong it cannot the function cannot operate on the same data source that is used in the for all okay so what we could do we have another collection has exact same data in there all the 10 students too and we don't have to go back to the database for that because it's already right here right okay so now we got two collections now inside the collection we'll actually update the second collection look it's gonna it's gonna allow us to do that now okay now we don't have any data in here just yet because it hasn't run yet and so we'll go through now now we updated the collection at the bottom of this what I'm hoping that we're going to be able to do is do a whole patch we're going to patch the my students and we're going to use the new data of this call temp student2 okay so this is the type of stuff that you might want to do or need to do if you need to do some uh you know massaging of the data or sort of clean up the data there might be some stuff like this that you might do inside of powerapps okay um Nick says Mego what does mega stand for I oh oh oh my eyes glaze over did I lose you guys at all okay let's recap okay that might help let's recap guys so what we did was inside of our our data source here for all the students obviously we have people in other countries okay so look at all these weird cities we have uh some of them are are spelled I don't even know if those are real names of cities or not okay but for the phone number we do have a phone number in here somewhere I'm just looking for a horizontal scroll bar or something where I can move around might have to do a refresh okay and now we do okay do you see that um we don't have no we uh we created this new column country phone code and we have a state but we don't have a country so let's say we're given this data and they didn't have any of the country codes well what we could do is we create this country phone code that will have will populate but what we want to do is we're going to grab the little country code and put it over there and then later on we could have a list of countries and all the country codes for those countries and have a one-to-many relationship there we could do that okay or we could add another column called the country and actually populate it based on that so that's what we're trying to do and then this is how we're doing it okay so I'm curious to see if this all works okay um and I might want to break this up into several steps just to make sure that things are working properly before we go through it all okay so I'm not going to do this patch at the end I want to make sure all these previous other steps are actually are working properly so the first thing is we're going to go to the data source get the my students drop it into that collection and we need a copy of it because we have a limitation within powerapps we can't update Something That We're looping through so we created a second one that has the exact same data we Loop through it and we make sure this country phone code is actually populated so we can use it later okay so let's go ahead and run this we'll hit the button let's see how long this takes oh that didn't take very long at all so here's a question we've got a little count label here oh it's the name of our there's new students it's time students okay so we have a thousand four okay oh my goodness put that out of the way copy paste and then in students too okay we should have the exact same amount of records and we do okay so now what we're gonna do in that wild it didn't take very much time to to actually execute that now the question is how long will it take to actually patch those a thousand records I'm gonna hit save I'm gonna run I'm gonna hit update country code so while that's running questions comments that is King says you want to master these functions you need to know the art of combining these functions together absolutely comments should be mandatory when doing this um I agree with that if you're going to be writing a lot of code there uh running comments for each step would be a great idea absolutely it's taking a while but this isn't something that you'll have the user do a lot this might be like a data maintenance screen or something that you do just to sort of manipulate the data to get it situated uh like a one-time type of thing and but there's a lot of times you're going to do these kinds of operations if you're doing this for very long because you're constantly one thing that we know is that data gets messed up a lot and you're always going to be cleaning data up you're going to have to do these kinds of operations to go through and and scrub your data or massage your data or shape your data there's all kinds of different um operations that are going to do these kinds of things yeah so the button came back which means okay it's done but these dots are still moving which makes me think it's still sort of maybe it's bringing the data back to refresh it I don't know but I know that I can go here and let me just refresh this and see if we've got some good um Country phone codes there now okay so let me use this scroll bar here now where we don't have a country phone code is these places where we didn't have a phone to begin with which are these new test records that were added but for everything else if I load up the 994 records we should have some good data here look at that all right isn't that nice right and that's an example of what I was talking about too you see all those blanks in there you may not want when you're doing um certain operations you might want to filter out all that blank stuff you know as you're working with your data so you know so um I wanted to make sure we got some good data for that because I want to do a group by yeah okay I want to do a group by um so let's um I think I might be inclined to duplicate this data screen so I might want to rip some things out and keep some others okay just as we move along so it doesn't get all uh really complicated here okay so let's take all that out and uh for here um I think I'm gonna just take all this out for now I'm Gonna Keep the my students Okay so we've got that okay so there's our data again and um Group by function so let's say we've got a listing of all of our students and they're in different countries let's say we want um them to be grouped or bunched up buying a particular column like the uh the country code that we just came up with right um and then within that they're sorted within those groupings Okay so let's say Group by okay parentheses so the source the source is the my students I'm going to indent that over now what column name do we want to group by okay well let's do that um country code um [Music] okay so that's the country code that we're gonna go by um and then the third one is the group name so the group name will be a group three code okay oh that should give us a data source that's been grouped now let's take a look at the the data we have here down below I need to zoom out to get a hold of this bottom here and bring it up and then Zoom back in okay so here's all the country codes and then all of them that match up that are grouped based on that country code is there a table of values wow wow okay so we might actually want to um let's say for this last one we'll say this item Dot entry own code okay so there's that and I'm um wondering if I could sort based on that sport based on the code okay I like that see you Lou So when you say it's a table of values that just mean um just the value field or does that mean all the fields that are in that record um I believe it has all of the the fields yeah and we'll take a look at that here in just a second here okay um okay so we want to sort like that and it looks like it did okay so yeah one's at the top so one's United States okay I like that so here we've got this item and we've got two columns we've got that country code and then we've got the table yeah okay so um so typically what you want to know is like the count the count of how many are in there so what I'm going to do is I'm going to take these labels out there are giving me um issues okay and what I want to do here is say uh I'm gonna see that table value it's like the country code I'm expecting to see that students let's see if it helped me out your account Rose nope I don't want to do it like that um but the column name and then the group name once both of those there this item entry code there we go now that gives us the table value by saying country code okay so you see we got 30 oh my goodness we got uh 37 people in the United States um you see all that yeah so I'm just wondering is somebody got slick with this um if they could cheat the delegation issues with that yeah would that be a good do you think that'd be a good deal for that give me an example um well okay so for I I I'm just this is the first time I've really seen it work like this so this is kind of neat for me um but I'm just thinking let's say you have you have 5 000 records and you got to start doing if you did a group by then you now you've got individual tables within that now so if if you could do a count and then do another Nest or not a count but if you can if you can say do a look up for the group and then inside the group do I look up within that group well now you're not have you don't have 5 000 records you're working with now now you're working with 40 groups maybe you know you take that 5 000 records and you broke it down to 40 different groups and you work within that I did does that make sense what I'm saying to you yeah yeah it does okay I um I don't think you can get around delegation issues with that okay um but I am going to look it up just real quick just in case um I'm wrong on that otherwise people would be using that all the all the time I've never heard of people using it to get around but um that would be smooth man if you could do that yeah because the uh the group by actually performs it once it comes back over okay my next question Darren because you're a SQL Server Master can SQL Server do groups and could you set up your stuff in the groups so that you can powerapps could consume it so if you had 5 000 records consumed within SQL Server yeah you know absolutely yeah I'll tell you what powerapps just got a lot a lot more powerful right there for me absolutely it did um so yeah the uh group buy isn't used uh like you probably won't use that every day but it's good to know about it because every once in a while you'll need to do some type of like a reporting screen and you're not using power bi they just want a screen on there that's going to give them stats on let's say if you're writing an app that keeps track of a call center okay give me a list of all the 20 call center agents and how many phone calls they've taken a day yeah you would do a group on that yeah without having to do a count inside of your um your gallery all the time you know so many so many reports I remember the old days we used to have Crystal Reports and you used to have to design groups right you had to break it down into groups you know like like if you're doing a Time Time Clock like you just got done talking about you know there's just so many operations for reporting that you need groups you know absolutely yeah all right so let's take a look at this again here so we got Group by okay so remove and remove if that is a way I I don't know why they didn't call it delete because that's exactly what it does is it deletes something okay deletes a record so let's go back over to this screen where we've got all this data here and what I'm going to do is I'm going to click a little um a little icon let's let's call this I'm looking for the trash icon so I'll click on that a lot of times I like to make it uh either a red or a maroon color okay sometimes the pure red is a little too harsh and what we could do here for the select is we can say remove and the first parameter remove is the data source okay so this is my students okay and then the second parameter is okay which which record do you want to delete yeah so in that case we could just say this item that gives you the underlying current record and it's as simple as that so if I run this and we have Dickie there we go they're gone now here's a question what if we go down here and we're no longer Servicing people in um let's say any any people that don't have a country code maybe it's junk data okay so what we could do is we could bring over a button here and we'll say delete junk data okay so what we could do here is remove if this is going to look a lot like a filter statement so we've got the data source which is my students criteria is is blank on what column that country code country code country phone code so if that's blank it's going to delete them okay I'm going to run this see at the bottom we've got six of those I want to click on this button here okay look at that it's gone so remove and remove if our our fairly straightforward simple things now heard in in designing your applications do you like to allow users just to click buttons to delete data what are your thoughts on that you always want to do a validation on there right yeah because because if you're somebody like me and I haven't had my coffee in the morning I'm clicking around and I've oh I hit the trash can oh I didn't mean to hit the trash can you know yeah yeah we're saying hey are you are you really sure you want to delete right um you know thousands of Records you know maybe that's gonna and you might want to say are you sure and then when you say yes are you really really sure and are you really really sure [Laughter] yeah okay what about update and update if now I do like update if I don't really care for update all that much and I'm going to move my head out of this here yeah I've never used either one of these yeah update so update is a lot like patch okay so you give it a data source you give it a record and you can specify all the values that you want to update that record with now what update does how it's different than patch is it actually replaces replaces the record where patch is very surgical it's like okay I want to update this record I want to change this one little column to this value this column to this value okay do an update it's going to replace and if you leave columns out it's going to throw in null values in those things oh wow okay I don't like update that much but update if does not have that blanket type of way of updating it what it is it's it's it's you give it a um you know criteria so if let's say we had um those six people with those country codes of blank we can say update f for every one of those that has a country code of blank okay well let's make sure we have some with some country uh codes of blank so I'm going to say collect patch so we've got some with some um codes there I'm gonna go down here and see if we have those now now we have five people without any country codes okay let's say um most of the people that come in are from the United States and if they have a blank country code I want them to have a code of of one okay so what we could do is instead of delete junk data we could have update jump data we'll actually fix it we'll massage the data sanitize it okay so set a remove if we'll say update of if it's blank I want to update that entry phone code B1 now when I run it before I hit the button notice that we have 37 people here and if I click on this button it will go off and update those six records and then we should have six more okay if I scroll down there they're gone what do you guys think of all that so I got a question there about that sure viewers I think I understood that you were saying the update it will actually do the entire record and if you don't put values in there it will just put null values in the in the fields that you don't uh request in the update if it doesn't do that in the update if it'll just update the field that you you um specified within that criteria yes okay okay I just want to make sure I understood that okay so that is that is kind of powerful then so it is a little surgery thing this only update this field with this data for that field don't don't check this field and then update the entire record mm-hmm okay yeah so I'm keeping an eye on all these comments coming in Jeff says covering these functions has given me several more ideas on how to construct my app that's great it's great to hear yeah and uh I always worry about the balance Within These um live presentations and we're talking about for beginners of a beginner venue I don't want to blow people out of the water for that are true beginners I also know that you guys aren't going to be true beginners for very long but I I just I like the idea of exposing people to these more complicated functions knowing that we're going to go into it deeper and somewhere down the road so that you don't go oh my God I can't get this and then you just don't want to do power apps anymore you know um yeah it's like too intimidating or something like that right guys so Kurt has a good series a beginner level series uh I think the very first name of the the video that you have out there is look how easy this is yeah you know yeah and you sort of Step through and talk about um you know showing how easy it is and how it it really shouldn't be intimidating for you guys to sort of get on them and make make progress on your first app and uh become familiar with power apps so yeah so I do have one request for you guys are you ready for this you're getting anything helpful out of this a comment or even a like really helps the channel and that's people like you know this is a good content much appreciated huh all right thanks for coming guys for some reason YouTube thinks you're gonna like this video next let's see if they're right or you can select this playlist which I've selected for you based on the content you're currently watching guys gotta hurry click one of them otherwise YouTube's gonna auto play some other video probably don't want

Channel: PowerApps Tutorial

Views: 14,723

Keywords:

Id: vrxe9_wmy7w

Length: 136min 39sec (8199 seconds)

Published: Sat Jun 17 2023