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.
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.
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Check out our new Discover Your Career Path blog post series and get all the details.