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!!!                    

4 REPLIES 4
Drrickryp
Super User
Super User

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!!! 

I cant help you but I have the same problem, Im trying to filter a list by another list where they are joined by a Lookup i.e. an object that contains a foreign key. Navigation properties dont seem to do the trick i.e:

Filter(List, !(Property in ListWithLookup.LookupProperty.Property))

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (2,900)