Counts for data that are over delegation limit

[Bochie]

Hi All!

Seeking advice on the best approach for my future scenario. I have a list in SharePoint that gets over 200 records daily for a year. Each record will have a Status. The Status values are 8 (status1, status2, status3, so on).

My users would want a table that shows the Status counts. I know I can use the GroupBy function for this. However, my fear is the delegation limit of counting it. First few months I will probably get by even if I increase my delegation limit to 2000. But my data for some status value will surely hit more than 2000. For my data, it's something like this:

Country	Status
USA	Status1
Japan	Status3
Germany	Status1
Argentina	Status1
China	Status5
France	Status4
Brazil	Status1
Thailand	Status2
Spain	Status1

I'm thinking a gallery table grouped by Distinct Country for instance using GroupBy Function... and then as another column to that gallery will be a sum of the Status... so the gallery will look like this:

column of unique countries	Status1	Status2	Status3	Status4	Status5	Status6	Status7...
USA	countofStatus1	countofStatus2	countofStatus3	etc
France

//countofStatus1
Sum(ThisItem.grpCountry, Status = "Status1")

//countofStatus2
Sum(ThisItem.grpCountry, Status = "Status12")

 My problem will be for that Sum function when it hits delegation limit. Is what I am thinking correct? Is it the right approach? What functions can I use with Sum so it does not hit delegation limit?

Thank you for any insight/advice!

[Drrickryp]

@Bochie 

To make this work you need to pre-filter your results, for example, by country and hopefully get groups less than 2000 items. If this still gives >2k rows you may need to further filter by quarters.  then get a subtotal for each country/quarter and then sum the subtotals for a grand total. 

[Bochie]

Hi @Drrickryp ,

Thank you for your response. When I pre-filter, can I all do this and pass it to collections? There will definitely be some that will go over 2k rows. My main concern is my users' need for the counts to be as close to real time as possible and how it will affect performance of the app. I can certainly make it real time but once I hit delegation, I was thinking I will use collections and a timer to constantly refresh the counts (say every 5 mins at least?) Also thinking I can create a separate list for the counts that gets refreshed by a recurring flow (using Get Items action for SharePoint) in Power Automate.  

[Drrickryp]

@Bochie 

Certainly collections work but they are limited to 2k items too. You may even want to limit what the users see to the most current results by Sorting by Descending order so they only see the most recent 2k results.  Because of its limitations, as your data grows the work arounds become more cumbersome.  SharePoint has more limitations than Dataverse or SQL and you are already pushing its limits.  I get that SharePoint is free and SQL and Dataverse are premium but in PowerApps as in life, you tend to get what you pay for. 

[Michael_Scott]

Have you looked into using a flow built in Power Automate? This is off the top of my head and has not been tested but this is what I'm thinking might work...
1- Get Items from SharePoint: this will get every item from the list passed into it. This can also be used to sort the data by entering a column name into the Order By field. Along with that you will probably want to test and make sure it is actually grabbing all items in the list. If it is not grabbing them all then you will need to set the Top Count field in this step to 99999 to make sure that all entries are grabbed.
2- now that you have a sorted list with every item from the list, you want to apply the required filters to the data to start making the smaller list that only has the entries you are interested in. To do this you will use Filter Array function and pass it the Value from the get items function right before it. For example I have a ledger app that is divided into paid and unpaid entries. In my flow to print all the bills for each of the 150 people each month, I get all items from my ledger list, order them by the column I want, then use the Filter Array to only keep the entries that have an outstanding balance greater than 0.
3- at this point you should have only the entries you want. If you still need to filter more then you can choose to either make a compound filter statement in step 2 or just add another filter array function call below the first one and pass it in the output from the first one. (it is very important you are filtering the array produced by the first filter array function and not the array output by the get items from SharePoint.) The next step is you create a variable of type 'int' and initialize it. To do this use the Initialize Variable function and set the type to integer (just a whole number like 0, 55, 894 etc) and the Value to a custom expression using the length function call. Will look something like this:
length(body('Filter_Array_Ouput_Here'))
this will output the number of items in the filtered array.

As a final rundown/example ill outline it with values.

List of Ledger Entries as entered in SharePoint in the order they were entered and not sorted yet.
1 Bob, Lawn Mowing, owes 0/25
2 Bob, Rent Payment, owes 250/500
3 Ruth, Lawn Mowing, owes 25/25
4 Mark, Propane Tank, owes 0/30
5 Mark, Rent Payment, owes 100/500
6 Ruth, Rent Payment, owes 0/500

Output of the Filter Array function used on the get items list to narrow the search (this also sorts by name due to the order by field in the get items function as mentioned above.)
1 Bob, Lawn Mowing, owes 0/25
2 Bob, Rent Payment, owes 250/500
3 Mark, Propane Tank, owes 10/30
4 Mark, Rent Payment, owes 100/500
5 Ruth, Lawn Mowing, owes 25/25
6 Ruth, Rent Payment, owes 0/500

The next step removes all the entries that are not for whoever we are searching for. For this example I will say that we only want entries with Name = "Mark" and owes > 0. so only the entries for Mark that also have an outstanding balance will be kept. Now the list is:
1 Mark, Propane Tank, owes 10/30
2 Mark, Rent Payment, owes 100/500

The last step would be to use the length function and save it to a variable. For example with a variable named 'NumEntries' we set its value to length(Body(above_steps_output)) and the variable NumEntries will now be set to the value of 2. 

I hope this was helpful at all. I am new to the forums so if I am bad at explaining I promise I will get better and I will always try to answer any follow-up questions you have as soon as I can get back to them. Good luck m8 hope this helps!

Get Items from SharePoint and sort if you want to/need to sort them

Filter the output from get items into a smaller list with only the entries you need. May take a compound filter or multiple filters to get only what you need.

Count the number of items in the filtered array and save that value before moving on to the next thing to count.