cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
junganthony
Level: Powered On

How to filter by not blank that can be delegated

Hi, I have a gallery thats connected to a Sharepoint Online (SPO) List, and I'm trying to apply a filter that will filter for only records that have one entry that is not blank (i.e. has ANY value in it).

 

I've tried using !(isBlank(value)), value <> Blank(), value <> "". All of these have the same issue that it cannot be delegated. Is there a way to filter out only records that have a non-blank value that is still delegatable to a SPO list?

Thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to filter by not blank that can be delegated

@junganthony 

I'm afraid to say that there is no way to do that filter without a delegation warning (at least that I know of).

"Not" is not delegable.  You could do value=Blank() - that would delegate, but that's not what you want...you want the opposite of that, so you need a Not, which again, is not delegable.

 

I believe you have but a couple of options:

1) Do the old "load all the records into a local collection" then filter against that.

2) If this is part of your design (finding records that are not blank), then I would work it into the data design.  In this case, perhaps consider adding a column that will be a true or false (yes/no) column.  This would need to be a real column (not a calculated column) and it would need to be populated based on your condition.  You could use Flow to set the yes or no of this column.  You could also use the built-in workflow of SharePoint to do this is well.  Either way, the goal is to get a column to be (let's say), yes/true if the value column contains a value, and no/false if it is blank.  This way you can Filter on the yes/no column to be 'true' - that is completely delegable.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

3 REPLIES 3
Super User
Super User

Re: How to filter by not blank that can be delegated

@junganthony 

I'm afraid to say that there is no way to do that filter without a delegation warning (at least that I know of).

"Not" is not delegable.  You could do value=Blank() - that would delegate, but that's not what you want...you want the opposite of that, so you need a Not, which again, is not delegable.

 

I believe you have but a couple of options:

1) Do the old "load all the records into a local collection" then filter against that.

2) If this is part of your design (finding records that are not blank), then I would work it into the data design.  In this case, perhaps consider adding a column that will be a true or false (yes/no) column.  This would need to be a real column (not a calculated column) and it would need to be populated based on your condition.  You could use Flow to set the yes or no of this column.  You could also use the built-in workflow of SharePoint to do this is well.  Either way, the goal is to get a column to be (let's say), yes/true if the value column contains a value, and no/false if it is blank.  This way you can Filter on the yes/no column to be 'true' - that is completely delegable.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

junganthony
Level: Powered On

Re: How to filter by not blank that can be delegated

Ah. I see. That's unfortunate, but thank you for the alternative solution. I'll give that a go and see how it works with the rest of the app. 

 

Do you happen to know why "not" delegable? I'm curious to as to why.

 

Thank you very much for your help.

Super User
Super User

Re: How to filter by not blank that can be delegated

@junganthony 

No, not entirely sure why that is non-delegable.  The only logical connection I can make is that it is a calculation, and, in general, calculations do not delegate. 

Something to always remember in PowerApps - shaping data within your App is important for efficiency and proper design and execution because the data is not how we need it, but sometimes the shaping of the data is pre-PowerApps.  Data may need to be shaped prior to the App because the internal shaping will not deal with things the way we need.  

Redundant as it may be, having a Flow/WorkFlow on SharePoint items to populate standard auxiliary columns (like ID's and Dates) with their equivalent values is very helpful for PowerApp design.  For example, making an auxiliary ID number column and having Flow/WorkFlow populate it with the record ID now opens up the ability to do more than just equality checks in your formulas.  Same with columns like dates - converting a date (like 6/3/2019) to a number (like 20190603) now gives us the ability to do greater than and less than filters without delegation issues.

Lot's of little gotchas to consider.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

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