How to filter by not blank that can be delegated

[junganthony]

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.

[RandyHayes · Accepted solution]

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

[junganthony]

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.

[RandyHayes]

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

[mcorlou]

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

[Anonymous]

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)

[mcorlou]

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. 

Have a good day.

[Anonymous]

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!

[Evina]

This worked perfectly for me in my clearcollect formula! Thanks!! 

[Dinashan]

Hi Randy,

I encountered the same issue about not being able to filter NOT(ISBLANK()) on my PowerApps Gallery. I tried your aforementioned solution number 2, which is creating checker column and use Power Automate flow to set "yes" or "no" of this column.
I made the flow with steps as follows:

1. If an item on my sharepoint list is created or modified, then

2. check Condition --> whether the column I want to filter is not equal to null

if yes --> then update item on checker column with text "YES",

if no --> then update item on checker column with text "NO".

But the result came to error with code "Action in update item flow may result in an infinite trigger loop". I realized that if the checker column is updated with "yes" or "no" text, the flow will regard it as item being modified, hence automatically triggering the 1st step again.

Could you please give a suggestion about the correct way of making flow for this checker column?

Thank you in advance.