Problems with ForAll trying to overcome the 500 row limit

[Meneghino]

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)

[v-monli-msft]

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

[Meneghino]

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.

[v-monli-msft]

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

[Meneghino]

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

This should be done as soon as practical.

[hpkeong]

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

[mr-dang]

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