Answered

Filter on Many-to-many criteria

(2) Share

Report

Posted on by Gyllentid

892

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?

Categories:

Connecting to Data

All responses (10)

Answers (1)

Gyllentid 892 on at

Like (0)

Report

Re: Filter on Many-to-many criteria

Yeah, there are many ways to work around the delegations. I have had to find them too. But sometimes it's not idea.

As per the annoyance with many to many, I have since created an in-between table in Dataverse to achieve the many to many. It became a little more seamless in app, but a little more tedious for the one working in model driven app. It's a give and take, innit.
Community Power Pla... on at

Like (1)

Report

Re: Filter on Many-to-many criteria

resolved delegation warning by using a flexible vertical gallery and setting Visible conditions on the item components themselves
Community Power Pla... on at

Like (0)

Report

Re: Filter on Many-to-many criteria

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!
Verified answer

Gyllentid 892 on at

Like (3)

Report
Re: Filter on Many-to-many criteria

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.
Gyllentid 892 on at

Like (0)

Report

Re: Filter on Many-to-many criteria

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!
Gyllentid 892 on at

Like (0)

Report

Re: Filter on Many-to-many criteria

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! 🙂
fuzzy_fuzz 229 on at

Like (0)

Report
Re: Filter on Many-to-many criteria

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
Gyllentid 892 on at

Like (0)

Report

Re: Filter on Many-to-many criteria

@fuzzy_fuzz Do you think it should be something else?
Gyllentid 892 on at

Like (0)

Report

Re: Filter on Many-to-many criteria

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

Like (0)

Report

Re: Filter on Many-to-many criteria

Can you tell more about what xxx_Courseschedule_SystemUser_SystemUser is?