Awesome, thank you Shane for making this video after my last post on your other video around relationships. What it tells me is that I’ve taken the right approach to my Many To Many solution, using exactly the same approach. It was quite funny but as I watched the video, I knew almost exactly as to what you were about to say 🤪. As an aside my app is all around risk management, and the many to many relationship is between a Risk and its possible Treatments, with treatments either coming from a shared pool of treatments, that can be applied to any number of risks, or as a specific treatment for a specific risk. Thanks again!!
Hey Shane! I would just like to add a case for your viewers to think about when picking a suitable many-to-many relationship table. One great advantage of the built-in many-to-many relationship in Power Pages is how it enables seamless portal-based data access for users. For instance, in a case where employees need access to training programs across various departments, each department might offer multiple programs, and each employee might be enrolled in several programs. Power Pages only recognizes and allows access to rows connected through the built-in many-to-many relationship. We recently implemented this for a large organization with around 3,000 users who needed permissions to view and edit their information. Relying on Power Apps licenses for each user wasn’t feasible due to licensing costs, so this approach provided an efficient solution.
Is it necessary to manually create the Mapping ID column and generate a GUID for it with the GUID function? Won't the mapping table already have a unique ID column by default that generates a GUID when a new record is created?
Yes, each row will automatically have a unique identifier GUID generated but that is one per row. In this case we are creating another GUID Mapping ID that we use to relate the haul. So if Dennis gave us 3 pieces of Candy we need all 3 of them to relate back together. We use that GUID to do that.
Great video Shane , at 11:26 when you mention As MYCANDY in the formula for my understanding can you explain abit more why you used As MYCANDY instead of just using ‘Halloween candy’ please
Sure. So anytime I am using ForAll I always do As MyCandy or something similar. You don't have to. But what happens now is when I am referencing fields from the record I am looping through in the ForAll there is no confusion MyCandy.Field1 is Field1 for the record in the ForAll. Very helpful in cutting down on any chance of confusion. Hope that helps.
So with all the lookups, how does app performance take a hit when the datasets get larger? Would it be better to create filtered collections so only one network hit happens rather than record by record?
Fabulous question. There is no right answer here but things I have: 1 - The table you are looking up against for names is usually small enough you can put in a collection and then lookup locally. 2 - Sometimes I add the column like house name to the mapping table 😲 DBA hate it but makes Power Apps happy. 3 - Get even more creative, like you are thinking.
@@ShanesCows I'd love to see a video where you have a model driven app that has a column that is using the Microsoft Entra ID lookup that allows you to pick more than one person from Active Directory, so it's a many to many using dataverse and AD
To solve this I’ll put the Mapping table in a Named Formula and add to it 2 lookup column containing the record of house and candy. Now i got a large table containing all the informations
I am sure someone will be happy to give you a long, technical explanation of why this is better BUT... if what you have built is working then I am all for it. I too have also done some really goofy storing the data in a column solutions. It is about finding what fits your needs best. 👻
Wouldn't it be cool if a container could hold a record, so the container does a lookup. Then all labels, Text inputs or images...inside the container would refer to ThisItem. ...
Awesome, thank you Shane for making this video after my last post on your other video around relationships. What it tells me is that I’ve taken the right approach to my Many To Many solution, using exactly the same approach. It was quite funny but as I watched the video, I knew almost exactly as to what you were about to say 🤪. As an aside my app is all around risk management, and the many to many relationship is between a Risk and its possible Treatments, with treatments either coming from a shared pool of treatments, that can be applied to any number of risks, or as a specific treatment for a specific risk. Thanks again!!
Awesome. Glad there were no surprises. Makes me feel even better that this was a straightfoward answer to such a tricky topic. 🎃
Hey Shane!
I would just like to add a case for your viewers to think about when picking a suitable many-to-many relationship table.
One great advantage of the built-in many-to-many relationship in Power Pages is how it enables seamless portal-based data access for users. For instance, in a case where employees need access to training programs across various departments, each department might offer multiple programs, and each employee might be enrolled in several programs. Power Pages only recognizes and allows access to rows connected through the built-in many-to-many relationship. We recently implemented this for a large organization with around 3,000 users who needed permissions to view and edit their information. Relying on Power Apps licenses for each user wasn’t feasible due to licensing costs, so this approach provided an efficient solution.
Awesome. Thank you for sharing. That is a perfect example of when you do have to use the built in Many to Many. ⭐
Homeboy talking about "Concats", I want some "Kit-Kats"!
🤣 This is the best comment of the month! 🤣
Very interesting thanks Shane, I suggest example in using for all 😊
🤩 Have a great week!
Is it necessary to manually create the Mapping ID column and generate a GUID for it with the GUID function? Won't the mapping table already have a unique ID column by default that generates a GUID when a new record is created?
Yes, each row will automatically have a unique identifier GUID generated but that is one per row. In this case we are creating another GUID Mapping ID that we use to relate the haul. So if Dennis gave us 3 pieces of Candy we need all 3 of them to relate back together. We use that GUID to do that.
@@ShanesCows Oh Ok. I re-watched it. Now I get it. Thanks
Great video Shane , at 11:26 when you mention As MYCANDY in the formula for my understanding can you explain abit more why you used As MYCANDY instead of just using ‘Halloween candy’ please
Sure. So anytime I am using ForAll I always do As MyCandy or something similar. You don't have to. But what happens now is when I am referencing fields from the record I am looping through in the ForAll there is no confusion MyCandy.Field1 is Field1 for the record in the ForAll. Very helpful in cutting down on any chance of confusion. Hope that helps.
@ Ah great stuff thanks for all your help
Thank you Shane
You got it! 👍
So with all the lookups, how does app performance take a hit when the datasets get larger? Would it be better to create filtered collections so only one network hit happens rather than record by record?
Fabulous question. There is no right answer here but things I have: 1 - The table you are looking up against for names is usually small enough you can put in a collection and then lookup locally. 2 - Sometimes I add the column like house name to the mapping table 😲 DBA hate it but makes Power Apps happy. 3 - Get even more creative, like you are thinking.
@@ShanesCows I'd love to see a video where you have a model driven app that has a column that is using the Microsoft Entra ID lookup that allows you to pick more than one person from Active Directory, so it's a many to many using dataverse and AD
To solve this I’ll put the Mapping table in a Named Formula and add to it 2 lookup column containing the record of house and candy. Now i got a large table containing all the informations
@@MrMal0w So does that look something like this: FormulaName = Filter(MappingTable, FilterColumn=Gallery1.Selected.Column);
Is there something that this solution provides over just saving the respective collections as a json string in a column on the record?
I am sure someone will be happy to give you a long, technical explanation of why this is better BUT... if what you have built is working then I am all for it. I too have also done some really goofy storing the data in a column solutions. It is about finding what fits your needs best. 👻
Wouldn't it be cool if a container could hold a record, so the container does a lookup. Then all labels, Text inputs or images...inside the container would refer to ThisItem. ...
I have never thought of that. This is a crazy good idea. 😎
I'd like to think i influenced one of your charity names 😁😁
Perhaps 🐸
Or Dataverse could just give us a no-SQL data table or column type (JSON) . 😈