Rajeev Pentyala – Microsoft Power Platform

In this article, lets learn how to apply a filter on polymorphic lookup in Canvas App.

What is a polymorphic lookup in Dataverse:

• Dataverse supports polymorphic lookups, which can refer to a record from any table in a set.
• For example, ‘ParentCustomerID‘ (i.e., Display name is ‘Company Name’) column in the Contact table is a polymorphic lookup, where you can select either an Account or Contact record.
• In below example, there are Contacts linked Accounts via ‘Company Name’ polymorphic lookup.

Now that we know what is a Polymorphic lookup in Dataverse, lets learn how to apply filters on polymorphic lookup in Canvas app by building a simple application.

Build a Canvas App using Nested Galleries and polymorphic lookup Filter:

In this sample Canvas App, I am going to add a ‘Contacts’ gallery inside an ‘Accounts’ gallery and show only those ‘Contacts’ linked to the ‘Account’ in the gallery row. Lets get started.

• Create a new Canvas App.
• On the form, add a Blank Flexible Height Gallery control ‘galAccounts‘ to the form and set Datasource as ‘Accounts’ Dataverse table.
  • You can add any Gallery type of your choice but the Flexible Height Gallery control’s row height auto expands as per the row’s content size.

• Now, select the galAccounts and add a new gallery ‘galContacts‘ pointing to ‘Contacts’ Dataverse table as shown below.

• This is how you configure nested galleries, in our case we have the ‘Contact’ gallery (i.e., galContacts) inside the ‘Accounts’ gallery (i.e., galAccounts).
• Preview the App and we would get all the available Contacts in Dataverse in each row of the galAccounts.

• But this is not what we want. We need to see only those Contacts linked to Account (i.e., ‘Contoso 1’ account row should only show ‘Contoso 1 – Contact 1’, ‘Contoso 1 – Contact 2′ and ”Contoso 1 – Contact 3’ contacts as per our available data).

• Lets solve the final piece of the puzzle of applying the filter on ‘Contacts’ by ‘Company Name’ column which is a polymorphic lookup.
• Select the galContacts gallery control, and in ‘Items’ property write following formula.

Filter(
    Contacts,
    AsType(
        'Company Name',
        [@Accounts]
    ).Account = ThisItem.Account
)

• Now play the App and you should see the filtered Contacts.

• You can also add a toggle button to show/hide the galContacts for collapse effect.

More about the formula:

• If you notice, the formula has AsType function. Because the ‘Company Name’ column in the Contact table is a polymorphic lookup, which accepts either an Account or Contact record, we have to use AsType function to specify the table type we are filtering.
• In our scenario, we need to filter ‘Contacts’ based on ‘Company Type’ column which holds a specific Account from galAccounts gallery.
• So in the Filter formula we have used AsType function with 1st parameter as ‘Company Name’ and 2nd parameter as [@Accounts] which refers to the ‘Account’ TableType.

We also have IsType function, which tests whether a record reference refers to a specific table type. The function returns a Boolean TRUE or FALSE.

• Following formula checks whether the ‘Company Name’ column of Contact table holds either an Account record or Contact type record.

If( IsType( ThisItem.'Company Name', Accounts ),
    AsType( ThisItem.'Company Name', Accounts ).'Account Name',
    AsType( ThisItem.'Company Name', Contacts ).'Full Name'
)

🙂