Junction Tables: Create a Simple Table as a Replacement to Many-To-Many Relationships

Learn with us how to create a simple table as a replacement to many to many relationships. Many to Many relationships are GREAT! .....until you want to store additional information about the 2 related records..... that's where Junction Tables come into play! Learn how to create a simple table to "join" 2 records from separate tables, and how to store additional Metadata about that relationship!

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:

Let's connect:


Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]
#PragmaticWorks

[haakonpoulsen]

This was a big help in determining data model in dataverse I'm setting up. Ultimately I decided to manually create many:many relationship tables after a long history in SQL Server database design. I can't fathom not visualizing or pulling up junction tables, it just makes sense to me to do it this way even if there isn't any extra data being stored on each row other than the reference IDs to the other to tables. I can't see the value in having MS create the tables in the backend in an out of sight out of mind sort of way, it seems to further obscure and abstract away the data model/structure from the user which I think will make it harder to maintain over time.

[candidesh]

thanks Nate for this contribution. You showed exactly what I needed. I could use it for my first PA-project for a canvas apps and junction tables with meta data.
What confused me was the naming convention for your tables and columns. Expressions like Student.Student or Class.Class are not claryfying the objects - especially for newbies.
As a long time database developer I used to name columns with a prefix i.e. lkp for lookup columns in the junction table. In your example lkpStudent instead of Student would make more clear whats the table or the column.

I struggled around to get my app running but in the end succeeded.
So for future videos you produce I wish you think about my proposal, whether it's a good idea to avoid further issues.

Finally I appreciate your work - well done! 😀😃

[el_witcher]

Holy shi*, you just saved me; this is going to kind of solve 92% of the problems I was having!
Thank you so much for this.
+1 subscriber and 👍

Greetings from Germany.

[fredbarnes]

Awesome! My mental block was how to join everything up to display in one gallery, but you simply put the "join table" in a separate gallery.

[hernanmauricioperezmunoz]

Amazing guys. I was looking it along days. Works fine for me, thanks so much

[AaronStevenson-spox]

Fantastic. Your tutorial style of explaining this is spot on! Just a question, if you didnt need meta data and used the previous auto many to many method with Relate and Unrelate, would that be accessible to say Power BI or direct SQL queries?

[WebdewWebsites]

The step I was hoping to see was a gallery where you chose the class and saw a list of students and grades. ie a filter across the junction table. Struggling with this one.

[mohammednizamuddin]

Great Tutorial, Nate!!. Have a question, is it possible that we loop through all the students and add Math as a subject for them using a single save/submit operation?

[badfingergoodwin]

Great video, thank you! Having set things up in this way, is it possible to filter the Student gallery based on a combobox selection to choose the class?

[MargaretSlade-rz]

Thank you for your video, I was hoping to use a junction table to create record level security in my app. The step to create a new column referencing table 1, never saved. I waited 20 minutes before I hit close tab as the column was still saving. Why will the table not save the reference to the new lookup table? There are no error messages. Seems as if you have a simple table with simple data types these actions work well. If you have business situations with complex tables that is an entirely different story.

[Stjopa-lqcv]

Very helpful content! But how do I use manually created junction tables in a model driven app? Easy example: Order, Product, OrderProduct. If I create the OrderProduct on my own and create lookups there (what is actually creating a many-to-many relationship), I can't fill this junction table from the Order Form. Usually the model driven app is designed to have this many-to-many created automatically, but then I neither have visibility into the junction table, nor can I use a RollUp field to summarize related positions or values. So what should I do? Create an outsourced calculation flow for a simple sum? I find it really hard that in 2024 there are still major limitations for easy stuff. Any help highly appreciated!