How to work with custom many to many Dataverse relationships

Introduction

The example we will be using to look at custom many to many (N:N) relationship is between students and sports. A student can enroll in multiple sports. And one sport can be taken up by multiple students. To review the data model, click here. The major diff between OOB and custom N:N is the middle/intersection table. In OOB N:N, that table is not accessible whereas in custom N:N, you create that table. So you can not only tap into it, you can store more details or metadata about related records. For example, for each student-sport relationship, it may be important to store if the student has hired a coach or not. The relationships are stored in a table called 'Student Sport Relations'. There are two LookUp columns - one to the Students table and the other one to the Sports table.

Displaying related records

Displaying related records in a custom N:N relationship is exactly the same as OOB N:N. 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 Student Sport Relations table. Next, you want to show the list of sports 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.'Student Sport Relations'

And then set the Text property of a label in Gallery2 to:

ThisItem.Sport.Name

Creating custom many to many relationship

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

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

IsBlank(LookUp('Student Sport Relations', Student.Student = Gallery1.Selected.Student && Sport.Sport = ThisItem.Sport))

Let's break this expression down:

1. We are doing a lookup to check if there is a record in the Student Sport Relations table where student is the student selected from Gallery1 and Sport is a given record in Gallery2
2. If the lookup returns blank, that means the selected student and sport are not related.

Setting the Visible property of the + icon to the above expression ensures that it remains visible only for those sports 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:

Patch(
    'Student Sport Relations',
    Defaults('Student Sport Relations'),
    {
        Student: Gallery1.Selected,
        Sport: ThisItem,
        Name: Gallery1.Selected.Name & " " * ThisItem.Name
    }
)

To learn more about the Patch function, click here.

Deleting custom 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:

!IsBlank(LookUp('Student Sport Relations', Student.Student=Gallery1.Selected.Student && Sport.Sport=ThisItem.Sport))

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

Remove(
    'Student Sport Relations',
    LookUp(
        'Student Sport Relations',
        Student.Student = Gallery1.Selected.Student && Sport.Sport = ThisItem.Sport
    )
)

To learn more about the Remove function, click here.

Recent articles

1. Many to one Dataverse relationships
2. One to many Dataverse relationships
3. OOB many to many Dataverse relationships