How to Join 3 Tables (or More) in PowerApps?

(0) Share

Report

Posted on by Mdroid

I have the following tables in sharepoint:

RoleTable

RoleID	RoleName
1	Technician
2	Coordinator
3	Office Worker

TrainingModulesTable

ModuleID	ModuleName
1	Safety Induction
2	Procedure A Training
3	Procedure B Training
4	Procedure C Training
5	Third Party Program Training

RoleRelevanceTable

RoleRelevanceID	LookUpRoleID	LookUpModuleID
1	1	1
2	1	2
3	2	1
4	2	2
5	2	3
6	3	4
7	3	5

This is their relation (RoleRelevanceTable has two "Many to One" relationships)

I want to create a gallery in powerApps that display the following output:

Role	Required Training
Technician	Safety Induction / Procedure A Training
Coordinator	Safety Induction / Procedure A Training / Procedure B Training
Office Worker	Procedure C Training / Third Party Program Training

How could I achieve this in PowerApps?

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

MudassarSZ365 591 on at

Like (1)

Report
Hi @Mdroid ,
Here’s a high-level approach to achieving this:
Use the AddColumns function to expand the RoleTable with a new column that will hold the concatenated string of module names.
Inside AddColumns, use the Filter function to get relevant records from the RoleRelevanceTable based on RoleID.
For each filtered record, use the LookUp function to find the corresponding ModuleName from the TrainingModulesTable using ModuleID.
Use the Concatenate function or similar text functions to create a single string of module names for each role.
Here’s a rough outline of how you could set up this formula in PowerApps:
ClearCollect( JoinedCollection, AddColumns( RoleTable, "Required Training", Concat( Filter( RoleRelevanceTable, LookUpRoleID = RoleID ), LookUp( TrainingModulesTable, ModuleID = LookUpModuleID ).ModuleName & " / ", "" ) ) )
This will look up the tab in the collection where the TabId matches the CurrentTabId.
Kindly mark the solution as accepted if it resolves your issue.
Best Regards
Muhammad Mudassar Mazhar

Was this reply helpful? Yes No
Verified answer

Mdroid 19 on at

Like (0)

Report
Hi @MudassarSZ365.

With a minor adjustment regarding the disambiguation of the RoleID, I was able to get what i wanted.

Note to anyone using Sharepoint Lists in a similar situation, if the "LookUpRoleID" and the "LookUpModuleID" are indeed lookUp columns, you will get a delegation warning. If you must avoid delegation issues at any costs you must use number field.

Below the final code.

AddColumns( RoleTable; "Required Training"; Concat( Filter( RoleRelevanceTable; LookUpRoleID = Role[@RoleID] ); LookUp( TrainingModulesTable; LookUpModuleID = ModuleID ).ModuleName; " / " ) )

Was this reply helpful? Yes No