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

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
RandyHayes
Super User III
Super User III

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

7 REPLIES 7
RandyHayes
Super User III
Super User III

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

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.

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
mcorlou
Regular Visitor

Hi,
i've solved this problem for a project like this > Non blank = All data minus Is Blank :

// Call all the data (Blank and non Blank)
ClearCollect(
DataSourceToClean;
DataSource
);;
// Remove the Blank data
RemoveIf(
DataSourceToClean;
valueToAnalyze = Blank()
)

Only the data with non blank value stay in the collection DataSourceToClean

But isn't that defeating the purpose?
Collecting prior to removing the blanks will still be non-delegable as you only get the first 500 records (or more if you extended it)

Yes you right sorry for my imprecision, in my app design; i collect data and I filter (with text fied) to call a more accurate set of data and then i use removeif. Like under. Maybe this problem can't be resolved by regular function but by design. SharedScreenshotforum.jpg

Have a good day.

You are probably correct!
I had the same issue, and my workaround was basically restructuring the entire function to avoid delegation, but I didn't find any way to just do !IsBlank and get it over with.

Anyways, thanks for sharing, have a great day!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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