cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

M:M Filter in CDS

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..

8 REPLIES 8
Highlighted
Community Champion
Community Champion

Re: M:M Filter in CDS

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.

Highlighted
New Member

Re: M:M Filter in CDS

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

Highlighted
Super User
Super User

Re: M:M Filter in CDS

Hi @karthickdtech 

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-...    

Highlighted
New Member

Re: M:M Filter in CDS

@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

 

Highlighted
Super User
Super User

Re: M:M Filter in CDS

Hi @karthickdtech 

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? 

Highlighted
New Member

Re: M:M Filter in CDS

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

Highlighted
Super User
Super User

Re: M:M Filter in CDS

Hi @karthickdtech 

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.

Highlighted
New Member

Re: M:M Filter in CDS

@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

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,233)