Many-to-Many, Gallery Inside Gallery

(0) Share

Report

Posted on by IPC_ahaas

1,387 Moderator

I have a Gallery of "Sites" that pulls from a Dataverse table.

I have a Gallery of "Sections" that pulls from a Dataverse table.

I have the Gallery of "Sections" within the Gallery of "Sites.

I have a Many-to-Many Relationship that connects the Sections with the Sites.

I noticed when I created the relationship, there is no visible column on either Table.

1. How do I populate this Relationship with values?

2. How do I represent the values with a nested Gallery?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @IPC_ahaas ,

Please refer to the Relate and Unrelate functions document:

Relate and Unrelate functions - Power Platform | Microsoft Learn

To put it simply, when two tables have N:N relationship, you can use Relate function to build the connection for 1 record in table A with mutiple records in table B, and vise versa. In sub-Gallery, you can use the relationship name to get related records from table B based on current table A record:

Best regards,

Was this reply helpful? Yes No
Verified answer

ivan_apps 2,189 Moderator on at

Like (0)

Report
Copy link

Link copied!

You are correct that there is no visible column for N:N relationships. Columns are only created for 1:N or N:1 relationships. What is created in Dataverse for N:N relationships is an intersect table that will hold the associations to the relationship.

If you open a many to many relationship you will see the intersect table name as well as the schema name for it. Unfortunately Power Apps canvas apps don’t do a great job of bringing this intersect table in or giving you that option. You can visualize it easily in Model apps but for canvas you must implement a workaround.

Workaround - you can bring this relationship data using Power Automate. It will be able to return to you the intersect table data you are looking for and then you can store it in a collection. I’ve implemented it to return the ‘teammembership’ table associations and there is a bit of gotchas and json parsing but it’s possible. Once you have it in a collection, you can implement nested Galleries as you would normally, having the inner Gallery reference the collection.

Was this reply helpful? Yes No
IPC_ahaas 1,387 Moderator on at

Like (0)

Report
Copy link

Link copied!

@v-jefferni @ivan_apps So, hilariously, it sounds like the easiest way to do this would be to instead manually make a separate table as an intermediatory with 1:N and N:1 with the other two tables ala Access 97. Then I'd have a real table that I can see and populate with data

Was this reply helpful? Yes No
ivan_apps 2,189 Moderator on at

Like (1)

Report
Copy link

Link copied!

That is very true - what you are doing is a custom N:N relationship and actually gives you more flexibility because you can add relationship properties whereas the out-of-the-box relationship doesn’t allow that.
for example, you can add how long that relationship is in place by adding dates to your intersect table.
Nothing wrong with that approach!

Was this reply helpful? Yes No
IPC_ahaas 1,387 Moderator on at

Like (1)

Report
Copy link

Link copied!

Thank you for the encouragement @ivan_apps , but I ended up surrendering to using a lookup and having "duplicate" values in one of my tables. It just made things easier. I'm sure I could figure it out on a longer time frame using N:N via the built in method or a custom table.

Was this reply helpful? Yes No