Create view for a Many to Many relationship using a custom junction table

[danielmbaquero]

Context:

I´m creating a model driven app to manage the activities and projects that are executed on my area. I was struggling with how to assign an activity to several users, this would be a N:M relationship cause a user can have several activities assigned to him and any given activity can have several users assigned. I read a forum where it recommends creating a custom junction table instead of using the default many to many relationships available in Dataverse. This was useful as it makes it possible to create a subgrid in the new activity form where the coordinator can assign different users to an activity. This is a diagram of the data structure:

ER schema

My need:

Now, I need to create a view, report or anything that let me see all the activities a given user is assigned to. Something like:

User 1 Name:
	Activity 1 name	Other fields form the activity table
	Activity 2 name	Other fields form the activity table
User 2 Name:
	Activity 2 name	Other fields form the activity table
	Activity 3 name	Other fields form the activity table

Does anybody know how to achieve this?

[rampprakash]

Hi @danielmbaquero,

Yes what you read is correct, in Model Driven app we cannot view N:N relationship with View

So to achieve your scinario, we can use Junction Table and can display the related values using Groupby User

[danielmbaquero]

Hi @rampprakash,

I may be not getting something pretty basic, but, how do I create that "junction display" and use the group by?

[rampprakash · Accepted solution]

Hello @danielmbaquero,

In the Reports try creating a new Report for Junction Table

in the Top Grouping Add User 

then in the Below Add Activities

Please mark as Answer if it is helpful and provide Kudos

https://microsoftcrmtechie.blogspot.com
https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA