Filter Query on GetItems based on a date value

[kevingeorget]

Hi Flow Users,

I have a SP list having a field called 'ReservationUntil'- a date field. An item may or may not have an value for that.

I need to configure a recurring flow that check for the items in the list based on 'ReservationUntil' value less than 7 days from today's value and send out remainder mails to the creator.

In the Flow, I'm not able to Filter query on the GetItems action as it contains null value as well.

This is the filter query i use-

@{formatDateTime(triggerBody()?['ReservationUntil'],'yyyy-MM-dd')}ge @{formatDateTime(utcNow(),'yyyy-MM-dd')} AND @{formatDateTime(triggerBody()?['ReservationUntil'],'yyyy-MM-dd')}le @{variables('FutureDate)}

FutureDate is a variable having the value Today's date+7 days.

If anyone has a workaround. could you please suggest.

Thanks,

Kevin

[AlanPs1]

Hi @kevingeorget 

Thank you for the detail in your post.

I wonder if you could only get the list item "ReservationUntil" if not null or not empty  in a condition, then do your date comparison.

expression something like:

not(empty(triggerBody()?['ReservationUntil']))

not(equals(triggerBody()?['ReservationUntil'], null))

If either of these are equal to true, then do the date comparison in the yes branch of the condition.

Worth a go?

If you have found my post helpful, please mark thumbs up.

If this post has solved your problem, please click "Accept as Solution".

Any other questions, just ask.

Thanks, Alan

[ScottShearer]

@kevingeorget :

I don't believe that you should have any issues using an OData filter in Get Items to meet your requirements.  The null values should not be an issue.

Have you tried using an OData filter? if you have and you had issue, can you post a screen shot of the filter?

Scott

[kevingeorget]

Hi @AlanPs1 ,

Thanks for the reply.

I'm able to filter the getitems query on items with 'ReservationUntil' not null (ne null). But then how do i get each record from the value set? I need to send out an email for each item where the Reservation date is less than 7 days.

I couldn't think of any options.

Regards,

Kevin

[kevingeorget]

Hi @ScottShearer ,

Thanks for the reply.

I haven't used the OData filter. Can you share some details regarding this.

Below the screenshot of the Get Items action and error screenshot.

[ScottShearer · Accepted solution]

@kevingeorget :

Please see my example below and give it a try.

I used a Button for test purposes - you should use a recurrence trigger.

I used a varible for the future date calc, but you could easily eliminate the variable and do the calculation in an expression directly in the OData filter.

Please note the single quotes around the expression and the reference to the variable.

I am assuming that the ReservationUntil column is a date column that is set to store just date and not date and time.

If you like my solution, please give it a thumbs up.

If my solution meets your requirement, please mark your question as Solved.

Scott

[kevingeorget]

@ScottShearer thank you for taking the effort to help me out. This indeed worked for me. I was missing the single quotes in the expression.