Many to many relationship- no related records

[Meshare]

Hi All, 

I am new to dataverse. I have two tables Department and Members and Department can have many members and members can be in  many departments .There is no lookup column between them . As per MS document there is no need to have lookup  for  many to many relationship so I created many to many relationship between these table and thinking that records will be related automatically based on one column available in both table which is having common text values .

1.My first query is can we create many to many relationship without having lookup field..

2.If yes then when I am adding a subgrid in main form of Department table to show related records of Members tables based on department selected  I can't see any related records. What is the reason for that.

If above is not applicable then please let me know how can I relate these tables to show related records of each other in model driven app.

[dpoggemann]

Hi @Meshare ,

In an N:N relationship, Microsoft will create a table behind the scenes to manage this and the table contains the primary keys from each related table, something like this:

Setting up the subgrid requires you to change the relationship setup between these two tables to allow it to be displayed when setting up the subgrid.  Here is an older article that is showing the legacy user interface but should give you the details you need to setup.  https://d365demystified.com/2022/08/06/adding-nn-relationships-in-sub-grids-correctly-in-dynamics-36... 

[Meshare]

@dpoggemann , thanks for the reply but my query is without lookup can we create N:N relationship and subgrid is just one of the way to show related records in model driven app . I believe if the relationship is established it automatically add associated view to show related records which in my case Is not working.Not sure if the relationship is built correctly. I don't know any other method to verify this.

[GuidoPreite]

inside an N:N relationship the values are not automatically mapped based on a common value, the user will need to manually define which records are connected

[Meshare]

@GuidoPreite , ok so you mean even after realationship is created we have to manually do few extra steps to relate records which each other .what is the process please can you elaborate.

[GuidoPreite]

@Meshare if you are using a model-driven app you will see a button like "add existing [Name of the table]", if you are using a canvas app you need to implement the logic to add the related records. If you plan to automatically map the records when it's created you will need a plugin or another kind of code (like a power automate flow) to associate the records.

[dpoggemann]

Hi @Meshare 

Microsoft does track the relationship but you need to "Add Existing Record" in the relationship.  If you have defined the N:N relationship you should see the ability from "Table A" in the "Related" tab to select your other table and add relationships.

The following is an example of Clinics and Dentists that I have setup in an N:N relationship.  I can "Add Existing" to each of them to relate the records together.  Again, this is not done through any custom fields, it is behind the scenes in a table that Microsoft is maintaining.

From Clinic table where I can add related Dentists...

From Dentist I can see the same and add relationships to clinics:

[Meshare]

@dpoggemann , I can see the same behaviour with my tables as you have explained so that means the relationship is setup correctly. Just a quick clarification that without manually relating the existing items from other table is it possible that automatically we can relate records with each other in many to many relationship so that in model driven app it automatically show related records for each other . I know @GuidoPreite explained in the thread that it is not possible without any plugin or power automate but just a double check with you. 

[dpoggemann]

HI @Meshare ,

No, this is not possible without some type of plugin / power automate flow / or other approach to utilize other fields on the tables to add the relationship (or remove if these values change).

[Meshare]

@dpoggemann and @GuidoPreite , could you please refer any plugin or powerautomate example which could automatically relate records. I have tried power automate 'Relate Rows' action to relate records but it works like 1:N . It relates record from one table to many records from another but not vice versa. 

Please suggest in case you have any reference point in this .