Microsoft Dataverse Adds Multi-Table Lookups

I'm happy to announce the preview of Multi-Table Lookups in Dataverse! This long awaited feature allows you to create a single lookup that can pull data from multiple tables providing greater opportunities to integrate your data into apps, flows, and more.

My blog post yesterday covered some of this, but here is how multi-table lookups work in Dataverse.

A multi-table lookup uses a single referencing table, and more than one referenced tables all in one to many relationships. The example I like to use for this is a library checkout. When you check out media from a library, they are considered "checkouts" even though it may be a book, DVD, or Audio CD. (Let's ignore the fact that people may not use these formats today, it just helps with the example). When you look up media in a library, it often returns results from multiple categories allowing you to select the appropriate item.

Until now, if you wanted to create such a schema in Dataverse you had limited options:

• Keep all your media in a single table
• Use one lookup for each table that contains data, resulting in multiple lookups
• Code your own solution

Now, you can create a lookup that will return results from multiple tables. Here is an example of the schema:

In this example, you can see that the MediaCheckout referencing table includes a lookup column. The multi-table lookup column allows relationships with more than one table, allowing it to retrieve data from the three referenced tables: Books, DVDs, and AudioCD.

With this lookup, a user could find the book and DVD for Gone With the Wind, as well as an audiobook on CD, or the soundtrack all with a single lookup.

A few points to note about this preview:

1. At this time, multi-table lookups can only be created and managed using API calls. A user experience is being worked on now and we hope to provide it soon.
2. You cannot convert an existing relationship into a multi-table relationship.
3. You can remove or add tables from the multi-table lookup using the APIs
4. The lookups can be used in both Canvas and Model Driven apps even though the creation UI is not available yet.

The blog post I had mentioned earlier provides links to documentation that outlines how to create these relationships via API.

I'd love to hear more about how you're using the new feature!