cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CarlHRVA
Level 8

Help with ODATA filtering based on date

I am hoping to find some help with filtering a very large list with an ODATA query using the "Get Items" action. The filter will be based on two separate parameters: Status and Resolution Date. The purpose of the flow is to archive items that have a Status of "Resolved" and a Resolution Date that is equal to or less than 30 days from the date that the flow is being run. I've got the Status eq 'Resolved' part working (the equation is correct but it returns too many results) and need to incorporate the date filter as well in order to trim the query down to a working size.  I've read a number of different blogs and tried a few different solutions but I can't get the equation to work when trying to incorporate the date as well and I don't have much more time to keep up with trial and error. Any and all help would be greatly appreciated. Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Help with ODATA filtering based on date

Hey @CarlHRVA 

Check out this post for a good example: https://powerusers.microsoft.com/t5/Building-Flows/Get-List-Items-problem/m-p/436724#M51293

HTH

Jay

-------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up

 

View solution in original post

18 REPLIES 18
Super User
Super User

Re: Help with ODATA filtering based on date

Hey @CarlHRVA 

Check out this post for a good example: https://powerusers.microsoft.com/t5/Building-Flows/Get-List-Items-problem/m-p/436724#M51293

HTH

Jay

-------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up

 

View solution in original post

Highlighted
CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

Thank you, Jay! I will review and try to implement on my end and let you know how it goes.

 

Super User
Super User

Re: Help with ODATA filtering based on date

@CarlHRVA 

If you need extra help with the expression let me know

The filter operator for less than or equal is: Le

To obtain a date value of 30 days ago use this expression: addDays(utcNow(),-30)

HTH

Jay

CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

Thank you very much, Jay. This is the error that I keep getting and was getting before when trying to create this expression:

 

{
"status": 400,
"message": "The expression \"Status eq 'Resolved' and ResolutionDate le 2019-12-15T14:43:06.9324635Z\" is not valid.\r\nclientRequestId: 6e1c8f27-0684-4311-a771-4b920b6ff2b5\r\nserviceRequestId: 6e1c8f27-0684-4311-a771-4b920b6ff2b5"
}
 
Any ideas?
Super User
Super User

Re: Help with ODATA filtering based on date

Hi @CarlHRVA 

Can you share a screen shot of you configuration of the 'Get Items' action and the value entered into the filter query field?

Thanks

CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

flow.PNG

CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

Wait.... is it because I'm missing the single quotation marks around the formula?

 

CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

I think that was it! But I'm still running into a delegation issue. Anyway to get around this?flow2.PNGflow.PNG

Super User
Super User

Re: Help with ODATA filtering based on date

Hi @CarlHRVA 

 

Under the Get Items click the settings and turn on the pagination option and set the value.

 

image.png

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!



CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

Thank you for responding! When I try to copy your set up I get the following error:

 

flow.PNG

Super User
Super User

Re: Help with ODATA filtering based on date

Hi @CarlHRVA 

 

You will need a Flow Premium Plan (Plan 1 or Plan 2) in order to set the Pagination threshold to more than 5000 items.

 

You need to use Do Until loop (Each iterate 5000) to get all items.

 

Please follow this link below.

 

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!



CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

This seems like what I need @abm ! I'm getting the following error though:

flow3.PNG

 

 

 

My "Get items" action looks like the below:

 

flow.PNG

 

My union expression in the Compose action looks like the below:

 

union(variables('varItems'),body('Get items')?['value'])

 

And my empty body expression in the condition looks like so:

 

empty(body('Get items')?['value'])

 

Any idea what I'm doing wrong? I tried adding the underscore between get and items like they do in the example but I still get the same error. Thank you!

CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

In the example it says output for the compose action should be the below:

 

flow.PNG

 

 

This is what I get:

flow.PNG

 

I assume this is a result of my compose expression not being correct but thought it was worth mentioning.

Super User
Super User

Re: Help with ODATA filtering based on date

Hi,

 

In the example the compose step is renamed to Compose Array Union. So if you click the three dots on that right side of Compose action step and click rename. This way users can change the default step name to a more meaningful name.image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!



CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

That makes sense, thank you. Any idea what is wrong with the expression and why I'm getting the error?

 

flow.PNG

 

 

CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

I added the underscore back to the expression and it seems to be fixed. Last question, if you don't mind. Could you share a link that could show me how to test the output in a browser like they did or is that something I should start a new thread for? Thanks!

Super User
Super User

Re: Help with ODATA filtering based on date

Hi @CarlHRVA 

 

Glad that you have resolved the issue. Yes I can see that and unfortunately I don't know about that URL.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!



CarlHRVA
Level 8

Re: Help with ODATA filtering based on date

Thanks for all of your help @abm  @Jay-Encodian 

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

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

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (6,541)