cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
svenvu
Level: Powered On

Filter a gallery based on combobox

Hi

 

Below you can see there is a simple combobox and a gallery where I want to filter the gallery based on the selection from the combobox that is set to single select. The difficult part is that I want to filter the Gallery on a field that comes from another table.

 

So if I set the items property from Gallery1 to filter Table1, I can't find the field "Country" because "Country" comes from Table2.  Any help is appreciated!

 

 

test.PNG

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter a gallery based on combobox

Hi @svenvu ,

Could you please share a bit more about the Region column in your Table1? Is it a LookUp type column which references values from the Region column in your Table2?

Do you want to filter your Gallery Items based on the available regions the selected Country Item (in your ComboBox1) related to?

 

I assume that the Region column in your Table1 reference values from the Region column in your Table2, is it true?

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

1. If the Region column in your Table1 is a Text type column:

Set the Items property of the ComboBox1 to following:

Distinct(Table2, Country)

Set the Items property of the Gallery1 to following:

Filter(
         Table1,
         Region in Filter(Table2, Country = ComboBox1.Selected.Result).Region
)

 

2. If the Region column in your Table1 is a LookUpo type column (your Table1 is a SP List):

Set the Items property of the ComboBox1 to following:

Distinct(Table2, Country)

Set the Items property of the Gallery1 to following:

Filter(
         Table1,
         Region.Value in Filter(Table2, Country = ComboBox1.Selected.Result).Region
)

 

Please take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
Super User
Super User

Re: Filter a gallery based on combobox

@svenvu 

Is Region the common key between the two lists, or are you using an ID to create your 1 to many relationship?

If is it region, then you can use a filter formula such as this:

Filter(Table1, Region = ComboBox1.Selected.Region)

 

I hope this is helpful and leads to some results for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Community Support Team
Community Support Team

Re: Filter a gallery based on combobox

Hi @svenvu ,

Could you please share a bit more about the Region column in your Table1? Is it a LookUp type column which references values from the Region column in your Table2?

Do you want to filter your Gallery Items based on the available regions the selected Country Item (in your ComboBox1) related to?

 

I assume that the Region column in your Table1 reference values from the Region column in your Table2, is it true?

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

1. If the Region column in your Table1 is a Text type column:

Set the Items property of the ComboBox1 to following:

Distinct(Table2, Country)

Set the Items property of the Gallery1 to following:

Filter(
         Table1,
         Region in Filter(Table2, Country = ComboBox1.Selected.Result).Region
)

 

2. If the Region column in your Table1 is a LookUpo type column (your Table1 is a SP List):

Set the Items property of the ComboBox1 to following:

Distinct(Table2, Country)

Set the Items property of the Gallery1 to following:

Filter(
         Table1,
         Region.Value in Filter(Table2, Country = ComboBox1.Selected.Result).Region
)

 

Please take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Administrator
Administrator

Re: Filter a gallery based on combobox

Hi @svenvu can you review the above replies and update the thread if they were helpful? 

 

Thank you,

 

@TopShelf-MSFT 

svenvu
Level: Powered On

Re: Filter a gallery based on combobox

Hi @v-xida-msft 

 

Below are some more questions I have about the solution for this problem. Feel free to ask me to create seperate topics for those questions! At this moment it's easier for me to keep the questions together.

 

The Region column in my Table1 is indeed a LookUp type column. If I follow your instructions I wasn't able to use the exact same formula. Table1Region.Value can't be used in my formula. Instead I had to use Table1Region.Table2Region which I found via trial and error with the suggestions I got when typing. Is this something you experience as well or could provide more info about this so we can learn from this?

 

If(
    IsEmpty(Combobox1.SelectedItems.Result);
    Filter(
        Table1;
        Year = DropdownYear.SelectedText.Value
    );
    Filter(
        Table1;
        Year = DropdownYear.SelectedText.Value;
        Table1Region.Table2Region in Filter(
            Table2;
            Country = Combobox1.Selected.Result
        ).Table2Region
    )
)

With this little change your solution works at this moment but I'm afraid it won't in a production environment where more data will be added because I get a delegation warning for "in". What exactly does this mean? Does this mean that Table2 only gets filtered for the first 500 records. Or does it gets filtered for all records but only return the first 500 results?

 

Is there a solution possible without a delegation warning because Table2 will have more then 500 records

 

I also wanted to display all items in the gallery when nothing is selected in the combobox. I used the IsEmpty-formula as you can see in the formula above but as you notice I had to use Combobox1.SelectedItems.Result instead of Combobox1.Selected.Result. Could you explain the difference and why I had to use Selecteditems with the IsEmpty-formula?

 

Another question I have is why the IsEmpty-formula doesn't kick in when the application opens. When I open the application, the combobox is empty but my gallery is also empty. The IsEmpty-formula only works when you first select a Region in the Combobox and then delete the selection. I really need a way to display all items in the gallery when a user opens the application. The user could then make a selection in the Combobox to filter the gallery.

 

Friendly greets!

 

 

 

 

 

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 388 members 5,608 guests
Please welcome our newest community members: