Filter a Gallery based on multiple combo boxes

[AntDim]

Hi team,

I've got a question about filtering a Gallery/Data Table in an easy way according to the selection in numerous (e.g. 5+) combo boxes in the Screen. I have looked through similar questions in this forum, but wasn't able to find the answer I'm looking for.

So at the moment, I'm able to achieve the outcome I want (i.e. having the Gallery displayed unfiltered when all combo boxes are empty, having the Gallery filtered when at least one of them has some values) using a very very very long If or Switch statement where I have to examine all the different permutations. For example:

Combo1 = Empty, Combo2 = Empty, Combo3 = Empty, Combo4 = Empty, Combo5 = Empty

Combo1 = Empty, Combo2 = Empty, Combo3 = Empty, Combo4 = Empty, Combo5 = Has a value

Combo1 = Empty, Combo2 = Empty, Combo3 = Empty, Combo4 = Has a value, Combo5 = Has a value

.....

.....

.....

There are 2^5=32 different combinations in this case, so you can imagine the Item property with all the statements and Filter() for my Gallery looks ridiculously huge, leave aside the time it takes to get it right.

My question is, what is the simplest way to Filter a Gallery when there is a big number of Combo Boxes (not dropdown, i.e. multiple selection is allowed) where the filtering depends on?

Any thoughts or feedback are hugely appreciated! Many thanks in advance

[CNT]

@AntDim You don't need all the permutations and combinations. The code is an example with 2 combos. You can adapt to as many as you want,

Filter(datasource, 
    (IsBlank(combobox1.Selected.Value) || column1=combobox1.Selected.Value) &&
    (IsBlank(combobox2.Selected.Value) || column2=combobox2.Selected.Value)
)

[WarrenBelz]

Hi @AntDim ,

You can do this with 10 statements (in groups of 2)

Filter(
   DataSource,
   (Len(Combo1.Selected.xxxx)=0 || FieldName=Combo1.Selected.xxxx) &&
   (Len(Combo2.Selected.xxxx)=0 || FieldName=Combo2.Selected.xxxx) &&
   (Len(Combo3.Selected.xxxx)=0 || FieldName=Combo3.Selected.xxxx) &&
   (Len(Combo4.Selected.xxxx)=0 || FieldName=Combo4.Selected.xxxx) &&
   (Len(Combo5.Selected.xxxx)=0 || FieldName=Combo5.Selected.xxxx)
)

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.

[AntDim]

Hi @CNT and @WarrenBelz , many thanks for your help. This is really appreciated.

I tried both suggestions of yours but I'm getting the following error in both:

Any thoughts why that and what I could do to fix it?

The combo boxes I tried this on are populated with Choices(DataSource.SPOFieldOfChoiceType) and the this SPO Field is the same filtering parameter.

[CNT]

@AntDim do it this way,

Filter(datasource, 
    (IsBlank(combobox1.Selected.Value) || column1.Value=combobox1.Selected.Value) &&
    (IsBlank(combobox2.Selected.Value) || column2.Value=combobox2.Selected.Value)
)

[WarrenBelz]

Hi @AntDim ,

If these are Choice fields, you need

(Len(Combo1.Selected.Value)=0 || FieldName.Value=Combo1.Selected.Value) && . . . 

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.

[AntDim · Accepted solution]

Hi @CNT, many thanks for your help again.

I tried your suggestion and I think it partially worked. The two issues I faced:

i) the filtering was only based on the last selection in the combo boxes. For example, if I selected "High" and then "Medium" in one of the combos, the Filter would only take into account "Medium".

ii) when I open the app, the full gallery is displayed as expected. If I selected some options from the combo boxes and then de-selected them (so back to no selection of the choices in any of the combos), the gallery would not get displayed at all (it was showing blank).

I tried make some changes (also taking the suggestion of @WarrenBelz into account) and this seems to work for now:

Filter(
   DataSource,
   (Len(Combo1.Selected.value)=0 || Field1Name.value in Combo1.SelectedItems.value) &&
   (Len(Combo2.Selected.value)=0 || Field2Name.value in Combo2.SelectedItems.value) &&
   (Len(Combo3.Selected.value)=0 || Field3Name.value in Combo3.SelectedItems.value) &&
   (Len(Combo4.Selected.value)=0 || Field4Name.value in Combo4.SelectedItems.value) &&
   (Len(Combo5.Selected.value)=0 || Field5Name.value in Combo5.SelectedItems.value)
)

 I'll keep an eye on its behaviour and let you know if any issues are faced, but if this keeps working as expected you've saved me and my eyes from lots of pain!

Thank you so much again.

[elle_bop]

Hi @AntDim @WarrenBelz @CNT,

I apprecite this post was over a year ago and apologies for reopening, I was jsut wondering if any of you knew how to expand this where one of the combo boxes refers to a column which is multiselect? For example please see an example of my sharepoing list below.

Name (free text)	EngagementStage (Multiple choice)	Client (Single Choice)
Bob	Donor, Supporter	Yes
John	Subscriber, Donor	No
Gren	Professional, Supporter	Yes

My goal is that if we were to select 'Donor' in the combo box referring to the Engagement stage then the gallery would filter down to include only Bob and John as 'Donor' is one of the choices. Can then also filter the Client combo box by 'No' which would filter the gallery again down to just John. 

Is this possible?

Thanks

Elin

[AntDim]

Hi elle_bop - wouldn't it be the case that a 'standard' combo box is still able provide this functionality you're looking for, as it actually presents a list of all possible choices (in your case: Donor, Supporter, Subscriber, Professional) and allows the user to select multiple of these?

[WarrenBelz]

Hi @elle_bop ,

If this is a multiple select combo box (and I am assuming Choice field based with an output of .Selected.Value ), on the OnChange

ClearCollect(
   colStage,
   ForAll(
      Self.SelectedItems,
      Filter(
         DataSource,
         Value in EngagementSource
      )
   )
)

then the Items of the Gallery will be colStage

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.

Visit my blog Practical Power Apps