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

5 REPLIES 5
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

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
 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (7,123)