cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hnguy71
Level 8

Collect All SharePoint List Items Using ForAll Loop

Good morning, All.

 

I'm playing around with the idea to grabbing all sharepoint list items into a collection. I'm taking Mr Dang's approach located here:

 

https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-ne...

 

The only difference between his and mine is that I have a table with a generated series vs his static table.  I believe everything works up until the last line of code which gives me the following error:
"Delegation warning. The lighlighted part of this formula might not work correctly with column "ID" on large data sets."

I seem to be able to grab up to 2000 records but my test list has 3004 items. Any help would be appreciated!

 

issue_1.png

 

Here's a debug test that I'm performing to see if I'm retrieving the necessary information (I'm expecting 3004 inside collection amount):

issue_2.png

1 ACCEPTED SOLUTION

Accepted Solutions
hnguy71
Level 8

Re: Collect All SharePoint List Items Using ForAll Loop

Solution with comments for anyone searching:

Setting up the stage: Using Microsoft Flow or SharePoint Designer, create a workflow to duplicate the default ID field to another NUMERIC field. Cannot be a calculated column (yet)

 

Within a screen's OnVisible property:

/*
// --- PROCESS START ---

PRE-REQUISITE:
1. Set Data row limit for non-delegable queries to 2000
File > App Settings > Advanced Settings > Value: 2000

2. If using SharePoint, index your number column to avoid list view limitation. */ // Just In Case: Unload staging collections Clear(colDummy); Clear(colIterations); Clear(colNumbersTable); Clear(col_Sample); /* Begin Generate Series This can retrieve ~ 2 million records Increase the count if you need additional records to be retrieved (hopefully not) */ ClearCollect(colNumTemp,[1,2,3,4,5,6,7,8,9,10]); ForAll(colNumTemp, ForAll(colNumTemp, ForAll(colNumTemp, Collect(colDummy, { dummy:1 }); Collect(colNumbersTable, { Number:CountRows(colDummy) }) ) ) ); // End Generate Series // Obtain first record, last record UpdateContext({FirstRecord:First(Sort(__DATASOURCE__, ID, Ascending))}); UpdateContext({LastRecord:First(Sort(__DATASOURCE__, ID, Descending))}); // Begin iteration capture UpdateContext({Iterations:RoundUp((LastRecord.ID-FirstRecord.ID)/2000,0)}); // Create a new collection to temporarily store our first ID to capture ClearCollect(varCounter, { min_Num: FirstRecord.ID }); /* Using a ForAll loop where column Number is less than or equal to the number of iterations Update iteration collection with current iteration number, min, and max numbers Update counter collection */ ForAll( Filter(colNumbersTable, Number <= Iterations), Collect(colIterations, { Number: Last(FirstN(colNumbersTable, CountRows(colIterations) + 1)).Number, min_Num: First(varCounter).min_Num, max_Num: First(varCounter).min_Num + 1999 }); Patch(varCounter, First(varCounter), { min_Num: Last(FirstN(colIterations, CountRows(colIterations) + 1)).max_Num + 1 }) ); // End iteration capture /* Finally, with a ForAll loop let's collect all our items ** Cannot use ID column to do delegations! ** ** The ID here must be a Number Field ** Change numID to your number field. */ ForAll(colIterations, Collect(col_Sample, Filter(__DATASOURCE__, numID >= min_Num && numID <= max_Num) ) ); // Unload staging collections Clear(colDummy); Clear(colIterations); Clear(colNumbersTable); Clear(varCounter); Clear(colNumTemp); // --- PROCESS END ---

 

View solution in original post

26 REPLIES 26
opticshrew2
Level: Powered On

Re: Collect All SharePoint List Items Using ForAll Loop

Hi @hnguy71 

 

I'm not going to lie, I did get a bit lost in the below.

 

I think your issue is here, you used "AND" instead of "&&":

ForAll(
    iter,
    Collect(
        datasource_temp,
        Filter(
            PowerApps_Integration_1,
            ID >= FirstRecord.ID + min && Number < FirstRecord.ID + max
        )
    )
)

Additionally PowerApps usually only returns 2000 records. This can be changed in the app settings but last time I check the upper limit was 2000.

 

Hope that helps,

J

hnguy71
Level 8

Re: Collect All SharePoint List Items Using ForAll Loop

hi @opticshrew2 ,

 

I did try both "AND" and "&&" and I received the same issue. The idea is exactly that. Since we know the limit is 2000 we need to do a loop to continue to return all of our items.

 

So to break down the logic:

 

Clear(datasource_temp);
ForAll(
    iter,
    Collect(
        datasource_temp,
        Filter(
            PowerApps_Integration_1,
            ID >= FirstRecord.ID + min && ID < FirstRecord.ID + max
        )
    )
)

1. First, we clear the collection: datasource_temp

2. We then do a FOR loop in which we use a collection to return the iterations that we will loop. We figure out the amount of iterations by creating a variable to get LastRecord.ID - FirstRecord.ID and divide that by 2000. So in this example, (3004-1)/2000 = 1.5 which we round up to 2.

3. We'll filter a collection where column Number is equal to or less than 2

4. So for each loop (2), we want to grab all the record IDs that is between MIN and MAX. It will continue to loop until it grabs all the content from the list into our newly created collection.

 

The image below shows the collection colNumbersTable where our MIN is 0 and our MAX is 4000. We'll want to return all items within that ID range with 2 iterations

issue_3.png

Super User
Super User

Re: Collect All SharePoint List Items Using ForAll Loop

@hnguy71 

The issue with your logic is that ID is not delegable for anything other than the equal operator.  So, the greater than and less than will not delegate on an ID.  

