Delegation - How to overcome the 500/2000 Row Limit with Collections

 At some stage, most of us will encounter the need to overcome the row limits for non-delegable queries. This is necessary to provide data aggregation for reports and charts, allow better searching of data with more specific search criteria, and many other reasons.

The best reference guide for this is Mr Dang's post here:

https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-need-to-switch-to-asp/m-p/22980#M9872

In this excellent post, Mr Dang describes how to use a ForAll loop to collect records into a local collection in batches of 500. You can find a full description of how to implement this technique in Mr Dang's post, but here's a brief extract. Well done Mr Dang!

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource,RecordId,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.RecordId-firstrecord.RecordId)/500,0)});

ClearCollect(iter,
	AddColumns(AddColumns(Filter(HundredChart,Number<=maxiter),"min",(Number-1)*500),"max",Number*500)
);

Clear(datasource_temp);
ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,RecordId>=firstrecord.RecordId+min && RecordId<firstrecord.RecordId+max)
	)
)

You can find other useful details of this topic in the posts here:

https://powerusers.microsoft.com/t5/General-Discussion/Overcoming-the-500-record-limit-locally/td-p/18379

https://powerusers.microsoft.com/t5/General-Discussion/Solution-Overcome-Delegation-for-Aggregate-Functions/m-p/71966

                                                 Or Another Article of Delegation  

This solution makes it possible for you to avoid making a column that calculates which block of 500 a record belongs to (I previously used a column called n). I made my formulas based on a CDS entity and performed calculations on the default RecordId field which is a Big Integer. I previous was using PrimaryId, since it was small numbers starting at 1, but the problem is that it is handled as text. RecordId is a value at least.

 

 

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource,RecordId,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.RecordId-firstrecord.RecordId)/500,0)});

ClearCollect(iter,
	AddColumns(AddColumns(Filter(HundredChart,Number<=maxiter),"min",(Number-1)*500),"max",Number*500)
);

Clear(datasource_temp);
ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,RecordId>=firstrecord.RecordId+min && RecordId<firstrecord.RecordId+max)
	)
)

 

There are three parts:

 

1. Determine the first record (firstrecord), the last record (lastrecord). Subtracting their RecordId value and dividing it by 500 determines how many times you would need to perform iterations (maxiter). The firstrecord's RecordId will be used as a reference for pulling in records later.

 

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource,RecordId,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.RecordId-firstrecord.RecordId)/500,0)});

 

2. Make a static table of whole numbers [1, 2, 3, ... 100 or whatever you want]. Filter it to use as the argument in ForAll later. It will give instructions to ForAll on how many times to "loop." So if in step 1, you determined that your number of iterations (maxiter) was 3, then the formula would Filter all whole numbers less than and equal to 3.

 

Note: my formula below is messy. I am using an existing table I have that only has a column with whole numbers. I had to add columns for the minimum 500 and upper 500 using AddColumns(). You may opt to make those columns in your table so it does not need to be calculated every time. I figure it's a small calculation so it's not a big deal.

 

ClearCollect(iter,
	AddColumns(AddColumns(Filter(HundredChart,Number<=maxiter),"min",(Number-1)*500),"max",Number*500)
);

 

3. The last part is where the formula pulls in records. First it clears the temporary collection (datasource_temp) that is used for holding the records. ForAll will pull in 500 records at a time for each whole number you Filtered in step 2. So if you have 3 whole numbers in the iter Collection (maxiter=3), then ForAll will pull in:

• all records with RecordId>=firstrecord.RecordId+0 and RecordId<firstrecord.RecordId+500
• all records with RecordId>=firstrecord.RecordId+500 and RecordId<firstrecord.RecordId+1000
• all records with RecordId>=firstrecord.RecordId+1000 and RecordId<firstrecord.RecordId+1500
• then it will stop because there are no other whole numbers in the "iter" Collection.

 

Clear(datasource_temp);
ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,RecordId>=firstrecord.RecordId+min && RecordId<firstrecord.RecordId+max)
	)
)

 

                    Or Another Article of Delegation  

Now that I've had some time to play with ForAll, I have a more elegant solution for pulling in 500 records at a time for reading. I still do not have a good solution for writing though. For this to work, you will still need a column in your Entity which describes which set of 500 it belongs to.

 

Big idea: 

1. Find out what the maximum n value is that describes how many sets of 500 you have. The formula I included also repairs the datasource if the last entry did not correctly have an n recorded.
2. Create a dummy collection that includes whole numbers that are less than or equal to the n value you found in step 1.
3. Use the dummy collection in step 2 as an argument in ForAll--"For each n in the dummy table, collect the 500 records from the datasource which are equal to that n."

 

UpdateIf(datasource,IsBlank(n),
	{n: RoundDown(Value(PrimaryId)/500,0)+1
	}
);

UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))});

ClearCollect(iter,
	Distinct(Filter(HundredChart,Num<=maxn.n),Num)
);

Clear(datasource_temp);

ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,n=Result)
	)
)

This can be done in a Button, Toggle, Timer, or whatever you want to trigger.

 

The only requirement is that you create a Table of whole numbers in a column [1,2,3,4,5, etc.] from which to pull your dummy collection. You can connect it to PowerApps as static data. I just used an existing "Hundred Chart" from a datasource I already connected. I do not know another way of making a collection with such whole number sets.

 

EDIT: Unfortunately, you will need to create an n value in your entity. I tried the following formula to try working around writng a column for n, but it has service limitations:

 

ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,(RoundDown(Value(PrimaryId)/500,0)+1)=Result)
	)
)

 

EDIT2:

Since UpdateIf does not delegate, you will not be able to fix all n that are blank. Instead, when writing a record, write n as 0 instead of blank so you can fix it. The change below can only fix the last record and may miss any others that do not have an n.

UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))});

If(IsBlank(maxn.n) || maxn.n=0, 
Patch(datasource,First(Filter(PrimaryId=maxn.PrimaryId)),
	{n: RoundDown(Value(maxn.PrimaryId)/500,0)+1
	}
)
UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))})
);

ClearCollect(iter,
	Distinct(Filter(HundredChart,Num<=maxn.n),Num)
);

Clear(datasource_temp);

ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,n=Result)
	)
)