Get the number of items created on predefined period from a sharepoint list

(0) Share

Report

Posted on by johnjohnPter

1,374

We need to build a report inside our Power Apps which show the following info:-

1) We have a SharePoint list named "Contracts", and we want to show the number of Contracts created from specific date to a specific date.

2) Each contract item inside the "Contracts" list has a status; "In Progress" , "Done", "Approved" & "Rejected". And we want to show the number of items at each stage from specific date to a specific date.

Now i know that i can build a collection inside my Power Apps , such as :-

Filter(Contracts, Created > varfrom && Created < varto);

but this collection can only hold max of 2,000 items..

So instead I am planning to do the following:-

1) Create a Power Automate flow which accept 2 DateTime parameters ( From & To) and which get called from Power Apps, then it get the related items using the built-in "Get Items" action which can get up to 100,000 items since we have premium license... and do the calculations and send them back to power apps..

So is my approach of using Power Apps with Power Automate the way to go?

Thanks

Categories:

Connectors

All responses (3)

Answers (0)

johnjohnPter 1,374 on at

Like (0)

Report

Get the number of items created on predefined period from a sharepoint list

@Pstork1

Thanks a lot for the great info
Mark Nanneman 329 on at

Like (1)

Report

Get the number of items created on predefined period from a sharepoint list

Have you considered using Power BI?

Another thing you could do is create new SharePoint lists to store your report metrics over time. It could be a row for every day or every week, and then you could have columns for each category and a date column (or date start and date stop column). Then you have a recurring flow tally the records in each category and save the numbers on your report metrics list. Then you can access and display that information in your canvas app.
Suggested answer

Pstork1 64,090 on at

Like (1)

Report

Get the number of items created on predefined period from a sharepoint list

Yes, I would definitely suggest doing this using a flow called from the Power app. But to clarify some things. Your Premium license means you can process up to 100,000 items in an Apply to Each loop. There is no specific limit on the Get Items since you are doing them in pages. I would recommend the following

1) Limit the columns you are retrieving by using a SharePoint View. That will decrease the size of the data set you are working with and increase performance.

2) Use a Filter Array action on the output of the Get Items to filter it to the subset you want to count. Then use the Length() function on that output to get the result. Append that result to an array variable.

3) Once you've got all the results calculated you can return the array you built using the HTTP Response action. You can capture it in Power Apps using a Collection.