Filtering/searching a table with a search box

[dlo4242]

HI,

I am trying to use a search box to filter down my table. I get the table to filter down using only one column at a time. But what I want to do is be able to search 2 columns to filter even more.

what the table has now. 

Filter(Table1,StartsWith('Protocol /SC',SearchBox.Text)) 

I want to be able to still search with just that column or be able to search with that column + another column. So i want to be able to search 'Protocol/SC' then from that filter it down to 'Drug Name/Strength'. 

Any help would be appreciated. Thanks in advanced! 

[StalinPonnusamy]

Hi @dlo4242 

Got it. With the single search, you are trying to search 2 keywords in 2 columns. Please try this

With(
    {item: Table1},
    Filter(
        item,
        StartsWith(
            Column1,
            Last(
                FirstN(
                    Split(
                        SearchBox.Text,
                        " "
                    ).Result,
                    1
                ).Result
            ).Result
        ) || StartsWith(
            Column2,
            Last(
                FirstN(
                    Split(
                        SearchBox.Text,
                        " "
                    ).Result,
                    2
                ).Result
            ).Result
        )
    )
)

Note: Please replace Column1 and Column2 in the above code

Result/Reference

In this, our assumption is the first keyword is always search column1 and the Second keyword search Column2

Thanks,
Stalin - Learn To Illuminate

[dlo4242]

Yes. so I tried it and still doesn't work. So with this single search box I want to search column 1 and column 2 combine. So if I search for Column 1 (Drugs) then all the drugs should show up. Now column 2 had different strengths like 20mg 25mg and 30mg but all i want is 20mg. I want to search for Drug 1 20mg and only have that show up.

Thanks,

Dominic 

[StalinPonnusamy]

That is exactly what the query does. But the problem I see in the query is Space. If the drug name has space then this approach will not work. The current code split the search text by space. In this example, we are using space, and looks like it doesn't go to work for you since search text/drug name may have space.

Current Search: Drug 1 20mg

System Splits by space, So column 1 search by Drug and Column 2 search by 1 since 2nd text when we split by space.

Other options to resolve this

Option 1 Search text should be separated by other char like + instead of Space

Example Drug 1+20mg

this case Items property to

With(
    {item: Table1},
    Filter(
        item,
        StartsWith(
            Column1,
            Last(
                FirstN(
                    Split(
                        SearchBox.Text,
                        "+"
                    ).Result,
                    1
                ).Result
            ).Result
        ) || StartsWith(
            Column2,
            Last(
                FirstN(
                    Split(
                        SearchBox.Text,
                        "+"
                    ).Result,
                    2
                ).Result
            ).Result
        )
    )
)

Simply change the char whatever we like to have

Option 2: Have separate text/search control for Column1 and Column2

Items properties of the gallery should be

With(
    {Item: DataSource},
    Filter(
        Item,
        StartsWith(
            Column1,
            TextSearchBox1.Text
        ) && StartsWith(
            Column2,
            TextSearchBox2.Text
        )
    )
)

Please let me know which options you like to proceed with.

Thanks,
Stalin - Learn To Illuminate

[StalinPonnusamy]

Hi @dlo4242 

Please let us know if anything needs on your post. We can help with this.

Please mark the post as Solved If I have answered your question.
Please give it a Thumbs Up if you find the suggestion helpful

Thanks,
Stalin - Learn To Illuminate