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

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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!

RandyHayes
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,064)