cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RussellGove
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!

24 REPLIES 24

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

I havent actually got it working yet. When i use the if statement, I get blue squigglys:

new.PNG

 

But without the if, it looks oK:

old.PNG

So it seems the if function itself is not delegatable on a sharepoint backend. Any Ideas?

IsBlank cannot be delegated. See link below for  more detail.

 

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

 

 

 

yeah, the IF statement itself is not delegatable either : https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

 I am not sure if your filter requirement will work this way: Item=If(condition, Filter(Splist,abc),Filter(splist,xyz))

@RussellGove @IvanZ 

Darn it.  I guess my delegation settings weren't set low enough to catch this.  Sorry guys.  I was definitely wrong on the delegation part

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

 

I have a similar filter, and IsBlank does trigger delegation warning. You can test for delegation with this formula:

Filter(
    FPARequests,
    IsBlank(tbYear.Text)
)

 

 

@IvanZ 

Here's what I am seeing with the following settings:

 

datasource: SP List

list size: 3000 records

data row limit for non-delegable queries: 1

 

Untitled.png

 

Likewise, no delegation warning on the previous code block I sent for whatever reason.  What's up with that?

I tested on your formula, it is indeed able to delegate. The reason why it works because IsBlank(Input.Text) return a value which doesn't query Sharepoint. If you do IsBlank(SPColumn.value) then it will get the warning. 

 

Hope this answer your question. 

 

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 (4,379)