Blog

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

Here are the best web links to help overcome the 500/2000 row limit.

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:


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:


Previous
Next
Related posts