RSMUS.comN:N relationships can be a great way to identify related records, however, they don’t provide us much value in the database side of things… or so I thought. It turns out, Microsoft actually does records of N:N relationships, but not in a simple, user-friendly way.
Microsoft seems to create a sort of makeshift entity that stores the intersection of the related records. You can find this “entity” by navigating to https://{crmdomain}.api.crm.dynamics.com/api/data/v9.0/. Once you get there, you will likely be overwhelmed with a screen full of text that goes on forever. Use the crtl + f search function to find the schema you are looking for. The schema will look something like entity1_entity2set. Note that at the end of the schema is the word “set”. This is how Microsoft is denoting the N:N relationship.
The image below show a portion of the results you will see when navigating to the link listed above. The example shown here displays an intersection of Microsoft’s Incident and Customer Asset entities, with a new schema of msdyn_incident_msdyn_customerassetset.
Example:
If you are looking for an N:N relationship between contacts and opportunities, search for contact using crtl + f. You will be looking for something along the lines of _opportunity_contactset. If one of the entities in the relationship is custom, expect the prefix to be in front of the entity names.
Now that you have your “intersection entity” schema name, you can query for records in that entity on a specific opportunity, for example. You will need to enter in the custom entity name, as it will not show up in the list of entities. In order to filter based on records related to a specific opportunity, you would act as if there was an opportunity lookup on the intersection entity, and filter the “opportunity lookup” for the specific opportunity.
Now you have pulled the records you need and can continue on with your flow!
Marti Freudenberg
Marti is a Senior Consultant in RSM's Dynamics practice. She has experience working with many aspects of Dynamics CE and is highly skilled in Power Automate Flows, and Portals. She specializes in Nonprofit and Association clients. LinkedIn