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

In this Pragmatic Works training video, Nate Halliwell explores how to replace traditional many-to-many relationships in Dataverse with a more flexible solution: junction tables. This method not only simplifies data modeling but also enables the storage of additional metadata about relationships that many-to-many models cannot easily support. The video builds on Nate’s previous lesson about many-to-many relationships and demonstrates how to structure, implement, and use junction tables inside of Canvas Apps.

Recapping Many-to-Many Relationships

To start, Nate reviews the concept of a many-to-many relationship with a familiar example: students and classes. In a simple setup:

• A student can be enrolled in multiple classes.
• A class can have multiple students.

In Canvas Apps, these relationships are easy to establish using Relate and Unrelate functions. However, the limitation is clear: while the relationship itself is stored, there is no space to capture extra details such as grades or enrollment metadata. This is where junction tables come in.

What is a Junction Table?

A junction table—also called a joiner table—bridges two tables and stores individual records for each unique combination. For example:

• Nate Halliwell – Math
• Nate Halliwell – Science
• Nate Halliwell – Technology

Each record links a single student with a single class, making it possible to include an additional column to store metadata such as the student’s grade. Unlike invisible system-managed tables, a junction table gives full flexibility to extend the relationship model.

Building the Junction Table

Nate demonstrates the steps to build a junction table in Dataverse:

1. Create a new table named Student Classes.
2. Set up columns:
   • Auto-number ID as the primary name column.
   • Lookup to Class table.
   • Lookup to Student table.
   • Grade column (whole number).
3. Establish one-to-many lookup relationships with both Students and Classes.

This setup allows each student-class combination to exist as a record with additional information attached.

Implementing in a Canvas App

After building the table, Nate walks through incorporating the junction table into a Canvas App:

• Set up galleries for students and classes.
• Insert a modern checkbox control to represent whether a student is enrolled in a class.
• Use the Patch function to create new records in the junction table when a class is selected.
• Configure the Remove function to delete a record if a class is deselected.
• Control the default checkbox state by checking if a record already exists for the student and class combination.

This design ensures that the relationship is dynamically updated whenever checkboxes are selected or cleared.

Displaying and Managing Metadata

The real power of the junction table comes with metadata. Nate creates a gallery to display all records for the selected student, filtered by the student ID. Instead of showing auto-numbers, the gallery displays class names linked through the lookup.

Finally, he adds a text input field to capture grades for each class. By using the Coalesce function, the app displays “0” as a placeholder if no grade is entered. When a value changes, the OnChange property patches the new grade back to the junction table, ensuring that the metadata is saved and retained across sessions.

Why Choose a Junction Table?

By the end of the demo, Nate highlights the advantages of junction tables compared to standard many-to-many relationships:

• Flexibility – Add columns to store metadata like grades, dates, or other details.
• Transparency – Records are visible and manageable within the Dataverse structure.
• Customization – Tailor the app interface with checkboxes, forms, or buttons to manage relationships.
• Performance – Maintain efficient app behavior while tracking additional details.

Conclusion

Nate Halliwell’s demonstration makes it clear that junction tables are not as intimidating as they sound. They provide a straightforward, powerful way to replace many-to-many relationships while unlocking the ability to store and manage metadata. For educators, developers, and app builders, this technique is invaluable when working with complex data models inside of Dataverse and Canvas Apps.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Dataverse Power Apps  and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.