How to work with polymorphic lookups in Dataverse

Introduction to Polymorphic lookups

Dataverse is a relational database that provides some rich relational capabilities. One such concept is polymorphic lookups. A polymorphic lookup is just like a lookup column except that it can lookup to more than one tables. For example, each record in Dataverse has an Owner column. This Owner column can refer to a record in the Users table or the Teams table. In other words, one record of TableA could have User1 as the owner and another record of TableA could have Team2 as their owner. To illustrate this concept, we will use the Owner column of the Student table records.

Displaying polymorphic lookup values - 1st error

Let's suppose there is a gallery - galStudents. The data source of Gallery1 is the Students Dataverse table. Add a label and set its Text property to:

ThisItem.Name

and then add one more label which we will use to display the name of the owner.

Next, add one more label lblOwnerName to display the name of the owner. You would think this would work:

"Owner name: " & " "  & ThisItem.Owner.Name

but it throws an error - "Invalid use of .". This is because the owner could be a user or a team. The name column for the Users table is 'Full Name' and the name column for the Teams table is 'Team Name'.

Casting error

The way to get past this error is to use the AsType function. It is a function that "casts" a record to a particular table. So changing the Text property of lblOwnerName to the following gets rid of the "Invalid use of ." error:

"Owner name: " & " " & AsType(
    ThisItem.Owner,
    [@Users]
).'Full Name'

The 1st parameter of the AsType function is the record we want to cast. The 2nd parameter is the name of the table that we want to cast the record to. After that when you type ".", it now shows the columns of the Users table.

The problem is in a gallery of students, some records may have users as their owners while some may have teams as their owners. So for students for which the owner is a team, you will get the following error:

Displaying polymorphic values correctly

To get rid of the casting error, we will use the IsType function. The syntax of the IsType function is similar to the AsType function. The only difference is it doesn't actually cast the record. It just checks if the record can be "type casted" to the specified table. The output of the function is either true or false. This allows usage of AsType only when a record is of the given type.

So changing the Text property of lblOwnerName to the following gets rid of all the errors:

"Owner name: " & " " & If(
    IsType(
        ThisItem.Owner,
        [@Users]
    ),
    AsType(
        ThisItem.Owner,
        [@Users]
    ).'Full Name',
    AsType(
        ThisItem.Owner,
        [@Teams]
    ).'Team Name'
)

To learn more about these functions, click here.

Recent articles

1. Many to one Dataverse relationships
2. One to many Dataverse relationships
3. How to work with custom many to many Dataverse relationships