Dataverse - How to work around the error "multiple levels of many-to-one relationship expansion aren't supported"

April 05, 2024

3 min read

Dataverse

I help mentor students through the Power Up program and on a few occasions, I've been asked to clarify formula that recurses multiple tables.

In one part of the demo app, an attempt to display related records can result in the error "multiple levels of many-to-one relationship expansion aren't supported" (highlighted in the screenshot beneath). What's the cause of this error, what exactly does it mean, and how can we work around it?

The remainder of this post explores these questions in more detail.

How to recreate the error "multiple levels of many-to-one relationship expansion aren't supported"

The app for this course is based on a library system. The data model is shown in the diagram below.

The notable features are as follows:

Book details (including book name and a book image) are stored in a table called Book
There can be multiple copies of each book - the BookCopies table stores the details of each copy.
When a copy of a book is loaned out, the loan details are stored in a table called Loans

To generate this error message we create a gallery with the Items property set to the Loans table. For each row in the gallery, we use an image control to display the book image. We attempt to retrieve the image by referencing the parent tables like so:

ThisItem.'Book Copy'.Book.BookImage

This formula will generate the "multiple levels of many-to-one relationship expansion aren't supported" error.

The reason for this error is because with Dataverse, it's not possible to directly reference table properties that are 2 or more parent levels above the current table.

How to workaround the "multiple levels of many-to-one relationship expansion aren't supported" error

Since it's not possible to directly reference table properties 2 or more parent levels above the current table, the workaround is to explicitly lookup the BookCopy record for the loan record and to use that reference to access the book image that belongs to the parent book record. Because we're navigating beyond no more than 2 parent levels this strategy will work.

The implementation of this technique looks like this:

LookUp(BookCopies,BookCopies=ThisItem.'Book Copy'.BookCopies).Book.BookImage

With this formula, the book image now displays correctly as shown here:

Although this technique works, the syntax that's used is highly confusing and many students seek clarification on what this formula means.

This formula calls the LookUp function to lookup a single record from the BookCopies table - this part is reasonably obvious. However, the most confusing part is the reference to ThisItem.'Book copy'.BookCopies in the criteria of the lookup. The naming might suggest that BookCopies is a related child table of 'Book Copy' but this isn't the case. BookCopies is the identifier that returns the ID of the record in the BookCopies table (which is of data type GUID). With Databaverse, the built in behaviour is that the property that matches the table name returns the ID (as illustrated below).

Since we can retrieve the unique ID of the parent BookCopies row, we can use this to look up the record in the BookTables. In the remainder of the formula, '.Book' returns the parent book record that's associated with the 'Book Copy' record, and '.Book.BookImage' returns the book image.

Conclusion

When we attempt to directly reference a table property that's 2 parent levels above the current table, we encounter the error "multiple levels of many-to-one relationship expansion aren't supported". The workaround for this error is to explicitly lookup a related record at a level that doesn't exceed 2 parent levels, and we can use this record to access the target field value.