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

Filter Query of Sharepoint rows between dates (dates specified in column)

Hi All,

 

I'm trying to automate sending out an excel sheet of rows filtered by a date column.

 

I want to include any row that has a value of the last 7 days.

Here's what I've tried:

 
 

 

But i keep getting this error :

 

The expression "2020-55-30" is not valid.
clientRequestId: 3e7e28fe-b34a-4ec3-9cc9-80ba03e5bf8a
serviceRequestId: 3e7e28fe-b34a-4ec3-9cc9-80ba03e5bf8a

 

 

Any help to filter the values by the last 7 days would be really helpful!

 

otsharepoint.PNG

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Hi

THis post explains how to filter items from a SP list whose DueDate is greater than today, where DueDate is date column:

https://powerusers.microsoft.com/t5/Building-Flows/FLOW-Get-Items-Filter-SharePoint-list-on-Date/td-...

 

This second post explains how to calculate a date by adding, substracting days from today, and change date format:

https://powerusers.microsoft.com/t5/Connecting-To-Data/Add-days-to-time-stamp/td-p/93096

 

Based on this approach, you need to filter items from a SP list whose DueDate is greater than 7 days ago.

I would try to insert the following expression...

formatDateTime(addDays(utcnow(),-7),'yyyy-MM-dd')

in your Odata Query

DueDate gt 'YOUR EXPRESSION HERE'

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

The screenshot is here:

 

otflowscreenshot.PNG

Highlighted
Dual Super User III
Dual Super User III

Hi

THis post explains how to filter items from a SP list whose DueDate is greater than today, where DueDate is date column:

https://powerusers.microsoft.com/t5/Building-Flows/FLOW-Get-Items-Filter-SharePoint-list-on-Date/td-...

 

This second post explains how to calculate a date by adding, substracting days from today, and change date format:

https://powerusers.microsoft.com/t5/Connecting-To-Data/Add-days-to-time-stamp/td-p/93096

 

Based on this approach, you need to filter items from a SP list whose DueDate is greater than 7 days ago.

I would try to insert the following expression...

formatDateTime(addDays(utcnow(),-7),'yyyy-MM-dd')

in your Odata Query

DueDate gt 'YOUR EXPRESSION HERE'

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted

@efialttes  thank you for the reply!

 

I tried implementing your solution but it still doesn't work:

 

otflowfail.PNG

otproof.PNG

 

Can you spot something I'm doing that is incorrect?

 

Thanks again for the help!

Highlighted
Dual Super User III
Dual Super User III

Hi

I think the problem is on the ODATA Filter: either the column name (You called it 'OverTime Start', right?) or the single quotes that shall be at the beginning and at the end of the expression (on the screenshot you just shared I am not sure if you added the closing quote)

 

Why the column name? Because if you define Column Names with special characters like spaces, Sharepoint will translate them internally and Flow relays on the SP Column internal name.

If you called the column OverTime Start I would try to define the ODATA Filter with OverTimeStart0, but not 100% sure this will be the internal translation. If not, either you can hover the mouse over the 'OverTime Start' dynamic content you already added in 'Create CSV' to get 'OverTime Start' internal column name or... remove temporarily the ODATA filter, execute the flow once and inspect the output of 'Get items' and search for 'OverTime Start' internal Column Name

 

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

Works!

 

You were right the column name was causing problems!

 

Thanks again!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Kudoed Authors
Users online (7,928)