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:
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
Can Dataverse lookup a lookup column which Sharepoint List can't
Thank you for your article, just wanting to know if Dataverse can lookup a Lookup column?
Currently, I'm stuck on SharePoint list by its limitations that Lookup column can't lookup another Lookup column, nor a calculated column can copy the value from a Lookup column.
Please if you could advise if Dataverse will be able to do so, thank you.
Hello Chrisy, Thank you…
Thank you for your message.
In Dataverse, a Lookup field references the GUID of a record (This GUID is autogenerated when the record is created)
It is not possible to reference any other column of the record.
So I think the answer to your question is that this would not be possible unfortunately.
Hope this helps.
Problems in multi lookup with specific table
Hi, I have a poly lookup pointing towards three different tables working fine.
When I try to add a fourth table, the lookup only shows the fourth added one and not the other three. This happens only with this specific table.
Important to know that these three tables had a relationship with the fourth table, and i deleted the relationships thinking this may cause the problem but no luck.
Any ideas what can cause this? maybe something in this table properties? maybe the past relationships?