How to work with OOB many to many Dataverse relationships

Introduction

The example we will be using to look at out-of-the-box (OOB) many to many (N:N) relationship is between students and courses. A student can enroll in multiple courses. And one course can be taken up by multiple students. To review the data model, click here.

Displaying related records

Let's suppose there are two galleries - Gallery1 and Gallery2. The data source of Gallery1 is the Students Dataverse table. And the data source of Gallery2 is the Courses table. Next, you want to show the list of courses that a student has enrolled in for the selected student from Gallery1. To achieve that, use the dot operator and set the Items property of Gallery2 to:

Gallery1.Selected.Courses

Creating many to many relationship

To link or relate a student with a course, we will again consider two galleries. Gallery1 (Students) and Gallery2 (Courses).

In Gallery2, we will add a + icon . Next, we will set its Visible property to:

!(ThisItem.Course in Gallery1.Selected.Courses.Course)

Let's break this expression down:

1. To the left of the in operator, we have ThisItem.Course which is the GUID of a course record
2. The right side is Gallery1.Selected.Courses.Course
   1. Gallery1.Selected is the selected student record from Gallery1
   2. Gallery1.Selected.Courses is the list of related courses (courses that the selected student has enrolled in)
   3. Gallery1.Selected.Courses.Course is the list of GUID of related courses

Setting the Visible property of the + icon to the above expression ensures that it remains visible only for those courses that a student has not enrolled in.

With that, to link or related a course with a student, set the OnSelect of the + icon to:

Relate(Gallery1.Selected.Courses, ThisItem)

To learn more about the Relate function, click here.

Deleting many to many relationship

To illustrate the functionality of deleting a N:N relationship, we will replace the + icon with the trash icon. Next, we will update the Visible property to:

ThisItem.Course in Gallery1.Selected.Courses.Course

After that, we will change the OnSelect of the icon to:

Unrelate(Gallery1.Selected.Courses, ThisItem)

To learn more about the Unrelate function, click here.

Scope error

Lets say we want to display the list of students along with the number of courses they are enrolled in inside a collection. To solve this, you would think that an expression like this will work:

ClearCollect(
    colStudents,
    AddColumns(
        Students,
        "CourseCount",
        CountRows(ThisRecord.Courses)
    )
)

But it doesn't! However, you get a warning and an error:

1. CountRows(ThisRecord.Courses) throws a delegation warning
2. ThisRecord.Courses throws an error that says "The specified column is not accessible in this context."

Here's how we will fix both the issues:

1. To get rid of the delegation issue, change ThisRecord.Courses to ThisRecord.Courses.Course. Counting a one column table vs a multi-column table gets rid of the delegation warning.
2. To fix the context error, all we need to do is to enable an experimental app setting called "Record scope one-to-many and many-to-many relationships".

To summarize, to make this work without any delegation issues, turn on the above mentioned experimental setting and use the following code:

ClearCollect(
    colStudents,
    AddColumns(
        Students,
        "CourseCount",
        CountRows(ThisRecord.Courses.Course)
    )
)

PS: Once you turn this experimental feature on, you will still a blue line under the code and the app checker throws a warning that reads: "A One-to-Many and Many-to-Many relationship is being referenced in this function's record scope. This may result in a large number of calls to your data source that can impact performance." This warning can turn into a reality depending upon the number of records in your data source. I have noticed significant performance issues with large amounts of data, to the extent that I had to turn the setting off and make changes to the data model so as to not use a N:N relationship.

Recent articles

1. How to work with many to one Dataverse relationships
2. How to work with one to many Dataverse relationships
3. A-Z: 26 tips! Learn how to improve your Power Apps!