Find results in table 2 based on table 1

[Anonymous]

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.

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

Hope someone can help me. 🙂

[Drrickryp]

@Anonymous 

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?

[Anonymous]

Hi,

A document can also be applicable for multiple roles. 

[uwenagel · Accepted solution]

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 · Accepted solution]

@Anonymous 

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-PowerApps-Step-3-Keys-and/ba-p/188640 

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

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. 

[Anonymous]

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]

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.