Help with ODATA filtering based on date

[CarlHRVA]

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!

[Jay-Encodian · Accepted solution]

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

[CarlHRVA]

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

[Jay-Encodian]

@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]

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?

[Jay-Encodian]

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]

[CarlHRVA]

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

[CarlHRVA]

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

[abm]

Hi @CarlHRVA 

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

Thanks

[CarlHRVA]

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

[abm]

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

[CarlHRVA]

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

My "Get items" action looks like the below:

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]

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

This is what I get:

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

[abm]

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.

[CarlHRVA]

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

[CarlHRVA]

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!

[abm]

Hi @CarlHRVA 

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

Thanks

[CarlHRVA]

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