cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Odata Filter Null

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Odata Filter Null

ne null - figured it out after several attempts 🙂

View solution in original post

15 REPLIES 15
Highlighted

Re: Odata Filter Null

ne null - figured it out after several attempts 🙂

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Odata Filter Null

thank you! just what I was looking for

Highlighted
Anonymous
Not applicable

Re: Odata Filter Null

Awesome, thanks for this, very useful!

Advocate I
Advocate I

Re: Odata Filter Null

Thanks, was just looking for how to do this!

Highlighted
Frequent Visitor

Re: Odata Filter Null

I could swear this happened. Smiley Tongue

 

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)

 

 

Highlighted
Advocate I
Advocate I

Re: Odata Filter Null

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

 

Highlighted
Regular Visitor

Re: Odata Filter Null

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. 

Highlighted
Frequent Visitor

Re: Odata Filter Null

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 😞 

Highlighted
Helper V
Helper V

Re: Odata Filter Null

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''

Highlighted
Advocate II
Advocate II

Re: Odata Filter Null

Thanks. Saved me many test runs.

Highlighted
Frequent Visitor

Re: Odata Filter Null

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

 

image.png

 
 
Highlighted
Advocate II
Advocate II

Re: Odata Filter Null

@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"
    }
  }
}

 

Highlighted
Advocate II
Advocate II

Re: Odata Filter Null

@jameel 

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

Highlighted
Regular Visitor

Re: Odata Filter Null

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 😫

 

 

Highlighted
Frequent Visitor

Re: Odata Filter Null

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

image.png

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:

image.png

 

Get items

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

image.png

 

Initialize variable

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

image.png

 

Apply to Each

  • I set the Output column to item count

image.png

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.

image.png

 

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.

image.png

 

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.

image.png

image.png

 

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

Helpful resources

Announcements
firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Users online (7,714)