Blog
Dataverse - How to work around the error "multiple levels of many-to-one relationship expansion aren't supported"
Have you encountered the error "multiple levels of many-to-one relationship expansion aren't supported" when attempting to write some formula? If so, this post clarifies the meaning of this error and describes how to work around the issue.
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.
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.BookImageWith this formula, the book image now displays correctly as shown here:
Conclusion
- Categories:
- dataverse
- Error - Diagnosing the error "Network error when using the Patch function" when saving data with a form
- Dataverse - The easiest way to modify Dataverse data outside of Dataverse and Power Apps
- Dataverse - How to use a checkbox control a set a yes/no field in Dataverse
- Dataverse - How to create Entity Relationship diagrams
- Dataverse - How you can more quickly bulk update data using the SQL language
- Dataverse - How to fix the bug in the 'Business Rules' editor that prevents numeric values from saving
- Dataverse - What are the benefits, and how to create formula columns
- Dataverse - How to sort the available choice items from a choice column
- Error - Unable to modify Dataverse tables with error message, Language id should not be null
- Dataverse - How to retrieve FetchXML or SQL for Dataverse views
- Data - Why is Dataverse so fast?
- Dataverse - How to create and use Dataverse views in Canvas Apps
- Dataverse - How to filter Dataverse choice columns
- Dataverse - How to switch to the classic designer when the option is not available
- Licensing - What are Dataverse Restricted tables?
- Media - How to save and work with microphone recordings
- Dataverse - how to access tables from another environment
- Dataverse - How to Patch the 5 most complex data types