cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Filtering on multiple fields

Hi ,

I have a gallery and i'm trying to give users several ways to filter it. I have controls above the gallery to let the user select a Year, region, customer or keyword to filter on. I want to use only the filters the user has selected. 

 

My script ends up looking like this (this represents just the conditions, not the actual filters):

conditions.PNG

 

After adding the actual filtering logic for each condition (and god forbid an other condition) this ends up being hundreds of lines of script that are largely incomprehensible, even to the author!

 

Is there a better way to do this?

 

What we really need is a real code editor! If i could edit this script in an intelligent VSCode editor I'd have this done in minutes. The way it is it's taking days!

 

Please give me back my curly braces!

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Filtering on multiple fields

@RussellGove 

Here's my pattern for filtering on multiple fields.  Put this code in the Items property of your gallery.

 

Filter(
    FPARequests,
    If(
        IsBlank(tbYear.Text),
        true,
        Year = tbYear.Text
    ),
    If(
        IsEmpty(cbxRegion.Selected),
        true,
        Region = cbxRegion.Selected
    ),
    If(
        IsBlank(tbCustomer.Text),
        true,
        Customer = tbCustomer.Text
    ),
    If(
        IsBlank(tbKeyword.Text),
        true,
        Keyword = tbKeyword.Text
    )
)

 

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

Highlighted
Super User
Super User

Re: Filtering on multiple fields

@RussellGove
Glad to hear it’s working now. Please mark the post that solved your issue as the solution so others can find it in the future.

View solution in original post

Highlighted
Super User
Super User

Re: Filtering on multiple fields

@RussellGove @IvanZ 

Could you please humour me and let me know if you are getting delegation warnings on this style of pattern?  I turned my delegation rows down to 1 using a list of 3,000 records and no error is appearing.  I know it says IsBlank is non-delegable for Text in Sharepoint but I am just not seeing the error.  I would appreciate it if you can help me do a test 🙂

 

Filter(
    FPARequests,
    IsBlank(tbYear.Text)=true Or Year = tbYear.Text,
    IsEmpty(cbxRegion.Selected)=true Or Region = cbxRegion.Selected,
    IsBlank(tbCustomer.Text)=true Or Customer tbCustomer.Text,
    IsBlank(tbKeyword.Text)=true Or Keyword = tbKeyword.Text
)

 

View solution in original post

24 REPLIES 24
Highlighted
Super User
Super User

Re: Filtering on multiple fields

@RussellGove 

Here's my pattern for filtering on multiple fields.  Put this code in the Items property of your gallery.

 

Filter(
    FPARequests,
    If(
        IsBlank(tbYear.Text),
        true,
        Year = tbYear.Text
    ),
    If(
        IsEmpty(cbxRegion.Selected),
        true,
        Region = cbxRegion.Selected
    ),
    If(
        IsBlank(tbCustomer.Text),
        true,
        Customer = tbCustomer.Text
    ),
    If(
        IsBlank(tbKeyword.Text),
        true,
        Keyword = tbKeyword.Text
    )
)

 

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

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filtering on multiple fields

Does that work with a sharepoint list?

Highlighted
Super User
Super User

Re: Filtering on multiple fields

Yes, it works with any datasource.

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.
Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filtering on multiple fields

And can it be delegated?

 

Highlighted
Super User
Super User

Re: Filtering on multiple fields

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filtering on multiple fields

I just get  a bunch of red squiggles when i paste in that code. Do you have a working example somewhere?

Can you explain how it works? I  don't see, for example, how it could possibly know what field in my datasource  should be compared with tbYear.Text

 

 

Highlighted
Super User
Super User

Re: Filtering on multiple fields

@RussellGove 

I've updated my code in the original post.  See below for response to your questions.

 

"I just get a bunch of red squiggles when i paste in that code"

I missed typing commas after "true" on each line.

 

"How it could possibly know what field in my datasource  should be compared with tbYear.Text"

I didn't put in this comparison initially as I did not see any in your original code.  I have now put in Year, Region, Customer and Keyword.  These names must match the column name in SharePoint.

 

 

 

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

Highlighted
Helper I
Helper I

Re: Filtering on multiple fields

Check out this video, it demo a fully multiple filtering gallery.

 

https://www.youtube.com/watch?v=pn50AKn3Q1Y&t=82s

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filtering on multiple fields

Thanskyou. This saved me hours and hours of work trying to do all those nested ifs and elses.

 

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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