Answered

Issue with creating N:N relationship on Virtual tables in dataverse

(0) Share

Report

Posted on by Odidepse

Hello Everyone!

I'm currently working on developing a model-driven app using Dataverse virtual tables, all of which originate from the same Azure SQL database. I'm facing challenges in establishing N:N relationships between the two entities mentioned below (Food and Person).

I'm wondering if it's indeed feasible to create N:N relationships between two virtual tables (both originating from the same SQL database data source - Food and Person), where the intersect table is also a virtual table?

When a user accesses a 'Person Table' record, it's essential that they can associate it with 'Food records (via subgrid) through N:N relationships. Any updates made to this association should be reflected in the intersect/junction table.

Here are the steps I've taken:

Created a 1:N relationship between Food and the intersect table.
Created a 1:N relationship between Person and the intersect table.
The intersect table has a lookup field for the relationship.

However, I'm still unable to create an N:N relationship between Food and Person for me to be able to create the subgrid. Has anyone encountered a similar scenario and successfully achieved this, or do you have any insights or suggestions?

Cheers

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

gulshankhurana 1,397 Moderator on at

Like (0)

Report

Hi @Odidepse

As you have an intersect table through which both Food and Person tables are connected, you do not need to separately create/define N:N relationship between these two tables.

Kind regards
Gulshan

Was this reply helpful? Yes No
Odidepse 16 on at

Like (0)

Report

@gulshankhurana

Thanks for your reply you are right but I cannot make it work. I had a second look and found out that the intersect table does not have a foreign key defined with the intersect table only have a primary key, the person id and food id are just columns. Unfortunately we cannot update the table and assign person id and food id as foreign key which is a current limitation on Synapse (can only create primary key). This would require me to establish relationship with the intersect table but unsuccessful. If I create a 1:N with the intersect, it creates a new lookup column which I cannot really use. I looked at how it behaves on dataverse when you create the many to many relationships between 2 tables, it creates the foreign key on the junction. I am looking at a way to create a relationship between existing column id on the intersect (e.g. column Person ID on Person table and Intersect table on column Person ID) via the maker portal (even on the classic interface if it is possible).

Was this reply helpful? Yes No
Verified answer

a33ik 3,306 Most Valuable Professional on at

Like (1)

Report

Hello,

It's not possible to create a native N-N relationship between 2 virtual tables - it's a platform limitation. More detailed information you can find here - https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/api-considerations-ve#table-definition-property-behavior-changes to be more specific:

In order to address your scenario you can use Manual N-N relationship. You can find a good explanation here - https://www.crmlady.com/2019/01/manual-many-to-many-relationships-nn.html

Was this reply helpful? Yes No
Odidepse 16 on at

Like (0)

Report

Thanks for your reply much appreciated. Unfortunately that wont work on my scenario where I have all three tables as virtual tables, the intersect table(virtual) has an existing personid and foodid and new relationships created by the user creates an intersectid.

If I create a new relationship of 1:N between the person table and the intersect table, it creates that new lookup column instead of associating it with the existing person id between personid.

I was really hoping there is something where I can establish relationship between the two 1:N tables on the intersect where the intersect already has the personid and foodid.

I encountered a huge block on the limitation of synapse to make the personid or foodid as fk, if those 2 existing id on the intersect table are tagged as foreign key, I won't need to create relationship as what @gulshankhurana mentioned.

I was looking for a way if dataverse side of creating 1:N relationships on the virtual intersect table with the objective of creating a many to many relationship with food and person via the existing intersect virtual table. Option I am looking at if this is not possible is to create the intersect table on dataverse and dataflow on the dataverse intersect table to the intersect table on SQL as it is a requirement to have the relationship records on SQL. Any thoughts is highly appreciated. Cheers

Was this reply helpful? Yes No
Odidepse 16 on at

Like (0)

Report

If you're considering constructing a PowerApp using serverless SQL (Data Lake) and virtual tables, especially for an N:N relationship, it's best to reconsider due to the substantial effort involved. Currently, it's not feasible to establish an intersect table with assigned foreign keys in serverless SQL. I've decided to move away from the virtual table concept and opt for dataflows instead. This approach involves creating and synchronizing tables with Dataverse and building the app from there, leveraging a well-structured Azure SQL relational database in the backend. While the virtual table idea is intriguing, I think it is yet to fully be developed and its potential and would need some improvements.

I'd like to highlight a crucial point for those exploring virtual table authentications: despite service principal being the initial choice presented, it's important to note that this authentication method isn't functional at the moment. Microsoft has confirmed that authentication via service principal is currently limited to logic apps and won't work for virtual tables.

Was this reply helpful? Yes No