Odata Filter Null

[WiggityZwiggity]

Does anyone know how to use an odata filter on a SharePoint Get Items action to filter out items with a null value in a text/string column?

I have tried: text_x0020_field ne ''

But that does not seem to work - what is the correct approach?

[WiggityZwiggity · Accepted solution]

ne null - figured it out after several attempts 🙂

[Meneghino]

thank you! just what I was looking for

[Anonymous]

Awesome, thanks for this, very useful!

[AronBrown]

Thanks, was just looking for how to do this!

[Brian-Hamilton]

I could swear this happened. 

EndDate eq null (Didn't work)

EndDate eq 'null' (Didn't work)

EndDate eq to the expression null (Didn't work)

EndDate eq to the expression 'null' (Didn't work)

EndDate eq null (Worked)

[AronBrown]

Yeah, if you tried to do the eq null again after the 1st of May or April, cant remember now but it fixed itsself with an update from microsoft

[tomoore]

And as of Jan1 2020, its broken again.  As previously posted:

EndDate eq null (Didn't work)

EndDate eq 'null' (Didn't work)

EndDate eq to the expression null (Didn't work)

EndDate eq to the expression 'null' (Didn't work)

🤣  Here I am...  HOWEVER,   EndDate eq 'null'  doesn't error out (only one that doesn't) but won't filter anything unless the string null is in the column/cell. 

[HillaRamo]

I thought I was going crazy. But this explains a lot. My flows used to work with date eq null... but suddenly not anymore. So I, too, think they must be broken now. 

Which means I have to figure out a workaround to 40958409094589 flows until this is fixed 😞 

[hjaf]

How could we solve this when the query is referencing variables.. if that variable in that context is '' then this will not work. having a if(empty(variable), null, variable) will not work either because this produces a query 'null' which translates into''

[YatinMumbai]

Thanks. Saved me many test runs.

[jameel]

Posting this as it worked - typing null as is, not as a dynamic function, no empty() or anything.

https://www.c-sharpcorner.com/blogs/how-to-check-null-value-in-odata-filter-of-get-items-action-in-f...

[eopara]

@tomoore 

It's tragic that we are back to this again. It fails for me; using Azure Tables.

Here is my own query

No_ ne null

No_ ne null

Here is the error message

{
  "odata.error": {
    "code": "InvalidInput",
    "message": {
      "lang": "en-US",
      "value": "One of the request inputs is not valid.\nRequestId:bb0ee6aa-c002-0097-7b08-27c0b3000000\nTime:2020-05-10T20:20:00.0511375Z"
    }
  }
}

[eopara]

@jameel 

This doesn't work as we speak. Please verify.

[Alan7]

I thought I was going crazy.  It no longer works for me either.

I did the following:

ColumnA eq null (didn't work)

ColumnA eq 'null' (didn't work)

ColumnA eq null (as an expression) ... (didn't work)

Now I have to apply workarounds to all my flows where I really need to filter on blank fields in records 😫

[jameel]

Hi  @eopara 

I finally had some time to test this in a makeshift flow and I'm still seeing it working using null as a literal. I see the Odata 4.0 spec indicating null is the correct literal too http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#_Toc31361030.

So i am super confused why it's not working for you suddenly.

Here's how my test is set up

1. Created a simple list with Title, Input, Output fields

I designed a flow where when i modify Input, it puts the total count of all non-null values from Input into Output. For example, for the above picture, there are 3 rows where Input is not null, so Output for rows where Input is not null = 3:

Get items

• I filter for when Input not equals null using syntax internal_name ne null

Initialize variable

• I set a variable to the length of the collection
• Value is set to dynamic expression: length(body('Get_items')?['value'])

Apply to Each

• I set the Output column to item count

And this works for me. 

I also tried setting a Trigger Condition to only trigger if Output is null and I'm not 100% sure this worked correctly.

Here I changed one of the 1s to 2 and the flow did seem to run anyway. So maybe something up with the trigger condition.

Just to be sure, I changed input ne null to input eq null and the workflow responded as expected, filling in the outputs where input was blank.

I wonder if flows get corrupted with certain updates and actions have to be rebuilt in some way?