Many-to-Many Relationship

[CNT]

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

[v-yutliu-msft]

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:

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,

[CNT]

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.

[v-yutliu-msft · Accepted solution]

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

 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,

[CNT]

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, 

Relate( Entity1RelatedTable, Entity2Record )

Unrelate( Entity1RelatedTable, Entity2Record )

Entity1RelatedTable is WRONG it should be Entity1RelationshipName

Please do these changes and save precious time for others.

Thanks.