Flow to list records from a Many to Many relationship

[Anonymous]

Hi All,

I have 2 tables regGroups and regPeople. They are related to each other in a Many to Many (NN) relationship.

I'm trying to create a scheduled flow to extract the related information for each group (regGroup.regPeople) to a separate table (with regGroup and regPerson columns) but just can't figure it out.

Can anyone point me in the right direction please?

Many thanks,

Jim

[dpoggemann]

Hi @Anonymous,

Please take a look at the following post for a user with similar issue working with the N:N relationships (https://powerusers.microsoft.com/t5/Building-Flows/Lookup-related-Many-to-Many-N-N-records-using-Flow/td-p/321147) 

Overall I personally don't like working with "N:N" relationships unless I have to.  I know sometimes it makes sense but I am unable to add metadata in this relationship since Microsoft is maintaining the table behind the scenes.  I usually recommend creating a table in the middle so you have a 1:N relationship on both sides.

Example:

regGroup --- 1:N --->   regGroupPeople <--- N:1 --- regPeople

This way you can add fields to the regGroupPeople to more identify this relationship and it really simplifies working with the data in forms / views / integrations with flow, etc.   Almost looks like you were trying to create this with the flow you are building.  I would explicitly create it in the Dataverse and maintain in this fashion.

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[Anonymous · Accepted solution]

Hi @dpoggemann , many thanks

I was able to access the NN records using the set suffix approach described in the linked post using the Dataverse connector instead.

From make.powerapps.com, I selected Data > Tables > and then the table name and under Relationships relationship table name e.g. XXX_reggroup_regperson

In the Flow, I created a Dataverse List rows action and set the Table name to XXX_regroup_regpersonset. It is necessary to add the set suffix to the table name. I also set 'Select columns' to XXX_reggroupid, XXX_regpersonid to improve performance.

I then added an 'Apply to each' action and set the output to the dynamic value (from the previous step).

Finally I added A DataVerse 'Add a new row' action to the 'Apply to each' action, selected the target table xxxMemberships as the table name, set regGroups to xxx_reggroupid (dynamic field from the previous step), and set regPerson to xxx_regpersonid.

IMPORTANT NOTE RE LOOKUP FIELDS: -

As these were lookup fields I had to then wrap each field with the name of the tables on the lookup /xxx_reggroups() and xxx_regpersons() respectively so it looked like: -

/xxx_reggroups(xxx_reggroupid)

/xxx_regpersons(xxx_regpersonid)

IMPORTANT GOTCHA: The DataVerse connector does not respect plural naming conventions! my xxxPerson table's plural table name is actually xxxPeople but that throws a 'Resource not found for the segment' error. I had to use xxxpersons LOL!

Done, all now working!

You may be right about creating a relationship table and indeed I had tried this approach but found it added complexity and performance issues managing the relationship metadata so I wanted to compare to letting the system manage the relationship for me and use flow to create a daily membership table from the related data to hold pertinent registration meta data.

I hope this helps others and thanks again for putting me on the right track,

Cheers, Jim

[kamstutz]

When creating the flow, what is the trigger?