cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JohnAageAnderse Impactful Individual
Impactful Individual

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?

View solution in original post

JohnAageAnderse Impactful Individual
Impactful Individual

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! 🙂 John

View solution in original post

9 REPLIES 9
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?

View solution in original post

JohnAageAnderse Impactful Individual
Impactful Individual

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 🙂

 

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.

JohnAageAnderse Impactful Individual
Impactful Individual

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! 🙂 John

View solution in original post

priya93 Helper I
Helper I

Re: Correct Filter Query for SharePoint Get Items where a i need to find the previous event date

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 Impactful Individual
Impactful Individual

Re: Correct Filter Query for SharePoint Get Items where a i need to find the previous event date

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

Re: Correct Filter Query for SharePoint Get Items where a i need to find the previous event date

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 Impactful Individual
Impactful Individual

Re: Correct Filter Query for SharePoint Get Items where a i need to find the previous event date

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

Re: Correct Filter Query for SharePoint Get Items where a i need to find the previous event date

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
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (9,391)