cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
Microsoft

Re: Get Items Filter Query to select null dates

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

Highlighted
Advocate V
Advocate V

Re: Get Items Filter Query to select null dates

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

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Get Items Filter Query to select null dates

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

Highlighted
Advocate V
Advocate V

Re: Get Items Filter Query to select null dates

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

 

Tks

Highlighted
Advocate V
Advocate V

Re: Get Items Filter Query to select null dates

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

Highlighted
Microsoft
Microsoft

Re: Get Items Filter Query to select null dates

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

Highlighted
Advocate V
Advocate V

Re: Get Items Filter Query to select null dates

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
 

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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!

Top Solution Authors
Top Kudoed Authors
Users online (9,498)