question on browsegallery filtering

[bh4]

hi guys,

I created a powerapp that links to a sharepiont list. I have 2 questions regarding the browsegallery window.

1. I want to create a filter that searches on title AND a status value 

The first part worked automatically (search on item Title):

SortByColumns(Filter('FILENAME', StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

The second one works on its own, but I don't know how to have the both active:

Filter('FILENAME', Status.Value= "In Progress" || Status.Value = "Funnel") )

2. Is there a way to make the search more forgiving? Eg show if even a part of the word is present in the title? Now it seems to be expecting a perfect match to the item title?

Thanks!

[mdevaney · Accepted solution]

@bh4 

Yes, there is a way to make the search more forgiving.  You can use the SEARCH function to return a set of results that partially match a string input.  I would suggest using the SEARCH function to evaluate the Title field.  You also wanted return any results matching either the Status "In Progress" or "Funnel".  FILTER would be best used here because we want to match the exact string.

Now that we have a clear approach to solve the problem lets write some code together.  First things first though, you must create a new text input control to receive the title seach string.  Lets call this txt_searchBar.

Next, create a new gallery and set the Items property to the following code.  We will write the code together step-by-step so it can be understood more clearly?  Notice how I decided to use the FILTER function first?  That is because FILTER is not subject to delegation and will evaluate all records within your dataset no matter the size.

Filter(
    your_datasource_name,
    Or(
        Status.Value = "In Progress",
        Status.Value = "Funnel"
    )
)

After you are satisfied the filter function is working its time to write our SEARCH function.  SEARCH in non-delegable but that's OK because FILTER cannot return more than 500 results anyways.

Search(
    Filter(
        your_datasource_name,
        Or(
            Status.Value = "In Progress",
            Status.Value = "Funnel"
        )
    ),
    txt_searchBar.Text,
    "Title"
)

Finally, it appears you want to SORT the results by Title.  Let's do that too.

Sort(
    Search(
        Filter(
            your_datasource_name,
            Or(
                Status.Value = "In Progress",
                Status.Value = "Funnel"
            )
        ),
        txt_searchBar.Text,
        "Title"
    ),
    "Title",
    If(SortDescending1,Descending,Ascending)
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[bh4]

@mdevaney  thank you so much for explaining it clearly.

That worked!

For some reason though i had to write in the formula:

TextSearchBox1.Text,

[mdevaney]

@bh4 

Thanks for the catch!  I have updated my answer to include .Text.

txt_searchBar.Text is necessary because we want to obtain the text string stored within the input field.  We could also do other (less useful things) like check the Font by typing txt_SearchBar.Font.