cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnAageAnderse
Memorable Member
Memorable Member

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions

have you tried the expression null?

View solution in original post

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

View solution in original post

9 REPLIES 9

have you tried the expression null?

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 🙂

 

Hi @JohnAageAnderse

 

you should get just something like this:

 

nullcondition.PNG

 

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.

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
Helper I
Helper I

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.

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

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,

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

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,

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,078)