Combining If, SortByColumns, and StartsWith to search a gallery populated with a collection

(0) Share

Report

Posted on by delluser

Hi everyone,

I am trying to include a search bar to search my gallery, which is currently populated by a collection.

Right now, I am populating my gallery successfully as follows: If Field1DropDown is not blank, filter Collection on Field1=Field1DropDown.Selected.Result. If Field1DropDown is blank, make my gallery blank.

I am sorting my gallery by "ID" field and using a context variable SortDescending to determine whether my gallery is sorted descending or ascending.

Here is my current gallery Items property which works great:

SortByColumns(
 If(
 !IsBlank(Field1DropDown.Selected.Result),
 Filter(
 Collection, 
 Field1=Field1DropDown.Selected.Result
 ),
 Blank()
 ),
 "ID",
 If(
 SortDescending,
 Descending,
 Ascending
 )
)

Everything falls when I try to add in my search bar functionality using StartsWith(). I know the basic structure should be something like:

SortByColumns(
If(
 IsBlank(SearchBar.Text),
 .....everything I had before with my Filter statement etc....),
 StartsWith(Field1, SearchBar.Text))),
 ...everything I had before with "ID" and SortDescending etc....
)
)

But for the life of me I cannot figure out the syntax. Probably something with parentheses, I'm not quite sure. If anyone has any advice here, I'd appreciate it so much! Thanks in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@delluser

Try to avoid using IF statements in your filter formula! This leads to a lot of redundancy in your formulas as well as confusion.

Please consider changing your Formula to the following:

SortByColumns( Filter(Collection, IsBlank(SearchBar.Text) || StartsWith(Field1, SearchBar.Text) ), "ID", If(SortDescending,Descending,Ascending ) )

Your other formula (if you're still trying to use that) should be:

SortByColumns( Filter(Collection, !IsBlank(Field1DropDown.Selected.Result) && Field1=Field1DropDown.Selected.Result ), "ID", If(SortDescending,Descending,Ascending ) )

NOTE: on the first formula, the logic is that if the Search text is blank, then you will get all results. If your intention is to have NO results unless something is entered, then change that first formula to:

SortByColumns( Filter(Collection, !IsBlank(SearchBar.Text) && StartsWith(Field1, SearchBar.Text) ), "ID", If(SortDescending,Descending,Ascending ) )

I hope this is helpful for you.

Was this reply helpful? Yes No
delluser 12 on at

Like (0)

Report
Copy link

Link copied!
Wow, that was awesome!! Thanks for the fast and accurate response. I ended up combining your first and second blocks of code like this to get both of those pieces of logic embedded into my gallery:
SortByColumns( Filter(Collection, !IsBlank(Field1DropDown.Selected.Result) && Field1=Field1DropDown.Selected.Result && (IsBlank(SearchBar.Text) || StartsWith(Field1, SearchBar.Text)) ), "ID", If(SortDescending,Descending,Ascending ) )
Works great. Thanks again so much!

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@delluser

Yes...I wasn't sure if you were trying to combine or replace. I assumed replace. Glad you figure out combine!!

Happy to help!

Was this reply helpful? Yes No