Dataverse : Multi-Table lookups

Today we take a look at a great feature released last year: Dataverse : Multi-Table lookups.

The Multi-Table Lookup is a type of Column in Dataverse similar to the Lookup type... except that it allows to look up records in multiple Tables at once, rather than in a single Table. Some out-of-the-box Columns already exist with a similar feature such as the "Customer" Column which allows to lookup records in "Account" and "Contact" Tables.

This feature allows to create and manage Custom Columns with that kind of functionality.

Microsoft has not provided yet a graphical interface to manage the Multi-Table Columns. The management of these Columns could be done using the Web API. The documentation can be found here: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/multitable-lookup#use-the-multi-table-lookup-apis. The API allows to make operations such as: create and delete these Columns, retrieve metadata information, add or remove relationships in one Column, update the metadata of a relationship, ...

An XrmToolBox plugin exists to help use these Columns, the "Polymorphic Lookup Manager" plugin:

This plugin allows to manage the Columns with a graphical interface and to use all operations available in the API.

In the interface, you can select the Table on which the Multi-Table Column is created, the Tables that it references, the characteristics of the relationships, ...:

For a small demo, I have created a Table called "TestAVE" and in it I want to create two custom Columns:

• My Simple Lookup: A usual Lookup Column, referencing the Table Contact. I create it only to compare it with the Multi-Table Lookup Column.
• My Polymorphic Lookup: A Multi-Table Lookup Column, referencing multiple Tables. For the demo, I use references to the following Tables:
  • Account
  • Campaign 
  • Case
  • Contact
  • Lead
  • Opportunity

Button "Create Polymorphic Lookup" allows to create the Column once the configuration is made.

The Column is displayed in the PowerApps configuration site (make.powerapps.com). I add it to the Main Form of the Table. One interesting point is that this Multi-Lookup Column almost looks like an usual Lookup Column. The graphical interface only shows one Table that it references. And little customizations can be done on it.

Here are printsceens for the configuration of the Multi-Table Lookup:

And here are printsceens for the configuration of the Simple Lookup:

We can hardly find differences between the two types of Column.

Here is a printscreen of the appearance of the Multi-Table Lookup Column in a Model-Driven App:

It looks just like the "Customer" Column.

You can select the Table in which you want to lookup a record, and then just select your record!

As mentioned above, the XrmToolBox plugin also allows to update and delete these Multi-Table Columns!

This Multi-Table Column can be really great. I hope you will enjoy it !

I guess Microsoft will provide a graphical interface in the future to manage these Columns. Meanwhile, XrmToolBox provides a great plugin to manage them.

Dataverse : Multi-Table lookups