cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
micross
Frequent Visitor

Match Substring to items in a list

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?

0 REPLIES 0

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,546)