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

Find results in table 2 based on table 1

Hi,

 

I am trying to build an app, where it will be possible to see which document a specific person must read.

I have Table 1 which shows the roles of each person. I also have Table 2 specifying documents for each role.

 

CROE_0-1654274018294.png

CROE_1-1654274041220.png

I would like to be able to show a list of which documents a person must read.

 

Hope someone can help me. 🙂

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
uwenagel
Resolver II
Resolver II

You'd better restructure your datasource the following:

 

Table1 should only have Name and Role as columns with Role being either a choice field (with Role1 through 4 as possibilities) or even better a lookup to another list that contains the possible roles. If one person has more than one role this would result in more than one record per name.

 

Similarly Table2 should have DocName, DocTitle and Role as columns with Role being the same as above. Again more than one document per role would result in more than one record.

 

You could then create an app with a ComboBox that has the following items: Distinct(Table1;Name).Result. In the OnChange event you could create a collection colDocs the following way:

 

ClearCollect(colRoles,Filter(Table1,Name = ComboBox1.Selected.Result).Role);
Clear(colDocs);
ForAll(colRoles,ForAll(Filter(AddColumns(Table2,"ROLES",Role.Value),ROLES = Role.Value),Collect(colDocs,ThisRecord.DocName)))

 

This collection can then be shown in a gallery on the same screen.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.

If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

Drrickryp
Super User
Super User

@CROE 

You are dealing with a many to many relationship similar to the classic books and authors example. For example, one author can have many books and one book can have many authors.

I would structure it with three lists, as this gives the most flexibility. The roles list is a junction table between names and documents. It is the many side of two one to many relationships.  My blog post discusses how to structure it in SharePoint and how to implement it in PowerApps.  

.https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Principles-and-Pow... 

The advantage to this approach over the one mentioned by @uwenagel  is that additional columns related to the role can be included as well.  

!1.jpg

With this underlying structure it will be easy to configure a search for documents based on selecting a person or by selecting a document and finding out which people will require them. 

View solution in original post

6 REPLIES 6
Drrickryp
Super User
Super User

@CROE 

It appears that one person can have many roles and one role can have many documents but each document can only have one role.  Is that correct?

CROE
Frequent Visitor

Hi,

 

A document can also be applicable for multiple roles. 

uwenagel
Resolver II
Resolver II

You'd better restructure your datasource the following:

 

Table1 should only have Name and Role as columns with Role being either a choice field (with Role1 through 4 as possibilities) or even better a lookup to another list that contains the possible roles. If one person has more than one role this would result in more than one record per name.

 

Similarly Table2 should have DocName, DocTitle and Role as columns with Role being the same as above. Again more than one document per role would result in more than one record.

 

You could then create an app with a ComboBox that has the following items: Distinct(Table1;Name).Result. In the OnChange event you could create a collection colDocs the following way:

 

ClearCollect(colRoles,Filter(Table1,Name = ComboBox1.Selected.Result).Role);
Clear(colDocs);
ForAll(colRoles,ForAll(Filter(AddColumns(Table2,"ROLES",Role.Value),ROLES = Role.Value),Collect(colDocs,ThisRecord.DocName)))

 

This collection can then be shown in a gallery on the same screen.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.

If the content was useful in other ways, please consider giving it Thumbs Up.

 

Drrickryp
Super User
Super User

@CROE 

You are dealing with a many to many relationship similar to the classic books and authors example. For example, one author can have many books and one book can have many authors.

I would structure it with three lists, as this gives the most flexibility. The roles list is a junction table between names and documents. It is the many side of two one to many relationships.  My blog post discusses how to structure it in SharePoint and how to implement it in PowerApps.  

.https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Principles-and-Pow... 

The advantage to this approach over the one mentioned by @uwenagel  is that additional columns related to the role can be included as well.  

!1.jpg

With this underlying structure it will be easy to configure a search for documents based on selecting a person or by selecting a document and finding out which people will require them. 

CROE
Frequent Visitor

Hi

Thank you very much. I have restructured my data, but I am still struggling with the search of documents based on selecting a person.

How can I filter my gallery showing documents based on the selection of a person in another gallery?

 

When I am searching for roles based on person I use this formula:

Search(Role_and_Init, Text(BrowseGallery1.Selected.Init), "Init")

uwenagel
Resolver II
Resolver II

In fact I'm proposing the same thing as @Drrickryp.

If you put your roles into a separate table you are flexible with the number of roles. You can always add another one.

I'm not sure about your table and column names. It seems you prefer a gallery of the possible names over the ComboBox I suggested. However this is similar. You just have to use the correct column for the Filter (or Search) function when creating the first collection (colRoles in my suggestion) of the possible roles of a person selected.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

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

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (4,792)