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?

View solution in original post

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

View solution in original post

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (1,192)