PowerApps Dataverse Lookup columns and Relationships - Create, Edit, View
Vložit
- čas přidán 25. 07. 2024
- 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.
Power Apps Consulting and training at www.PowerApps911.com
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 - Věda a technologie
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.
Awesome!
Great Video. Liked the super hint by using GUID column at the end. 👍
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.
This video was NOT one second to long!!!👌👌👌 Just a quick question, why are you not that big fan of forms???
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!
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.
Yes, I need to do that video on why Forms are not my friend. 😁
Another amazing video, thanks!
Man thank you sooo much. Really Great Video
Shane, thank you so much for what you do! Your videos are great and so helpful!!!!!
I have created my relationship and can filter and view parent/child data. I am struggling with the groupby function. The lookup column is not an option and therefore I am unable to group by any of the lookup record data. What am I missing? Can you help me?
thanks again for being you and doing what you do. I appreciate you!
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!
Glad to help. 😀
Great stuff Shane. Enjoying your sessions immensely. Keep it going!
Garry
Thanks, will do!
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
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.
...and on the eight day...crosswalks were invented because they just make sense.
Hi Shane... Another dataverse related question. I have a dataverse table that has a column that looks up values from another dataverse table (to create choices). The app attached, shows the choices available in the form experience just fine. I would like to have a default value of "New" in a dropdown when a new record is being created so that the least number of fields needs to be manually changed. I have tried several approaches which work in sharepoint well, but the dataverse version has be stumped. I've tried the IsBlank() approach, however when I use this in an If() it errors out saying it needs a valid value. I'm guessing somewhere I need to get the info from the table that holds the choices rather than try to populate the main table with a text field? It's becoming quite the conundrum.. Any tips?
Shane, thanks for your video and all your videos for that matter. I've found them to be very helpful in getting my feet wet in the PowerApps world. I'm using Dataverse tables in a Model-Driven app (prototype at the moment, soon to be more an MVP) and at 6:15 the thing that is not clear to me is, if I don't want to use the default 'Primary Column' (which is always a text field), as the lookup column I display (say in one of my 'Views'), how do I change that setting? I'm probably misunderstanding the way primary column is set but to me changing the name of the primary column does not change the primary column function so if I had a multi-line text field that I was trying to pull through to another view (as opposed to say the 'Name' field), how would I set the multiline text as the default field? What I've ended up doing thus far is using 'Related' to specify the field that displays in my 'Views'. The problem I'm having with that however is that after I've a few of them (let's say 5 of these 'related' fields), I keep getting an error when trying to add more 'related' fields to 'View'. Any suggestions and or help you can offer on this is much appreciated!
Hi Shane... A question about the relationship side. I have two tables one with product and price, the second with the order for the product. I can perform the lookup to populate the product and if I enter the price manually can have a column that calculates the total on the fly (calculated/rollup). I can't see how to have the price auto populate into a column in the order table. I was expecting something like being able to use lookup feature in the Excel sense of looking up the product entered in the order in the products table, then returning the price column value.
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.
Hey Shane. That totally makes sense. I haven't made any content like that, but I get what you want to do. I will put on my list to ponder on.
Funny enough, we talked about that in our Model-driven/Dataverse class yesterday. 😎
Cant wait for the Many to Many episode of Dataverse in both Canvas & MDA scenarios
It will be crazy. 😬
Great stuff Shane Young!
Thank you. Have a good day!
Hi Shane, is there a way to bulk update the LookUp Column from dataverse using patch only? I tried the same approach from SharePoint List however, it does not update the respective value
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.
Thanks, @ShanesCows - you're a lifesaver! Thanks for your videos! Now how do we delete a lookup column? Thank you much!! I find myself entangled in relations disallowing to delete a lookup column and yet not being deleteable themselves. What a doozy! I need to delete a bunch of tables!????
Great video. I have a question regarding relationships. I have two tables Product and Region. I created a model driven app, when i have a region form where i can create region and below that a section where i can add products. So I can see all products within a region. But where is this relationship stored within Dataverse. If i downbload both tables i dont see any column refering to Products when i open excel of region? So how can i find that relation. I need that within my sql server for other purposes
Hello Sir,
Is there a way we could load unique values in lookup field
Thanks so much for your video. Can you create a clone of a table. Like create a duplicate of user table with only what I need
Excellent walk through, supplements the Microsoft material perfectly!
Glad it was helpful!
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 :( ?
Carlos sometimes Power Apps does that because it is having a bad day. Sometimes because your formula isn't right. Remove one of the conditions. Does error go away? Then you know which condition is causing the issue so you can try to troubleshoot.
Hi Shane - wondering if that many to many relationship video is available?
can you change the primary column after creating the table. is their a way to display any column you want from look up table.
The best!!
Thanks! 🤩
Thanks for the great videos on Power Platform Shane! I have a question about relationships... if you have two tables which has key columns before the relationship is creates (similar to what you are showing at 6:52 with Department ID key column in both tables), how do I create a relationship between tables? When I try to create a relationship, dataverse wants to create a new column in related table which doesn't make sense to me because the data already exists in the Department ID column! If I try to create a relationship anyway, I get an error "An attribute with the specified name already exists". How can I tackle this?
Hmmm. That I am not sure about. If you look at your Table there is a section for Keys. I wonder if you could define the key their manually or not. I don't know.
I do have exactly the same issue, could you find any solution? Thanks
@@pwagma Probably not the best way to go about it, but I ended up leaving tables as is without defining relationships and manage it in PowerApp through LookUp and other ways to connect the data in the app. You can always create relationship using lookup columns and name the lookup column something that doesn't create the conflict.
@@bhargavshukla Yes I am doing the same, but when I want to filter through a whole group (For example, all the items in Table A, that in Table B they have a column with X information, so you filter between 2 tables) I am having some delegation issues, that I would like to have a definitive fix.
I have a Model Driven app and when you click a button I run a Power Automate Flow
I have two tables with a many to may relationship between them
Requests and Candidates
When the user click the button Power Apps send the request Guid to the flow
Now I need a way to find all the candidates related to the request Guid. List rows does not work because you cant see the table in the dropdown
How do I do that?
Great Video Shane, you mentioned not being a fan of forms, what do you use instead? Cheers, Colin
I prefer to put individual controls on the screen and use Patch to save them. This older video will show you, I need to do a newer one. :) czcams.com/video/MclJ_O9HvJQ/video.html
Hi Shane, thanks for this wonderful video to explain the relation ship.
Do you have any snowflake relation ship videos on dataverse. ?
I don't. Sorry.
Hi! wondering if you've made the separate video for many to many relationships in PA?
I have not. Just not a very popular topic so it hasn’t made it up the list. 🫤
Hi Shane. I already have 2 distincts tables "Customer" and "Contract". As the 2 tables come from an excel, there is a common value between the 2 tables but not a real a GUID to link them. Is that possible (and How) to polute related links afterwards ?
You can do a LookUp on any table with any column. The LookUp function will find the first matching record. So you should be okay.
I have the following two related tables in Excel:
1. Driver (Columns: Driver_ID, Driver_Name)
2. Drops (Columns: Driver_ID, Date, No_Of_Drops)
Driver_ID is the primary key to the first table (Driver) and a foreign key to the second one (Drops). I inserted a gallery in my Power Apps app and set the gallery's datasource to Drops. When I associate the formula ThisItem.Driver.Driver_Name to the gallery's title field, it does not work. Requesting for help with this problem.
Hey Shane,
I really like SQL. Can I connect to dataverse and write SQL code to create tables?
I think only read only. docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query I have not tried it before though.
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
Filter(List, LookUpColumn.Value = Dropdown.Selected.Value)
The key is when using a Lookup column in a filter you need to add that .Value to it. Hope it helps
@@ShanesCows Hello Shane, thank you very much for the quick reply. Unfortunately it does not work. Value and = are underlined in red. But thanks for your help anyway, I'll find a solution at some point. Maybe the formula is different when using Dataverse. Best regards, Kai
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.
I want to be clear. Tables have data and columns need to be linked are also have data.
Something unexpected occurred. The Video Department table doesn't show up in the list of 'Related table'... any thoughts?
Hey Shane, Thanks for the superb tutorial. A dumb question may be, please help me understand -> How does Dataverse know at runtime which column in the child table will map to the corresponding parent table? Dataverse is just asking us which table to perform lookup into against the lookup column in the parent table, however , how does it know which column in the child table will have the value matching with that in the parent table's lookup column ?. Generally in SQL databases, we specify a specific column in the parent and child table for the mapping, right ?
It does the match based on the unique id which is a GUID column. It displays the Primary name column when looking at the relationship.
@@ShanesCows I still dont get it. If I have GUID column in the two tables, how one connects with the other?
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.
I don't have one. Honestly we so rarely use Many to Many I never came up with a good video. You could try sendig your question to the free support at www.powerapps911.com and see if someone doesn't have some help for you.
Hi Shane, what if your two tables already have data to be used for a relationship? For example - You have Video Employees and Video Department tables. A Video Employee HAS 1 Department. My data in dataverse reflects this by having employee code as a column in Video Departments and Video Employees. How do I set this relationship up?
This seems like the normal situation. We already know Bob is in the video Department, he already has a manager, someone isn't filling that into our form. How do you set this relationship up so the form contains fields from both Employees and Department, so you can set up filters by department manager and what not.
Very helpful!. Hoping for Model Driven app to dataverse connection pls.
I need to do Model Driven. I do, I do!
Great! So what would this be: Many persons (table a) apply to multiple tasks (table b). From table b we want to see which tasks are chosen by one, many or noone in order to decide who in table a does what.
You need a 3rd intermediate (JOIN) table to connect items between tables.
Interesting video.
I’ve just started yesterday with PowerApps!
I’m currently looking for way to create Primary column that would be a formula (value concatenation from two mandatory fields which are lookups). Do you know if it possible? Errors messages aren’t informative (please try again later…).
I don’t believe that it is possible. Primary column can only be text.
@@ShanesCows , thank you for answer. My idea was to limit the user inputs and serialize values used further down the relationship trees.
Just abit confused Shane if you create a lookup column between two tables and there’s no records how does dataverse know what relationship type it is
Wild you please make a Video for the multi filter when the data sources is data verse
And thanks in advance
What do you mean multi-filter? I have a few different videos on that already. czcams.com/video/kLGglidmPxg/video.html czcams.com/video/YYizaX6gXW0/video.html Both videos use SharePoint but nothing changes because you use a different data source.
@@ShanesCows thnanks
Hi Shane... did you make the many to many video? I can't find it!
I have not. Sorry. We were actually chatting about it today. Still not sure when I will cover it. 🙃
Can anyone point me to a tutorial on doing this but with SQL tables that don't have these build in advantages of DataVerse?
Is dataverse better than SharePoint? If so, how come?
I think that the many to many relationship would be like the sharepoint lookup column allowing multiple values.
Yes and no. SharePoint stores those multiple selections as a table of records in the parent. So thing of it as letting you create a bunch of many-to-one connections to a single table. The children dont' know about their connection back to the parent. In Dataverse there is a hidden table that keeps all of the many-to-one connections BUT it keeps them in both directions. So the children know all of their parents. So kind of the same but technically very different. It will be one of the harder things to explain when I make that video. 😊
@@ShanesCows This is very helpful, I am more interested towards many-many relationship and how do we utilize this in the column. Just like the way you are explaining on choosing the multiple sports to a single person. Do you have any video created for this ?
Pls show how to connect ms access to dataverse in power app, such that ms access will be front end. Pls educate us. Thanks
powerapps.microsoft.com/en-us/blog/announcing-power-apps-microsoft-access-security-mobility-new-experiences/ Check out this feature. It is in preview.
Maybe a video on how to do this when you have multiple many to one tables to drill through to get a value onto a gallery
Is there an easy way of deleting all of the records in a Dataverse Table?
Not that I know. If it is a one time thing you could use Edit in Excel and delete all of the rows. If ongoing PowerApps can do RemoveIf(Table, true) and that should delete all. Deletes are usually pretty slow.
Am I correct in saying that CDS is a multi-collaboration Database similar to how many people can work live on the same Excel document? eg. not just sharing table structure but the data in them - so if say you had a Medical software package and a Finance package built off the CDS and both mapped to the same table called 'person' then if you changed the details of a person in one of the frontends it would instantly change in the other also? That is amazing stuff if it is. One source of truth.
Why don’t you like forms? Do you have a video about it?
Dave I talk about it in my best practices video. czcams.com/video/WUy2IaTa56g/video.html
Please help. I created a form from scratch in powerapps. I want to send it for a mult- approval and save the outcome on excel online. Also, update it. Can you help? Have been following your videos and i believe you can do this with your level of professionalism.
My request has been 2week old. But no response. Where are you Shane?
Where is the Many-to-Many video?
I haven't made one. We don't use it very often in the traditional sense so I haven't made it, sorry.
Patch Lookups using AADUsers in Dataverse will not work by just referencing the fieldname.Selected method.
For example, if you are using AADUsers and are potentially tying a dropdown to an AAD User Group (to limit the users in the dropdown) you can't just say:
{SomeLookupColumn: DataCardValue1.Selected}
While this will work with some Lookups, it all depends on the Type of lookup it is. In these cases I use a LookUp in the Patch Field:
{SomeLookupColumn: LookUp('AAD Users', 'Display Name'=DataCardValue1.Selected.displayName)}
** Assuming your dropdown is using Display Name **
*golf clap*
🤗 😘
When I utilized the Dataverse table for a lookup, it made the app require Premium licenses. You should warn people that will happen.
Hi Shane,
congrats for the video. I just started using Canvas and Dataverse.
I wanted to ask you if it is true that relationships have only one level: in practice, do you create a relationship table1 => table2 => table3 ?
I ask you this question because I saw another video [ czcams.com/video/d5ZsgLzUGNw/video.html ] which explains how it is not possible.
You can have multiple levels the thing that doesn’t work is polymorphism. Record.RelatedRecord.AnyColumn Works 😍
Record.RelatedRecord.RelatedRecord.AnyColumn Does Not Work 😑
@@ShanesCows Thank
Shane!
wut wuut
👍
Hi Shane, thank you for the videos.... I've created a view in Dataverse and I'm trying to populated my dropdown in power apps using one of the columns from my view and I'm struggling to get it right please help here is my formula Distinct('Travel GLS (Views)'.MyView.MyColumn)
Works fine with my table but not the view.
Distinct('Travel GLS (Views)'.MyView, MyColumn) Shouldn't that be a comma? But I have never tried with a view so not 100% sure.
Im doing a custom field submission and using a list as the selection criteria for the department i am updating and this keeps telling me "Network error, must be a valid record"
Patch(
Budgets,
Defaults(Budgets),
{
Name:TXT_nameOfBudget.Value,
Description:TXT_descriptionOfBudget.Value,
Assigned_Capital_Budget:Value(TXT_CAPEX.Value),
Assigned_OPEX_Budget:Value(TXT_OPEX.Value),
'Financial Year':TXT_FinancialYear,
'EPMO ID': LookUp('EPMO''s', pag_prm_EPMO = EPMO_List.Selected.pag_prm_EPMO)
}
)
TextInput.Text you need to specify the output not just the control name. This video might help czcams.com/video/W8JjF2k0IQA/video.html
HI Shane, I just started using Dataverse as source, I noticed that the lookup column is only showing the default Name, is there a way to get 2nd or 3rd columns from another table with loookup?
The primary column is the only one shown for the relationship. But if you in a gallery you can do. ThisItem.LookupColumnName.OtherField to get what you want.