I want to build some dashboards inside our canvas app which summarize data from SharePoint lists. now i know that we can not directly query all the data from SharePoint inside our power apps, where we can only get up to 2,000 items. now in our case we have some SharePoint lists that get around 100-150 items per day for storing work orders, so around 37,000 items per year.
For example inside our work orders list, we have those main fields:-
1) Work Order Status
2) Work Order Technician
3) Create date
4) Closed Date
Now we want to build a dashboard to show the number of work orders for each technician, by selecting the month (or all months) and the year and the status. For example, we can know how many work orders were closed by a technician named Tech-A in May 2022. or how many Canceled work orders in all months in 2023 for Tech-B, as so on.. so i am planning to approach this in this way:-
1) Create an azure function which run daily at 1 am .
2) The function will use PnP core SDK which can get all data in patches.
3) then create a SharePoint list named WorkOrderSummary which stores the following info:-
Technician Name, Month, Year , Number Of Work Orders, Work Order Status.
4) then build some charts inside power app which will query and filter the data from the WorkOrderSummary list.
now on paper this approach should work, but not sure if it is valid when some to live? or i can have a better approach to follow?
Thanks
