Canvas Apps Dataverse Expressions

L – Lookups in filters – How to work with them!

A girl looking up
A girl looking up

Introduction

Filter is a super useful function. So are lookup columns. Both filter function and lookup columns combined together, they bring Dataverse's relational capabilities to the forefront. Understanding lookup columns inside of filter can be tricky. To explain this complex looking concept, we will take the following example:

  1. Consider two tables - Instructors and Courses
  2. First, the Course table has a lookup column to the Instructors table
  3. Next, the name of the lookup column on the Courses table is Instructor
  4. Finally, the GUID / unique ID column on the Instructor table is also called Instructor

Lookups inside filters demo

Continuing from the data setup, let's say you want to filter the list of courses for a given instructor, something like this:

Demo of lookup columns inside filter function
Demo of lookup columns inside filter function

Explaining lookups inside filters

The Items property of the above gallery is:

Filter(
    Courses,
    IsBlank(cmbInstructors.Selected.Instructor) || Instructor.Instructor = cmbInstructors.Selected.Instructor
)

In the above expression, ignore the IsBlank. That was added so that when there is no instructor selected, I want to see the entire list of courses instead of a blank gallery.

Moving on, let's focus on the 2nd filter condition: Instructor.Instructor = cmbInstructors.Selected.Instructor

There are three references to instructors. Here is the explanation:

  1. 1st instructor on the left side of the equation is the instructor lookup column on the Courses table
  2. Then the 2nd instructor on the left side of the equation is the GUID column of the instructor lookup record on the Courses table
  3. And finally, the instructor on the right side of the equation is the GUID column of the selected instructor (using the combo box)

To learn more about Dataverse, click here. And to learn more about Dataverse relationships, click here.

Stay tuned for the remaining 14 tips!

Recent articles

Leave a Reply