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

MrNigel 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
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
MrNigel 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

MrNigel 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

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

MrNigel 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

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,055)