Trying to link two collections based on common field

[Smartacus]

Hi all, I'm trying to create a report table in my canvas app from multiple SharePoint lists, but I can seem to make it work no matter what I try.  What I'm trying to achieve is to have several columns from SharePoint List 1 appear in the same table as those from SharePoint List 2.  Here are some background details that may be useful:

Source Data:

SharePoint List #1 ('DB-Project')	SharePoint List #2 ('DB-Project-Team')
Relevant Fields:  Project ID (auto number) Project Name (text) Project Assessor (text)	Relevant Fields: Project ID (maps to 'DB-Project' table) Project Manager (text) IT Owner (text) Enterprise Architect (text)

Collections:

• clearCollect(collectionProject, ShowColumns('DB-Project',"Title", "ID", "projectAssessorValue")
• clearCollect(collectionTeam, ShowColumns('DB-Project-Team',"ProjectID","ComplianceConsultant","ProjectManager","ITOwner","EnterpriseArchitect"))

Gallery (items): Office365Users.SearchUser({searchTerm: searchBoxUserAssign.Text,top:5})

Table 1 (items):

Filter(collectionProject, And(ID in collectionTeam[@ProjectID], ID = tableAssignments.Selected.ProjectID))

This is a problem because it only grabs the selected item.  It doesn't iterate for all items showing in the other table.

Table 2 (items):

Search('DB-Project-Team',varSelectedAssignee,"ProjectManager", "ITOwner", "EnterpriseArchitect")

How can I display these as a single table? Thanks in advance!

[WarrenBelz · Accepted solution]

Hi @Smartacus ,

Try this for a start - I have used your Filter values posted

Search(
   Filter(
      AddColumns(
         'DB-Project',
         "Manager",
         LookUp(
            'DB-Project-Team As aPT,
            aPT.'Project ID' = 'Project ID',
         ).'Project Manager',
         "Owner",
         LookUp(
            'DB-Project-Team As aPT,
            aPT.'Project ID' = 'Project ID',
         ).'IT Owner',
         "Architect",
         LookUp(
            'DB-Project-Team As aPT,
            aPT.'Project ID' = 'Project ID',
         ).'Enterprise Architect'
      ),
      ID in collectionTeam[@ProjectID] && 
      ID = tableAssignments.Selected.ProjectID
   ),
   varSelectedAssignee,
   "Manager", 
   "Owner", 
   "Architect"
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Smartacus]

It took some more fiddling around with the nuances, but it worked!  Thank you!!