Collection of many to many relationship dataverse

[barbossa_94]

Hey all,

i am trying ot achieve to create a collection for my on start property of my canvas app. I have an employee table with names and a table with expertises looking like shown below. The tables have a many to many relationship. 

NameTable

Name	email	....
Max	max@gmail.com
Anna	anna@gmail.com
....

Expertise Table

Category	Expertise
Software	AutoCad
Software	Powerapps
Software	Maptek
Processing	Planning
Processing	Design
Consulting	Onboarding

My desired outcome as a collection is this:

Name	Category	Expertise String
Max	Software	AutoCad | PowerApps | Maptek
Max	Processing	Planning
Anna	Software	AutoCad | PowerApps
Anna	Consulting	Onboarding
...

The idea is to have a collection that shows all names with all their expertises. A simple version like this would also do for me, however the above is prefered.

Name	Expertise
Max	AutoCad
Max	PowerApps
Max	Maptek
Max	Planning
Anna	AutoCad
Anna	PowerApps
...

I have no idea how to get a collection with all records from the name table. Usually i go and select a name in a gallery and then just have a second gallery that uses a function like gallery1.selected.Expertise but this will only show it for one person of course.

Any help towards those many to many relationships are highly welcomed.

[citdevpros]

Hi @barbossa_94 ,

where is the information that says e.g. "Max is related to Processing/Planning"? 

The "desired outcome" collection somehow knows this, but it can't be determined from only the Name and Expertise tables. Many-to-Many relationships have a table in between the 2 related entities.

[barbossa_94]

Hi @citdevpros,

you are correct. if you do M-M relationship you need an intersect table. if you set up a many to many relationship in dataverse it creates this table automatically, but we dont get to see it (read here: Create many-to-many table relationships in Microsoft Dataverse overview - Power Apps | Microsoft Lea...) 

Since we can reference it through galleries though (e.g. gallery 1 has the names and then gallery 2 you just set to gallery1.selected.Expertise ) i am wondering how to reference the intersect table to create a collection

[citdevpros]

My apologies, in my read of your question I made the error of making an incorrect assumption.

Edit 2024-02-29: see follow up reply. It is possible to address the relationship. However the part of this post showing that the relationship doesn't manifest in a collection when you collect the data source is still true.

I am happy to be proven wrong, but I don't believe that the "Expertise" property of the Name table data source is addressable in any way. Granted it can be used (per your example scenario) in the second gallery, but it appears that galleries have super powers to see the intersect table when they look at the data source.

To support this contention, when you ClearCollect a collection from the Name data source, either straight from the Name data source e.g.

ClearCollect(col_NameExpertise, Name)

or by using Name in the Items property of a gallery and then collecting AllItems of the gallery e.g.

ClearCollect(col_NameExpertise, gal_Names.AllItems)

and then you view the collection on the variables screen, the Expertise column is not shown. I would expect there to be a column of type table that you can ungroup, but clearly its absence suggests that the intersect table doesn't manifest as a data type we can work with.

I see no way to get this data into a collection other than to remove the relationship and create your own relationship table as a regular table. 

[citdevpros]

Hi @barbossa_94 ,

I managed to prove myself wrong. PowerApps doesn't make this easy to figure out, even though the solution is simple.

This creates your "desired" outcome. but without the Category column.

ForAll(Name As T1, {Name: T1.Name, Expertise: Concat(Expertise, Expertise, " | ")})

I am not sure how this will work for you given that I am simply using the table and column names you provided in your example, and you use the words Name and Expertise for tables and fields. In my testing I had unique naming.

The challenge lies in referencing. PowerApps won't let me do the ForAll without the "As T1" alias, and Intellisense then offers me T1.Expertise in the Concat to reference the related table/relationship, but it will tell you that the column is "inaccessible in this context". Take off the alias and it works. What I am trying to say is THESE don't work

ForAll(Name As T1, {Name: T1.Name, Expertise: Concat(T1.Expertise, Expertise, " | ")}) //doesn't like T1.Expertise

ForAll(Name As T1, {Name: Name, Expertise: Concat(Expertise, Expertise, " | ")}) //No T1 prefix on Name column

ForAll(Name, {Name: Name, Expertise: Concat(Expertise, Expertise, " | ")}) //No "As T1", wont' recognise Expertise as a table in Concat

 Hope you can make that work.

[barbossa_94]

Ok i made some progress in here with @citdevpros 's help. 

