cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gidrone_NS
Advocate II
Advocate II

Delegation limit with Filter SharePoint list

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
cwebb365
Solution Sage
Solution Sage

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.

View solution in original post

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

View solution in original post

7 REPLIES 7
cwebb365
Solution Sage
Solution Sage

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.

View solution in original post

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. 

 

tmpid.jpg

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

View solution in original post

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. 

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.

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
Advocate II
Advocate II

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,143)