PowerApps Dataverse Lookup columns and Relationships - Create, Edit, View

in today's show we're told the powerapps dataverse lookup column so the idea here is we're going to create a table and we're going to create the lookup relationship we're going to explain the different types of relationships and we're all going to do it with those sharepoint slang because most of you understand sharepoint lookups so i'm going to try to equate the two to help you guys connect some dots but first here's our intro hi my name is shane young with powerapps911 those guys and today we're going to talk about dataverse lookup columns or relationships right that whole mini to one one to mini mini to mini like we're going to explain what all of those are and then once we've kind of explained them we're going to set the earth i guess we're going to set them up and then explain them as we're doing it whatever you don't care and then what we're going to do is we're going to jump over to powerapps canvas apps and we're going to consume those a little bit to understand like how you would use those with a form with a patch and a gallery and just kind of get the advantages and when i say we're going to add a little bit of a sharepoint slant to this the idea is that i realize a lot of my audience has built sharepoint lookup columns and you understand how those work and what they are and dataverse column lookup columns aren't that much different right the data versus lookup columns are them but better so but i think by connecting a couple of dots there you know people i've talked to before they have those light bulb moments so i'm hoping to have you a couple of light bulb moments by equating the sharepoint but who knows anyway let's switch over to my desktop and take a look over here what i want to do and go over here dataverse and then to my tables and so in a previous video we built the employees table right so down here we called it video employee and so we went through and we built this table and it's just kind of got some basic columns right remember you can always do this little custom over here and see just the columns that we added and so what i want to do now is i want to add another column to this list and if you don't know about that one i'll put the video link somewhere up there so you can go watch that because you need that to get through this one but anyway so we're going to do is we need to add a column here and we're going to add a lookup column but before we can look up the department we need to know where that data comes from right so we need to create a table for that so quickly we're gonna go over here to tables we're gonna do a new table and then over here for this point name we're gonna call this uh video departments i'm just putting video in front of everything i don't forget where i did this for and then down here now real quick before we go forward one thing to understand about using relationships here i can power apps or sorry in powerapps in sharepoint we do a relationship we can define the the column that we show in like the drop downs and things like that in dataverse it is always going to show this primary name column and you can't change the primary name column after the fact so you want to make sure that whatever column you're using here is going to make sense when you're looking up and so for me i'm just going to change it from name to department name but if you wanted this to not be if you want this to be the department phone number was what you you know wanted the relationship to show then you'd want to make that your primary one here right so think about this for a second when you're building these for your real your real environments so there's that we're going to say create and then now all we're going to do is we're just going to add one maybe two columns so let's add a column real quick and we'll just call this uh department manager man i can't spell boom and it'll just be text awesome and then we'll just add one more column for department yeah we'll do phone numbers so i just said department phone number all right depart phone number that is also text in this particular scenario right yeah now you might be saying why don't you use phone no we haven't talked about the phone data type before if i was building this out to be part of model driven apps and i wanted that phone number to behave like a phone number that would be a good choice in this scenario but but right now we're not worried about column types right we're worried about relationships so we're not going to go down that rabbit hole but that would be an adventure for another day so we're just going to use text okay so there you go we'll say done awesome and then down here in the bottom right if i move my face over for a second we'll say save table and in like five seconds it came back okay now that i've built that i also want to quickly populate a little bit of data right having some sample data is going to make it easier for us to do this later so what i want you to do to do that just like in the last video is data and then edit data in excel it says hey here's the file i just downloaded we're going to open that file and then after a moment excel pops open you i have to enable editing right my browser protecting me from bad excel files so i'm saying yes i want this one and then the add-in is going to load and then you should see the blank structure so then quickly we'll just have an executive department and then we'll set the department uh manager to be executive ella and the department number to be i don't know our phone numbers i think this so we'll just make that doesn't matter okay and then what would you do you'd go down here the bottom right under my face again and say publish and a few moments later you start to see some of these other fields get populated you know that it all worked and if you really want to see like see like created by lookup didn't get uh populated just hit refresh after you publish and then all the other fields would come pull be pulled in okay all right so now i'm gonna pause the video i'm gonna add like three or four departments because we just want some sample data right so i'll be right back okay i typed in a whole bunch of data and so then once again under my face we'll just press publish and we'll get all that data in there and then just to truly make sure it worked you hit the refresh and when it all shows up right these created buys and all these dates get populated i know the date is there perfect okay good enough we now have a table i don't need to save that so now let's go over here to tables and let's go back down to video departments or no sorry video employees video employees right here and so we're going to add a column now there's a few there's two different ways we can add a relationship the first way we're going to do it is the way that i think is the most natural right this is the same way we do it in sharepoint you go to add a column and here we're going to call this um department yeah let's call it department and so then for the data type for this column if you scroll down the bottom there is a lookup right here right so you choose lookup which is the same you do in sharepoint and then you say all right for what related table and so in this table we should have video departments now and that's it right there's not a lot of thought or process that goes into it you just say done and then we can say save the table under my face again and in just a moment we now have a blank but department table that's ready to do these lookup relationships and so if you're thinking about shane like what the heck is a look up a relationship right it's exactly this so the idea is that we have employees right we have all these people in this list and now we want to have another table another list of data and we want to reference values over there so in our case we're going to pull departments so instead of when we add um chewy as an employee here having to type in a bunch of stuff about the department that is repetitive all the time and having that all tied into this one record for chewie instead we just say hey we're adding a new employee chewie and we want to connect to the other lists department and we want to pull in the executive department and so then all the data about the executive department is maintained in the executive list but we just have the relationship we just know to go over there to get the data but if we go over here and change the executive manager from being nikola to being jennifer then the list over here gets updated and next time you view chewy and you drill into this department you'll just see executive and jennifer in that case right like the data is connected by the ids between the two so you don't have to store all the data in one big giant table right so that's the idea of relationship but you want to have related tables you want some data in this one some reference data over in this one and you want a way to connect those okay and so to that end that relationships you know if we look over here in dataverse we're going to see that you know we had columns right here beside columns is relationships and so this is where dataverse shows us all of the relationship columns that are available right and so you can see if we look down here look there's department so by adding a lookup it added relationship and if we kind of scroll over here we can see it gave it a name we don't really care about the name it knows what table it related it to video departments and it knows the relationship type right and so in this case that is mini to one so this is where it gets a little scary right this is where my sharepoint people check out because they've never had to think about mini to one what does mini to one mean all that means is that many different employees right so whether it's chewy or juan or greg or steven it doesn't matter different employees many employees and they all are connecting their data to one department right so chuwi is connected to one department one is connected to one department sarah is connected to one department so many over here connected to one over there because the idea is that both sarah and aubry are both executives so both of them are connecting to the one executive over here right the one executive department so many people connecting but you're only making one connection right juan can't be in both accounting and i.t he's either an accounting or he's an id he's related to one department or the other not multiple but 10 people in our company could all be in the it department so that's the mini to one right sounds super scary the important thing to know is that by creating that lookup column you created a mini to one so that what does that tell you for years now in sharepoint when you went into sharepoint said add a column and added a lookup column and sharepoint you've been creating a mini to one relationship sharepoint was just gentler right sharepoint didn't tell you that nerdy detail because it was afraid to scare you but i can tell you from the people i've talked to it was scary words so that's why we want to break down that many to one you also sometimes see that written as like into one or in colon one mini to one if you up here to add a relationship you hit a little drop down you're gonna see you can add mini to one which is what we just did but we used the lookup column you could also add a one to many so if we'd been over in departments and wanted to make a connection or set up this relationship while we're in departments we would have said hey departments we want to have a one to mini for employees i'll be honest like no one i know his brain thinks that way and i'm sure one of you are saying i do but for most people we go to the parent as i think of this right the employee table where we're having the mini down to the one department so but one to many is the opposite right when we created the mini to one remember the mini to one was from employees to department that was automatically defined but if you but if we go look now let's just go look real quick if we go to tables and we go back to video and departments now if you look at relationships what you're going to see over here is look department is listed here and in this case the relationship is one to many because it knows that it is a relationship back to video employee right so you don't actually care but i just want you to see that we created it from the many to one perspective because that's the way it works or the way that all of our brains work and the lookup column automatically did it but under the hood dataverse knows that when we go to departments that it has a one right to mini relationship backwards so not super important but i wanted you guys to see that this is here also keep in mind if you create this and you come over here and refresh it doesn't show up sometimes it takes a minute or two for this relationship to show up but you don't really care that it's here but i'm just trying to be complete with you guys as you see these pieces another thing that i think is worth noting is as we think about the data so when we go to powerapps in a few minutes i'll probably say a skin over there anyway but when we go to powerapps and we start manipulating this when you add a record right so when we go on edit one and we put him in the accounting department we are going to change juan's record we're going to update juan's record and and create that link that reference to the accounting department but if we go to the accounting side and looked at that record that record doesn't get changed accounting doesn't technically know that one is linked to it right so many the mini side of the relationship gets changed the one side does not so it's a small nuance once again you probably don't care but i want you to kind of think about that if you're really trying to understand this stuff at a deeper level all right let's go to relationships so that is many to one and one to many now there's also down here at the bottom many to many i'm not gonna lie that one is a little more scary so the idea there is if we want to create a late relationship where so instead of saying departments what if we had a table called sports and we want to be able to go to an employee and choose the different sports that they love they want to be able to choose multiple so it was a mini too many right so we're going to go into wah or to let's see we'll go to me and so i love soccer right so i'd find soccer and then i also want i also love basketball and i love volleyball and i love man whatever i like sports but so i could select those mini over here right so the idea is that me one person can like multiple sports but they're related and so what happens in a mini to many is that there becomes this magical hidden middle table that defines all of those relationships as but it maintains them as many to ones but both sides all the way through the reason i bring that up is because i don't want to talk about many to many any more than that today i'm going to make a separate video because working with many to many relationships it is more complex it is harder there is new concepts you have to understand but the good news is is that like i can't think of a single one of my customers where we have a many-to-many relationship that i've like had to do right i'm sure somewhere if we tried hard enough we'd have but but many to many relationships aren't very common typically we end up with many to ones and we end up you know designing the data model to account for that if they want to have those relationships we designed the data model a little bit differently to account for that avoiding many to minis but anyway we'll do a separate video at some point on many of the minis right now we're really just concentrating on many to ones okay so there you go so now that we've done this we've got a relationship let's go over to powerapps and talk about how to connect the dots right so how to relate data and then how to view that back in a gallery so what we'll do is we'll just go over here to create i was like trying to say how i'm gonna do this we'll do a blank app we'll do a blank canvas app obviously and then we'll call this uh relationships i might even spell it right in dataverse and i'm just going to do a simple tablet and say create okay so now that's in here first thing i'm going to do is i'm going to pull in a gallery right so insert i'll click on galleries i don't know why my galleries hide over here but they do and then if we just search right here we should be able to say uh we're just do video and then there's video and then for here we're going to do employees right because we want to pull in employees is that parent data so we pull in video employees and then we'll just kind of change this to be title and subtitle and we'll say hey show us the not the created date we'll just do their depart oh we're not gonna do department um we'll do their last name whatever it doesn't matter all right so there's our list and so the first thing i wanted to talk about is like the easiest way to maintain or work with these relationships right because we want to relate greg into one of the departments so if you are still using forms i know i'm not a huge fan but that's okay if you're using forms you can just don't edit form on here we'll go right here we'll make it bigger because i like it to be bigger we'll say data source is video employees and then it throws a couple columns in there and then we'll say edit fields and we'll just say get rid of created because we don't need that one so remove and we'll add a field and all we're going to add is their first name their last name and their department say add and then close that yeah close that and we'll set this thing just to be one make it bigger okay so just like that now if we um put this form into or so we'll change the item property here and then we'll set the item to be gallery one dot selected boom we see greg's data and so if we go in here and we hit the drop down there are the departments that we created right we didn't have to do anything so we're going to put greg into hr and then if we want to save that data right what do we do we just go up here we create a button and then we say on this submit form and then form 1 and if we hit play and then we say we'll say this to save it greg should now be updated and if we get rid of his last name over here and say this item dot department we're going to see one of the interesting pieces of this so look when you do this item.department that's your lookup column it comes back it's telling me it's a record what do we do we have a record we use our shovel right okay the dot or the the period if you do a dot and then look it pulls in all the fields for us so we've got department manager department name department phone number and then all the other backend fields but if we do department manager we should see hr henry because that's the department manager this is one of the greatest features of dataverse for me right is when you do these relationships you can use this dot notation to dig down into the relationship and pull in all the fields right whereas over in sharepoint what we had to have done we would have had to add those as columns and they would have shown up as department colon department manager right they would have been this weird interface and they have delegation challenges and a bunch of goofiness so we don't have to do that we don't have to use a lookup function with power with dataverse we are just connected in here you know and so then now like if we go to me and then we add me to the executive department save it boom there's executive ella and then if we go down here jeff right same thing jeff can be and i t save it and we got it arma so just like that right if you're just using forms super simple and what's really happening here if you click on the drop down they're just using the choices function and so the choices function knows that department is a lookup column and so then it is pulling in the values from that other table right so that's how that is working now if we kind of take this thing shrink it up a little bit so that's if you just want to use forms i don't love forms but we we like we want to talk about right sort of say submit form here so what if you want to do the same thing with patch okay fair enough so for patch the way that this works is what you're going to do is you're going to say patch not passed patch what do i want to patch i want to patch video employees we're just going to create a new one so we'll do defaults of employees and so then we know that uh first name is required so first name we're going to set this to uh patty pat or patty since we're doing the patch and then the only other column we want to put in here is department so for department in the case of using a patch what it is expecting is it wants you to pass it the record of the data that you're looking for right so one of the easiest ways to do this is we could do a input and we could do a drop down and so then in this drop down we would say hey you i want to use um video departments now so i need to add that as a data source wait a minute so another interesting thing about lookup columns right remember we added video employees as a data source right we did that powerapps was smart enough that when we referenced the departments we did the lookup uh reference it had to go fetch that data so we added departments as a data source automatically for us we didn't have to do that it just put it here ah pretty cool so for the items here what do we want we want video departments so the video departments remember that's the list right so that's all the different people and i can change maybe instead of showing department manager oh sorry i'm gonna do that maybe instead of showing department manager we wanted to show department name or department phone number because that's what you want them to look it up by whatever you're looking to hunt that down via which word department name that is what you put in the drop down here and so with patch all we have to do now is say uh i'm sure it's drop down one dot selected and that's you're going to stop right there you're not going to do selected dot a field name right because department wants the record right it wants to know what record that you're tying this to so you're going to want to provide it that whole record but if we do this this should create a new one called patty and patty would be an executive so let's press the button and see what happens group go to scroll the bottom patty and executive ella what what what what kind of cool right like that's pretty straightforward in my book but with patch that is the key now what if and what if i wanted to hard code it to the executive department all right what would i do in that particular case so the key is that this has to be a record so what i typically would do is some type of lookup right so i would say look up from video departments and then a condition that would always be true right so we're going to cheat right now and just use um what do we call this field department name so we'd say department name department name equals executive and so as long as that lookup returns the record that you want which in this case we hover right it says a record and you can see the record well it's not going to show it to us but that would do that would set them to hard code to be your executive right let's just test it so we'll just do patty 2 and now we'll say play press the button and if we scroll down here well did i not change your name to patty too i thought i did oh you know what i'm doing i know what i'm doing i'm guessing let's just go up here no that should be long enough all right let's try again first name is patty to without the space maybe the space is confusing or not confusing it but is just not showing up press the button there you go i don't know we want we'll ignore why the other one didn't show up with the space but there you go so patty 2 and executive all right so the idea here is that you would do a lookup now you might say department name might not always be unique that is fair then remember what is the primary key in in these data sets right so make.powerapps.com so if we go to dataverse and then we go to our tables so then we go to departments and go to data and so if we say show me all columns there is this video department right there is the that is the primary key right so you could copy that or let's do a different department to it so copy this one so it starts with c ends in a nine i always do that to make sure i get it right and so then you would go here and you would change this to put in that and then you would say instead of department name you would say video departments like that and so i think it's mad so it's telling me that i'm comparing um a grid to text which is annoying it used to not have this problem so you put the grid function around that like so and so now if we press the button patty 3 should be in it right so press this do this paddy 3 is it irma so that's how you different ways that you could patch these connections if you want to you know be more rigorous in your controls now there is a third way that you can do establish these relationships and so that is called relate and so the idea of relate is it's a function that would build this relationship for you so it doesn't create the record it's not going to create the uh the new record like patch did it's not going to create the or do anything like forms did all it does is build does the relationship side so there's a function called relate another one called unrelate honestly on many to ones or one to minis we don't ever use those functions so we're not going to talk about those in this one but when we go to many the mini in the next time we talk about this stuff with many to many you have to use relate and unrelate so we'll we'll get to them but we don't have to do that today all right so the last piece of this i always want to talk about one more second and then we'll be done is so now that we've built this what if i want to filter this data right so i only want to see the uh the executives so what are you going to do you're going to go here you're going to say filter employees and you're going to say where department dot and then in our case department name equals executive like so and then now we just see the four people that are executives yay so nothing complex about that but that's important to note because remember i told you like executive the executive record and department has not been being changed has been using it right the executive department doesn't know what employees are related to it so you're like oh i can't do it but so by filtering the data you can figure out who all of the executives are but if we were to look at our employees right they do know all of this so like if we go back over here to uh video employees and remember i told you guys like one of the things i really do a lot is i use edit data in excel to understand so we don't want to open video employees this time and the reason we're going to open up video employees if i can click on the file is i want you to see over here that you know so we have the department it is a lookup so executive right and so it gives you the little picker but if you're ever trying to manipulate these you can't just i can't go here to juan and say juan you're in it right i can't just go here and type in juan i t and b if i hit publish so i should publish and then let's just hit refresh and look juan's it got wiped out because you can't put in text for here but if you need to manipulate these pieces like wearing my super pro tips if you scroll all the way to the right you're going to see yeah video um if it was a snake it would have bit me it's right here so look these are those i t uh uh those those uh ids so if i go right here and copy this so the grid for whatever department this is and paste it in here for one let's see that's for five um so jeff so it'll be i t right so one is currently nothing now if we hit publish now it says juan is i t so if you ever need to manipulate these relationships on the excel side change the grid column not the text column bingo bingo you've got what you want okay seriously people that is like everything i think i can think of right this video got longer than i wanted i apologize but but hopefully this is a good stuff right you should not be afraid of look up columns right mini to one one to many you understand it it's the same thing just from two different directions we're going to come back to many to many i promise at some point but mini to one is the same thing as your sharepoint so all those things all those look up relationships you've been doing in sharepoint there's been many the ones you just didn't know it so awesome so they have any questions any comments leave them below you know always happy to help you with this type of stuff right answering your questions or you know powerapps 911 we have full-on consulting services where we've done this for hundreds or thousands of millions or zillions or trillions of people because we get a lot of these uh type of calls so yeah so anyway i think that's what i've got for today so with that i'm gonna say thanks and have a great day hey me again before you go click on the subscribe button right join the list of hundred thousand plus people that subscribed already or if you need any help right check us out at powerapps91 we do big projects little projects we do training we do everything and we can help you or if you want to see more videos you probably do then just click on the playlist above cool thanks and have a great day

Channel: Shane Young

Views: 89,300

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, powerapps dataverse tutorial, powerapps dataverse lookup column, powerapps dataverse relationship, power apps dataverse, powerapps dataverse, powerapps relationship, dataverse, dataverse power apps, powerapps many to many, powerapps many to one, powerapps polymorphic lookups, powerapps patch lookup, powerapps forms lookup, powerapps edit data in excel, powerapps n:1, powerapps tutorial, dataverse microsoft tutorial

Id: BPkv7S11Zp8

Length: 30min 36sec (1836 seconds)

Published: Mon Feb 28 2022