How to work with many to one Dataverse relationships

Introduction

The example we will be using to look at many to one relationship is between courses and instructors. A course can be taught by one instructor. But one instructor can teach many courses. To review the data model, click here.

Displaying related records

Let's suppose there is a gallery - Gallery1. The data source of Gallery1 is the Courses Dataverse table. Now, you want to show who the Instructor of each course is in a label within Gallery1. There are several different ways you can fetch the instructor's name but the easiest method is to use the dot operator. Set the Text property of the label to the following expression to display the instructor's name:

ThisItem.Instructor.Name

It is important to note that Instructor in the above expression isn't the name of the table that the lookup field on the Courses table looks up to. It is the name of the lookup column on the Courses table. If the lookup column in the Courses table was named "Assigned Instructor', the above expression would have been:

ThisItem.'Assigned Instructor'.Name

Creating many to one relationship

The easiest way to link two records in a many to one relationship is to use a form and add the lookup column as a data card. You can then use SubmitForm to create/update a many to one relationship. In this example, to link an instructor to a course, add a form (let's say its name is Form1) and set its DataSource property to the Courses table. Add a couple of data cards (some data cards get added by default, so add these only if they don't get added automatically) - Name, and Instructor columns. Set the DefaultMode of the form to FormMode.New (to create a new course). Then all you need is a button with the following OnSelect property to create a new course and link the selected instructor to it:

SubmitForm(Form1); ResetForm(Form1)

The setup is as simple as shown below:

Deleting many to one relationship

There are two ways to delete a many to one relationship between two records: one uses SubmitForm and the other one uses Patch. Let's take a look at both of them:

1. SubmitForm: Using SubmitForm to delete a many to one relationship is as simple as blanking the lookup column and then submitting the form. Add a form and set its DefaultMode to FormMode.Edit. Set its DataSource to the Course table. And finally set its Item property to Gallery1.Selected where Gallery1 is a gallery of Courses. Now if you blank out the combo box of the instructor data card and submit the form using SubmitForm, the relationship will get deleted.
2. Patch: The concept of deleting a many to one relationship using Patch is similar to SubmitForm. In other words, you have to "blank out" the instructor value. Here is the expression to achieve the same result as above using Patch:

   Patch(
       Courses,
       Gallery1.Selected,
       {Instructor: Blank()}
   )

With either of the two approaches, you will observe that the value in the Instructor lookup column in the selected Course record doesn't actually blank out. The way to fix this behavior is to enable the Formula-level error management app setting. It can be found under Settings->Upcoming features->Experimental. Both approaches listed above will work fine after you turn on this feature. This hidden piece of information can be found here as a note.

Multiple levels of many to one

Let's suppose there is a gallery of courses and you want to display the instructor's department name. There are two levels of many to one relationship here: between courses and instructors and between instructors and departments. I wouldn't blame you if you think the following expression will work:

ThisItem.Instructor.Department.Name

After all, the dot operator is one of the most useful features. But when you do that, you will see an error that says "Multiple levels of many-to-one relationship expansion aren't supported". To get past this error, change the expression to the following:

LookUp(Instructors, Instructor = ThisItem.Instructor.Instructor).Department.Name

Let's break this expression down:

1. ThisItem.Instructor is the record in the lookup column Instructor on the Courses table
2. ThisItem.Instructor.Instructor is the GUID of the above record
3. LookUp function fetches the Instructor record using the GUID from the above step
4. Then the dot operator fetches the department of that instructor
5. And finally the dot operator fetches the name of the department

The usage of the lookup function breaks down the multiple levels of many-to-one relationship expansion into two parts, hence this expression works fine.

Recent articles

1. How to work with one to many Dataverse relationships
2. A-Z: 26 tips! Learn how to improve your Power Apps!
3. How to calculate the height of a flexible height gallery!
4. How to create custom errors in Power Apps!