Correct Filter Query for SharePoint Get Items where a column of Single line of text is empty?

[JohnAageAnderse]

I have a custom list in SharePoint Online in which is a column of type "Single line of text".

In my recurrent flow, I want to get items in which this column is empty, meaning that no value has been entered yet.

I have tried the following in the Filter Query field in the Get items step:

columnName eq '' - two single quotes.

length(columnName) eq 0 - apparently length should work in REST, but not here!

Anyone knows how to define the correct expression in the Filter Query field?

Kind regards, John

[Pieter_Veenstra · Accepted solution]

have you tried the expression null?

[JohnAageAnderse]

Yes, thanks for asking. The expression columnName eq null resulted in a failed flow.

columnName eq @{null} - is how it looks like in advanced mode (copy/paste into a text editor 🙂

[Pieter_Veenstra]

Hi @JohnAageAnderse

you should get just something like this:

Just go to expression and add null and the test will work. I just wrote a blog post about it on the community blog. I'm just waiting for this to be published. I will let you knwo when it is there.

[JohnAageAnderse · Accepted solution]

Thank you for your reply, but I am not talking about a condition step, I am talking about the Get Items step and the Filter Query field!

Anyway, I have now found the solution for this issue - using the expression editor to add the null comparision value will not work in the Filter Query field.

Browsing through other topics with something similar, I located this information:

http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

In section 4.5. Filter System Query Option ($filter), between the two tables, is a statement "Note: ISNULL or COALESCE operators are not defined. Instead, there is a null literal which can be used in comparisons."

The key information here is that the Filter Query understands a null literal!

I tried that and it worked - so the resulting Filter Query field content is:

columnName eq null

That simple and so hard to find! 🙂 John

[priya93]

Hi All,
Can you please help me to find correct filter to find the last event details whenever a new event is created, Actually i want to fetch the  details of previous events occurred in sharepoint calendar whenever a new event is created. For eg: if i am creating an event on today, and i have last previous event on some other date. Therefore i want the previous details of event.

[JohnAageAnderse]

Hello Priya93

You are asking a completely different question than in the original post. I suggest you start a new topic instead, as that will also make your post more visible to everyone.

Kind regards, John

[priya93]

Hi John,
I have posted my problem in the following link https://powerusers.microsoft.com/t5/Help-with-the-Forum/Correct-Filter-Query-for-SharePoint-Get-Item...  . Can you please help me if you know the solution as it is very urgent .
Thanks and Regards,

[JohnAageAnderse]

Hello Priya93

I can't open that link - it looks to be a private notes page link 🙂 "... notes/privatenotespage/tab ..."

Make a post and provide the link to the post.

Kind regards, John

[priya93]

Hi John,
Sorry for above🙂. I have updated link. Please go through.

https://powerusers.microsoft.com/t5/Help-with-the-Forum/Correct-Filter-Query-for-SharePoint-Get-Item... 
Thanks and regards,

[IPC_ahaas]

Seems like using null doesn't work anymore as the column isn't returned at all if null.

[Guz_90]

Hi, so how can we  do it now?