cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrNigel
Advocate V
Advocate V

Get Items Filter Query to select null dates

Hi,

 

I want to use a Get Items action to only select rows from a SharePoint list where SurveyRec (a date/time field) is empty (or null).  I have no issues using filter query with text fields, but a date/time field obviously needs an expression of some kind as it is treating 'null' as a string:

FilterQuery2.PNG

{

"status": 400,
"message": "String was not recognized as a valid DateTime. Creating query failed.\r\nclientRequestId: d0689b7d-d1e8-46d1-a8ce-ac5f7666a362\r\nserviceRequestId: d0689b7d-d1e8-46d1-a8ce-ac5f7666a362"
}

 

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
rocamp
Microsoft
Microsoft

Hi @MrNigel 

 

You should just be able to do this by just writing null with no quotes. Let me know if that works.

 

SurveyRec eq null

View solution in original post

CORRECTION!! I checked my List and there were no null rows due to the amount of testing I have done!  I deleted a date and sure enough the query only selected that row 😀

 

Many thanks,

 

Nigel

View solution in original post

8 REPLIES 8
rocamp
Microsoft
Microsoft

Hi @MrNigel 

 

You should just be able to do this by just writing null with no quotes. Let me know if that works.

 

SurveyRec eq null

View solution in original post

Hi, if I use null the Flow runs OK but doesn't select any items.

 

Tks

CORRECTION!! I checked my List and there were no null rows due to the amount of testing I have done!  I deleted a date and sure enough the query only selected that row 😀

 

Many thanks,

 

Nigel

View solution in original post

Hi @MrNigel 

 

Happy to help, and glad it's working for you! If you could mark my specific reply as the solution I would greatly appreciate it!

 

Best regards,

 

Robert

MrNigel
Advocate V
Advocate V

FYI this is the final Get Items filter query description.  The scenario was we needed to send out reminders to customers who hadn't replied to a survey after seven days.  The SharePoint List contained these date/time columns:

 

  • SurveyDate - the date the survey was sent
  • SurveyRec - the date the survey was received

The logic for sending a reminder is:

 

  • SurveyDate is not null (they had been sent a survey)
  • SurveyRec is null (they haven't replied)
  • Today-SurveyDate > 7 (don't send the reminder until a week has elapsed)

The first two calculations are direct, the last one needed an expression to calculate the DateDiff.  This is built using the Expression tab (how I still miss Advanced Mode 😪)

 

  • formatDateTime(addDays(utcNow(),-7),'yyyy-MM-dd')

The full filter query looks like this:

 

SurveyRec eq null and SurveyDate ne null and SurveyDate le '@{formatDateTime(addDays(utcNow(),-7),'yyyy-MM-dd')}'

 

FilterQuery3.PNG

The Flow is scheduled using a Recurrence trigger and we set it to run Tuesday and Friday at 10am.
 
Thank you Community for helping solve this for me.
 
Nigel
 
gr1974
Frequent Visitor

I have tried to use the columnName eq null, but am getting the following error:

 

The expression "Interop Test Complete Date eq null" is not valid. Creating query failed.
clientRequestId: a17dd6ed-02db-4a78-b3b6-59c4f6f1e863
serviceRequestId: a17dd6ed-02db-4a78-b3b6-59c4f6f1e863

 

Is there something I am formatting wrong for syntax?  There are definitely null values in that column, I copied the column name directly from the list settings instead of typing it in, and I have verified that there are no hidden characters in the date field.

@gr1974 

The likely reason for your error is that you are not using the columns internal name - internal names have no spaces. 

The easiest way to find the internal name is to create a Flow with a Get Items action and point to your list.  Take a look at the output of a run - however the column is referred to in the output is how you need to refer to it in your query filter.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
gr1974
Frequent Visitor

This was exactly what I needed, filter is working properly now.  Thank you!

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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.

Users online (3,457)