Lookup related Many to Many (N:N) records using Flow

[muzhasan]

In my data model I have established a many to many (N:N) relationship between two Entities. When I Get a CDS Record in Flow, how can I lookup any of these Related records?

I have tried Getting Records using the N:N Reationship Entity Name as the Entity, but this isnt recognised as a valid source.

So as a work around I tried to use a Mutli-select Option Set. Again in Flow I was unable to see any of these multi select values on my Record.

Can anyone provide any guidance on how I can work with N:N relationships or Multi-select option sets in Flow? I am loathed to create Custom Entity just to hold the relationships...

[yashag2255]

Hi @muzhasan 

To get the related records for a particular record of the entity, you can loop through and get those records. Please see the solution in the below thread and check if that works for you:

https://powerusers.microsoft.com/t5/Building-Flows/Sales-Rep-from-Dynamics-to-HTML-Table/m-p/291418#...

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[muzhasan]

Thank yo for your response.

Unfortunately I am unable to use any Dynamics 365 actions due to a corporate policy in place. I can only access records using the CDS actions or Web Services.

Is there still a way for me to acheive this?

[yashag2255]

Hi @muzhasan 

Yes. You could do that with the CDS actions too. Please give it a try and let us know if you face any issues. 

[muzhasan]

It is not clear to me from this article how I need to approach this.

My problem is that when I Get a record from entity A, there are no dynamic fields available to the related entity B.

Maybe if we approach this the other way: How can I add a new N:N realtionship between two CDS entities using flow ? If this can be answered then it might explain now these relationships work.

[muzhasan · Accepted solution]

I just found an article which states how to access the related table.

https://www.sikich.com/insight/how-to-find-n-n-relationships-with-dynamics-365-logic-apps-and-flow/

Essentially you have suffix the relationship entity name with the word 'set' and then its can be found using the List records action. 

Just testing it now...

[donalmc]

There is a much easier way to do this than the suggested solution.

Essentially, query one side of the relationship and add the M2M table as a link entity.

You can even also add the other side as a link entity to the M2M table.

Entity A => Link Table <= Entity B

1. Set the query up with FetchXML Builder tool from XrmToolBox.
2. Copy the Fetch XML
3. Add a List Rows Dataverse action.
4. Select one side of the relationship (e.g. Table A)
5. Paste the Fetch XML into the Fetch XML Query field and tidy up

[CamFi]

@donalmc that sounds like a brilliant solution. Are you able to show how that would look like - i am very interested to know how to create that solution

[donalmc]

@CamFi - I'll describe it using the systemuser table

1. Open Fetch XML Builder in XRM Toolbox and create a new query.
2. Select the systemuser table and add the attributes you will need.
3. Right-click on systemuser and click add a link-entity.
4. Select the M:M relationship that you want to use.
5. Right-click on this table and add a filter that filters on the other table.
   
6. Click the FetchXML tab in the results window and copy the Fetch XML
7. Go to your flow, add the List Rows action for Users, paste the XML into Fetch XML Query window and update the value="00000000-0000-0000-0000-000000000000" with the GUID of the record on the other side of the relationship.