M:M Filter in CDS

[Anonymous]

Hi All,

I have an issue on filtering the users from the team.I am using CDS.

Scenario:

User Entity 

Team Entity 

both the entity has M-M relationship.

Onstart of the app For the current User I have filtered the teams and stored in the collection.

Code:ClearCollect(teammaint,First(User).'Teams (cre33_SystemUser_crd13_Team_crd13_Team)')

Now I need to filter all the users belong to each team

I am not able to filter it Can you guys help me on this..

[eka24]

Please try:

Set(LoggedUser,LookUp('User Entity',UserColumn=User().Email,TeamName) OnStart .

Then do this to filter:
Filter('Team Entity',TeamColumn=LoggedUser)

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Anonymous]

I am able to retrieve the current user -->teams

For example:

Karthick-->1.team A,team B

Now My issue is to find the other members who belong to this team A and team B

[Drrickryp]

Hi @Anonymous 

Remember that an N:N relationships is actually two 1:N relationships with a junction table.  PowerApps creates the junction table automatically when you select a many to many relationship when building entities.  However, it is possible, and sometimes desirable to create your own junction entity and then use it in formulas that can describe both sides of the relationship.  For more information and an illustration, see my post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-Step-3-Keys-and/ba-p/188640    

[Anonymous]

@Drrickryp  Thank you for the quick response 

Junction table has been created in default understand.

Now I need to pull the other way round from the list of teams to the users.

I have the team list of user A

User A->team A,team B in a collection

Now I tried ForAll  to iterate one by one the team and retrieved Users for each team 

Ex:Team A->userA,User B

Team B ->user C,user D

But when i store in the collection it is empty

So As per your suggestion I need to create a custom entity which has relationship with team and user entity.

It is correct

[Drrickryp]

Hi @Anonymous 

It looks like your issue is the same one illustrated in the documentation https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-references .  Does this help? 

[Anonymous]

@Drrickryp  Sure Ill go through the documentation and let you know

[Drrickryp]

Hi @Anonymous 

So as not to confuse you, there are two ways to deal with this.  One is to use the built in N:N relationship between entities.  That is what is referred to in the documentation in the post above yours. It has the advantage of simplicity as PowerApps does all the work.

The other approach is to build your own junction entity.  This latter has the advantage that you can add additional fields specific for that relationship.  For example, the classic N:N relationship between Students and Teachers.  The junction could describe a Class that includes the grade the student gets or the location of the class, neither of which belongs in either of the other tables.

[Anonymous]

@Drrickryp 

When i try to pull the users from team i am getting 501

dynamics.com/api/data/v9.0/crd13_teams?%24filter=contains%28crd13_name%2Ccrd13_name%29&%24select=crd13_teamid%2Ccrd13_name%2Ccre33_SystemUser_crd13_Team_crd13_Team 501