Collecting large lists in PowerApps from SharePoint
Collecting large lists in PowerApps from SharePoint
// — Process Start —
// — Clear all collections —
Clear(colDummy);
Clear(colIterations);
Clear(colNumbersTable);
Clear(cCollection); // — Collection where store all Sharepoint records
/*****************************************************************************************
Start generate records series
List up to 2 million records, if necessary increases the value of the records in the vectors
*******************************************************************************************************/
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)}
)
)
)
);
// — Series end
// — Get the first and last record of the list
UpdateContext(
{
FirstRecord: First(
Sort(
sharepointList, //— Sharepoint list to go find the records
ID,
Ascending
)
)
}
);
UpdateContext(
{
LastRecord: First(
Sort(
sharepointList, //— Sharepoint list to go find the records
ID,
Descending
)
)
}
);
// —The capture of records begins
UpdateContext(
{
Iterations: RoundUp(
(LastRecord.ID – FirstRecord.ID) / 2000,
0
)
}
);
// —Create a new collection to register the first ID found
ClearCollect(
varCounter,
{min_Num: FirstRecord.ID}
);
/*****************************************************************************************
A ForAll loop is used where the column number is less than or equal to the number of iterations
Updates the iteration collection with the current iteration number, minimum and maximum numbers
update the collection of counters
********************************************************************************************************/
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 of capture of records
/*************************************************************************************
Finally, with a ForAll loop we will collect all our items
** !! Unable to use the ID column to make delegations !! **
** The ID here must be a numeric field (numID)**
****************************************************************************************************/
ForAll(
colIterations,
Collect(
sharepointList, //— Sharepoint list to go find the records
Filter(
sharepointList, //— Sharepoint list to go find the records
numID >= min_Num && numID <= max_Num
)
)
);
// Downloads collections
// — END —
