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

Dataverse Lookup columns are how you create a proper relationship in your dataverse table. In this video, you will learn what are relationships, why Many-to-one, One-to-many, and Many-to-many aren't so scary, and how to create the relationship. With your table structure in place we then go to Power Apps and update the relationship with Forms and Patch. Finally, we use edit data in Excel as the final tool to maintain these powerful relationships. And don't worry we also talk about viewing the data with polymorphic relationships. Much cooler than it sounds. Also, because most people come at this with a SharePoint background we do some compare and contrast between the two platforms.

0:00 It starts
1:58 Create a Dataverse table to use as our lookup table
4:11 Use edit data in excel to quickly populate the new table
5:43 Add a PowerApps Dataverse Lookup column to the table and connect it to the new table
6:41 Explanation of a lookup or relationship between two tables
8:01 Dataverse Add Relationship types Many-to-one, One-to-many, and Many-to-many. N:1, 1:N, N:N
12:58 Brief overview view of a Many-to-many relationship.
14:56 Create a PowerApps Canvas app to explore the relationship from the app side
16:30 Use a form to update a dataverse lookup column from power apps
17:54 Configure the gallery to display values from a lookup column via the polymorphic lookups
19:52 Use the Patch function to create a record with a lookup column via a dropdown or a lookup function
24:25 Using the GUID primary key for the lookup
25:49 Overview of the Relate function and Unrelate function
26:33 Filtering on the Dataverse Lookup column
27:38 Use Edit data in Excel to modify a Lookup column

[smilebreathegoslowly]

I've worked as a software engineer for over a decade and I totally disagree that many to many relationships are not common. They are very common, and if you are going to be a top notch power app developer then you should know how to identify them. I would agree that many apps don't use them when they should and that can make the app clunky. So, if you want to be a cut above, then know how to extract the info to identify when they are needed. For example, one student can have many classes and one class can be attended by many students. Thus, you need a student class join table that sits between the student and class table. One book can have many authors, and one author can have many books. Thus, you need a book author join table. These are not "magical" tables. Maybe power apps abstracts the join table away from our worries, but at the least you should understand this concept. And understand that many to many relationships are often needed if you want to maximize the efficiency of your app and how easily you can query and sort data.

[Saadssk]

Thanks so much Shane Dataverse videos are much appreciated. i feel like this video was specifically created for me. cant wait for many to many episode of dataverse. also, would like to see a full video of why you don't like forms even tho forms are much simpler.

[mebanet]

Dude!!! You just saved me so many months of time. I was learning Python, PyQt5, MySQL…. and I can do everything I trying to do just this simple.

[franknielsen]

This video was NOT one second to long!!!👌👌👌 Just a quick question, why are you not that big fan of forms???

[damianchrystie]

Thanks Shane awesome. Using patch with a lookup is so simple, but with many other not getting the syntax correct. Even ChatGpt had it wrong!

[HKRC-qchk]

Cant wait for the Many to Many episode of Dataverse in both Canvas & MDA scenarios

[ShaneC]

Thanks. This was extremely helpful. Now I need to see how to create a many to many relationship by building the intersect table manually so it can have it's own columns/metadata associated with the relationship. For example... an N:N relationship between Projects and Employees where I can track each employee's role in relation to each project they are assigned to.

[carlosenriquecontrerasespi]

Shane great job!!! thanks....now I am getting a message: "Error whwn trying to retrieve data from the network" I got a lookup function with just two conditions, any help :( ?

[GarryWatson]

Great stuff Shane. Enjoying your sessions immensely. Keep it going!
Garry

[JD_JR]

This is super helpful! I have my relationship and am using a model driven app.
But I cannot figure out how to populate the lookup column by default to show in a view in my app.
In Power BI for example, I just make the relationship and I can lookup values related from other tables.
But in Power Apps / Dataverse, it appears I have to "fill" every relationships column with a primary key as you did at the end to make them match.
My list will update constantly so automation is mandatory. But perferrably, I would want it to not be this complex or require any automation at all.

[fzawadi]

Great Video. Liked the super hint by using GUID column at the end. 👍

[aneismchardy]

This was great and so helpful... as are all of your video's. I'm looking forward the the many-to-many discussion! I am in the process of moving a list from sharepoint to DV because of the multiple many to many relationships in it. It is a person list where the people can belong to 1 or more "working groups" as well as "committees". I plan to add a canvas app to a power portal that lists the users and allows filtering by one or more of said WGs and SubCs. That sounds *complex but* doable right?? I would normally do this in a relational DB but I am determined to leverage the MS Power Suite and not convert data back and forth all over the place.

[Father_Prime]

Hi Shane,

Thank you for this video - it is extremely helpful in understanding the how Lookup columns truly work.

Would it be possible to get a video (or short) where you show how both of the tables here were populated with Data? I'm hoping to gain some insight into how to design/populate the initial table (Employees) in order to flow into the table being looked up (Departments).

Thanks again!

[juandavila]

Thank you, a very very useful video! I am trying to work with dataverse table, but there are a lot misteries for me hahahaha
Thanks again

[benjaminrutledge]

Was there going to be a Many-to-Many focussed video coming? I wasn't able to find it on the channel, and I really need some guidance.

[Ricardo]

I think that the many to many relationship would be like the sharepoint lookup column allowing multiple values.

[gates]

Very good video. Thanks.

My question: is it possible to create relationships between dataverse tables already have data in it. In other words, can we create relationships after importing data to tables?

Thank you in advance.

[raumkult_sichtbeton]

Hello Shane, Thank you for the great video, and all videos in general!! These really help me move forward with my project. I still have a question about this video because that was my big hope when I found it. Unfortunately I can't get it to work and I'm at a loss as to how I can filter the gallery using a dropdown with the relationship column. In the case of the video, basically with the Department Name column. I need this so often in my app and I can't get it to work. Do you perhaps have a tip for me? I would be very grateful to you for that!! Many greetings Kai from Germany

[derrickhall]

Excellent walk through, supplements the Microsoft material perfectly!

[colindunbar]

Great Video Shane, you mentioned not being a fan of forms, what do you use instead? Cheers, Colin