How do I match substring to items in a list?
How do I do a Contains match on a list?
I have a microsoft form that auto populates an excel sheet. One question on the form is a multicheck box that allows users to select the various categories that their entry falls under. For each entry, these category tags are saved as a string separated by ";" in a single cell. The data is structured this way by microsoft default and adding a formula to edit this formatting is not supported for excel sheets connected to microsoft forms.
//tags are formatted as:
"Clothing;Banking;Food;"
In my app, I have a table with this data in a gallery and I have a combo box that lists all those categories. I want to be able to filter by categories selected in the combo box. This works right now for entries with a single "tag" but does not work for entries tagged with multiple categories. I am also filtering by many other things. Here's a snippet of what I am using now that does not meet all my needs:
//This is what I'm using right now that only works for entries with a single category tag
SortByColumns(
Search(
Filter(
[@Table1],
// checkbox filters
If(
fee_filter.Value,
program_fee = "Yes",
program_fee <> Blank()// fee
),
// combobox filters
If(
IsBlank(servicecat_filter.Selected),// service category
true,
Service_category in servicecat_filter.SelectedItems
)
),
// making some fields searchable by text search
TextSearchBox1.Text,
"Organization_Name",
),
"Organization_Name",
If(
SortDescending1,
Descending,
Ascending
)
)
I'm not sure how to approach this problem. It seems that `Split` is very difficult for my use case as an entry can have any number of tags, and I'm not sure how to match a string/substring to a list as shown in the code snippet below that throws an error:
// Why doesn't this work?
// combobox filters
If(
IsBlank(servicecat_filter.Selected),// service category
true,
IsMatch( Service_category, servicecat_filter.SelectedItems, Contains ), //<--CHANGED***
)
Any ideas of how I should approach this problem?
