How to work with one to many Dataverse relationships

Introduction

The example we will be using to look at one to many relationship is between department and instructors. A department can have many instructors. But one instructor can only belong to one department. 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 Departments Dataverse table. And the data source of Gallery2 is the Instructors Dataverse table. We want to show the list of related instructors in Gallery2. To achieve that, there are two ways:

1. Filter:

   Filter(Instructors, Department.Department = Gallery1.Selected.Department)

   A common mistake is to compare two records e.g. you might be tempted to write the above expression as:

   Filter(Instructors, Department= Gallery1.Selected)

   You cannot compare two records like this. If you want to compare two records, you have to compare their GUIDs. In this case, that translates to Department.Department = Gallery1.Selected.Department. Another thing that might be confusing is Department.Department. The Department to the left of the dot operator is the name of the lookup column on the Instructor table. The Department to the right of the dot operator is the GUID of the department record. This might seem confusing to many people. That's because the lookup column on the Instructor table and the GUID column of the Department table have the same name. If the name of the lookup column on the Instructor table would have been 'Department Record', then the filter expression would have been less confusing:

   Filter(Instructors, 'Department Record'.Department = Gallery1.Selected.Department)

2. Dot operator:

   Gallery1.Selected.Instructors

   The dot operator is one of the most powerful tools in canvas apps. I like it because there is no explicit filter involved. Which eliminates confusing code like Department.Department. Another reason is that with the filter expression as in the previous step, sometimes you will see the entire list of records first. That list will then get filtered. That doesn't make for the best user experience.

Creating one to many relationship

To link or relate an instructor with a department, we will again consider two galleries. Gallery1 (Departments) and Gallery2 (list of Instructors that are not related to any department).

The first step is to get the list of instructors that are not related to any department. In other words, Instructor records where the lookup column is blank. To achieve that, there are two ways:

1. One with delegation:

   Filter(Instructors, IsBlank(Department.Department))

2. One without delegation:

   Filter(Instructors, 'Instructors (Views)'.'Instructors w/o Department')

The first expression results in a delegation warning. How do you fix that? Dataverse views to the rescue! Create a simple view that filters out records with a blank Department lookup column. Here is how to set up the view:

Now add a + icon to Gallery2 (Instructors gallery). We will use it to link instructors to a department. The way to link or relate two records in a one to many relationship is by using the Relate function. Set the OnSelect of the + icon to the following:

Relate(Gallery1.Selected.Instructors, ThisItem)

To learn more about the Relate function, click here.

Deleting one to many relationship

To illustrate the functionality of deleting a one to many relationship, we will replace the + icon with the delete icon in the Instructors gallery. Then, we will set its OnSelect to:

Unrelate(Gallery1.Selected.Instructors, ThisItem)

Recent articles

1. How to calculate the height of a flexible height gallery!<.a>
2. How to create custom errors in Power Apps!
3. A-Z: 26 tips! Learn how to improve your Power Apps!