Filter on Many-to-many criteria

[shavora]

So, I have a business requirement that only users who are related to a course should be able to view the course in our Course tool in Powerapps. 

The best way would be to be able to filter the gallery as follows: 

Filter('Course schedule', xxx_Courseschedule_SystemUser_SystemUser.'Primary email' = User().Email)

This gives errors that I am comparing a table with text.

Filter('Course schedule',Lower(User().Email) in Lower(xxx_Courseschedule_SystemUser_SystemUser.'Primary email'))

This gives a delegation warning and I don't get any results, although it's below max amount of records. 

Same with using the distinct:

Filter('Course schedule',Lower(User().Email) in Lower(Distinct(xx_Courseschedule_SystemUser_SystemUser,'Primary email')))

Due to that many people can be logged to one course and that many people are logged to several courses, it's a many-to-many relationship. Using Dataverse and Office365Users. Is this a lost cause?

Could a collection help?

[fuzzy_fuzz]

Can you tell more about what xxx_Courseschedule_SystemUser_SystemUser is?

[shavora]

Hey @fuzzy_fuzz yes it's the relationship name between the datasources. You will find it in the relationship tab on the entity.

[shavora]

@fuzzy_fuzz Do you think it should be something else?

[fuzzy_fuzz]

Is this xxx_Courseschedule_SystemUser_SystemUser  stored in a Sharepoint list?

I would suggest to make a collection with xxx_Courseschedule_SystemUser_SystemUser.'Primary email' to see what is inside, so u can access to the data that matters to you.

ClearCollect(whateverCol, xxx_Courseschedule_SystemUser_SystemUser.'Primary email')

 Run this code with a button and see what's inside File->Collections->whateverCol

[shavora]

hi @fuzzy_fuzz it's in a Dataverse, not Sharepoint. Basically, when two tables are joined with many-to-many relationships a hidden joint table is created between them, hence they don't work like look-ups.

Unfortunately collections don't seem to work on the relationship-name alone. It was a good thought! 🙂

[shavora]

Does anyone have any idea how to solve the business requirement?

Basically we have a need for many-to-many relationship between users and courses AND the requirement that only the courses you have should be visible. I tried looking into security roles and none could be specified from a relation of records, and neither have I found a way to filter it on User().Email.

Please advice!

[shavora · Accepted solution]

So, I have found a solution for this. Hoping it will hold up...

For other people who might be looking into ways into filtering data on a Many-to-many relationship between two tables I have collected the steps by steps.

The way that I did this is by first creating a gallery with SystemUsers. I filter this on the current users email:

Filter(SystemUser, Lower(Text('Primary email')) = Lower(User().Email))

I added a check-box control so that the user has to click on their record.

By clicking the gallery item we ensure that in a hidden gallery control all related records are listen:

GalleryUsers.Selected.'Course schedule (key_Courseschedule_SystemUser_SystemUser)'

Here we reference the relationship name between the two related tables.

 

In a button control that is made visible when the checkbox is ticked add a ClearCollect with AllItems in the hidden gallery.

 

In the gallery where you want to filter on related items we now can filter on the collection:

Filter('Course schedule', 'Course schedule' in ColCourses.'Course schedule')

 

Hoping this can help someone else working with many-to-many relationships where teachers can have many studentsand the students can have many teachers. This is geared for filtering on the current user.

[Anonymous]

This is amazing, I've been banging my head for weeks and you sir have resolved the same business requirement I have! Thanks a bunch 😄 Although, I have a delegation warning because of "in" collection, but that okay, a solution is better than no solution!

You're a hero!

[Anonymous]

resolved delegation warning by using a flexible vertical gallery and setting Visible conditions on the item components themselves