cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NamegomJan
Helper II
Helper II

Sum function does not work properly

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?

13 REPLIES 13
R3dKap
Super User
Super User

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?

NamegomJan
Helper II
Helper II

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
Super User
Super User

@NamegomJan 

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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
R3dKap
Super User
Super User

@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?

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,909)