Intro to Many to Many Relationships in Canvas Apps: A Beginner's Guide

In this beginner-friendly walkthrough, Pragmatic Works’ Nate Halliwell introduces how to identify and implement many-to-many (M2M) relationships in Power Apps Canvas Apps. Using a familiar school scenario—students and classes—he shows how to relate records, display related data in galleries, and control relationships with the Relate and Unrelate functions. He also contrasts native many-to-many relationships with a junction table approach for scenarios that require extra metadata like grades, teachers, or periods.

Why Many-to-Many? The School Analogy

Nate frames M2M with a simple model: a Students table and a Classes table. In real life, one student attends many classes, and each class has many students—classic M2M. A single lookup column would only create a one-to-many (1:N) relationship, which can’t represent reality in this case.

Creating the Many-to-Many Relationship in Dataverse

1. Open your solution and navigate to the Students table (starting table doesn’t matter).
2. Choose New > Relationship and select Many-to-many.
3. Select the related table, e.g., Classes.
4. Name the relationship (e.g., Student to Class) and click Done.

Behind the scenes, Dataverse creates a virtual relationship “table” that tracks which students relate to which classes—no physical table is added to your solution. In Canvas Apps, this relationship becomes accessible as a related column; for a student record, you can reference ThisItem.Classes to get a table of related class records.

Building the Canvas App UI

Nate demonstrates a simple management screen made of two vertical galleries:

• Gallery 1: Lists Students (title-only layout).
• Gallery 2: Lists Classes (title-only layout) with a Checkbox in each row.

The goal: when a class checkbox is checked for the selected student, the app creates a relationship; when unchecked, it removes it.

Relating and Unrelating with Formulas

In the class gallery’s checkbox:

• OnCheck: Relate(GalStudentMgmt.Selected.Classes, ThisItem)
  This relates the selected student to the current class row.
• OnUncheck: Unrelate(GalStudentMgmt.Selected.Classes, ThisItem)
  This removes that relationship.
• Default (checked state): Evaluate whether the current class is already in the selected student’s related classes, e.g.: ThisItem.Class in GalStudentMgmt.Selected.Classes.Class.

With these formulas, users can click through students and see checkboxes persist for existing relationships. Nate validates the approach by toggling classes for several students and confirming the state updates correctly.

When a Junction Table Is the Better Choice

Native many-to-many relationships are great for linking records quickly. However, they don’t store additional metadata about the relationship itself (e.g., a student’s grade in a specific class, the teacher, or the period).

For richer scenarios, Nate recommends a junction (joiner) table, such as StudentClass, which includes:

• An auto-number primary key.
• A Lookup to Students (1:N).
• A Lookup to Classes (1:N).
• Any additional metadata columns (e.g., Grade).

Example records for Nate might be “Nate Halliwell – Art – Grade 92” and “Nate Halliwell – Technology – Grade 95.” In the app, you can place a gallery filtered to the selected student’s StudentClass records and edit those metadata fields directly.

Choosing the Right Pattern

• Use Many-to-Many when you only need to associate records and display related lists, with no per-relationship metadata.
• Use a Junction Table when you must store and manage extra attributes about the relationship (grades, roles, time frames, etc.).

Key Takeaways

• Dataverse M2M behaves like a virtual link table; Canvas Apps can reference it via related columns (e.g., .Classes).
• Relate and Unrelate provide simple, reliable control of relationships at runtime.
• For real-world scenarios with rich relationship data, a junction table offers flexibility and scalability.

By following Nate Halliwell’s step-by-step setup and formulas, Canvas App makers can confidently model many-to-many scenarios and choose the right data pattern for current and future needs.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Canvas 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.