Gallery to display filtered items from a data sourceItem

[Sam4]

Hi, I woudl like to do the following, not sure if that is possible you I would appreciate any idea.

I have two tables,

• Table1 with two columns (Building and ItemID)
• Table2 with three columns (ItemID, Name and Number).

I also have the following elements:

1. Dropdown1 - This is a Distinct of Building in Table 1.
2. Gallery1 - This is a filtered gallery displaying the items in Table1 which building is the one selected in Dropdown1.
3. Gallery2 - This should be a gallery displaying the items in Table2 filtered in Table 1 by the value of the Dropdown1.

In the example below, If  I select in Dropdown1 "A",  Gallery1 should display items 1,2,3 and Gallery2 should display "Anna", "Lewis", and "Jack". 

Dropdown1 and Gallery1 work fine, but I have problems with Gallery2. Is there any way to use the "Building" from Table 1, to filter the items in Table2?

Example:

DropDown1: ["A","B"]

Table1: Building --- Item 

================

A --- 1

A --- 2

A --- 3

B --- 4

B --- 5

B --- 6

Table2: Item --- Name --- Number
=======================

1 --- Anna  --- 333

2 --- Lewis --- 222

3 --- Jack   --- 111

4 --- Laura --- 444

5 --- Mary  --- 555

6 --- John  --- 666

Thanks in advance

[WarrenBelz]

Hi @Sam4 ,

Can you please tell all other (if applicable) table, field and control names/types you are doing this with and any relevant code you have written on filters etc.

[WarrenBelz]

One further question, is the item ID unique to the building?

[WarrenBelz · Accepted solution]

Hi @Sam4 ,

I will may still want your code, particularly the filter used on Gallery1, but I think I can see what you are after here. You want to get two galleries based on two lists filtered on one drop down being the building.

First thing, do you have less than 2000 records? If so, you can collect the data and use the In statement.
So if Gallery1 was based on colBuilding with Building and Item as the fields, and  Gallery2 was based on colItems with Item, Name and Number as the fields you could look at Items of Gallery2 as something like

Filter(
   colItems,
   Item In colBuilding.Item
)

Please let me know if this works for you.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[eka24]

Consider the following (See attached as well)

1. Set the Dropdown items to   Distinct(Table1,Building)
2. Set Gallery1 items to   Filter(Tablle1,Building=Dropdown4.Selected.Result)
3. Then Set Gallery2 items to   Filter(Table2,ID=Gallery1.Selected.ID
4. Kindly change dropdown name and table name to yours)

If you like this reply, please give Thumbs Up. And if this solves your problem, please accept this reply as the solution.

[Sam4]

Spoiler

Hi @WarrenBelz , thanks for your reply. I think your solution could work for me with a minor change, just changing the collection for the gallery (basically because Gallery 2 is the filtered table I need)

Filter(
   colItems,
   Item In Gallery2.AllItems.Item
)

I've tested this and it work but I get the delegation warning, and I would expect to have more than 2000 records in that list.

However, I',m using at the moment SharePoint as a data source and I'm expecting the final data source will be SQL, and I've other examples where I could use "in" with SQL and no delegation warning. Actually, in the documention "in" is delegable function: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview.

But in any case your solution is doing what I was asking so thank you 🙂

Kind Regards

[WarrenBelz]

Thanks @Sam4 ,

I try to steer people to the logic of a solution. I am a SharePoint user, so delegation workaround involving collections are sort of the norm.