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

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

 

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

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 (6,968)