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

ne null - figured it out after several attempts 🙂

View solution in original post

16 REPLIES 16
Highlighted

ne null - figured it out after several attempts 🙂

View solution in original post

Highlighted

thank you! just what I was looking for

Highlighted
Anonymous
Not applicable

Awesome, thanks for this, very useful!

Highlighted

Thanks, was just looking for how to do this!

Highlighted
Advocate I
Advocate I

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

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

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
Anonymous
Not applicable

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

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

Thanks. Saved me many test runs.

Highlighted
Frequent Visitor

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

@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

@jameel 

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

Highlighted
Regular Visitor

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

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?

Highlighted

spent quite some time and I could of sworn the first thing I tried was eq null and it didnt work, spent a few hours trying other methods to come back the next day and try eq null again and it worked. It had to be user error on my part id imagine. lol 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (8,579)