cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Community Champion
Community Champion

Problems with ForAll trying to overcome the 500 row limit

I have a table: MyTable, with two columns: ID and MyAmount

The table has more than 500 rows, so Sum(MyTable, MyAmount) does not give the right answer, since it only sums the first 500 records.

I thought I would be able to solve this with the new ForAll function, but it is not working.

 

Here is what I tried:

ClearCollect( LocalAmountSum,
	ForAll( RenameColumns([500, 1000, 1500, 2000], "Value", "MaxID"),
		Sum(
			Filter(MyTable, ID <= MaxID && ID > (MaxID-500) )
			, MyAmount
			)
		)
	)

What happens is that I get 0 values for all rows of the resulting LocalAmountSum table.

 

Can anyone help?

 

PS MyTable is in SQL server (Access Web App)

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Problems with ForAll trying to overcome the 500 row limit

Hi Meneghino,

 

SQL Server shouldn't have the 500 rows limitation, as it supports query delegation. For more information, refer to the below thread:

https://powerusers.microsoft.com/t5/PowerApps-Forum/500-rows-Limit/td-p/8348

 

Mona Li

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Champion
Community Champion

Re: Problems with ForAll trying to overcome the 500 row limit

Hi MonaLi.  I understand that delegation is supported for filtering for example, in that will look over all the rows of the database, but PowerApps will still only return the first 500 rows it finds.

Of particular issue to me is how to sum an amount over a (filtered) sub-set of rows, whereby the sub-set has more than 500 rows.  Or even how to sum an amount over all rows, if the table has more than 500 rows.

This is not resolved by delegation yet, as far as I know.

The issue would also be resolved by access to views in that one could have a view with subtotals by category that would have a total of less than 500 rows.  But views are not yet accessible via PowerApps.

Hence I was trying the work-around with ForAll, but I encountered the issue described in my first post.

Highlighted
Community Support
Community Support

Re: Problems with ForAll trying to overcome the 500 row limit

Hi,

 

I think this limitation will be solved as this thread said:

https://powerusers.microsoft.com/t5/PowerApps-Forum/Maximum-500-rows-Excel-table-in-PA/td-p/6600

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Champion
Community Champion

Re: Problems with ForAll trying to overcome the 500 row limit

Thanks Monli, but the real problem is that the Sum function is not yet delegable.

This should be done as soon as practical.

Highlighted
Community Champion
Community Champion

Re: Problems with ForAll trying to overcome the 500 row limit

Hi

 

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

from Tutorials explains clearly about:

- what can be delegated and what can't and

- condition (blut dot) showing delegation might be affected.

 

While awaiting more improvement, wish my sharing will make us alert of delegation limitation while creating apps.

 

Delegable functions

The next step is to only use formulas that can be delegated. Included here are the formula elements that could be delegated. However, every data source is different, and not all of these are supported by every data source. Check for blue dot suggestions in your particular formula.
These lists will change over time. We are working to support more functions and operators with delegation.
Filter functions
Filter, Search, and LookUp can be delegated.
Within the Filter and LookUp functions, the following can be used with columns of the table to select the appropriate records:
And (including &&), Or (including ||), Not (including !)
In
=, <>, >=, <=, >, <
TrimEnds
IsBlank
Constant values, which do not include context variables or collections
Portions of your formula that evaluate to a constant value for all records can also be used. For example, Left( Language(), 2 ) does not depend on any columns of the record and therefore returns the same value for all records. It is effectively a constant. Use of context variables, collections, and signals may not be constant and therefore will prevent Filter and LookUp from being delegated.
Some notable items missing from the above list:
If
+, -, *, /, Mod
Concatenate (including &)
ExactIn
String manipulation functions: Lower, Upper, Left, Mid, Len, ...
Signals: Location, Acceleration, Compass, ...
Volatiles: Now, Today, Rand, ...
Context variables and collections
Sorting functions
Sort and SortByColumns can be delegated.
In Sort, the formula can only be the name of a single column and can't include other operators or functions.
Other functions
All other functions do not support delegation, including these notable functions:
Table shaping: AddColumns, DropColumns, ShowColumns, ...
Aggregates: Sum, Average, Min, ...
First, FirstN, Last, LastN
CountRows, CountA, Count
Concat
Collect, ClearCollect
CountIf, RemoveIf, UpdateIf
GroupBy, Ungroup

 

hpkeong
Highlighted
Community Champion
Community Champion

Re: Problems with ForAll trying to overcome the 500 row limit

Can we get an update on when the other functions will be delegable: near or distant future?

 

Aggregates: Sum, Average, Min, ...
First, FirstN, Last, LastN
CountRows, CountA, Count
Concat
Collect, ClearCollect
CountIf, RemoveIf, UpdateIf
GroupBy, Ungroup
Microsoft Employee
@8bitclassroom

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,553)