cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
just_an_intern
Frequent Visitor

(a fun algo problem perhaps) How to display gallery items based on many-to-many relationship

Hello,

 

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 

 

 

 

Thanks in advance! 

1 ACCEPTED SOLUTION

Accepted Solutions
just_an_intern
Frequent Visitor

Resolved: Shavora's solution did it for me https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-on-Many-to-many-criteria/m-p/1445394#... 

 

However, it's not a delegable solution.

1. Create an invisible gallery called HiddenDepartmentList with items: First(Filter(Employee, Employee.Email = User().Email)).Departments 

2. Either add to Screen1 at OnVisible or to App at OnStart: ClearCollect(colUserDeps, HiddenDepartmentList.AllItems) 

3. Add to Event gallery at Items: Filter(Sort(Events, 'id events',Descending), Department.Departments in colUserDeps.Departments)

View solution in original post

2 REPLIES 2
just_an_intern
Frequent Visitor

Resolved: Shavora's solution did it for me https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-on-Many-to-many-criteria/m-p/1445394#... 

 

However, it's not a delegable solution.

1. Create an invisible gallery called HiddenDepartmentList with items: First(Filter(Employee, Employee.Email = User().Email)).Departments 

2. Either add to Screen1 at OnVisible or to App at OnStart: ClearCollect(colUserDeps, HiddenDepartmentList.AllItems) 

3. Add to Event gallery at Items: Filter(Sort(Events, 'id events',Descending), Department.Departments in colUserDeps.Departments)

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

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,952)