Power Apps & Dataverse: Relational Tables Made Easy

this wasn't many to many that that's as hard as it gets right uh many to many supposed to be hard man that's all there is to it it's really all there is to it yeah it's kind of cool it's just a simple three Table app and we've done one-to-many one to many and then if you look at the three tables together it's many to many so all right guys so this is a little session we're gonna talk about uh dataverse and relationships between tables this seems to be the big sticking point for a lot of people's lives right really just we're gonna just talk about relationships right with databases the other relationships maybe not so much but with databases yeah we're going to talk about that so so it seems like it's a big problem for a lot of people when they're dealing with databases and I'm sure that you've dealt with it many times um it seems like people understand how to create tables they understand how to show tables they understand how to search they understand how to do filters but then we start talking about relationships we go into the one to many and we go to one to one and then people say many to many and they start scratching their heads and they go I don't know what I'm gonna do what am I gonna do you know and it's and so they get stuck and then you know that's the end of their their end of their their Journey right so what I'm hoping to do today and I've been talking to you about it a few times is I wanted to try to come up with some kind of a simplified demo that takes the mystery out of this thing and I'm really glad that we're getting this opportunity today to try to do that you know excited about that so should we just step right in start doing it it's just step in and start doing it man did you want to do an ERD first you know what that would be a great idea unless because I know you like to do ards see I've got an ERD you always ask me Kurt do you have your ERD and I said yep it's right here in my head and he's always shaking his eyes not acceptable all right you tell me what to create here Kurt so okay I don't want to call her table table one right nope I want a people table a table called people now what do you think about calling the table a singular uh value like person do you like that I I do and I don't it's I don't know it's a preference thing maybe I'm gonna follow yearly today man people now I try that because you know power uh danaverse likes to stick a s to try to pluralize things pull in there is it gonna be people's is it gonna be a people's table or is it going to just be is it going to understand you know it'd be interesting um so so now the people table now all I really want now obviously we've got you've got the very first field in there it's going to be called people and what that is the dataverse always creates a field that's going to be the same name as the table and what that is that's a grid a globally unique identifier and that's going to be used that's a very important um field for us to be dealing with with relationships and with filtering and searching so that's a it's a really it's a it's a very nice feature that powerapps puts or powerapps dataverse puts in and it's one that we shouldn't mess with um I'm not another field we want is name and you've got that sitting right there just like that see you're just reading my mind there so dataverse wants to put a name they want to put a name field in there that now when you go into dataverse for you get guys that are just starting to play with this dataverse thing you know you're creating tables you're going to see about 20 fields in there that you didn't create and that's they use that for various reasons of which I know none but for but this is what I do is I leave them alone and I just I just worry about our our Fields but there's one field that it creates well actually we just this is the second one that it creates that we're going to use and that is the name field and Darren has taught me that you know it's going to put it out there you might as well use it might as well use it for something useful well one of the things that we're going to do today is we're going to create a database table of people and the most important thing about people well besides your income is your name right so we're just going to go ahead and leave that name and we're just going to keep this really simple and it's going to be it's going to be a data type Char which it's already a data type Char so um that's all we're going to need in there and we're going to leave it at that and that's going to be our table so we've got two Fields Now understand it if you're going to make an app that's going to be dealing with people like maybe it's customers maybe it's employees maybe some you're going to put a lot of other fields in there but I really want to keep this as Bare Bones as possible I want you guys just to see the exact fields that you really got to have you know and really for the people table and for the relationships to work this is really all you have to look at okay so we're just going to leave it at that so we got a People table so let's go create another table and let's create a table called services you know let's say you know I've seen I've seen one of our our students wants to be wants to start a lawnmower business and they're going to have different services and I know another business that's going to be dealing with all kinds of different types of Services of which I don't know so let's just put a thing out there just call it Services because it could be a lot of different things maybe we could be we could be calling this customers and accounts we could be calling this uh customers and orders and product IDs and we could do anything but for today we're just going to create a thing called Services okay and just like in the people table it's going to create a field called services because and actually in this case do we want to just call it service maybe we just want to call it service because yeah I was I was gonna ask if that was okay but I didn't want to yeah I didn't want to uh Mr flow up there yeah it's okay yeah and and look if you think I'm going down the wrong tracker or just want to check on me just just check on me sometimes Kurt needs to be checked on now now you know so so we're calling a wellness check right [Music] so just like in the people table the service table is going to create another field it's going to be globally unique identifiers that is going to in this case it will identify each record for the service and then we have the name field which is just like in people and we're going to use that name field to to describe that put the description of the service it's just going to be a service description and for today that's all we're going to do with these two tables now understand that if you're going to build an application you would have lots of stuff in the people table and lots of stuff in the service table lots of different fields to to capture all the different kinds of data that you would need in there you know like for people you might want a social security number and birthdays and addresses and emails and phone numbers Services might be costs and types and categories and just all sorts of things you would want to flesh out but again let's not get away from the real meat and potatoes of what we're trying to do here we're trying to deal with relationships so this is all we really need so now we're going to create the first key what's this key thing here what's up what's that good stuff good stuff so like that globally unique identifier okay that's going to be a very it's going to be globally unique it's going to be a unique number a unique well it's not really a number a set of letters and numbers and it's going to be a key so that we can do searches on this thing so we can do and it will be when we're looking for that specific person it's going to have that globally unique ID and the database table will go out there and find it so the the searches that we're going to do will use that grid to find the right record is that close enough I think so so a key is a way to to find something quick if you ever need to look something up right it's typically unique because it happens yeah you're going to use this all the time in lookups lookup functions whenever you're doing a lookup you're going to be wanting to use that because when you're wanting to try to find that one exact record you want to use that right that's right so yeah it's really important and I guess maybe that's something that's hard for people to understand when you're beginning levels at database creation and stuff is that key but it's it's not rocket it's not rocket surgery is that Rockets or surgery it's not rocket surgery but but it is important and it is kind of hard so don't get overwhelmed with that idea of of keys and all that you're here you're gonna hear primary keys and foreign keys and all that stuff but it's just it's just ways of looking things up so is this data going to relate to each other at all it will once we build the relationships but we need to get a third table to do that oh and okay and what I always call this is a mapping table right it's gonna be a naming table okay and what I'd like to do is I like to use both both names and then Maps so what I would call this was people service map all right you could call service people map but I'm just saying people service map that just tells me this is my naming convention that I've adapted over the years because what it does it tells me that this is going to be a mapping table that's going to identify both of those other tables so I just use the other two tables and and this is actually what's going to create the relationship and we're going to we're going to do this in two different ways one way I'm going to show you guys how to do it the second way I'm going to challenge you guys to see if you can replicate it on your own and and I'd be interested to find out if you were successful at it and if you learned anything by doing that or if you got stuck and you need some help we're going to be here for you if we if you need help so we have this people service map table and again it's going to give you a globally unique identifier of which at this moment we're not too worried about right but we're going to create two other fields in here this is where the this is where it gets slick okay we're going to use a another data type called a lookup now I I call it a data type now other people say it's a type of field and it is but I can sit I treat it just like a data type because you're going to use it for nothing but looking up so just like you use numbers for for doing math and you use letters for showing labels this we're going to do a lookup for looking up so it's going to be a lookup we're going to create two lookups and I have a naming convention that I've used over the years and especially since I started with powerapps and dataverse is you you call it a look at now in the old days we used to just call IDs you would have you would have uh people ID and service IDs right yeah right but for dataverse they've kind of changed that and they've they've made this so simple so what we do now is we're just going to have a lookup and I'm going to put a the name lookup the word lookup in the name so what we're going to do the first one we're going to do is we're going to call it people look up right and we're going to make that a data type of lookup of course you can't really show this in your ERD um tool but but we know what it is and then the second field is going to be the service lookup foreign so now this is where the mystery's at what is this lookup doing hey if 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 so what it's going to do is exactly what it says it's going to do remember you always put the I get confused in this I I used to get confused in this a lot in the beginning of my uh database live you put in the many side you go and you put the look up in the many side and point to the the the one side so in this case we're going to point to people we're gonna have the people look up always going to be in the many side and it's going to point to the the one side so it's going to be a in this case it'll be a many to one right and then you go over here to the service lookup and you're going to point it to the service now you notice he drew that over to the the keys he didn't put it to the name right he didn't put it to the name because see you could have more than one Darren niece or Kurt Larson's in there I hope not because one's enough right but but you we know that there's only going to be one people there now think about this if we're if we're trying to put this in our head one person or one people one person could have many services right I might have maybe I want my car wash my grass cut my garbage taken out my I don't know whatever Services you're offering right right and maybe I'm a customer that's going to have many of those services so that's a one-to-many on the other side when we want to find out well how many cars do we need to wash in this this today well we go out here we say well Darren's got got one Kurt's got one so we need to we need to find out the people that have the service so that again that's a one to many right so you got many you got a one to many on both sides and that's what that's why they call it many to many because you got many services dealing with many people right and a lot of times you'll see it like in the middle like this I know it's trying to make it big and yeah and everything but typically the mapping table will be in the middle right yep yep exactly right exactly right and what we're going to show you today is how to do the many the one to many or the many to one however you want to look at that um we're going to show the people side with the services I'm gonna I'm gonna write a little app that's going to slip show how we can put this stuff in a gallery I'm going to create this stuff in the dataverse I'm going to throw a couple names in there we're not going to create credit screens or anything I'm just going to put this stuff I'm going to do the big No-No I'm going to go directly into the data the data tables and put this the names in there and then we're going to see if we can't just go ahead and create something okay okay let's do it now you're gonna you're gonna show us how to do this on on your computer right that's right so okay do I need to do any kind of sharing have you already got it you're already set up man look at that look at that wow see that's why we have Darren around he knows how to do these things okay so I I just have this blank table here actually what I want to do first I want to go over here to these these tables I'm going to create I want to create our data structures right and I'm gonna I'm not gonna make a big scene we've already discussed all this stuff so hopefully this is going to be pretty straightforward now I'm going to create a new table now that we did an ERD diagram and it looks beautiful I can use that ERD diagram to create my table and I'm going to create a table here called people and I'm going to save it and we have Jeopardy music playing in the background is this a savings [Music] I'm almost thinking they were they had a scribe out of there writing it all in by hand or something it was taking so long okay so then I'm gonna go over here to my Collins yeah and you'll see that we've got a name they put name in there and it created people and you'll see that the people field is a unique identifier just like we said well look at that any any chance you could zoom up on that occur just in case some of us have bad eyes or we're getting a little old and we not might need things uh yeah that might help us out a little bit there is that going to help it is okay so I'm gonna okay so we're out of this and I'm that's it that's all we've done now this paper table is done it is finished so I'm gonna go back to my tables here and I'm gonna create a new another new table okay and I'm going to create this service and you'll notice I'm just going to put service singular and it's going to do the duplicate for me [Music] look at that well that's fast and I'm gonna go over here click on columns where's my columns that hit over here I zoomed on it so now I'm lost okay and oh my goodness look at all those columns there all those what the heck happened here oh you know why because the first time the first time they didn't catch up yet now it's avocado right it's all right so I'm gonna go over here to Collins let's do columns again and I'm going to do a little trick here I'm gonna go over here to the name I'm gonna sort that and what it's going to do well I didn't do what I thought it was gonna do because we didn't put any custom stuff in there did we um usually if you do if you do the custom if you put it in your own tables or your own fields in there it'll put that in there for you anyway so it's got you'll see it's got the name and it's and it should have a service it should have service in here somewhere I'm not seeing it though oh yeah it's right there okay so we got the unique identifier and we got the single line of text all these other fields were created for us that that's what we talked about earlier on okay so then we're gonna go back and we're going to create a third table a new table and we're going to call this people the other people service map like that we're gonna save it and this is where we're actually creating all most of our field work it's going to be done right here in this we got those two fields that matter the most in this relationship when we're building a relationship so I'm going to go ahead and go to Collins I'm going to do new do a new column I'm going to call this people look up and I'm going for the data type you'll see this lookup right here and I'm going to select look up you go back in there I think our our faces were covering it up and I had to adjust oh okay you want me to go back a little bit yeah go back to the data type my our faces were covering that up so when I when I entered this people look up I had to put in a data type and I and I selected from here you know it starts it off at text and I just went on here to look up and I chose lookup from here just like that and when you do a lookup there's an additional field entry that you have to do you have to give it the related table so you come over here and you give it the related table and this is going to be the people table so if I just start typing people it puts it right there for me just like that look at that bam and I hit save very good that's that column is now done now we have to do one more column as soon as the Jeopardy music gets done playing [Music] and the scribes get done writing everything they have to write okay then we do a new column and we'll do service table or service table Service uh lookup and I'm going to go to single line see where it says single line of text there data type I'm going to change that change that to lookup and look up and I'm going to find the related table and it's going to be service right so find the service there it is right there and I hit save and I think Darren that that's all we need as far as our data tables are as far as creating our data but I want to put I want to populate our data with our tables with a little bit of data just a little bit okay not a whole lot um just enough to make people see what's going on so I'm going to go over here to my tables and I'm going to find my my people table down here I'm going to create name for Larson come down here and put their niece I'm gonna put Bill Frey oops he oh Trey oh boy I can't oh and Steve ow I didn't get the St that's good enough that's enough names in there because show what we're gonna try to do yeah and then I'm gonna come back to my tables come down to service go down my name and I'm gonna go HVAC level one I don't know what kind of service we'd run so I'm just throwing stuff in here guys level one maybe I'll do some HVAC level two maybe I'm gonna oh that grass got garbage yep uh walk the dogs take out trash seems like your keyboard's cutting out there on you I know I see that what's going on it's Bluetooth technology you just can't win with it uh wash car right so that's enough that's enough services so we got some people data in there we got some service data in there so now we can go out here and build an app to show how these these relationships are going to work right okay okay so what I'm gonna do is come over here to powerapps and wow that got small oh it's because I got the browser set up I want to I want to bring that back to 100 because otherwise it gets kind of weird yeah there okay I think that'll work better for us okay so what I'm going to do is the first thing I'm going to do is I'm just going to go ahead and bring some data in because I know that I need the data so I'm going to go ahead and bring the data in right and I'm going to add those tables I'm gonna add the tables there's my people and see all my tables services I just saw I thought I saw it there it is services and people service Maps those are the three tables that we're going to work with those three right there and I'm going to go ahead and bring in a gallery in here I'm going to do a blank vertical gallery I'm gonna put people on there make that a little bit smaller I'm gonna make that a lot smaller I'm not as good at this as there it is Darren Darren is wicked good at this I it sometimes you can go a little too fast though too so this is you're right you're right I just feel like such a klutz when well compared to what what you guys do uh okay I'm just that's good enough I'm gonna put a border around this Gallery just so that we can kind of see it in action right let's put a little border there uh I'm gonna go ahead and put a label in it insert a label don't you love these galleries it just automatically as soon as you start doing this is what I like about uh this this rapid application development you can just you're seeing it work just as just as fast as you're you're putting stuff on the screen right yeah in the old days it would just take forever just forever now one thing I want you guys to know what I don't want you guys to get hung up on this is something that I always get hung up on I'll put labels out here and maybe I'll have a label over here off the right and I've I'll be selecting it and it's not doing anything when I want to do my my click events right I do a slick I select and on select my on select's not working and I'll have this complicated code in there and I'll be breaking the code down to to its simplest form trying to figure out why is it not working and what it does what I find out is is that I'm clicking on the label and not on the gallery is what's happening when I have my when I have the labels out here in the gallery in the template so what you have to do is is of the on select this is this boy I tell you what I've wasted so many hours on this so in this on select make sure that you have a select parent on that sometimes you don't have it sometimes either either I'll go in there and I'll be taking it out for one reason or another but just make sure that select parents in there on your on your labels that are in here because or if you get stuck and you can't figure out why your click events aren't working check that first right just kind of yeah okay so we have this done and really at this point just for now I don't really care I just we just have people now we could do some sorts on this thing and we might be doing some filters in this thing if you guys are getting complicated in your apps but for now we're just showing people in here okay and then I'm going to go ahead and put another guy and insert another gallery another blank vertical to put him over here make that small they go yeah that'd be okay like that guess I need to hook some data up to it don't I we'll put Services we'll go to the services you know we said service but it puts services for us right and I'm going to go ahead and insert another text label oh I didn't do that right did I let me delete that sorry I'm just going to insert always make sure that you click on the little template in there before you do your labels because otherwise it puts it outside the gallery and then you get stuck on that for but if you do it right it just puts it pops it right in there for you just like that right kind of nice huh and then I like to do another little thing just I just like to create a little highlight on that so what I'll do is I'll go over here to my Advanced first thing I'm going to do is I want to label these right let me put a board around this one let's label these right always give it a good label we're going to call this gal people and then I'm going to come over here and I'm going to name this one gal services okay I'm going to come down here into the advanced go all the way to the very bottom template fill and then I'm going to say if this item dot is selected you gotta do this first item [Music] that is selected color not light blue now let's make it zero and see how that just kind of puts a little highlight in there for you just just something so that way we can see what's being selected I'm gonna take this code I'm going to highlight that I'm gonna hit Ctrl C because that's the hotkeys for copy right I'm gonna come over here to the gallery I'm gonna find this item or the template field click on template fill and I'm gonna hit Ctrl V there we got it there too right 50's just a little skinnier so they're more kind of uniform so it's halfway looking the same then I'm going to do this I'm going to do a third gallery and insert the third Gallery here um blank pretty cool [Music] [Music] and right here let's get this template set up [Music] people service Maps put a border around him and I'm going to insert a text label before I put one inside I'm gonna put one outside here and what I'm going to do that'll look pretty good like that I'm just going to put a little a little label on top of here so we can see what we're working with services four then we're going to say ampersand remember to say gal people got selected dot name so let's see how we got that going I'm gonna put that in bold make him bold and then I'm going to insert another label Okay so we've got we've got ourselves designed here now now all we have to do is just build do the actions to actually build the relationship to create the relationship and we're going to do this right on the fly as we're selecting records here so as I select this you'll see I've got if I select whatever record is it's going to change the name it's going to be services for this person right so now what I'm going to do is I'm going to build a relationship what I want to do is my action for this is going to be when I click on this when I click on our service over here I wanted to add it to this field to this column over here and pretty simple right and then I'm going to double check it I don't want duplicates in there so we got we're going to have to do a couple things we're gonna have to do a look up in there to make sure that it's not in there and we're going to have to uh then we're gonna have to do a patch in there so I'm going to go ahead and uh set up a go into the gallery got that gallery on and I'm gonna do the on select okay right here what I miss well I've got I've just set up a couple tables here set up the tables got the got the uh got the middle the middle mapping I'm getting ready to do the action now it's looking good what I explain I go I'll go ahead and repeat it just so that it always helps people to hear it again anyway so uh what the action I want is when I click on it click on a service over here I want to put it over here into this middle gallery okay and then I also want to double check because I don't want to do duplicates in that middle Gallery so that's that's the hardest it's the hardest part of this whole thing okay so ah so I'm going to go ahead and set up this this this first I think I want to do it this way yep I think I do so I'll select I'm going to go ahead and bring this code out code window out I'm gonna do it if blank huh I'm gonna do I'm going to do a lookup of the people service Maps I'm gonna look in that people service map table to see if if the now we that that's the that's the the the data source now we're gonna do the where Clause right and we're going to say if uh there was something that we I wanted to do nope this is fine this is going to work I'm going to say um people let's see Gallery people actually no I don't want to do it that way I want to say we're in the map so I'm going to do it this way um I got I gotta think this dude just a little bit Yeah Yeah I'm gonna say this is why they pay software developers the big bucks because we have to concentrate and use our brains to do absolutely absolutely so we're gonna go people look up uh people right your mere mortals would have smoke coming out of their ears and concentrating so much that's why I smell something burning that's why hey Kurt when I was growing up man we were so poor we could not afford to pay attention that's right so broke can't pay attention equals um gallery people not selected not people right bam look at that and and we're gonna say service lookup um okay Dot service equal uh and that is going to be this item right because I mean this record I mean this I'm in this Gallery it's gonna be this item not service perfecto but if it's blank so I did a double quote double quote there or a double quote double quote right frame right print that closes out this this lookup and this blank so if it's blank that means we don't have it in there if it's blank we're going to go ahead and do a patch I'm going to patch it into people service Maps the other people service match turn right here and I'm going to do defaults because it's a new record and I'm gonna do it in a people service map foreign because we don't have many fields it's going to be pretty simple right it's true I'm going to do a name because we're going to use that name so that way we can I love this I didn't explain this when I was doing the table when I was creating the table but it the the people service Maps also creates a name field and because we want to use that name field for something useful I think it's really good to go ahead and put I like to put both the people and the service in there so that that way when we're looking at it we're browsing at our data you know later on when you've got this huge huge 100 screen program and you can't figure out why something's not working you can go look at the data and you can kind of see what you're working with right so I'm going to put in this name field I'm going to put um the gallery people got selected dot name Ampersand I'm gonna do a space you know what let's make that a little bit better space and then a dash and then there's Bass just to give it a little something to look at there and then we're going to say this item oh boy I forgot the ampersand this item Dot uh name there we go and then for the for the now this is the the part that's kind of kind of complicated for people to get now don't get don't get overwhelmed in this it's pretty simple people look up mine didn't show that off you know why I didn't do it because I haven't I didn't do a comma over here that's what I really like about it they do every time that's why I love about that intellisense once you start typing you don't see that intellisense popping up you know there's probably something wrong so I stopped and I had to go take a look what did I do wrong that way you go you don't get too deep into it that's one thing I like about that intellisense so now if I do that people look up I bet it shows it look at that people look up and I'm going to say uh gal people thought yep that's done now that's you're just going to give it a record huh that's crazy isn't it isn't that crazy you don't have to give it an item or nothing just give it the whole record just give it the whole thing I loved it is the creators of dataverse were sitting there if the Architects were sitting there saying well what do we want to give them just give it all to them just just give it all to them you know give them the whole record so this is so powerful guys because now when you're when you're out there in your program somewhere you're you've already received not only did you receive when you did that look up not only did you receive the the identification the the right record location but you've got every piece of data already there it's already there you don't have to go looking at anything else just look in one spot it's a One-Stop shop I love it so we're gonna do the same thing here too we're gonna do service lookup and it's gonna be gal oh no it's not it's gonna be this item not nothing it's going to just be this item because it's a whole record and this item is whatever the record you're on that's true yeah look at that complicated stuff folks so now that you got code in there do you want to test it and then once you test it and you see a good record go in there I've got a question I want you to sort of break down the code that we just very nice so so what I've got here I'm hopefully it works but it seems like it likes it right I don't see any red squigglies as uh Lou likes to call shout out to Lou Nolan there with her her uh her what do you call it the Lexicon you got the Lexicon here yeah um I'm gonna go ahead and do what Darren like well it's not letting me there's something wrong oh you know why because I did a patch but I didn't close it I didn't close my if see another thing when I went to format the text you see I can't format the text so there's a problem there so that tells me if I put a so I put one of those things there there see how the format text came up I closed my yes that's what happened so according to Hoyle which some of you guys are way too young to know what that means um there used to be a book called The Book of Hoyle which all the card game rules and chess game rules and everything was in we always referenced the book of oil so according to Hoyle this is supposed to work right here let's go see what it does so if I click on I guess you gotta run the program first let's see click on this it's not working oh it did work look at that oh my gosh look at it worked Aaron first time look at that now if I click on one again it doesn't work but if I click on take out the trash it puts it in there let's take click on again nope doesn't put it in there so there we go we've we sort of got it working it's not pretty yet because what I want to do is I want to do a couple sorts in here and um I think what we need to do is a filter because this isn't quite ready because look see if I click on Curt here and I put this in here it's going to put Curt stuff in there too see there so it's not quite ready yet but it's close but we got this part working right we got the action working I'm getting excited are you excited Darren I'm excited what was your question well let's go look at the code you had there uh click on the gallery go and then we'll open up the code window and let's look at that okay so what exactly is up with this if statement that that first parameter the if statement what are we checking there again right so I again the according to the action that we talked about earlier on we want to when we click on this we want to bring we want to bring this service over to the middle middle uh Gallery but if we click on it again we don't want to we don't want to bring in another another record we don't want HVAC level one more than one time for each each uh for each people for each people right so that's what that's doing so let's go back and look at that code again so I'm checking to see if it's blank if this lookup doesn't return anything it returns a blank record if it's blank it'll be a null it'll be black we we went and did a query on that mapping that middle table there and we looked for something that had the the person that was selected and the service that was selected to see if it was there first because if it's already there we didn't want to add one right exactly right but inside of your query here we reference the lookup field then dot people um now what what data type is that that's the grid right that's the Gwen yeah so that's why it was so we talked about that uh quite a bit in the beginning when we were creating the the ERD diagram that's the reason why we talked about it so much it's so important because of that grid thank you for bringing that up now have you ever seen would we be ever be able to like down below we just gave it a record for both of those lookups is there any chance that we could do people look up equals gallery.selected without them I can I've never tried it that way I've always done it this way but I bet you could I don't see why you couldn't let's try that out you might you want people look up equals equal selected right yeah it should work and equal see the equal side we got the red squigglies there yeah so if you hover over that red squiggly what does it say it's gonna tell you record against the grid well I'm hovering yes I had it incompatible types for comparison okay so it based on what we're seeing there it looks like we're not able so the very first thing you did is almost a necessity because down below I almost get the idea oh I can just throw a record here why can't I just say record equals record and match that up so that's like we actually have to keep that in there right yeah and that's very misleading too because why can't you compare a record versus a record right because it's not really a record it's really a grid you're really looking at a grid it's record versus a grid is what it really is yeah but you should be able to do that but the reason why is because we're doing a lookup and within that lookup that's that's it's needing that globally unite unique ID I can't say it right now you just watch out maybe in two weeks Microsoft will change something where we could just do a compare the record to record right right people look up equals because when I see code down below like we like we did there we just put the record in there on that patch it sort of makes me think that I could compare it but it doesn't matter you know I guess I've always done it this way just because it's easier for me to to for my own I just lucked into it I guess like I said I've always done it this way just because it's easier for me to um if I look back at the code I can see what am I searching for here you know in this record what am I searching for in that record right but you would think yeah anyway that's a good that's a good thing to point out there Darren dot people right yeah that works pretty good huh yeah so let's let's do a couple things here first thing I want to do is I want to do some sorts because it's just bugging me it's just gonna bug me if I don't so I'm going to go over here to items besides that you guys might want to see what the sword looks like so I'm going to sort now if you're doing a sort what I would be doing is I would be doing and I might use name actually for this I would have a first name and a last name and maybe even a middle initial but probably not first name last name and then I would have a sort name I would have a sort name which would be last name and first name so that way it would just show it all at one time or you could go ahead and stuff it in the name field because it's there anyway and you might as well use it for something useful but you could do you know whatever whatever you want but anyway in this case I'm just gonna say I'm just doing a sort on whatever we got here so it's not going to be a real good source it's going to be a sort of it's a sort of Sword right it's a sort of sorts people and I'm going to do it on name right at least we see the D's and the G's and the K's all right there right and then I'm gonna do the same thing over here little item holy cow what happened there okay and then I'm going to do that now this is really the the most important line of code in the whole thing that we've done of course obviously that lookup was pretty important that we did over here in that patch and that's how you add a record now we could we could talk about the patch statement we could do an entire live stream on patch in fact I think you probably have several times in fact I think you have a video out there that's a pretty popular video on patch don't you dare yeah if you do a search for derony's patch I'll come right up yeah so so but in this case the other important thing to do is the filter this filter lines that we're going to do right here this items here we're gonna do filter on we're gonna do uh filter that map and then it's going to be where um be people look up right [Music] yeah people look up people look up Dot people equals equals uh gal people got selected uh people and I'm gonna do a sore no no I'm not gonna do a sword there I could I could but because of the way I set up that name it's not going to do too much good to do a sword on that so we're just gonna go ahead and leave it as soon very good anyway so let's let's see if that works now let's see so when I click on we're going to cut some grass and then I'm going to come over here to Dalton it still had all adult and stuff in there didn't it yeah but see in that kind of smooth I like it it's just just it filters just the ones that we selected over here now there's one more thing that we need to do because maybe we're maybe you guys are a lot like me and you can you have your yes and no moments where your yes and no and yes and no so I you know maybe you don't want to you put something in there and then you say no I didn't really want it in there yes I did want it in there so I think we need to do a delete in here I just want to put a delete in this thing so I'm going to go ahead and make that just a little smaller I'm going to insert an icon I'm gonna actually insert a trash icon right there I'm gonna put that little guy right here make him just a little smaller and if and then we'll check the visibility on that guy visible is going to be whether or not I've selected him or not right so it's going to be this item dot is selected and I'm going to click on this guy on the on select I'm going to say remove people service Maps comma this island voila so now when I have these yes and no moments I can click on this and it gets rid of it come over here oh that's kind of sloppy looking and they face that too I can't stand it okay I can't stand it points off points off foreign knows about points off that's right when Kurt is the professor of a class you make a mistake man there's gonna be some red marks on your printout that he gives back to you I am going to make a point of it aren't I that's so funny not you know it when you deal with uh the points is the currency nothing nothing works faster than red marks and points off oh okay so there we have it this now it's going to work a little bit nicer right so if I come down here I can get rid of this one what do you think Darren looks pretty good to me so we're doing to many stuff here this wasn't many to many that that's as hard as it gets right not many to many it's supposed to be hard man that's all there is to it it's really all there is to it I really hope that we took a lot of mystery out of this thing so I I want to pose a challenge there I'm going to pose a challenge yeah um to you and to all the other people but it's probably not going to be much of a challenge for you but so what what happens if in this little if you guys were following along with this and drawing this up and I suggest you guys take the recording of this and look at it and study it and actually replicate try to replicate this because you learn programming by programming not by just listening to me or reading a book or listening to Darren although listen to Darren it will maybe come close to making you a good programmer but you get to be a real good programmer by doing it so I really suggest you guys replicate this but I also want to throw a little challenge to make you an even better programmer okay because if you can figure out how to do this on your own and just try to wrestle with this in your mind take this Services let's maybe add another Gallery in here maybe underneath this one move this around play with this thing it's your it's your screen do it what you want but for every when I click on a service here I want to see all the customers that have take out the trash as a service um or if I click on HVAC level two I want to see all the customers that pop up with HVAC level 2 in here so it's kind of a reverse of what we just did here right and that actually would finish out the many to many part of this thing because what we've done really essentially is a one to many Right One customer has many services but we didn't do the many side so I wanna I don't want to do the many side I want you guys to do the main side and then I want to I want I want us to be able to have an opportunity to talk about if you get stuck yeah so so I'm thinking uh you got my my imagination going here as we're writing an app so this screen this scenario that you have here on this screen here it's really based on customer you got the middle Gallery filtering based on the customer right so let's say it's very customer Centric right so we click on Darren we find out all the services that he has done on a routine basis but let's see let's say each of those services are performed by different technicians so let's say all the technicians don't go out to the house at the same time right so let's say I'm an HVAC level maybe HVAC level one is they just do maintenance they just go out there and clean out the ac lines and make sure it's good maintenance HVAC level two is they're going to replace a blower or they're going to replace a a piece of equipment in the HVAC uh system okay so let's say we've got a different screen and say I'm an HVAC level 2 type of guy I only do HVAC level two type of work who are all my customers I need to go around to today or this week or this month you know so I'm just thinking of the business scenario in which you said you're challenging all you know these students here to create this other side it could it could be on the screen but it could be a completely different it could be a completely different app so we've got maybe an app for all the service technicians then we have a uh an app for maybe the customers that are ordering the services right right what are your thoughts on that absolutely well definitely you and you would be able to use the same tables to do that the same table structure could build separate apps for it if you wanted to you know um I think it would just be a good Dynamic practice just to see if you can catch all the all the different ones that are that are in the service all the all the different customers are in the service at this point but you're right you could actually build an entire app for that yeah and then what once you get this accomplished what we can do and I hope we do in future is we take this this simple many-to-many relationship concept and take it out to different levels like you just did where you can actually say okay well now we're going to deal with instead of the customer Centric we're going to make this doctor Sentry we're going to make this we're going to make this the repair the technician Centric you know I like it yeah that's kind of cool it's just a simple three Table app and we've done one-to-many one to many and then if you look at the three tables together it's many to many so I'm hoping by you going guys watching this material that that Kurt graciously uh took us through that you guys uh feel very comfortable with one to many and also many-to-many yep yep absolutely and this is just part of what we're going to be doing in the in the up the near future with the accelerator right for the accelerator program this accelerator program we're good this is just like this is just a piece of what we're going to be doing in that absolutely 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 thanks

Channel: PowerApps Tutorial

Views: 5,686

Keywords: Darren Neese, PowerApps, Power Apps, Power Apps Tutorial, PowerApps Tutorial

Id: cml6hcMb7V0

Length: 58min 46sec (3526 seconds)

Published: Mon Jul 31 2023