cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gidrone_NS
Level: Powered On

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
Level 8

Re: Delegation limit with Filter SharePoint list

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.

hnguy71
Level 8

Re: Delegation limit with Filter SharePoint list

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

7 REPLIES 7
cwebb365
Level 8

Re: Delegation limit with Filter SharePoint list

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
Level 8

Re: Delegation limit with Filter SharePoint list

So what you could do and I think works, or should in theroy Smiley Happy, 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

hnguy71
Level 8

Re: Delegation limit with Filter SharePoint list

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
Level 8

Re: Delegation limit with Filter SharePoint list

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
Level 8

Re: Delegation limit with Filter SharePoint list

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
Level 8

Re: Delegation limit with Filter SharePoint list

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

Highlighted
Gidrone_NS
Level: Powered On

Re: Delegation limit with Filter SharePoint list

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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 200 members 3,736 guests
Please welcome our newest community members: