cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User II
Super User II

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
Highlighted

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

Highlighted
Super User II
Super User II

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
Highlighted

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

Highlighted
Super User II
Super User II

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 🙂

 

Highlighted

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

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

Highlighted
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.

Highlighted
Super User II
Super User II

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

Highlighted
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,

Highlighted
Super User II
Super User II

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

Highlighted
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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,488)