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.
@StephenSchusterE
3 ай бұрын
...and on the eight day...crosswalks were invented because they just make sense.
@mebanet
2 жыл бұрын
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.
@ShanesCows
2 жыл бұрын
Awesome!
@Saadssk94
2 жыл бұрын
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.
@ShanesCows
2 жыл бұрын
Yes, I need to do that video on why Forms are not my friend. 😁
@damianchrystie4384
Жыл бұрын
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!
@ShanesCows
Жыл бұрын
Glad to help. 😀
@franknielsen3219
2 жыл бұрын
This video was NOT one second to long!!!👌👌👌 Just a quick question, why are you not that big fan of forms???
@HKRC-qc4hk
Жыл бұрын
Cant wait for the Many to Many episode of Dataverse in both Canvas & MDA scenarios
@ShanesCows
Жыл бұрын
It will be crazy. 😬
@ShaneC0271
Жыл бұрын
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.
@ShanesCows
Жыл бұрын
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. 😎
@carlosenriquecontrerasespi4266
Жыл бұрын
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 :( ?
@ShanesCows
Жыл бұрын
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.
@fzawadi
2 жыл бұрын
Great Video. Liked the super hint by using GUID column at the end. 👍
@GarryWatson111
2 жыл бұрын
Great stuff Shane. Enjoying your sessions immensely. Keep it going! Garry
@ShanesCows
2 жыл бұрын
Thanks, will do!
@anonymous-user
Жыл бұрын
Great stuff Shane Young!
@ShanesCows
Жыл бұрын
Thank you. Have a good day!
@JD_JR
7 ай бұрын
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.
@juandavila4035
2 жыл бұрын
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
@aneismchardy7097
2 жыл бұрын
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.
@PhilippedePerthuis
9 күн бұрын
Hi Your channel is great very clear. So and I have a question. I have 3 tables Students with a many to many relationship to Sessions which has a many to many relationship with Calendar. And I would like to be able to select an item in Calendar and display all the students attending. I mean all the students in all the sessions related to this item in Calendar. I cannont find a way to do that. Could you help. That would be great.
@andresperez1116
2 жыл бұрын
Man thank you sooo much. Really Great Video
@Malhosainy
2 жыл бұрын
Another amazing video, thanks!
@derrickhall8791
2 жыл бұрын
Excellent walk through, supplements the Microsoft material perfectly!
@ShanesCows
2 жыл бұрын
Glad it was helpful!
@benjaminrutledge3692
4 ай бұрын
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.
@ShanesCows
4 ай бұрын
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.
@laurentalphonseferreira5251
Жыл бұрын
The best!!
@ShanesCows
Жыл бұрын
Thanks! 🤩
@ABCD-ABCDEFGH
6 ай бұрын
can you change the primary column after creating the table. is their a way to display any column you want from look up table.
@Father_Prime
2 жыл бұрын
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!
@alexsegal5397
Жыл бұрын
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!????
@Ricar91do
2 жыл бұрын
I think that the many to many relationship would be like the sharepoint lookup column allowing multiple values.
@ShanesCows
2 жыл бұрын
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. 😊
@swaroopvuppala
2 жыл бұрын
@@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 ?
@sconsalter
9 ай бұрын
Something unexpected occurred. The Video Department table doesn't show up in the list of 'Related table'... any thoughts?
@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
@ShanesCows
Ай бұрын
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
@raumkult_sichtbeton
Ай бұрын
@@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
@gates1978
6 ай бұрын
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.
@gates1978
6 ай бұрын
I want to be clear. Tables have data and columns need to be linked are also have data.
@mardithpascua8112
2 жыл бұрын
Very helpful!. Hoping for Model Driven app to dataverse connection pls.
@ShanesCows
2 жыл бұрын
I need to do Model Driven. I do, I do!
@blaschkesita
2 ай бұрын
Hi Shane... did you make the many to many video? I can't find it!
@ShanesCows
2 ай бұрын
I have not. Sorry. We were actually chatting about it today. Still not sure when I will cover it. 🙃
@johnbrennan8442
Жыл бұрын
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
@user-pf3gc1sy5s
Жыл бұрын
Hi! wondering if you've made the separate video for many to many relationships in PA?
@ShanesCows
Жыл бұрын
I have not. Just not a very popular topic so it hasn’t made it up the list. 🫤
@tonykuiper9127
2 жыл бұрын
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.
@mardithpascua8112
Жыл бұрын
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?
@ShanesCows
Жыл бұрын
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.
@maanalshraky1
2 жыл бұрын
Wild you please make a Video for the multi filter when the data sources is data verse And thanks in advance
@ShanesCows
2 жыл бұрын
What do you mean multi-filter? I have a few different videos on that already. kzitem.info/news/bejne/zIJ9zaCfnZ-Bq5w kzitem.info/news/bejne/uo-f4JWOb5mJimU Both videos use SharePoint but nothing changes because you use a different data source.
@maanalshraky1
2 жыл бұрын
@@ShanesCows thnanks
@kalpeshpatil8521
Жыл бұрын
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 ?
@ShanesCows
Жыл бұрын
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.
@anacarolgomesr
10 ай бұрын
@@ShanesCows I still dont get it. If I have GUID column in the two tables, how one connects with the other?
@fredbarnes196
Жыл бұрын
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
@davekjellmarong9381
Жыл бұрын
Why don’t you like forms? Do you have a video about it?
@ShanesCows
Жыл бұрын
Dave I talk about it in my best practices video. kzitem.info/news/bejne/uIuvmH2XjZNmaZw
@colindunbar3927
2 жыл бұрын
Great Video Shane, you mentioned not being a fan of forms, what do you use instead? Cheers, Colin
@ShanesCows
2 жыл бұрын
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. :) kzitem.info/news/bejne/rpmisJOFcnqnfYY
@dereckmarchinski5584
2 жыл бұрын
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!
@rjobaanable
Жыл бұрын
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
@bhargavshukla
2 жыл бұрын
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?
@ShanesCows
2 жыл бұрын
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.
@pwagma
2 жыл бұрын
I do have exactly the same issue, could you find any solution? Thanks
@bhargavshukla
2 жыл бұрын
@@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.
@pwagma
2 жыл бұрын
@@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.
@minocat23
2 жыл бұрын
Hi Shane - wondering if that many to many relationship video is available?
Жыл бұрын
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 ?
@ShanesCows
Жыл бұрын
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.
@DataisKing
Жыл бұрын
Is there an easy way of deleting all of the records in a Dataverse Table?
@ShanesCows
Жыл бұрын
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.
@maritheron3150
Жыл бұрын
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?
@damn68
Жыл бұрын
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
@saveeshmply
Жыл бұрын
Hi Shane, thanks for this wonderful video to explain the relation ship. Do you have any snowflake relation ship videos on dataverse. ?
@ShanesCows
Жыл бұрын
I don't. Sorry.
@TheOmegaAlfa
2 жыл бұрын
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…).
@ShanesCows
2 жыл бұрын
I don’t believe that it is possible. Primary column can only be text.
@TheOmegaAlfa
2 жыл бұрын
@@ShanesCows , thank you for answer. My idea was to limit the user inputs and serialize values used further down the relationship trees.
@briankgarland
9 ай бұрын
Can anyone point me to a tutorial on doing this but with SQL tables that don't have these build in advantages of DataVerse?
@ivanmathias955
2 жыл бұрын
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.
@akokohjoel9854
2 жыл бұрын
Pls show how to connect ms access to dataverse in power app, such that ms access will be front end. Pls educate us. Thanks
@ShanesCows
2 жыл бұрын
powerapps.microsoft.com/en-us/blog/announcing-power-apps-microsoft-access-security-mobility-new-experiences/ Check out this feature. It is in preview.
@dopeset2
2 жыл бұрын
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!
@louisbertini
2 жыл бұрын
Hey Shane, I really like SQL. Can I connect to dataverse and write SQL code to create tables?
@ShanesCows
2 жыл бұрын
I think only read only. docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query I have not tried it before though.
@StephenSchusterE
2 жыл бұрын
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 **
@nehahiranmayeshrehari2030
2 жыл бұрын
Hello Sir, Is there a way we could load unique values in lookup field
@tonykuiper9127
Жыл бұрын
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?
@joenice242
2 жыл бұрын
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.
@bschraders
2 жыл бұрын
You need a 3rd intermediate (JOIN) table to connect items between tables.
@chris8534
2 жыл бұрын
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.
@AdamHaas
6 ай бұрын
Where is the Many-to-Many video?
@ShanesCows
6 ай бұрын
I haven't made one. We don't use it very often in the traditional sense so I haven't made it, sorry.
@bdrowe
6 ай бұрын
When I utilized the Dataverse table for a lookup, it made the app require Premium licenses. You should warn people that will happen.
@alysonmendes8238
2 жыл бұрын
Is dataverse better than SharePoint? If so, how come?
@PatrickBellWebDev
Жыл бұрын
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?
@rodrsturge663
Жыл бұрын
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.
@angrybatvoice
2 жыл бұрын
*golf clap*
@ShanesCows
2 жыл бұрын
🤗 😘
@adewunmiakinyode4924
2 жыл бұрын
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.
@adewunmiakinyode4924
2 жыл бұрын
My request has been 2week old. But no response. Where are you Shane?
@michelelomonaco5118
Жыл бұрын
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 [ kzitem.info/news/bejne/xWuQ2ZuCs4d4gaw ] which explains how it is not possible.
@ShanesCows
Жыл бұрын
You can have multiple levels the thing that doesn’t work is polymorphism. Record.RelatedRecord.AnyColumn Works 😍 Record.RelatedRecord.RelatedRecord.AnyColumn Does Not Work 😑
@michelelomonaco5118
Жыл бұрын
@@ShanesCows Thank Shane!
@ybunsee
Жыл бұрын
wut wuut
@ShanesCows
Жыл бұрын
👍
@MrTmapp
Жыл бұрын
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) } )
@ShanesCows
Жыл бұрын
TextInput.Text you need to specify the output not just the control name. This video might help kzitem.info/news/bejne/uG6A0HpopGJ6hHY
@mtamitolanga
Жыл бұрын
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)
@mtamitolanga
Жыл бұрын
Works fine with my table but not the view.
@ShanesCows
Жыл бұрын
Distinct('Travel GLS (Views)'.MyView, MyColumn) Shouldn't that be a comma? But I have never tried with a view so not 100% sure.
@mardithpascua8112
Жыл бұрын
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
Пікірлер: 119