cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmalm
Helper II
Helper II

3 Source Tables Filtering with Foreign Keys

This one is a little complicated so bare with me. I have 3 tables that are sharepoint lists with basic design below (note the post-fix "fk" = lookup column/foreign key, "pk" = primary key):

Table 1 Fields:  table_1_pk
Table 2 Fields: table_2_pk, table_1_fk, table_3_fk

Table 3 Fields: table_3_pk

I have a gallery1 of table 1's records that when selected brings the user to a new screen that has a gallery2 of table 3's records. I need to filter the items in gallery2 with table 2 records where table_3_fk = table_1pk and table_3_fk = table_3_pk.

I've tried many things and I think I'm close. I believe the items field of gallery2 could be as simple as a filter(table3, table_3_pk in table2.table_3_fk.Id), but there's apparently more. I've also thought of creating a collection of the table 2 records where both foreign keys match and then filtering.

Please help!!!                    

3 REPLIES 3
Drrickryp
Super User II
Super User II

Hi @cmalm 

You are absolutely on the right track.  Please check out my posts on keys and relationships 

 https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-...  and the follow up post on how to use those principles to develop an actual app. 

https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple... .  The basics are that you can use Dropdown controls for the Parent list and galleries with a Lookup()'s using the foreign key to show data from the third table. 

Thank you for the links. While informative, it doesn't help my situation. I'm not using cascading galleries for everything. My gallery1 is the location when selected can be referenced for the foreign key in table2. Note I can't use the junction table between 1 and 3 for gallery2 items as table3 is already a junction table with foreign key values that I need to reference.

Ok. I've made progress but I could use a little help. I've created a collection that filters all records from table2 where table1_fk matches the selected table1_pk. I'm stuck on the items field of gallery2. The below is what I've come up with but has errors:

Filter(table3, table3_pk in collection.storageFK)

I truly believe this should work, but I believe the issue is the "storageFK" column in the collection that references a record/lookup. A simple ".Id" does not alleviate the problem.

PLEASE HELP!!! 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,548)