cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Impactful Individual
Impactful Individual

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.

View solution in original post

Highlighted
Helper V
Helper V

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

View solution in original post

7 REPLIES 7
Highlighted
Impactful Individual
Impactful Individual

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.

View solution in original post

Highlighted
Impactful Individual
Impactful Individual

Re: Delegation limit with Filter SharePoint list

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

Highlighted
Helper V
Helper V

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

View solution in original post

Highlighted
Impactful Individual
Impactful Individual

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. 

Highlighted
Helper V
Helper V

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.

Highlighted
Impactful Individual
Impactful Individual

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 ;). So many less delegation issues using Azure SQL DB. 

Highlighted
Advocate II
Advocate II

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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,361)