cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rich106
Level: Powered On

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 Team
Community Support Team

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
Level: Powered On

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 Team
Community Support Team

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
leyburn19
Level 10

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 368 members 6,327 guests
Recent signins:
Please welcome our newest community members: