The Ultimate Guide to Lookups in Model-Driven Power Apps and Dataverse

we need to talk about lookups in model driven apps you have all got so many questions and I'm here to answer them with this Ultimate Guide to lookups in model driven apps I'm going to go through everything that everyone's asked and that you need to know here there's chapters along the way you can jump straight to what you want or follow along for the entire Journey we're going to talk about the concept of tables in dataverse what they are what types of relationships you can create we'll have a look at the core concept of what a lookup actually is when you would use it and how to create one then we're going to get into some of the things that cause problems around the primary name column and why it matters and what you can do when the primary name column isn't the thing that you want by configuring lookup views by working with quick view forms as well from there what if just displaying additional information on a table isn't enough and you want to actually be able to bring copy data across from related tables that's called mapping we're going to go into that how to create filtered lookups where you've got for instance a state and then you only want to see the cities that are in that state rather than the whole long list of things little bit about security what if I want someone here to not be able to create a new row in the table when I'm asking them just to select from an existing set of values you can do that too importing because that can get a little bit tricky when you're importing a data table and you've got a look up in there and last but certainly not least this question that everyone has about why can't I add multiple values into my lookup and how you should go about setting up your data relationships to solve for that relationships between tables are a core Concept in building out your data model and your model driven apps what we've got here is the ability to create three different types of relationships but really it's only two the first one here is the idea of a one to many relationship this is what we're going to be working with with most of this tutorial because that is where lookups come into the picture so we're going to be working through a scenario here where we are leasing out cars and we've got a whole fleet of cars that we are leasing out each time somebody leases out the car there's one person who can have multiple leases that's a one to many relationship they might be leasing multiple cars over a period of time there might only be one that's active but one person can have multiple laces the other type of relationship we can create is a many to one this is the same relationship but flipped around the other way so let's look at it from the point of a particular car lease we've got lots of car leases in the database but each one of those can only have one person so if it's a lease you are having a look up effectively to who is the person who holds that lease we're going to go down another level with this as well and have a look at the idea of these cars need servicing so each car can have multiple service appointments but a service appointment will only have one car hopefully they're only servicing one car at a time so the one to many many to one are the same relationship just depending on your perspective so those are the first two types of relationships that we can create the third one and we're going to come back to this right at the end is a many to many relationship and we're going to switch into a different scenario here which is around working with books and authors you might have a book that only has a single author in which cas you one too many relationships are fine but lots of books have multiple authors and this is where people get stuck in being able to come in and say but in my lookup now I want to work with two authors and I can't add two we actually need a many to many relationship to handle that rather than a lookup so we're going to have a look at that as well let's take a look at how to create a lookup column take a look at how to create a lookup column and what that's actually functionally doing I've got here my model driven app and I've got a table already here with a list of cars in it this is going to serve as the way that I'm keeping track of the lease agreements that I've got now each one of these I actually want to connect to the person who is doing it so rather than just having a flat table here with another column that has the name of the person and then I might have to add their phone number or their email or their address that's actually another table I've got a contact table in the system that I want to use for that so rather than adding a whole lot of extra things here I'm going to add a single column here that is a lookup okay connection to that particular contact or customer that I'm working with and then all of the information about the customer and all of the things they do the history of their leases the history of anything else we do can be connected directly to that person so the easiest way to do this um in my uh maker experience here I've got my car table up on the screen what I want to do here is to add a new colum now with the lookup column what I can do is create the actual column and the lookup Allin one go if you're coming from a a background in using SQL or other sort of database configuration you don't need to be creating any primary keys or anything we're basically going to just do a quick step here to connect and join these tables together so the way that we can do that is to say let's create a new column so I'm on the car and I want to point to look up to the contact table that's in my database I'm going to call this one customer and then the data type here is a lookup don't be tempted by the one there that's called customer that's actually a polymorphic lookup which is a whole other thing that allows you special properties to choose either an account or a per or a contact so if you've got a business where you actually want a customer that could be an organization or a person that's the one to use but in this case we're just dealing with an individual person so we're going to go for the garden variety lookup here incidentally polymorphic lookup is my favorite terminology in the whole platform so we got the customer the lookup and then which table are we connecting to and this now gives us a list of all of the different tables in the system it's easier if you just start typing it rather than scrolling through that whole list so I want this to be a lookup to the contact table now as soon as I click save that's doing two things that's creating the actual lookup so when I'm viewing the car I've going to have a little magnifying glass icon and I can go and find and connect the related person or customer who's got that lease but it's actually also creating that many toone relationship between the car table and the contact table so now we have our customer which is a lookup if I go back into the car table we can see in the relationship section that has now actually created the customer which is the relationship with that contact table and that many to one if it makes more sense to you you can do it the other way around you can say I want to create a many toone relationship you would choose the contact table here and then in creating the relationship it will automatically create the lookup so whichever way makes more sense to you you will end up with the same thing you just don't have to do both things create the lookup creates the relationship or create the relationship creates the lookup I do it the way I've shown you because that just makes more sense for my brain if you're coming at this from a low code angle chances are that's how you'll do it if you're coming at it from a database angle you'll probably do it the second way so now that that's done what I want to do is add that onto my for form so I've already got a form here with all of the information in it about the car and the leasing details and what I want to do is to bring that customer information in here so let's just drag and drop that across into here and save and publish that form and then take a look at how that works back in my app experience here I've got this particular car and I'm ready to lease it out to a customer you'll see the lookup column here has got that magnifying glass and I can just come in here and start typing the name of a particular customer customer so I know I've got Sally sample is the one who is going to be leasing this car I can type that in save that and now I've made a connection between the record in the system for Sally and this particular lease now that's all well and good because this is a nice simple example s sample her name comes up there's everything I want in there I'm using the contact table so let's have a little bit of a look at something where it isn't quite as simple as that where that name isn't the thing that you want to see on the screen let's take a look at what happens when you want to work with a lookup column and the primary name of that lookup column is not terribly useful for you which does happen a lot so I've got here a service appointment we've just got say a service appointment reference number and I want to add a particular car to it so I come in here to do my lookup and I get this kind of useless thing so I've got car1 we've just got some sort of Auto numbering in there there's a date here this is actually when the record was added which is largely pretty useless and so it's very hard for me to find anything let's say I do actually know the unique number of the car and I can click that again it's not particularly helpful there's nothing much there so what can we do about that the first thing that we can do is to configure What's called the lookup view so I've got my car table here if I have a look at the columns that I've got available in here you'll see that the car is the primary name column this is the one here as an auto number so always have a think when you're creating tables about the power of that primary name column whatever that is there's no getting around it that is what is going to show in the lookup but configuring the lookup view is one of the ways that you can make this a little bit more friendly and then we're going to have a look at another one in the next section so we want to come into the car table click here on views and then you will find when you've created your custom table that this one will be here there's a thing in here called a lookup view which is created by default but it has these two kind of useless things you've got your primary name column and then create it on so what we're going to do is just remove that because it's not terribly useful and we're going to bring some other columns in here that are more useful now as we go into the lookup view you'll find the one that you add next is going to be the easiest one to see but we can add some more in here so let's say we've got the license plate as something that might be useful for us to have a look at as well as the make and model of the car so once you've got the columns in there that you actually want to be working with what we can do is save and publish and then we're going to go back here the other thing you want to do is go into this quick find so if for instance it's easier for you to search by the license plate than the car ID number then we want to make sure that that's in here this is determining which columns are going to help you find things so we can come in here and say edit the find table columns and come down here and choose the license plate let's also put the make and model in there just a good measure and click apply and then we're going to save and publish that now depending on how much data is in your system the indexing on those fields can take anywhere between a few minutes and overnight uh I've got short demo data here so it shouldn't take too long and we should see this in action pretty much straight away let's take a look at the experience now I'm going to go in and put my service details in there again and now when I look up for my car you will see a couple of different things firstly instead of that rather useless date I've now got the license plate number in there that was the second column that came up but if I expand it now I'm actually seeing more details so that can help you find the right thing you can also search by those things that we instructed that we wanted to search by so if I know that the license plate is abc123 now I can actually search for that in there find it you'll see it's found it in that column and select that it's still only showing you the primary name I am going to show you some other ways of dealing with that in a second but in terms of finding the right thing and making the right connection between the tables that lookup view is going to take you quite far if you want to have a look at more columns when you're making the search you've also got this Advanced lookup in here so we can come in and choose the main view that we've been working with and then you can search for things in here so we might say let's find all of the Camry's for instance and there they are there and you could come in and say yeah actually I want that one and select that and now that's the one that's connected in there so again all well and good but you're still only seeing car6 on the screen it's been easier to find the right car but now what you want is to see more of that information on there because I don't have a clue I'm doing this surface I have a clue what car 006 is and so the way that we can handle that is with something called a quick view form not at all confusing so let's come back in here and the quick view form needs to be on the table that we're displaying which is the car so this is actually sitting in the forms even though it's called a quick view it's a form so we're going to come in here and you will see again when you've created this custom table by default there will be a quick view that's in there and we can bring this up you can actually create more than one of these for different forms and different types of scenarios if you want to so this allows us to bring some information across so we are going to first of all hide the owner we don't need that uh to be showing on there but let's say what I want to see here is the make model like license plate and in fact the customer who the person is that we have got list out to so we've got all of those things in there tip for you here remove or hide the ID because when you do the look up you're going to see that anyway and otherwise it's just going to be duplicated on the screen so we're going to put that in there we are good to go now let's save and publish that one and then there's one more step we have to do which is to put this little form think of this as like a widget we're going to put that on that service appointment o Ms table so what we're doing here is we're going to put some another section on the form here and bring that information in so that we can see what's going on so we will come back into the solution here going to go into my tables and find the service appointment table and then navigate through into that main form first thing I'm going to do is add a new component I want this to be sitting in a new section and we're going to call this one uh details and what I'm going to do is bring this down into here so that it's all sort of sitting with the look up and then the details directly underneath it so the element that I want to bring in here is a display element and there it is there quick view and now I can select the quick view form so the lookup is the car and which one do I want I've only got one in there but you can see you could have different things in there if you wanted to see it in a different way somewhere else and we're going to click done and I didn't put my cursor in the right place no drama we will just drag that down into there so you'll see this is where I took that car ID out cuz we're going to have the car ID make model license plate and customer let's save and publish that and have a look at what that has done back in my app let's create a new service appointment you'll see that's updated now we'll pop our service in there again let's uh give it a date and time here as well so we're going to do this at 8:00 a.m. on uh on this date and now when I go to look up the car I've got all of those things again so someone's calling up it's car abc123 so I can just search for that and find it and there it is and all of that other information is now visible to me so even if you can't change the primary name to something more sensible we've now got all of that information let's save this one because I'm quite happy with that you've got all of that information visible on the screen but what if that's not enough what if you actually want to copy the information onto the table because this is actually just giving you a read only information in here let's say there's something like the mileage on the car and you want to take a moment in time capture of that onto the service record you don't want it just to be this readon link the way we can achieve that is with something called mapping so we're going to go back into our solution here and if you want to map Things what you need to do is to create the column on both tables to be in exactly the same data format so if I go back into my table here and have a look at the car table we'll see that I've actually got a column in here for the mileage let's say we're getting sort of regular updates in there so we've got mileage and that's a whole number what I need to do to be able to map this across into the service appointment is to create exactly the same thing in the same format the name doesn't have to be the same but you're going to make it a whole lot easier for yourself if you do so let's create a new column in here we'll just do it a slightly different way so you can see something else so we're going to create my mileage and this data type must be the same so this is a number and it's a whole number and I'm going to click save the next thing I need to do is put it on the form so that we can see it so let's come in here I'm going to go back into my main form and let's put it in that car detail section so let's say the other things there are fine if we just view them from that car table but mileage we really want to be sitting in there and let's save and publish that one too so to create mappings we do actually still need to switch back to the classic experience so what we want to do is go right back up to the top level of the solution here not just in the form where we were and choose switch to Classic tip for you here if you can't see that setting and you will need to be an administrator to do this in the Power Platform admin Center you can come in here into your environment select settings and then under product Behavior you'll find this option here show the switch to Classic button and you can toggle that on there are very few reasons to still need to do this but for now this is one of them what we want to do here is in this relationship between the car and the service appointment we're actually mapping something from the car to the service appointment which is the one to many way around it's easier if you do it from that point of view so what we want to do here is click on entities over the side here car and open that one up and click through here into the one to n relationships and you'll see that there's a whole lot of things in here that are very system driven but this is the one that we want here the relationship through to the service appointment that's going to pop up another window let's just expand that out and zoom it up a little bit so we can see what's going on and you'll find you've got an option here called mappings what we want to do here is create a new mapping we click on new and this gives us the screen that shows us all of the different things again let me just kind of bring that up between the source and the target so on the side here we have got the car on this side we have got the service appointment so what we want to do is scroll down and find our mileage which is a whole number is going to map across to the mileage there and we click okay now there is an option in here to say generate mappings that will automatically generate all of the possible mappings for you but I'm showing you how to do it sort of as an individual thing once we're good we can save and close that we'll Zoom this down a bit and publish all customizations there to push that through this can be a little tedious in the old experience which is why it's better not to use it unless you absolutely have to all right and we're done the other thing about working with mappings is the logic actually has to go from the parent to the child so the way that we were trying to create it before opening up the service and using the lookup we need to do it the other way around we need to start from the car create a service and then it will push that mapping through so we can use subgrids to wake that a whole lot easier what I'm going to do here is go into my form into the main form and this is the other side of that one to many relationship here where we're seeing it from the one to many point of view so we've been looking at that lookup where we've come from the many to the one this is the reverse of that so on the car we can have many service appointments I like to put this in another tab you can do it on the main form if you like so we're going to go in here and say component one column tab let's select that and change the name to be service appointments in this section here we can also change that label to be service appointments and the component that I want to bring in here is a grid this is a subgrid I only want to see the things that are related that box should probably be checked by default but that's for another day and I want to bring in all of the service appointments and click done let's get this heading out of the way by clicking hide label we've got that in there let's save and publish that and have a look at how our mapping is now going to work back in my app let's say we are scheduling a service here for car number four we've got our service appointments here so this is where we'd see the history of all of them we'll create a new service appointment and there we go now we've actually got the car the mileage and if you wanted to you can actually change that at this point these ones are read only they're just being displayed here but this is actually writing to that table so let's put a service number in for this one and we will give it a date and time as well and save that and then that piece of data is now written in there as anything else you might like to choose to lock it down once it's written which you can do but so we've got the difference there between the quick view form just giving you visibility of what's going on and the mapping actually writing data across to that table another little bonus tip here when you're working with mapping if you want to map a choice column so let's say we had the color of the car and you wanted to put the color of the car into there as well when you set up your choice the first time you do it please make sure you choose this yes recommended for the global choice because then a global choice will very easily sink across otherwise you're in a bit of a world of pain with doing that so on the car table you're going to create a choice column as a global Choice when you go to your service table you're going to choose that same Global Choice and then you're in for an easy experience let's switch gears a bit here do you see what I did and have a look at the idea of filtering and cascading lookups let's go back to the tables here we've got the service appointment and we want to know the location of the service appointment I've already created a couple of extra tables in advance here I've got a state and a city so the state I'm just using some of the states in the in Australia here where I am and then the city actually has a lookup column to the state so you should see that in there so each City belongs to a state and in the service appointment we want to be able to choose both of these but once I've chosen my state I want to only see the locations that are related to that first thing I need to do is create relationships so the service needs to look up to both the state and the city so we're going to create a new column here called State and that is going to be a lookup to state and then we're going to do the same thing for the city we're going to create another new column here called City also a lookup to the city table and save navigate back to my service appointment and let's have a look at the form and we're going to add both of those things to the form so we'll click through here into my main form I'm going to add another another component here which is a one column section and we will just bring that up to sit underneath the service notes there let's give it a name of location and then I'm going to come along here into my table columns and we are going to bring the state into there and the city into there as well now if I do this let's show you what happens here is that we can save and publish that come back into my application and give this one a refresh and now I've got my state and city lookups I can come in here and come in and look for I should have tidied up that view but we you go with it Victoria New South Wales and Queensland so Victoria is where I am and then if I come in here and look for the city I've got all of these things but these are all cities across the whole of Australia for those of you who are not local to where I am what I really want is St Kilda Richmond and Milbourne are the ones that are local these other places are not local to me so the way that we can apply the this filtering if we come back into the form here while you are on that City column scroll down here and open up filtering and check this box filter by related rows and we want to filter this by the state save and publish so now what happens in that experience is that after I've chosen the state I will only see the rows that are related to that so again back into my application here we'll refresh this to pick up those changes and now that I've got Vic selected I can go into cities and there we go Melbourne Richmond St Kilda those are the ones that are in that state just to show you something else for these uh areas that you might not be familiar with New South Wales just to show you that it's different I can go in here Bondi GLE Sydney I'm getting different values if I choose Queensland then I am only getting Brisbane as an option so I've got a filtered look up there for that dependent cascading Behavior between the two tables let's talk secur for a minute one of these other questions that comes up is here we go I've got a service appointment I'm going to add a car but there is this new car button here which would allow me as the admin user to create a whole lot of additional records we don't want that if the user is coming in and they can't find it we don't want them to just go in and add another one and that will cause duplication I am admin here I can do anything I like let's switch across and have a look at the view from another user here this is logged in as Dan and you can see that when Dan comes in he actually can't select that new car Option he has to choose from one of the cars that are available and the way that we've achieved this is with security and permissions on the table if we don't want him adding one here we actually don't want him adding one anywhere so if we have a look at the security there he is in this basic user role and the basic user does not have permission to add rows to that table so we're controlling that behind the scenes with the security now let's have a look at how we can work with importing to a lookup so I've got a different scenario here we're working with a recruitment app where I've got a number of roles that I'm recruiting for I've got another table here that has got a whole lot of contacts in it but I need to import my applications now the application table in here has two lookups the application is for a certain role it looks up to a role and it looks up to a contact so let's take a look at two different ways of making that import work so from my model driven app here I can go import from Excel import from CSV I'm just going to choose the file that I've got here for my applications click next and review the mapping and now what we've got is a lookup you'll see we've got the two lookups the one to candidate and the one to roll now the roll lookup is relatively easy because the r names in there are all unique unique and what it's doing here if I click on the magnifying glass it actually shows you which field which table column rather it's using to make that match and it needs to be something that's unique so I'm going to leave the roll to the name because I know that I've got a unique role name and that's going to work candidates however in reality the first name last name combination is not unique if we've got a large database of people you can have spelling mistakes you can have duplic ations you're not so guaranteed to find the right thing so again I've clicked on that little magnifying glass icon to open it here and instead of mapping onto the full name which is the primary name column in that contact table I'm going to get rid of that and then in here find something so I'm actually going to use the email because the email is likely to be unique so I can make that mapping to the lookup field happen based on a piece of data in there that I think is unique so we'll click okay and then finish that import and there we go we've got those applications through let's take a look at what that's done so this is an example of a table that has two different lookups in it another quick view form here so we've got the application that is to a particular candidate so it's created that connection to that particular record based on the email and we've also got the look up there to the role let's take a look at the thing that causes the most confusion with lookups which is when people ask why why can't I add more than one thing in a lookup so I've got my Bookworm database application here I've got a book with the title I've got different formats I've got a description of the book and I want to look for the author I've created this as a lookup which is a common way that people would think about this I've got a book I want to look up to an author so I've actually got another data table in here for my authors and this one is Neil giman so I can look that up select that and we're good what people want to do then is go in and add another one and of course you can't this one is actually authored by Neil and Terry pratchet if I start to type something in I just it just won't let me do it I can choose Terry pratchet but it's going to replace him which isn't what we want now the reason that this behavior is there it's not you is because back to our foundation here about what this is doing this is a many toone relationship this is designed specifically that you can only look up to one value because by definition it is one to many or many to one in there what you're actually trying to do here is something that's called many to many let's go through what that looks like you've got two options here for how you can handle this idea of having multiple selections the first one is on the screen in front of you here this is called a multi- select Choice column so let me get rid of these values to show you here the formats are available we've got paperback hardback audiobook ebook and you you can see that I can go in and select more than one which is the behavior that people are looking for however can you imagine this works well because the formats are there's only ever four formats I'm rarely going to make a change to that I'm not going to do any slicing dicing reporting I don't need to go now go to Paperback and look at all of the books in paperback it just doesn't make any sense this is a property that sits on this table with a relatively short static list where I want to choose multiple options so multiple choice column there is a perfectly valid response can you imagine if you're trying to create a database of hundreds or thousands of books and you've got thousands of authors that experience there's no sort of it's just a terrible experience that list would go on and on and on every time you have a new author into the mix you'd have to go into the configuration options to change it just don't use that something like this it's good for that use case it's not so good for here now you might notice here I've got look for author one I've actually got a couple of different tables here so that I don't mess around with the data model too much so let's go back into the solution here I'm going to go back into my book table and I'm going to create what's called a many Tom many relationship because books can have many authors and authors can have many books so the one to many theme doesn't make any sense here so I'm going to go in here create a new many to many relationship with the table called author and done if you're coming from a database background you would think of this as having to construct something in the middle to make this work so technically what it's actually doing is creating a many to one and a one to many to join them up this many to many bypasses that whole thing you won't be able to see or interact with that table that sits in the middle it's under the hood so from the front-end experience here it's going to look like those tables are directly connected in this many to many way books have many authors authors have many books I'm going to create some sub grids and show you how all of that works if you needed to do something in the middle like name what type of author they were or something else you would need to sort of create the multiple relationships from each side I'm not going to go there many to many here is as simple as it looks on the box even though there's stuff going under the hood low code you don't need to construct it you don't need to know or worry about it if you're coming from a low code background never mind all of that you've now got many to many relationship so what we're going to do here is come into the form and I'm going to go into my main form here and I'm going to get rid of that look up to the author one table which was just a placeholder so that I could demonstrate the uh alternative and and why and how it doesn't work so we will come in here and click on that and remove it and what I'm going to do is add a subgrid here to see the authors now typically when I have ADD subgrids are like nice sort of long rows but the author is actually just going to be the name I don't need or want any other information in there so I don't want it in a great big wide tab so what we're going to do is come into the components here and add a one column section under here let's call this authors and then we're going to put in here a grid subgrid and we want to check the option here to say just show me the related records and there it is their authors cuz we've created that many to many relationship it can get to it and we click done and again I've got that in the wrong spot drag that down I've got to learn to put my mouse in the right place let's click on that and hide that label so we're looking good let's save and publish that we could also do this the other way around so I could go into the table for author and create a similar subgrid for books so now with this many to many relationship you if you're solving a different problem you're not just going I need to do a multi select in my lookup you need to be thinking about the data model right from the start go back and watch at the start of the video if you're not sure when you're designing your data model is it a lookup is it many to one or is it actually many to many if it needs to go both ways then this is how you need to set it up let's see how we go now with the new layout and Terry and Neil can both be authors of this book so you can see here we've got rid of that author column we'll come in here and we want to add an existing author and now I can look up things but now I can do my multiple select I want Neil gaimon and Terry pratchet and we're going to add them give it a second there we go and then you'll see it's actually fully responsive interface if I zoom it up I've got that nice card view Zoom it down again nice and clean in there what other questions have you got about lookups have I covered all of them let me know in the chat and if you would like to level up even further with your model apps and check out this video here as always thank you so much for your support and thanks for watching

Channel: Lisa Crosbie

Views: 11,923

Keywords: lookup, dataverse power apps, microsoft dataverse, power apps, powerapps, model driven powerapps, model driven powerapps tutorial, model driven power apps, dataverse relationships

Id: ICLGQ1cIo9M

Length: 36min 1sec (2161 seconds)

Published: Mon Nov 13 2023