Filtering using multiple filters (text boxes and dropdowns)

(0) Share

Report

Posted on by PowerAddict

7,316 Most Valuable Professional

Data source is CDS. I have a gallery that needs to be filtered by several filters, lets assume 5 for now.

Any one of them could be blank. If one is blank, then the gallery shouldn't be filtered on that particular filter.

I initially had something like this: Filter(Entity1, If(IsBlank(Dropdown1.Selected.Value), Column1 = Dropdown1.Selected.Value, true),............If(IsBlank(TextInput1.Text), TextInput1.Text in Column5, true))

This worked fine until we hit the delegation limit. Now, I am trying to rewrite this expression factoring in delegation. One of the things I noticed is that if I check for blank values outside of the Filter, then there are no delegation warnings. In other words, the actual filters are all delegable.

However, that is almost impossible or very difficult to do because I will have to account for all the situations in which none, or one, or two, or three, or four, or all five filters could have values.

I am at a loss now. Any help will be highly appreciated. Please let me know if you need any other info.

Thanks,

Hardit Bhatia

https://thepoweraddict.com

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!
@PowerAddict

To summarize your post below I believe the question is "how can I create a gallery filtered by 5 different controls while ignoring any blank controls."

This is what I would try to use in your situation. The code IsBlank(TextInput1.Text)=True tells the FILTER to ignore that line when the dropdown control is Blank.

Filter( Entity1, IsBlank(Dropdown1.Selected.Value)=True Or Column1 = Dropdown1.Selected.Value, ..., IsBlank(TextInput1.Text)=True Or TextInput1.Text in Column5 )

I originally came up with this pattern to help another user do the same thing in SharePoint while avoiding delegation limits.

Filter( your_datasource_name, 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 )

Link To My Original Post:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Filtering-on-multiple-fields/m-p/406120/highlight/true#M119400

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

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Thanks @mdevaney Reading the original post and how it progressed was real fun! I think this will work, I will wrap it up and reply back if it doesn't.

Thanks,
Hardit Bhatia
https://thepoweraddict.com

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!

@PowerAddict

That original post was... challenging. I was starting to think there was no possible way to do it, was almost about to give up, and then *EUREKA* together we finally found the solution. I am crossing my fingers here and hoping it works for you too.

On another note: I would really like to get my hands on CDS soon. I've just started a Community Plan with the intention to give it a Test Drive. I am sure I will be coming back to you with questions about that soon... You are lucky to be working with such new technology!

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Yes, it worked perfectly fine! I wanted to share it with as many people as possible so I wrote a quick blog on it and credited you and the others who were involved in the original post!
And yes, CDS is fun. It is sometimes difficult to imagine any other data source now 🙂 I am always available to answer questions, have discussions and more importantly, have fun!

Feel free to reach out any time!

Thanks,
Hardit Bhatia
https://thepoweraddict.com

Was this reply helpful? Yes No