Retrieve Many-to-many (N:N) related records in Dynamics 365 using Microsoft Flow

By | January 11, 2019

Introduction:

Microsoft Flow has definitely leveraged the capabilities of Dynamics 365 to a next level and with the long list of connectors, it provides seamless integration across various different digital ecosystems.

In this blog we are going to discuss a workaround to retrieve records linked to each other via many to many relationship in Dynamics 365.

Currently, flow provides the capability to retrieve records linked to each other via One to Many relationship by using expand property but when we used the expand property to retrieve records linked to each other via Many to Many relationship, it didn’t provided the expected response. As a workaround we have to pull the records via a link entity (An intermediate entity that holds record of relationship between two different entities).

For our reference we would take the example of Marketing list and Contact records as they are linked to each other via Many to Many (N:N) relationship. We would perform some operation on list related contact records.

Following are the steps to achieve the above described objective:

Retrieve Many to many related records in Dynamics 365 using Microsoft Flow

In the above image you can see that we have used the link entity named “listmembers” which holds the link between contacts and marketing list, this listmembers entity is not available by default but you can access it by selecting Enter custom value from the Entity Name dropdown and then write it manually in the Entity Name text field.

In order to filter the relevant records we have used a filter query that makes sure that only rows that contain link between the list and contact with the provided guid are retrieved and nothing else should be retrieved so we have used _listid_value eq <guid>.

Once we get the list of link entity records then we will use an apply to each to retrieve contacts from Dynamics CRM one by one and we can perform any further operation after the retrieve contact records(list record step).

  • Listmembers – It is the name of our link entity(may differ depending on your related entities)
  • _listid_value – It represents listid of Marketing list in link entity record
  • Entity(field name) – It represents contactid in link entity record

Note: The guid of the _list_value may differ as per your requirement. Also the unique identifier for the contactid is a parameter named Entity from the retrieved list which may also differ depending on the link entity.

Conclusion:

Using above steps user can retrieve records linked to each other via many to many relationship in Dynamics 365 using Microsoft Flow.

Free 70% of storage space in CRM with Attachment Management Apps!

Attach2Dynamics – Store and manage documents/attachments in cloud storage of your choice – SharePoint, Dropbox or Azure Blob Storage from within Dynamics 365 CRM.
SharePoint Security Sync – Robust and secure solution to integrate Dynamics 365 CRM and SharePoint Security Sync thereby ensuring secure access to confidential documents stored in SharePoint.

5 thoughts on “Retrieve Many-to-many (N:N) related records in Dynamics 365 using Microsoft Flow

  1. Jack

    I have an N:N in D365 and can see the joining table/entity through the Metadata Browser in XRMToolbox, but if I manually type in like you suggest it’s saying the entity doesn’t exist. I literally copied and pasted the entity name just to be safe, no luck. Any thoughts?

    1. inogic

      You can only call the entity in Microsoft flow or data query by its entity set name and for the link entity you might use the link entity name rather than its plural name or entity set name.
      Solution: – Please follow the below steps to get link entity’s set name.
      1) Open the N-N relationship.
      2) Copy the Relationship Entity Name.
      3) Go to your custom flow, paste Relationship Entity Name in the entity name field and without giving any space add ‘set’ into it.
      For example:- new_account_target to new_account_targetset.
      Or you can verify the entity set name by using the given link if that does not work. And make sure that for setting up the filter criteria you have taken the property name by referring it as well and search entity set name. https:///api/data/v9.1/EntityDefinitions?$filter=LogicalName%20eq%20%27< add you relationship entity name here >%27

      Thanks!

  2. bala S

    HI, there is N:n relationship between Contact and Forum (adx entity in Community portal). I’m using the intersect entity in the list records step but in the next step where I need to access the contact, the contacted doesn’t show up in the Dynamics content. How can I access the contact id so I can play around with the Contact record?
    Thanks!!

    1. Inogic

      First confirm the logical name of contact id in intersect entity, for that go to api and search for your intersect entity as shown in below screenshot:
      In my case my intersect entity name is “new_contact_new_forumset” and logical name of contact id is “contactid”.
      Microsoft Flow
      You will not get it in Dynamic content in flow so you need to type an expression “items(‘Apply_to_each’)?[‘contactid’]” to access “contactid”.
      In flow you can use it as shown in below screenshot:
      Microsoft Flow
      Hope this helps.
      Thanks!

      1. khat

        Hi Inogic,

        You post helped me lotssss..thank you! God bless.

Comments are closed.