Collect All SharePoint List Items Using ForAll Loop

[hnguy71]

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!

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

[opticshrew2]

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]

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

[RandyHayes]

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

[hnguy71]

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:

Any ideas?

[RandyHayes]

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

[hnguy71 · Accepted solution]

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

[Rens_]

Hi hnguy71

Thanks for your solution. 

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

[Anonymous]

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]

@Anonymous,

Step 1: Trigger=When an item is created

Step 2: Update Item SharePoint where

ID = ID,

NumberField = ID