cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
eka24
Super User
Super User

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
Not applicable

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

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
Not applicable

@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

 

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
Not applicable

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

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
Not applicable

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,680)