ReadyXRM

A new feature landed in preview last week which is something that has been a big ask of Microsoft for many years, multi-table lookups, otherwise known as polymorphic relationships.

What are polymorphic relationships and how can they help you?

A polymorphic relationship lookup allows you to specify more than one type of Dataverse table to relate to. A prime example that has been in use for years in Dynamics 365 CE is the customer lookup in the Common Data Model where a lookup on a table like an opportunity or case can point to either an Account or Contact record.

Up until this point, a customer relationship was the only type of “polymorphic” relationship available to makers, until now.

More of a video person? Check out the video version of this post on the 365.Training YouTube channel.

Use Case: A custom Payment Table

Consider a use case where we want to track payments against records in a Power App. If we are using Dynamics 365 for Sales, maybe we want to track payments against orders or invoices.

In my example, once again I will use my standard classroom management app where I would want to track payments against custom tables I created; registrations, classes or even courses.

Since we want to track payments to three different table types, designing a solution becomes a bit complicated.

The Activity Table Solution

One way to create a table that can relate to multiple different table types is to create the table as an Activity table type.

The benefit is that we can relate payment records to any other tables in the app (as long as they are enabled for activity tracking)

The downside is that in order to add payment records, we need to use the model-driven app Activity menus and views, which can be a bit awkward. Payment records would be mixed in with other activity types like phone calls and emails:

The other large downside is that security is applied to the Activity level, meaning if users needed to see regular activities like emails, phone calls, etc, but NOT payments, we would not be able to apply that to the security role.

Standard Table, Many Lookups

The other alternative is to add a unique lookup for each table type you want to link to. Security is easily implemented. The downside is you have many different lookups pointing to different tables, and you may consider implementing business rules to hide/show the unused lookups. Views can be a bit awkward as you can have different columns for different lookup types, but you have much better control than with an Activity view.

Multi-table lookups to the Rescue!

While they may be other solutions to this problem, lets take a look at multi-table lookups. Polymorphic relationships are currently in PREVIEW (so not be implemented in a production environment, not yet anyway).

The current maker experience (at time of this writing) also does not have a UI available to creating these relationships. However, Tanguy Touzard, maker of the XrmToolBox has already released a tool to allow makers to create these relationship types.

Polymorphic Lookup Creator

The tool allows you to create a multi-table lookup without needing to use any code (the Microsoft docs provide the SDK methods to create these lookup types).

Once the multi-table lookup is created, it can be added to a model-driven form in an app.

The benefit is that you can choose not only which record, but different types of records (table types):

The views also become more accessible by only having one column for all the related tables;

The downside is that polymorphic relationships can cause some issues when using advanced find or querying in reports. Users may also get a bit confused with the lookups pointing to different table types.

Summary

Multi-table relationships adds yet another method to resolve unique business requirements while building business applications. The polymorphic relationship feature adds a feature to Microsoft Dataverse that is not readily available in other data sources like SharePoint or Azure SQL.

Cover Photo by Serena Repice Lentini on Unsplash

Nick Doelman has been very busy with project work lately and hasn’t posted on this blog in a while. He hopes to get back to regular schedule very soon with some more exciting content! Nick is also a Microsoft Most Valuable Professional (MVP) for the 5th year in a row. Nick is also a trainer and content creator for https://365.Training Follow Nick on Twitter at https://twitter.com/readyxrm