One possible solution to this is to use Flow or another mechanism to duplicate the ID number into a seperate number column.  You can delegate your greater than and less than on number columns. 

One would think that the ID column is a number and thus can use delegable number operators, but unfortunately again, only the equal operator will work on the ID with delegation.

 

I hope that gives you some more information to help.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Collect All SharePoint List Items Using ForAll Loop

Hi @RandyHayes 

 

I'm currently trying to use a calculated column with a number format but it doesn't seem to work. It HAS to be a number column using Flow / Designer? I'm about to try using Designer right now and come back with my findings.

 

EDIT:

So I'm using Designer to add the numeric value to a number column successfully but in my test I'm now having a different error with my code:

 

//Begin Generate Series [Working]
ClearCollect(colNumTemp,[1,2,3,4,5,6,7,8,9,10,11]);Clear(colNumbersTable);Clear(colDummy);ForAll(colNumTemp,ForAll(colNumTemp,ForAll(colNumTemp,Collect(colDummy,{dummy:1});Collect(colNumbersTable,{Number:CountRows(colDummy)}))));Clear(colDummy);Clear(colNumTemp);
//End Generate Series
UpdateContext({FirstRecord:First(PowerApps_PBI)});
// [Working]
UpdateContext({LastRecord:First(Sort(PowerApps_PBI,ID,Descending))});
// [Working]
UpdateContext({Iterations:RoundUp((LastRecord.ID-FirstRecord.ID)/2000,0)});
// [Working]
ClearCollect(iter,AddColumns(AddColumns(Filter(colNumbersTable,Number<=Iterations),"min_Num",(Number-1)*2000),"max_Num",Number*2000));
// [Working]
Clear(datasource_temp);ForAll(iter,Collect(datasource_temp,Filter(PowerApps_PBI, calc_ID >= FirstRecord.ID + min_Num && calc_ID <= max_Num)))

At the bottom line again this is the error:

error_1.png

error_2.png

 

Any ideas?

Super User
Super User

Re: Collect All SharePoint List Items Using ForAll Loop

@hnguy71 

Seems like an easy fix...add a calulated column on the ID.  But, I'm sorry to say, calculated fields are not delegable.  They need to be real actual number fields.

 

So again, the potential solution is to throw together a Flow to populate the new Number column with the ID on all the records in the list.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Collect All SharePoint List Items Using ForAll Loop

Solution with comments for anyone searching:

Setting up the stage: Using Microsoft Flow or SharePoint Designer, create a workflow to duplicate the default ID field to another NUMERIC field. Cannot be a calculated column (yet)

 

Within a screen's OnVisible property:

/*
// --- PROCESS START ---

PRE-REQUISITE:
1. Set Data row limit for non-delegable queries to 2000
File > App Settings > Advanced Settings > Value: 2000

2. If using SharePoint, index your number column to avoid list view limitation. */ // Just In Case: Unload staging collections Clear(colDummy); Clear(colIterations); Clear(colNumbersTable); Clear(col_Sample); /* Begin Generate Series This can retrieve ~ 2 million records Increase the count if you need additional records to be retrieved (hopefully not) */ ClearCollect(colNumTemp,[1,2,3,4,5,6,7,8,9,10]); ForAll(colNumTemp, ForAll(colNumTemp, ForAll(colNumTemp, Collect(colDummy, { dummy:1 }); Collect(colNumbersTable, { Number:CountRows(colDummy) }) ) ) ); // End Generate Series // Obtain first record, last record UpdateContext({FirstRecord:First(Sort(__DATASOURCE__, ID, Ascending))}); UpdateContext({LastRecord:First(Sort(__DATASOURCE__, ID, Descending))}); // Begin iteration capture UpdateContext({Iterations:RoundUp((LastRecord.ID-FirstRecord.ID)/2000,0)}); // Create a new collection to temporarily store our first ID to capture ClearCollect(varCounter, { min_Num: FirstRecord.ID }); /* Using a ForAll loop where column Number is less than or equal to the number of iterations Update iteration collection with current iteration number, min, and max numbers Update counter collection */ ForAll( Filter(colNumbersTable, Number <= Iterations), Collect(colIterations, { Number: Last(FirstN(colNumbersTable, CountRows(colIterations) + 1)).Number, min_Num: First(varCounter).min_Num, max_Num: First(varCounter).min_Num + 1999 }); Patch(varCounter, First(varCounter), { min_Num: Last(FirstN(colIterations, CountRows(colIterations) + 1)).max_Num + 1 }) ); // End iteration capture /* Finally, with a ForAll loop let's collect all our items ** Cannot use ID column to do delegations! ** ** The ID here must be a Number Field ** Change numID to your number field. */ ForAll(colIterations, Collect(col_Sample, Filter(__DATASOURCE__, numID >= min_Num && numID <= max_Num) ) ); // Unload staging collections Clear(colDummy); Clear(colIterations); Clear(colNumbersTable); Clear(varCounter); Clear(colNumTemp); // --- PROCESS END ---

 

View solution in original post

Rens_
Level: Powered On

Re: Collect All SharePoint List Items Using ForAll Loop

Hi hnguy71

Thanks for your solution. 

For SharePoint this seems to be working, but it isn't for Excel.

 

 

Anonymous
Not applicable

Re: Collect All SharePoint List Items Using ForAll Loop

Hi, how do you copy the ID field to the Number field in sharepoint using MSFLOW? I can't seem to figure it out.

hnguy71
Level 8

Re: Collect All SharePoint List Items Using ForAll Loop

@Anonymous,

 

Step 1: Trigger=When an item is created

Step 2: Update Item SharePoint where

 

ID = ID,

NumberField = ID

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 398 members 5,859 guests
Please welcome our newest community members: