cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNT
Super User
Super User

Many-to-Many Relationship

Hello
I have a M-to-M relationship between two entities User and Location (Custom entity). I've used the Relate function to relate the records from these 2 entities and that works fine.
I my Canvas app I have 2 galleries - One for User and the other for Location.
 
In the Item Property of the Location gallery when I use the following formula, it shows the related Locations only for me (I am a Global admin).
galSelectUser.Selected.Location
But when I use the following formula (where cxe006_SystemUser_cxe006_Location is the name of the relationship), it shows the Locations for all users.
galSelectUser.Selected.'Location(cxe006_SystemUser_cxe006_Location)'
 
Any idea why it works like this. Is this a bug? 
 
 or is there any valid reason for this behaviour. Any help would be greatly appreciated.
 
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions

Hi @CNT ,

After a lot of tests and I know how to figure out this problem.

The key point is distinguish where to use relationship name and entity name.

(Sorry that I made a little mistake before, please follow my following steps)

1)create N:N relationship between User entity and Location entity in CDS

in my test is test422 entity and User entity

4221.PNG

 Please notice: my relationship name is 'test422S (crba2_test422_SystemUser_SystemUser)'

2)in powerapps, use Relate and Unrelate function to create relationship

insert combo box1, set its Items:

Users

insert combo box2, set its Items:

test422S

On your side, it should be: Users, Locations (use entity name here)

insert a button to relate, set the button's OnSelect:

Relate(Combobox1.Selected.'test422S (crba2_test422_SystemUser_SystemUser)',Combobox2.Selected)

On your side, you should use formula like this:

Relate(Combobox1.Selected.'Location(cxe006_SystemUser_cxe006_Location)',Combobox2.Selected)

(use relationship name here)

3)filter based on selected user

insert gallery1, set its Items:

Users

insert gallery2, set its Items:

Gallery1.Selected.'test422S (crba2_test422_SystemUser_SystemUser)'

On your side, you should use:

Gallery1.Selected.'Location(cxe006_SystemUser_cxe006_Location)'

//you should use relationship name here.

 

Then gallery2 will only display the locations related to the selected user.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yutliu-msft
Community Support
Community Support

Hi @CNT ,

Do you want to filter Location  based on selected  User ( User and Location relationship is N:N)

If so, you do these steps:

1)create M:M relationship for these two entities like this:

4216.PNG

 

2)Relate and unrelate User with one or more Location

The formula should be like this:

Relate( record in User entity, record in Location entity )

For example:

Relate(First(User),First(Location))

3)filter Location  based on selected  User

gallery1's Item: User

gallery2's Item:

Gallery1.Selected.Location

//please notice that you need to use entity name in this formula, not relationship name in this formula.

 

I suggest you learn about the examples in this doc:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yutliu-msft 

 

//please notice that you need to use entity name in this formula, not relationship name in this formula.

I have done everything just as u have said. But for whatever reason, when I use the Entity name it shows ONLY the Locations related to me (Global Admin) and not other users, but when I use the relationship it shows for everyone. I know it is strange.

Could you please investigate further. Thanks.

Hi @CNT ,

After a lot of tests and I know how to figure out this problem.

The key point is distinguish where to use relationship name and entity name.

(Sorry that I made a little mistake before, please follow my following steps)

1)create N:N relationship between User entity and Location entity in CDS

in my test is test422 entity and User entity

4221.PNG

 Please notice: my relationship name is 'test422S (crba2_test422_SystemUser_SystemUser)'

2)in powerapps, use Relate and Unrelate function to create relationship

insert combo box1, set its Items:

Users

insert combo box2, set its Items:

test422S

On your side, it should be: Users, Locations (use entity name here)

insert a button to relate, set the button's OnSelect:

Relate(Combobox1.Selected.'test422S (crba2_test422_SystemUser_SystemUser)',Combobox2.Selected)

On your side, you should use formula like this:

Relate(Combobox1.Selected.'Location(cxe006_SystemUser_cxe006_Location)',Combobox2.Selected)

(use relationship name here)

3)filter based on selected user

insert gallery1, set its Items:

Users

insert gallery2, set its Items:

Gallery1.Selected.'test422S (crba2_test422_SystemUser_SystemUser)'

On your side, you should use:

Gallery1.Selected.'Location(cxe006_SystemUser_cxe006_Location)'

//you should use relationship name here.

 

Then gallery2 will only display the locations related to the selected user.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yutliu-msft 
OK, I've accepted u'r soultion. But there are 2 thing that need to be corrected in the following documentation page. 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate

Firstly, It does not talk about using the relationship. In the example it just says,

Relate( First( Products ).Contacts, First( Contacts ) ) - THIS DOES NOT WORK!

Secondly, the syntax for Relate and Unrelate say, 

RelateEntity1RelatedTableEntity2Record )

UnrelateEntity1RelatedTableEntity2Record )

Entity1RelatedTable is WRONG it should be Entity1RelationshipName

Please do these changes and save precious time for others.

Thanks.

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,616)