Unanswered

Filter criteria to split words in a textbox and OR check multiple columns for each of the words?

(0) Share

Report

Posted on by CU24030619-0

Hi,

I have a two-screen Power App, where on the first screen you define search criteria and on the second you see the results of filtering a gallery's records using values from the first screen.

This has been working as expected.

I have been asked to put a free text field on screen 1 (called "SearchText") and to include a filter which includes records for any of the words typed into SearchText in any of several fields.

So, e.g., I have 2 columns in my collection: Description, Comments

If I type "check both columns" into SearchText on Screen 1, when I go to Screen 2 I want:

The search term broken up into distinct words by " ": [check, both, columns]
Any records returned by other filter criteria from Screen 1 to be expanded by any records where any of the search words appear in either the Description OR Comments columns

What I have tried in the `OnVisible` property of Screen 2:

ClearCollect(
    SearchTerms,
    Split(SearchText.Text, " ")
);
 
ClearCollect(
    cQueryResult,
    If(
        ClearFilters,
        Sort(
            cGalleryRecords,
            'Name (Title)',
            SortOrder.Ascending
        ),
        Filter(
            Sort(
                cGalleryRecords,
                'Name (Title)',
                SortOrder.Ascending
            ),
            If(
                CountRows(ChooseCategoryBox.SelectedItems) > 0,
                'Name (Title)' in ChooseCategoryBox.SelectedItems.'Name (Title)',
                true
            ),
            If(
                CountRows(ChooseSizeBox.SelectedItems) > 0,
                CountRows(
                    Filter(
                        Sizes,
                        Value in ChooseSizeBox.SelectedItems.Value
                    )
                ) > 0,
                true
            ),
            ForAll(
                SearchTerms As SRCHWRD,
                SRCHWRD.Value in Comments || SRCHWRD.Value in Description
            )
        )
    )
);
Reset(ProductsGallery);

The above is clearly wrong - I would love some help in figuring out how to add records where any of the search words are found in either Description or Comments (or ThirdColumn or FourthColumn and so on).

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

SwatiSTW 741 Super User 2025 Season 2 on at

Like (1)

Report

You want to filter gallery records where any word typed in a search box on screen 1 is found in either Description or Comments column on screen 2.

1. Add this line in Screen 2's OnVisible property to split words from the SearchText field:
   ClearCollect(colSearchTerms, Split(SearchText.Text, " "))
2. Use this code to filter and collect the gallery data:
   ClearCollect(
       colQueryResult,
       If(
           ClearFilters,
           Sort(cGalleryRecords, 'Name (Title)', SortOrder.Ascending),
           Filter(
               Sort(cGalleryRecords, 'Name (Title)', SortOrder.Ascending),
               If(
                   CountRows(ChooseCategoryBox.SelectedItems) > 0,
                   'Name (Title)' in ChooseCategoryBox.SelectedItems.'Name (Title)',
                   true
               ),
               If(
                   CountRows(ChooseSizeBox.SelectedItems) > 0,
                   CountRows(
                       Filter(Sizes, Value in ChooseSizeBox.SelectedItems.Value)
                   ) > 0,
                   true
               ),
               CountIf(
                   colSearchTerms,
                   Lower(ThisRecord.Description & " " & ThisRecord.Comments)
                   Contains Lower(Result)
               ) > 0
           )
       )
   )
3. Reset the gallery after applying filters:
   Reset(ProductsGallery)
4. You can add more fields in this part if needed:
   ThisRecord.Description & " " & ThisRecord.Comments
   like this:
   ThisRecord.Description & " " & ThisRecord.Comments & " " & ThisRecord.ThirdColumn

This will make your search match any word in multiple columns.

Was this reply helpful? Yes No
CU24030619-0 2 on at

Like (0)

Report
Hi @SwatiSTW, thank you for your reply! For some reason I had to change some of the syntax in your reply. I had to use `Value` instead of `Result` and an `in` operation rather than a `Contains` operation. (I don't know why I had to change this).

Now I'm wondering if I can add searching a multiselect column to the CountIf operation?

Current CountIf:

CountIf( cSearchWords, Lower(Value) in Lower(Description & " " & Comments & " " & Othercontacts) ) > 0

So, if I have a `Color` column in my List, which can contain multiple values such as one or more of [Red, Green, Orange, Brown, Yellow] if I type "Green" into the search box, is there any way I can also check if the `Color` column for each record has "Green" as one of its values?

Was this reply helpful? Yes No