To achieve a collection which holds the name and then a string of the expertises you have to do the following:

First of all: In the canvas builder go to Settings --> Upcoming features --> and activate "Record scope one-to-many and many-to-many relationships"

Then create a collection as follows:

ClearCollect(col1,

ForAll(

NameTable as T1,

{Name: NameTable.Name,

Expertise: Concat(T1.ExpertiseTable,Expertise ," | "(

}

)

)

This results in a table with the names and a string with their expertises.

However the category is missing. @citdevpros With this knowledge gained, do you think we can realize a table like this?

Name	Expertise	Category
Max	AutoCad	Software
Max	PowerApps	Software
Max	Planning	Processing
Anna	AutoCaD	Software
Anna	....	....
...	....	...

[citdevpros · Accepted solution]

So caveat emptor on using experimental features. However turning on the "Record Scope" feature does do the trick. It is not straight forward though - despite this feature "allowing table operations", the fact remains (per my previous post) that the relationship doesn't manifest as a column we can reference, so we can't just simply ungroup it.

I am not saying this is the perfect answer, but it works.

This entire solution is based on Concat() being the only function I could get to actually reference the intersect table property of the NameTable datasource (there may be others).

Note to produce the data in the intersect table, you only need 3 steps. The 2 additional steps are only to group by category, per your original requirement. Here's the break down of the 5 steps, you could nest these but it doesn't make it easy to understand:

//Collect the related data as a nested delimited list
ClearCollect(col_Result_A, ForAll(NameTables As T1, {Name: T1.Name, xExpertise: Concat(T1.ExpertiseTables, Category & ";" & Expertise, "|")}));

Produces:

//Initiliase the next collection, we will use it as a Collect-only in the ForAll
ClearCollect(col_Result_B, Blank());

//Split the outer delimited values onto their own rows, collect in the inner collection col_Result_B, col_temp is just a dummy
ClearCollect(col_temp, ForAll(col_Result_A, ForAll(Split(xExpertise, "|"), Collect(col_Result_B, {Name:Name,  xSplit: Value}))));

 Produces (the Value column comes from initialising with Blank()):

//Split the category/expertise pairs into their own columns
ClearCollect(col_Result_C, ForAll(col_Result_B, {Name: Name, Category: First(Split(xSplit, ";")).Value, Software: Last(Split(xSplit, ";")).Value}));

 Produces:

//Group expertise by name/category
ClearCollect(col_Result_D, GroupBy(col_Result_C, "Name", "Category", "Expertise"));

Produces:

//Create pipe delimited list of expertise, now by the groupings
ClearCollect(col_Result_Final, ForAll(col_Result_D, {Name: Name, Category: Category, Expertise: Concat(Expertise, Software, "|")}) );

Produces:

Use this collection as the Items property of a table, and you have your original requirement.

[barbossa_94]

Hi @citdevpros ,

first of all, Thank you. This does work and leads to the result wanted. Also pretty straight forward once you understand it. Thanks for breaking it down. Is there a way to nest it smarter (more efficient) than simply sequencing all the functions with ";" ?

[citdevpros]

@barbossa_94 you're welcome, was a good learning for me too.

You can consolidate it, but only so far and it's pretty much impossible to make sense of, even less so if someone else wants to try to understand what you have done.

ClearCollect(col_Result_B, Blank());
ClearCollect(col_temp, ForAll(ForAll(NameTables As T1, {Name: T1.Name, xExpertise: Concat(T1.ExpertiseTables, Category & ";" & Expertise, "|")}), ForAll(Split(xExpertise, "|"), Collect(col_Result_B, {Name:Name,  xSplit: Value}))));
ClearCollect(col_Result_Final, ForAll(GroupBy(ForAll(col_Result_B, {Name: Name, Category: First(Split(xSplit, ";")).Value, Software: Last(Split(xSplit, ";")).Value}), "Name", "Category", "Expertise"), {Name: Name, Category: Category, Expertise: Concat(Expertise, Software, "|")}) );

You can't nest the first line; you have to initialise that collection as empty and you can't nest that. You can't nest the second line into the third, since it's not col_temp that you want, it's col_Result_B that you populate inside the 3rd ForAll loop.

Good luck with it.

[barbossa_94]

@citdevpros thanks again. This was certainly a life saver. I do believe that microsoft has to make this intersect table accessible somehow in the future. This is not sustainable at all to offer the many to many feature without doing so. For now I recommend everyone else to build an intersect table manually instead of using the microsoft virtual one.