Delegation limit with Filter SharePoint list

[Gidrone_NS]

I've already gone through the numerous threads on this issue and I was trying almost everything without success.

I have SharePoint list "Adresar" with 5.360 items. Powerapps delegation limit 2000. I try batch in collection with filter function Button OnSelect:

ClearCollect(colTotal, Filter('Adresar', ID >= 1 && ID < 2000));
Collect(colTotal, Filter(Adresar, ID >= 2000 && ID < 4000));

1. Why i have delgation warnings when I use Filter on SharePoint? PA put blue wave line under ID and >=

2. CountRows(colTotal) is 2.000!!! When I change delegation limit to 500, i get CountRows = 500

In all threads I find that Collection are allmost unlimited, but in my PA I have limitation on collection too!!!

My PA version is 3.19053.21 and I am afraid there is or Bug or some limitation with Plan (PA for Office365).

[cwebb365 · Accepted solution]

The SharePoint ID field for a table is a number field in PowerApps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field.

[cwebb365]

So what you could do and I think works, or should in theroy :), is go to the SharePoint list and create a calculated column and use [ID] as the value, and set the return value as a number. Then this will put the ID in that column, then you technically should be able to then key off that column. 

[hnguy71 · Accepted solution]

It won't work. I tried that a while back. RandyHayes explains as to why it wouldn't work and in the thread I came up with a solution:

https://powerusers.microsoft.com/t5/General-Discussion/Collect-All-SharePoint-List-Items-Using-ForAl...

[cwebb365]

well **bleep**, that was going to be my backup suggestion to use flow, or SPD workflow to populate a real number column, then you could even index it as well. 

[hnguy71]

Yeah. I started using SP Designer for all my potential big lists now for two reasons:

1- A simple index workflow that way I don't cut into my monthly MS Flow count

2- I can build and forget it. I don't have to fix connections if my password expires.

[cwebb365]

I'll one up you. I just quit using SharePoint and use Azure SQL backend for my apps ;). So many less delegation issues using Azure SQL DB. 

[Gidrone_NS]

Thank you all... 

Crucial information for me is that SharePoint ID only support equal '='!!! as @cwebb365 write in his post.

Finally, my solution is:

1. Add column to my list - TempID (not calculated field because don't work)
2. With PowerShell I copied ID to TempId (almost 12 hours to finishe coping 33K items)
3. Change my app to support managing TempID (only for new items app need to patch TempID)
4. Use this code for OnStart app (combine @hnguy71  and of course Mr Dang)

ClearCollect(HundredChart,[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100]);

Set(g_recFirstRecord,First(Categorisations));
Set(g_recLastRecord,First(Sort(Categorisations,ID,Descending)));
Set(g_numMaxIter,RoundUp((g_recLastRecord.ID-g_recFirstRecord.ID)/2000,0));

ClearCollect(colIter,
AddColumns(AddColumns(Filter(HundredChart,Value<=g_numMaxIter),"min", 
(Value-1)*2000+g_recFirstRecord.TempID),"max",Value*2000+g_recFirstRecord.TempID));

Clear(colCategorisation);
ForAll(colIter,Collect(colCategorisation,Filter(Categorisations,TempID>=min&&TempID<max)))

This solution WORKS form me. Actually I have list with 33.659 SharePoint Items with 3 columns + systems columns and solutions collect all items for 30-40 seconds!!! In my case this time is acceptable.

If anyone need Powershell code I will share it, just ask.

Thank you again.