Question about gallery filtering: The SharePoint list column I'm filtering includes multiple names

[T_A_M]

Hello -

I'd like to filter a gallery with a combo box control. The data source for my gallery is a SharePoint list, and the column I'm filtering by is a multi-line text column called Names. The problem is that each item sometimes contains several names, each separated by a semicolon.

The Items property of my combo box is NameCol.Title. (NameCol is a collection that is populated by names from a different SharePoint list).

I'd like to be able to filter my gallery so that if someone chooses "John" from the combo box, it will show an item where the Names column in SharePoint is something like: Tom;John;Sally. The formula I'm using now only works if there is a single name in my Names column.

Any help would be greatly appreciated! Thanks.

[rubin_boer]

hi @T_A_M i assume you are using something like Filter(source, column = combobox...) , change the filter to Filter(Source, combobox.selected.value in columnName) 

hope it helps 

[Mink21]

Hi @T_A_M ,

Create a collection in the OnChange propierty of the combobox:
ClearCollect(colComboBox, ComboBox1.SelectedItems)

Then in the gallery Items use the following code:
Filter(Source, ColumnFromSource in colComboBox.ColumnFromCombobox)

I hope this helps!

[sathyav]

Hello @T_A_M , 

My understanding is that you would like to filter the Gallery Items based on text column which has values like "Tam;John;Sally"

You could use the Search Command rather than Filter command.

Search(DataSource,DropDown.Selected.Title,"Names")

The code translates to Search the Datasource in the column "Names" for the value that has been selected in the DropDown (NameCol.Title)

Wrote a detailed article here. Refer if you need more information

[T_A_M]

Unfortunately, that didn't work. The gallery does filter, but only if there's a single name in the Names column like before. (And, I'm getting a delegation error).

[sathyav]

Hello @T_A_M , you could refer my answer above about the usage of Search. Hopefully, that answers your query. 

[T_A_M]

That works very well! However, if there is nothing selected in my combo box, the gallery shows no results. Is there a way to have the gallery show all results until the user chooses something in the combo box?

[sathyav]

Glad ! It worked 🙂 
@T_A_M 
To Handle Blank Values 

If(IsBlank(TechDropDown.SelectedText.Value),TestList,Search(TestList,TechDropDown.SelectedText.Value,"Names"))

The code translates to selected Value of the DropDownbox is blank, then display entire datasource (TestList in my case). If it is not blank then Search the Datasource in the column "Names" for the value that has been selected in the DropDown 

[T_A_M]

That works, but I also have some other combo boxes that I'm using for filtering, which are coded like this:

If(IsBlank(TechDropDown.SelectedText.Value), TestList, Search(TestList, Filter(Company=ComboBox2.Selected.Title), TechDropDown.SelectedText.Value, "Names"))

The If function means that when there is no value in the TechDropDown combo box, ComboBox2 does not work for filtering either.

If you have any ideas for getting around that, I'd greatly appreciate it. Thank you!

[v-yujincui-msft · Accepted solution]

Hi @T_A_M ,

Maybe you could try the following formula:

With({DS:Filter(TestList,If(!IsEmpty(ComboBox2.SelectedItems),Company=ComboBox2.Selected.Value,true))},If(!IsBlank(TechDropDown.Selected.Value),Search(DS,TechDropDown.Selected.Value,"Names"),DS))

Best Regards,

Charlie Choi