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

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
Super User
Super User

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

have you tried the expression null?

Highlighted

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

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! Smiley Happy John

4 REPLIES 4
Super User
Super User

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

have you tried the expression null?

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

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 Smiley Happy

 

Super User
Super User

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

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.

Highlighted

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

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! Smiley Happy John

Helpful resources

Announcements
firstImage

Microsoft Flow Online Conference

Join us for a FULL day of FREE Microsoft Flow Sessions from some of the best minds in the industry!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 55 members 4,287 guests
Please welcome our newest community members: