We will learn and understand to perform read & write operations with Polymorphic lookup in Canvas App. Before we start, make sure to subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.
What is Polymorphic Lookup?
Multi-table lookup or Polymorphic lookup type field allow a user to use a specific table that has multiple One-To-Many (1:M) relationships to other entities / tables in the environment. A single lookup type column can refer to multiple other tables. A lookup value submitted to the multi-table type column will be matched to a record in any of the related tables.
Multi-table types are currently available in Microsoft Dataverse as static type attributes such as “Customer” lookup, which connects to Account and Contact. The new multi-table features gives users the power to define any other multi-table lookups they may need.
Reading or retrieving value from a Polymorphic Lookup
- As name suggest, the Polymorphic Lookups will target more than one table / data source. Thus, we will need to specify the table / entity before consuming a Polymorphic Lookup.
- We will leverage the functions AsType and IsType for achieving our requirement.
IsType()
The IsType function tests whether a record reference refers to a specific table type. The function returns a Boolean TRUE or FALSE.
AsType ()
The AsType function treats a record reference as a specific table type, sometimes referred to as casting. You can use the result as if it were a record of the table and again use the Record. Field notation to access all of the fields of that record. An error occurs if the reference isn’t of the specific type.
Understanding the scenario of demonstration
- In this scenario, we have a Dataverse table named “Subscriber” which contains a Polymorphic Lookup field “Polymorphic Lookup” targeting two tables namely “Deliverables” and “Marketing & Campaigns“.
- We will first allow the user to filter the Subscriber table according to the “Name” by passing an input parameter and further fetching the Polymorphic Lookup value for the given Subscriber row or record.
- Considering the Power FX formula, we will initially retrieve the complete data of our polymorphic lookup field on a button click and store it in a variable. Below is the Power FX formula for performing this operation.
Component Type | Component Name | Component Usage |
TextInput | TextInput | Used to take an input value from the user for filtering Subscriber table’s row/ record |
Table | Subscriber | Table which contains a Polymorphic Lookup field |
Column | Polymorphic Lookup | Polymorphic Lookup field present in the Subscribe table |
Table | Deliverables | Target table for the Polymorphic Lookup field |
Table | Marketing & Campaigns | Target table for the Polymorphic Lookup field |
Set(
varGetPLV,
LookUp(
Subscribers,
Name = TextInput.Text
).'Polymorphic Lookup'
)
//Note: - We have stored the complete value in a variable named "varGetPLV".
- Now, we will segregate the previously retrieved polymorphic data separately as per the target tables. In our case we have two target tables namely “Deliverables” and “Marketing & Campaigns“, thus we will use the “IsType” function to segregate the target tables and the “AsType” function to retrieve target table specific value from a Polymorphic Lookup. Below is the Power FX formula for performing this operation.
If(
IsType(
varGetPLV,
'Marketing & Campaign'
),
AsType(
varGetPLV,
'Marketing & Campaign'
).Name,
AsType(
varGetPLV,
Deliverables
).Name
)
- The output of the above Power FX formula will provide us the value stored in a Polymorphic Lookup field.
Updating or inserting value in a Polymorphic Lookup
- As name suggest, the Polymorphic Lookups will target more than one table / data source. Thus, we will need to specify the table / entity & target row / record before inserting a value into a Polymorphic Lookup.
Understanding the scenario of demonstration
- In this scenario, we have a Dataverse table named “Subscriber” which contains a Polymorphic Lookup field “Polymorphic Lookup” targeting two tables namely “Deliverables” and “Marketing & Campaigns“.
- We will first allow the user to select a new value for a Polymorphic Lookup, then allow the user to filter the Subscriber table according to the “Name” by passing an input parameter. Further we will insert the new value in the Polymorphic Lookup field of the filtered Subscriber row / record.
- Considering the Power FX formula, we will consume user inputs such as target table name, lookup value and target table’s row name. Further we will insert a new value in Polymorphic Lookup field on a button click as given in the below Power FX formula.
Component Type | Component Name | Component Usage |
TextInput | TextInput | Used to take an input value from the user for filtering Subscriber table’s row/ record |
Dropdown | DropdownToggle | Used to take an input value from the user for selecting the target table |
Dropdown | DropdownData | Used to take an input value from the user for selecting the lookup value |
Table | Subscriber | Table which contains a Polymorphic Lookup field |
Column | Polymorphic Lookup | Polymorphic Lookup field present in the Subscribe table |
Table | Deliverables | Target table for the Polymorphic Lookup field |
Table | Marketing & Campaigns | Target table for the Polymorphic Lookup field |
If(
DropdownToggle.SelectedText.Value = "Deliverables",
Patch(
Subscribers,
LookUp(
Subscribers,
Name = TextInput.Text
),
{
'Polymorphic Lookup': LookUp(
Deliverables,
Name = DropdownData.SelectedText.Value
)
}
),
Patch(
Subscribers,
LookUp(
Subscribers,
Name = TextInput.Text
),
{
'Polymorphic Lookup': LookUp(
'Marketing & Campaign',
Name = DropdownData.SelectedText.Value
)
}
)
);
- The output of the above Power FX formula will insert a new value in a Polymorphic Lookup field.
Validating our implementation in Canvas App
We will validate our implementation of reading & inserting value in a Polymorphic Lookup within a Canvas App as shown below.
Hi, great explanation. Can you also show how to filter Activities with Activity Type in Canvas App
Thanks in advance
Hi, Thank you. I will surely look into your request.
I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.