Answered

Filter on Many-to-many criteria

(2) Share

Report

Posted on by Gyllentid

894

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:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

fuzzy_fuzz 229 on at

Like
a
(0)

Report
Copy link

Link copied!

Can you tell more about what xxx_Courseschedule_SystemUser_SystemUser is?

Was this reply helpful? Yes No
Gyllentid 894 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Gyllentid 894 on at

Like
a
(0)

Report
Copy link

Link copied!

@fuzzy_fuzz Do you think it should be something else?

Was this reply helpful? Yes No
fuzzy_fuzz 229 on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Gyllentid 894 on at

Like
a
(0)

Report
Copy link

Link copied!

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! 🙂

Was this reply helpful? Yes No
Gyllentid 894 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

Gyllentid 894 on at

Like
a
(3)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Gyllentid 894 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No