Filter Gallery by ID of different sources

[Magnifica]

Hey Everyone,

I need to filter a gallery of source A by the Item ID compared with the input Value of a field in source C && another input Value of source C and the Item ID of source B.

In Source A i have my material

In Source B i have my Events

In Source C i have the ID of the material and the ID of the event it belongs to.

Now i would like to present the material based on the users selection of the Event.

Thank you in advance for your attention.

[Maxwell · Accepted solution]

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":

1. Create a "Drop down" control
2. Set its "Items" property to "Events" (Note: this would correspond to "Source B" in your post)
3. 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:

1. Create a "Vertical Text" gallery
2. 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

[Magnifica]

Thank you so much!! Its exactly what i was searching for. And the explanation was great.

One more question.

In the Relations collection i have one column with quantaty (of the material)

This i want to present in a field next to the MaterialName. How could I achieve that?

Thanks in advance! 

[Maxwell · Accepted solution]

Hey Magnifica,

  There's a couple small changes we can make to the previous example to get this extra data to display.

  First, we're going to have to modify the items property of our gallery to return the whole record and not just the material name. We'll do this by removing the ".MaterialName" from the end of the property's value.

Filter(Materials,ID in Filter(Relations,EventID=EventDropdown.Selected.ID).MaterialID)

Because of this, there might be a few places in your gallery where you need to tell it specifically to display the "MaterialName" now. You should be able to do this through the sidebar UI when you have the gallery or one of the gallery entries selected.

Next, you'll want to make sure you still have the gallery selected. In my previous example, we were using a text gallery, so each gallery entry has a Body, Heading and Subtitle. Chances are good that the "Body" property of this gallery (over in the Sidebar UI) says something like "MaterialName". We're going to change the body of each entry to display the quantity of the material associated with our selected event.

1. Click on the text field immediately below "Body" (in the sidebar UI)
2. Enter the following for its value:

   LookUp(Relations, MaterialID=ThisItem.ID && EventID=EventDropdown.Selected.ID).Quantity

Let's go over what we're doing here. First, you'll notice that we're using "LookUp()" and not "Filter()". The difference between the two is that "LookUp()" will return a single record while `Filter()` returns a table of records. In our case, I believe a specific combination of "MaterialID" and "EventID" to be unique. In this case, we can use "LookUp()" instead of "Filter" to avoid having to get the filtered record out of a table later. Some more information about these two functions (as well as "Search") can be found here: https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/

Next, we're saying we want the record we find to have a "MaterialID" equal to "ThisItem.ID". In this case, "ThisItem" refers to the record being referenced by any given entry in our gallery. This is why we removed ".MaterialName" in the previous step; we were going to need to access the record's "MaterialID" field.

After that, we've got a familiar looking condition. We're making sure the relational entry we select shares an "EventID" with the one selected from our drop down. I figured that there might be multiple relational entries with the same "MaterialID", this ensures we only get the entry relevant to the event we've selected.

Finally, we tell it that we are only interested in the "Quantity" field of the specified record.

Hope that helps,

  Maxwell, PowerApps-Staff

[Magnifica]

Hello Mexwell,

Amazing, exactly what i was looking for! Thank you - it works perfectly.

[Magnifica]

If i now would want to have the option to delete an item from the Relation collection with a X icon. How would it look like to do so? 

[Maxwell]

Hey Magnifica,

  A similar question has already been answered here: https://powerusers.microsoft.com/t5/PowerApps-Forum/delete-screen-confirmation/td-p/26204

  Please note that this implementation also displays a confirmation screen before allowing the user to delete a record. Since deleting a record is a potentially destructive action, I believe this is a good idea. It will help prevent a user from accidentally deleting a record.

  A quick summary about the linked answer:

1. It adds a "Delete" button to this gallery.
2. This button navigates to a secondary, confirmation screen with two buttons.
3. On this confirmation screen, if the "Yes" button is clicked, the record is deleted and the app navigates back to the primary screen.
4. On this confirmation screen, if the "No" button is clicked, nothing is deleted and the app navigates back to the primary screen.

Let me know if you want further clarification on the linked answer,

  Maxwell, PowerApps-Staff

[Magnifica]

for the post and the explanation.

I would like to add new items to the Relation collection. How do i get the right connected Event ID and Materiel ID?

- I want to open an excisting Event and add new Matreials to this event (means i want to add items to the relation collection with ID of the Materiel and the amount)

- I wan to have add a new Event set date location etc and Materials to this event (items to the relation collection with ID of the Materiel and the amount)