Hey Magnifica,
I think that I've got a solution that will work for you. Inside your gallery, you're going to want to set its "Items" property similar to:
Filter(Materials,ID in Filter(Relations,EventID=EventDropdown.Selected.ID).MaterialID).MaterialName
Below is an example that helps explain what is going on in the above statement. The example should also help explain what the various values are and how they correspond to the data you are working with.
First, I've set up three collections that I think roughly match the data you're working with:
ClearCollect(Materials, {ID: "1", MaterialName: "MaterialName1"}, {ID: "2", MaterialName: "MaterialName2"}, {ID: "3", MaterialName: "MaterialName3"});
ClearCollect(Events, {ID: "1", EventName: "EventName1"}, {ID: "2", EventName: "EventName2"}, {ID: "3", EventName: "EventName3"});
ClearCollect(Relations, {ID: "1", MaterialID: "2", EventID: "3"}, {ID: "2", MaterialID: "3", EventID: "1"}, {ID: "3", MaterialID: "1", EventID: "2"})We've got:
- a "Materials" collection, whose entries have an "ID" and a "MaterialName" (Note: this corresponds to "Source A" in your post)
- an "Events" collection, whose entries have an "ID" and an "EventName" (Note: this corresponds to "Source B" in your post)
- a "Relations" collection, whose entries have an "ID", a "MaterialID" and an "EventID" (Note: this corresponds to "Source C" in your post)
Next, I've created a "Drop down" control to allow the user to select an event by its "EventName":
- Create a "Drop down" control
- Set its "Items" property to "Events" (Note: this would correspond to "Source B" in your post)
- In the "Advanced" sidebar, set the "Value" under "Items" to "EventName". This will cause the "Drop down" to display entries containing an "EventName".
Finally, create a "Vertical Text" gallery to display our related materials:
- Create a "Vertical Text" gallery
- Set its "Items" property to
Filter(Materials,ID in Filter(Relations,EventID=EventDropdown.Selected.ID).MaterialID).MaterialName
Let's break down the various parts of this "Items" statement. We'll start with the innermost statement and move outwards. I'll replace parts of the statement we've already looked at with an "X" to limit clutter.
EventDropdown.Selected.ID
This is referencing the "ID" field of the "Event" record we selected from our "Drop down".
Filter(Relations,EventID=X).MaterialID
Here, we filter the "Relations" table, grabbing any records with an "EventID" that match the value we selected in our "Drop down". The last bit tells it we're only interested in the "MaterialID" field of the filtered records.
Filter(Materials,ID in X).MaterialName
Finally, we filter the "Materials" table, grabbing any records with an "ID" that matches one of the "MaterialID"s we filtered in the previous step. Similar to last time, we're only interested in the "MaterialName" field.
With all this in place, whenever the "Drop down" is changed, we should see our gallery update to display a list of "Materials" which are related to the chosen "Event".
Let me know if you have any questions,
Maxwell, PowerApps-Staff
