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

question on browsegallery filtering

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User
Super User

@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."

 

View solution in original post

3 REPLIES 3
mdevaney
Super User
Super User

@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."

 

View solution in original post

bh4
Frequent Visitor

@mdevaney  thank you so much for explaining it clearly.

 

That worked!

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


TextSearchBox1.Text,

@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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,675)