How filter gallery based on Combo box's SelectedItems?

[rxrx]

Hi,

I have a combo box that allows multiple choices with options, for example, ["City - Cafeteria", "City - Grocery", "City - Restaurant"]

This corresponds to data source (online excel)'s 'Location' column. However, in the excel, the data entered in the column 'Location' could be written as "City - Cafeteria/Grocery" instead of 1 single word.

I would like the gallery item with "City-Cafeteria/Grocery" as Location to show up after filtering no matter if the user chooses "City - Cafeteria" or "City - Grocery" or both options in the combo box.

I think that I may use Filter(ExcelTable, Combobox_selecteditems_string in Text(Location)), but I do not know :

- how to access all SelectedItems's text value

- whether an iteration is needed to cover the search of each selected options' text in Location

- whether the option "City - Grocery" can be searched in Location's "City - Cafeteria/Grocery" since there's "Cafeteria/" in between.

Or if other formula is better?

Thanks for helping.

Thanks for helping!

[HenWang · Accepted solution]

Hi @rxrx ,

Yes, the iteration is needed when filtering the data and please try below formula:

Filter(ExcelTable,
If(CountRows(ComboBox2.SelectedItems) = 2, Last(Split(First(ComboBox2.SelectedItems).Value," _ ")).Value in Text(Location) || Last(Split(Last(ComboBox2.SelectedItems).Value," _ ")).Value in Text(Location), 
If(CountRows(ComboBox2.SelectedItems) = 3, "Cafeteria" in Text(Location) || "Grocery" in Text(Location) || "Restaurant" in Text(Location),
First(ComboBox2.SelectedItems).Value in Text(Location)))
)

BR,

Hen Wang

[HenWang]

Hi @rxrx ,

Just by the way, please also consider add the if condition to check whether combobox has selected items when doing the filter if necessary. Just need to add the if in the beginning of my provided formula:

If(!IsEmpty(ComboBox.SelectedItems),   my formula , excel_table)

BR,

Hen Wang

[rxrx]

Hi, thanks HenWang! 

Some follow up questions here:

1) this works when there's only 3 choices in the combo box, if ever there's a dozens of choices, is there a better way to deal with the situation except for writing all situations using First/Last/FirstN...? Isn't there a way to access the options with a sort of index like: array[0] or something?

2) I have other multiple-choice combo boxes who take distinct excel column content as options so no need to do split like previously. For example a column called "Series".  But when I try to write Filter(InfoSheet, ComboBox1.SelectedItems.Value in Text(Series)) to filter my gallery, it says that "Value is an invalid argument type. Cannot use Table values in this context". However, the combo box's display fields is written as below: how should drill down the SelectedItems. to access each option to filter my gallery?

I tried to concat the Series combo choices results to a text label using Concat(ComboBox1.SelectedItems,Value,",") and it can work. So I don't understand why Value won't work in the filter formula. Thanks for helping.

[rxrx]

Hi, thanks HenWang! 

Some follow up questions here:

1) this works when there's only 3 choices in the combo box, if ever there's a dozens of choices, is there a better way to deal with the situation except for writing all situations using First/Last/FirstN...? Isn't there a way to access the options with a sort of index like: array[0] or something?

2) I have other multiple-choice combo boxes who take distinct excel column content as options so no need to do split like previously. For example a column called "Series".  But when I try to write Filter(ComboBox1.SelectedItems.Value in Text(Series)) to filter my gallery, it says that "Value is an invalid argument type. Cannot use Table values in this context". However, the combo box's display fields is written as below: how should drill down the SelectedItems. to access each option to filter my gallery?

[HenWang]

Hi @rxrx ,

If there are more that 3 choices, issue would be pretty difficult. I suggest creating a new post about this question as I have run out of my brain😂.

BR,

Hen Wang