Sum function does not work properly

[Anonymous]

Hi everyone,

I have the following code, which worked properly until April. After I came back from my vacation it does not work anymore, and I am wondering why.

I have a sharepoint list (SendPoints), and am collecting the data via collmypoints. Then I am trying to Sum all the points a person received in the recent month and write it in the variable ( varMyRecPointsThisMonth). However the variable is empty even though it should output a specific value.

Clear(collmypoints);;
Collect(collmypoints;SendPoints);;
Set(varMyRecPointsThisMonth;Sum(Filter(collmypoints;Title=varLoginUserEmail && Month(SendDate)=Month(Now()));Points));;

btw. I am using German syntax.

Can anyone help?

[R3dKap]

Well technically speaking, a local collection inside an application won't hold more than 500 (well, according to your app settings with a max of 2000) records with a single Filter() or Search() or ClearCollect() operation.

With Power Apps you can make applications that address datasources with millions of records, but WITHIN the application you have to work with less than 2000 records. So your app must be built so that the user can restrict the data to be manipulated.

For instance, if you have an application that manages timesheets for 10 000 people and store weekly reports for each one of them, you will end up with a datasource with 10 000 x 52 = 520 000 records for each year.

Now when a user or a manager will want to work on some of the timehseets data, the application should force the user to select a small period of time to retreive the minimum amount of records.

About your case in particular, can you give us some insights on the volumes you will have in your datasource?

And the app? How much data is it supposed to manage? Have you provided the user with filters in the app to reduce the records to be retreived?

[Anonymous]

Thanks, that was actually the problem. Changing it to 2000 solved the problem for now. However in some months the records will exceed 2000. 

The user himself cannot filter. Actually this is the Status of the points that a user received for the current month. So the function is collecting the data from the datasource (Sharepoint list) and filters the data for the current month. But the sharepoint list itsself exceeds 500 records right now and it is limitless growing. 

Can we already filter when fetching the data from the datasource? SendPoints is the sharepoint list. It would be enough if we can filter somehow like: Only collect the data to collmypoints from SendPoints when SendDate = CurrentMonth.

This is the current code:

Collect(collmypoints;SendPoints);;

[RandyHayes]

@Anonymous 

Actually your app can work with as many records as you have memory for.  

However, it is not practical to have that many records in your app.  The key is to delegate your filtering to the datasource so that you are working with less records ... and all the records needed.

Your problem is that you cannot delegate the Month function.  So, in order for PowerApps to evaluate your formulas, it must pull down all of the records that it can.  This is incredibly wasteful for performance and memory.  My guess is that your individual person that you need to sum records for a particular month will have less than 2000 records to sum.  So...why pull all the records in the datasource just to filter it down to maybe 20 or 30 records (or whatever, but still less than the record limit - transfer record limit - there is no record limit inside of PowerApps)?

So yes, you will eventually exceed the 2000 transfer limit and what you really need to do is look at your data and how to make your criteria delegable.  The missing piece is that you do not have a Month column in your datasource.  This would solve your problem.  If you either write the Month (numeric) in the datasource when you store your records, or employ a PowerAutomate flow to do it for you, then you will have a column in your datasource that is delegable.

This would then be your formula that is completely delegable and would give you the Sum you are looking for.

With({_mnth: Month(Today())};
    With({_items: Filter(yourDataSource; 
                            Title=varLoginUserEmail && 
                            yourMonthColumn = _mnth
                         )
         };
         Sum(_items;Points)
    )
)

I hope this is helpful for you. 

[R3dKap]

@RandyHayes is right: your app can work with as many records as you want... but I'd add : "...as far as you address the datasource directly and make sure your calls are delegable".

Meaning, the following code will do the job only for the first 2000 records of your SendPoints datasource because your condition (the Month() function) cannot be delegated to SharePoint (delegation limits for SharePoint are available here: https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope...😞

UpdateIf(SendPoints, Month(SendDate)=Month(Now), {Status: "Open"})

On the other hand, the following code will do the job for all corresponding records (even if there are 200 000 records concerned) because the condition is delegable to SharePoint (we suppose that the Status field is a text field here):

UpdateIf(SendPoints, Status="Open", {Status: "Closed"})

But what you need to understand is, whatever happens, for performance reasons, none of the following code will retreive more than 2000 records (even the second one which has a delegable condition in the Filter() function):

ClearCollect(colMyPoints, SendPoints)
ClearCollect(colMyPoints, Filter(SendPoints, Status="Open"))

So what you need to do is ask yourself what could be a good way of reducing the number of rows to be retreived at some point from your SendPoints list to be used by your application?

For instance, if the SendPoints records you need to retreive are only the ones for the current user, that's a good start. Then maybe, only records for the current month could be retreived. And that may be enough to make sure that you will always have less than 2000 records to manage. And you could provide a left arrow and right arrow icons to allow the user to navigate from one month to another, using a ClearCollect() to reload the correct records.

Now, about selecting the records corresponding to a dedicated month, you should follow @RandyHayes's suggestion of adding a Month column in your datasource. Now if your datasource is already filled with tons of data, you'll have to populate that new Month column with the month from your SendDate column by creating a small script using PnP PowerShell. You could also do it with a single UpdateIf() call in Power Apps but if you have tens of thousands of records, it will take hours (same for a flow with Power Automate). PnP PowerShell will be the fastest. Are you familiar with PnP PowerShell?