Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Need help for connecting two tables based on two columns

(1) ShareShare
ReportReport
Posted on by 13
Hi, i have two tables like below;
 
Table 1 name: tblActivity_plan
 
project_code activities target
DP.2816 # of name 10
DP.2816 # of xxx 3
DP.2816 # of yyy 4
DP.2816 # of zzz 5
DP.2816 # of zzz3 5
DP.2816 # of zzz4 5
DP.2816 # of zzz5 5
 
Table 2 name: tblActivity_update
 
 
project_code activities updates
DP.2816 # of name 15
DP.2816 # of xxx 13
DP.2816 # of yyy 44
DP.2816 # of zzz 95
DP.2816 # of zzz3 75
DP.2816 # of zzz4 95
DP.2816 # of zzz5 57
CS.0000 # of name 799
CS.0000 # of yyy 878
 
 
My goal is to create a table in Gallery like below;
 
  activities target updates
  # of name 10 15
  # of xxx 3 13
  # of yyy 4 44
  # of zzz 5 95
  # of zzz3 5 75
  # of zzz4 5 95
  # of zzz5 5 57
 
 
So that, in the final output table, I want add the updates for each activities for the relevant project code.
 
I have a listbox named listProject which shows the project_code for selection. 
 
I used the below code; but in my code; the first updates value is repeated in all rows.
 
ForAll(
    Filter(tblActivity_plan, project_code = listProject.Selected.project_code),
    {
        activities: activities,
        target: target,
        updates: Coalesce(
            LookUp(tblActivity_update, project_code = listProject.Selected.project_code && activities = ThisRecord.activities, updates),
            0
        )
    }
)



// For txtActivity
Text property of txtActivity:
ThisItem.activities

// For txtTarget
Text property of txtTarget:
ThisItem.target

// For txtAchievement
Text property of txtAchievement:
ThisItem.updates
 
 
 
So it gives an output like below which is not I am expecting.
 
project_code activities target updates
DP.2816 # of name 10 15
DP.2816 # of xxx 3 15
DP.2816 # of yyy 4 15
DP.2816 # of zzz 5 15
DP.2816 # of zzz3 5 15
DP.2816 # of zzz4 5 15
DP.2816 # of zzz5 5 15
 
 
I will be glad to have your support.
 
Thank you
  • Verified answer
    MH-19011837-0 Profile Picture
    MH-19011837-0 13 on at
    Need help for connecting two tables based on two columns
    thanks for the suggestion but while waiting for this post to be approved, I was able to solve it using the below codes;
     
    AddColumns(
        Filter(tblActivity_plan, project_code = selectedProject) As AP,
        Achievements,
        Coalesce(LookUp(filteredIndicator, activities = AP.activities, updates),0)
          
        )
     
  • Suggested answer
    DBO_DV Profile Picture
    DBO_DV 4,415 on at
    Need help for connecting two tables based on two columns
    Hey, 
     
    Can you try this formula? This should work: 
    ForAll(
            Filter(tblActivity_plan, project_code = listProject.Selected.project_code),
            {
                activities: activities,
                target: target,
                Updates: Coalesce(
                    LookUp(tblActivity_update, project_code = tblActivity_plan[@project_code] && 
                           activities = tblActivity_plan[@activities], updates)
                )
                
            }
        )
     

    If this solved your problem, please mark it as Solved to help others find the solution faster.
    If you found it helpful, consider giving it a Like to support each other in this community!

    Thanks, and happy building!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,445

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,741

Leaderboard