How to display gallery items based on many-to-many relationship in PowerApps / Dataverse?

Question

I need help with displaying gallery items based on many-to-many relationships.

Context:

So far, I built a separate canvas screen that can build a relationship between an "Employee" to many "Departments" based on this documentation: https://powerapps.microsoft.com/et-ee/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

Now on another screen, my "Event" Screen, which consists of an edit form and a vertical gallery, a User (an employee) can submit an Event form (containing a single Department in a dropdown box) which is connected to the "Event" Table in Dataverse.

This is my question:

In the 'Items' of a Vertical Gallery, how do I filter such that an Employee logged in can view Events based on the Departments that belong to them.

Here's an example/scenario:

Pedro belongs to the "Finance Department" and the "Product Department"

Aiko belongs to the "Computer Eng. Department"

Suraj belongs to the "Product Department" and the "Mechanical Eng. Department"

Ahlem belongs to the "Finance Department"

This is the result I want based on the scenario above:

-What Pedro sees in his Vertical Gallery is only a list of Events that have a "Finance Department" selected value and Events that have a "Product Department" selected value.

-What Aiko sees in her Vertical Gallery is only a list of Events that have a "Computer Eng. Department" selected value.

-What Suraj sees in his Vertical Gallery is only a list of Events that have a "Product Department" selected value and Events that have a "Mechanical Eng. Department" selected value.

-What Ahlem sees in her Vertical Gallery is only a list of Events that have a "Finance Department" selected value

Dataverse Tables and columns (with data types):

1. Event(s)

• id event (primary)

• department (Lookup to single Department)

• other fields/columns of this table are not important

2. Employee(s)

• id empl (primary)

• employee name (Text: Plain text)

• email (Text :Email)

• (Department(s) is a hidden column in Dataverse because its a many-to-many relationship )

3. Department(s)

• id department (primary)

• department name

• (Employee(s) is a hidden column in Dataverse because its a many-to-many relationship )

The relationships:

• Many Employees To Many Departments (Many to Many)

• Many Events To One Department

Hints:

'Employee(s)'.email = User().Email

Answer 1

In the 'Items' of a Vertical Gallery, how do I filter such that an Employee logged in can view Events based on the Departments that belong to them.

This will be 2-3 step process. First you will have to get Guid/UserID of loggedin user. You will have to filter systemuser Table (dynamics/dataverse Table). Note User().Email gives you Email of your Office365 user but you can easily figure out how to get logged in user Guid.

Then you will have to fetch Departments where user is part of. Take a look here

Now you got say from a list of 100 departments filtered 3-4 departments where logged in user is part of.

Once you have department you can easily get events a event and Department are 1-N or N-1 relationship. Here you can filter based on lookup filed.

Note: At later stage you might want to consider/think about Delegation. Delegation is restriction if you have for example more than 2K records Powerapps will only perform all the operation on first 2k records. It will not give you correct result.