cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

ODATA Filter Query for Date

Is there any way to set an ODATA filterquery to filter all records where some field is less than or equal to today's date? Tried searching around and couldn't find much on the topic.

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: ODATA Filter Query for Date

Hi @ bangorkeith,

 

 

Do you want to get records which's specific column is less than or equal to today's date?

Where is the records saved in?

 

I would create a flow to get items of a sharepoint list which's "Start Date" column is less than or equal to today's date,

Please refer to screenshot below to create the flow:

Capture.PNG

 

The expression in the "Filter array" action as below:

@lessOrEquals(formatDateTime(item()?['Start_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))

The flow would run successfully to get all items which's "Start Date" column is less than or equal to today's date as screenshot below:

Capture.PNG

 

 

 

Regards,
Alice Zhang

Highlighted
Regular Visitor

Re: ODATA Filter Query for Date

Prematurely marked this as solved...

 

The data is stored in a SharePoint list, in a Date formatted column. The following error is returned when I test the flow:

 

InvalidTemplate. The execution of template action 'Filter_Join_Date_1' failed: The evaluation of 'query' action 'where' expression '@lessOrEquals(formatDateTime(item()?['Start_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'

A more thorough explanation:

I need to data from a sharepoint list with the following parameters:

  • End Date (date column in sharepoint) is greater than or equals todays date
  • Join Date (date column in sharepoint) is less than or equals todays date
  • Title equals some value, say 'Chief of Staff'

I had been attempting this with an ODATA filter query directly in the "Get Items" action from SharePoint, but the workaround there was ineffective (checking for a default "End Date" value set far in the future; never was able to get it to filter based on "Join Date". I assume all three of these filters would need to be combined in one "Filter Array" action for this to work properly, but I'm unsure how to go about doing that. Syntax also seems to be an issue, as evidenced by the above quote.

Even if we got past those issues, would it be possible to pull the "Email" column from any item left and use it in assigning an Approval? I'm unclear if the "Filter Array" action actually filters and edits the array output of "Get Items", or if the Body is it's own filtered output and the input array is unchanged.

 

Highlighted
Community Support
Community Support

Re: ODATA Filter Query for Date

I'm unclear if the "Filter Array" action actually filters and edits the array output of "Get Items", or if the Body is it's own filtered output and the input array is unchanged.

 

Hi @ rich106,

 

The error message which your provided told that there are some item's "Start Date" column is null, the expression "formatDateTime()" couldn't process the null value.

 

Please make sure all columns which you would use with expression "formatDateTime()" wouldn't be null.

 

 

The "Filter Array" action actually filters( not edits)the array output of "Get Items", the Body is it's own filtered output and the input array is unchanged.

 

If you could make sure the "End Date","Join Date" column of all items is not null, you could refer to screenshot below to create the flow:

Capture.PNG

 

 

You could fill in expression in the Filter Array as below:

@and(equals(item()?['Title'], 'Chief of Staff'),and(greaterOrEquals(formatDateTime(item()?['End_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd')),lessOrEquals(formatDateTime(item()?['Join_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))))

 

 

The expression in the "Assign to" field of "Start an approval" as below:

item()?['Email']
The flow would run successfully as below:
Capture.PNG

If there would be some items' "End Date" or"Join Date" column are null,you could refer to screenshot below to create the flow:

Capture.PNG

You could fill in expression  in the Filter Array 2 as below:

 

@and(not(empty(item()?['End_x0020_Date'])),not(empty(item()?['Join_x0020_Date'])))

 

 

 

You could fill in expression in the Filter Array as below:

 

@and(equals(item()?['Title'], 'Chief of Staff'),and(greaterOrEquals(formatDateTime(item()?['End_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd')),lessOrEquals(formatDateTime(item()?['Join_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))))

 

The expression in the "Assign to" field of "Start an approval" as below:

item()?['Email']
 

The flow would run successfully as below:

Capture.PNG

 

 

Regards,
Alice Zhang

Highlighted
Super User
Super User

Re: ODATA Filter Query for Date

This seems very complex.  I have a daily schedule that simply gets the result in the Get Items Filter

 

This gets all the records where my DateTo Column is less than yesterday.  The formul in the expression box is addDays(utcNow(),-1)

 

Note the formula is inside ''

 

In you case it would need to be simple   utcNow()   Need to be conscious that you may need to adjust for time zone if there is a need for accruacy

 

 

lthan.PNG

 

 

 

 

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,574)