Answered

Populate fields in Form1 (datasource: Table 2) based on selected item in Gallery(datasource: Table 1). Primary key (Unique Id Field) is not same in both tables.

(0) Share

Report

Posted on by WhatsTheAnswer

I am trying to create a training planner in PowerApps by connecting two lists in sharepoint. The idea is to select the Weekday (w1d1, w1d2, w2d1, w2d2 etc.. )in Gallery which pulls up the fields, "Trainer", "Training start date", "Training End date" etc.. for that particular day for a Joinee in Form. Unique Id for Table 1 is 'Joinee' but Unique Id for Table 2 is 'Week'. How can I achieve this.

Thanks.

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@WhatsTheAnswer

Have you tried creating a LOOKUP type column in your Table1?

Open up SharePoint and go to List Settings > Create A New Column. Select the Lookup type and then choose what fields you would like to like to add from Table2. Then go back into PowerApps and REFRESH the datasource for Table1. The week will now be available as a column in PowerApps as a part of Table1.

Hope this idea helps!

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
WhatsTheAnswer 19 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @mdevaney, but I am unable to figure out how lookups work in sharepoint lists (yet another thing to learn).

Would it be possible to use Gallery1.selected.item (from Table 2)and then Filter those fields in Table 1 instead?
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@WhatsTheAnswer

To clarify: are you trying to tell PowerApps which column to write to in Table1 based on the options in Table2?

If yes, I can see some potential difficulties. It appears your weeks are spread across several columns in Table1 (example: week 1, week 2, week 3). If you are wanting to filter these based on a dropdown I am not sure how it could be done. Typically something you want to FILTER is in a single column (example: just a single week column and a new row for each entry)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
WhatsTheAnswer 19 on at

Like
a
(0)

Report
Copy link

Link copied!

I am sorry I was not very clear.

There are actually only 4 weeks and in each week, 5 days are divided as d1, d2, d3, d4 and d5. So, for Week 1, it is like w1d1, w1d2,w1d3.. For Week2, w2d1,w2d2 and so on.. (Please see attached images)

Table 1 has list of persons with unique credentials in 1st column followed by each days details (Trainerw1d1, StartDatew1d1, EndDatew1d1) in subsequent columns.

Table 2 has only the details for 'List of trainings' for each day (w1d1,w1d2). User would need to select the Week tab (1-4)> then Day (1-5) in Gallery > and then fill in the details for that particular day in a Form (Trainerw1d1, StartDatew1d1, EndDatew1d1). Finally this data will be saved in Table 1 (which is the main table).

I am trying to create a relation between tables using w1d1, w2d2, w3d3... but I am unable to do it.
mdevaney 29,993 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@WhatsTheAnswer

Rather than try to create a relationship between Table1 and Table2 I am going to suggest we eliminate that idea altogether. I just don't see how its possible with your current data structure. Hopefully you are open to seeing another potential way forward.

Here's my idea.

#1 Add this code to any existing code the OnSelect property of each week tab

Set(currentWeek, "W1") // for the Week 1 tab Set(currentWeek, "W2") // for the Week 2 tab Set(currentWeek, "W3") // for the Week 3 tab Set(currentWeek, "W4") // for the Week 4 tab

#2 Add a column to your gallery's datasource called DayCode. For each record in the Items property add the day as shown below (pick one only).

"D1" "D2" "D3" "D4" "D5"

#3 Add this code to any existing code already in the gallery for the OnSelect property

Set(currentDay, DayCode)

#4 Change the Visible property for any fields you want to show/hide based on the selection. For (Trainerw1d1, StartDatew1d1, EndDatew1d1) you should use this code.

"W1D1" = currentWeek & currentDay

#5 Repeat step for for all other fields to be shown/hidden.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
WhatsTheAnswer 19 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you for your help.
Verified answer

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@WhatsTheAnswer
Can you please Accept as Solution?

Best of luck in building your app.