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  Go Head With Another Article 

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)
	)
)