How to work with indirect Dataverse relationships

What are indirect relationships?

The example we will be using to look at an indirect relationship is between instructors and the students enrolled in their courses. An instructor can take up multiple courses and each course could have multiple students enrolled in them. Its called indirect because the Instructors and Students table do not have any explicit relationship. To review the data model, click here.

Displaying records in indirect relationships

Let's suppose there is a gallery - galInstructors with its Items property set to the Instructors Dataverse table. Next, you want to show the indirectly related students in galStudents. To achieve that, you would think using the dot operator will work:

galInstructors.Selected.Courses.Students

This throws an error - "The specified column is not accessible in this context". To overcome this, do a ForAll to loop through each course related to the selected instructor to then get the list of related students. To do that, set the OnSelect of galInstructors to:

ClearCollect(
    colCourses,
    ThisItem.Courses
);
Clear(colAssignedStudentsTemp);
ForAll(
    colCourses,
    Collect(
        colAssignedStudentsTemp,
        ThisRecord.Students
    )
);
ClearCollect(
    colAssignedStudents,
    Distinct(
        colAssignedStudentsTemp,
        Student
    )
);

Let's break this down:

1. First, we collect the list of courses related to the selected instructor in colCourses.
2. Then we loop through each course in colCourses and collect the list of related students in colAssignedStudentsTemp
3. We then have to capture the distinct list of students from colAssignedStudentsTemp as one student could have enrolled in more than one course. So there might be multiple occurrences of a given student in colAssignedStudentsTemp

Scope error

Now it throws an error on ThisRecord.Students. The error message is the same as before - "The specified column is not accessible in this context". To overcome this error, turn on the following experimental setting - "Record scope one-to-many and many-to-many relationships". Then go back to the formula bar, just add a space or any character and then remove it. The error will go away and colAssignedStudents will have the list of all students that are indirectly related to the selected instructor.

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.

To read more about record scope, click here.

Recent articles

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