automated report from SharePoint list, filtered by Status column, limit to 1 day in past - addDays expression PowerAutomate

Question

I am trying to automate a daily report that will look at an existing SharePoint list and only pull the entries marked as 'In Process' for the past 1 day. I think I have everything functioning correctly except for limiting the history to 1 day instead of the entire life of the list (which is large).

Additionally, not sure how to get my data to display better - would like the data in regular date/time format and the Status column to just show 'In Process'.

Answer 1

Dear Drea,

You may use Filter Array and type the formula @AND(lessOrEquals(formatDateTime(item()?['Time'], 'MM-dd-yyyy hh:mm:ss tt'), formatDateTime(addDays(utcNow(), -1), 'MM-dd-yyyy hh:mm:ss tt')), greaterOrEquals(formatDateTime(item()?['Time'], 'MM-dd-yyyy hh:mm:ss tt'), formatDateTime(addDays(utcNow(), -2), 'MM-dd-yyyy hh:mm:ss tt'))) to filter the items.

In my demo list, there are five items and you can see the qualified items are item1 and item 5.

So the flow can be created as follows.

Compose: items('Apply_to_each')?['Title']

Compose2: formatDateTime(items('Apply_to_each')?['Time'],'MM-dd-yyyy hh:mm:ss tt')

Compose3: items('Apply_to_each')?['Status']?['Value']

Value in Append to array variable:

{

"Title": "@{outputs('Compose')}",

"Time": "@{outputs('Compose_2')}",

"Status": "@{outputs('Compose_3')}"

}

Welcome to share any updates when you have time.

Thanks for your effort and time!

Sincerely

Cliff | Microsoft Community Moderator

Answer 2

Hi,

This list I have has over 100k entries - pulling the entire thing every time will take too long and make the files larger than they need to be. I want to filter the Query from within the Get Items trigger. I know it's possible to filter based on the label, I just need to add the time restriction of only the previous day.

Answer 3

Dear Drea,

Thanks for your updates and letting us know the requirement.

If you want to use Filter Query, you can use the following example expression.

Time le '@{addDays(utcNow(), -1,'yyyy-MM-dd hh:mm:ss tt')}' and Time ge '@{addDays(utcNow(), -2,'yyyy-MM-dd hh:mm:ss tt')}' and Status eq 'In Progress'

To format the .csv table data, you can format it as follows.

Title: item()?['Title']

Time: formatDateTime(item()?['Time'], 'MM-dd-yyyy hh:mm:ss tt')

Status: item()?['Status']?['Value']

Welcome to share any updates when you have time.

Thanks for your effort and time!

Sincerely

Cliff | Microsoft Community Moderator

Answer 4

Hi Cliff,

Thanks again for all the detail you provided. It's really helpful. I'm still a bit of a noob at Power Automate. My flow is getting hung up on Get File Content, but I noticed that you have expressions in your triggers where as the template I was following to build my flow only used SharePoint dynamic content. Could that be the reason my flow getting hung up? Or is over 20 minutes a normal time for a flow like this to take?

Answer 5

Dear Drea,

Thanks for your updates.

Even though you use Filter Query, based on the count of the qualified items, the flow will take long time and may suspend for the large count which is in the range of 1-5000.

If the qualified items' count is larger than 5000, you may add multiple Get Items actions with the filtered query and let them read the first round 1-5000 items, the second round 5001-10000 items and so on. You may add the Filter Query with the expressions like ID gt 'number' and type 5000 on Top Count as the maximum item count each Get Items action is 5000.

For your reference: Solved: Get Items from Large SharePoint online List - Power Platform Community (microsoft.com)

You can refer to the way Mattw112IG mentioned in the thread above to deploy your flow in your environment.

In my environment, 5000 items with the simple filter query runs 5 minutes in a list with more than 100000 items. You can try Top Count which is less than 5000 to see if the time is reduced in your environment.

Please also use the feedback tool below our reply to submit your feedback for us (pictured below), which will definitely help others in the community who are experiencing similar problems to find solutions to their problems more quickly.

Thanks for your understanding and have a nice day!

Sincerely

Cliff | Microsoft Community Moderator