Answered

ForAll with large data using Sharepoint list x PowerApps

(0) Share

Report

Posted on by EdevaldoJeronim

I have 02 sharepoint list and i need to copy all record from table01 to table02. The table01 have 66000 records and when i try to user the code below, only the first 2000 records are copied to table02.

Please, see my code below and help me how can I solve this problem, because i have more table to migrate.

---------------------------------------------------------------------------------------------------------------------------------

Refresh(Table01);;
ForAll(Table01;
If(IsBlank(
LookUp(Table02;IdMaterial = Table01[@IdMaterial]));
Patch(Table02;
Defaults(Table02);
{
IdMaterial:Table01[@IdMaterial];
DescMaterial:Table01[@DescMaterial]
}
);
Patch(Table02;LookUp(Table02;IdMaterial = Table01[@IdMaterial]);
{
DescMaterial:Table01[@DescMaterial]
}
)
)
)

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @EdevaldoJeronim

Quoting from the documentation of ForAll() function: "When used with a data source, this function can't be delegated. Only the first portion of the data source will be retrieved and then the function applied. The result may not represent the complete story. A warning may appear at authoring time to remind you of this limitation and to suggest switching to delegable alternatives where possible. For more information, see the delegation overview."

You are running into one of the common problems with SharePoint, that being that many functions are not delegatable. I suggest that you review @WarrenBelz 's blog to see if SharePoint is the right back end for your data. https://www.practicalpowerapps.com/

You might be better off downloading your list to Excel and then reimporting it back into SharePoint in a new list. Although I haven't tried this, there may be a way to copy your list as a template in SharePoint. https://sharepointmaven.com/how-to-create-a-custom-list-in-sharepoint-by-copying-an-existing-custom-list/
Verified answer

EdevaldoJeronim 99 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp ,
thank you to the answer, I solved this problem using the link https://poszytek.eu/en/microsoft-en/office-365-en/powerapps-en/overcome-2000-items-limit-using-power-apps-collect-function/
but the solution is very very very slow to add or update 660000 records, I think should be another solution
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@EdevaldoJeronim

There is a different approach that you can take if you are using your list for reference only and not adding new items or modifying existing ones. You can use the import from Excel static connector to import tables up to 15000 items from Excel. If you carve up your data to several tables, you can import them all and then use a collection to combine them back. I used this approach in my blog post where I imported 45,000 US zip codes https://powerusers.microsoft.com/t5/News-Announcements/Automatically-Prefill-City-and-State-using-Zip-Codes-in-your-App/ba-p/200465

The advantage is it takes literally a minute to import the tables and combine them. Another advantage is that all PowerApps functions are delegatable to the final collection and they execute blindingly fast. Again, the disadvantage is the tables are not editable. So, in very specific cases, it is an extremely useful solution.
EdevaldoJeronim 99 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp ,
to recover the records (66000) into my collection using the method above was very fast, normal process.
The problem is i need to get these records (66000) in my collection, check if the item exist in the list in Sharepoint and if need add the new item. This process take me around 10 hours and is impossible to work this way.
I don't know what i'm doing wrong.

See below the code:

Refresh(TblMaterial);;
Refresh(TblMaterial_Excel);;
Set(firstRecord;First(TblMaterial_Excel));;
Set(lastRecord;First(Sort(TblMaterial_Excel;ID;Descending)));;
Set(varRowsCount;lastRecord.ID - firstRecord.ID);;
Set(iterationsNo;RoundUp((lastRecord.ID - firstRecord.ID) / 2000;0));;
Collect(iterations;Sequence(iterationsNo;0));;
ClearCollect(MyTblTemp;Filter(TblMaterial_Excel;Id_Calculado < 1));;
ForAll(
iterations;
With(
{
prevThreshold: Value(Value) * 2000;
nextThreshold: (Value(Value) + 1) * 2000
};
If(
lastRecord.ID > Value;
Collect(
MyTblTemp;
Filter(
TblMaterial_Excel;
Id_Calculado > prevThreshold && Id_Calculado <= nextThreshold
)
)
)
)
);;
ForAll(MyTblTemp;
If(IsBlank(
LookUp(TblMaterial;IdMaterial = MyTblTemp[@IdMaterial]));
Patch(TblMaterial;
Defaults(TblMaterial);
{
IdMaterial:MyTblTemp[@IdMaterial];
DescMaterial:MyTblTemp[@DescMaterial]
}
);
Patch(TblMaterial;LookUp(TblMaterial;IdMaterial = TempTblMaterial[@IdMaterial]);
{
DescMaterial:TempTblMaterial[@DescMaterial]
}
)
)
);;
ClearCollect(MyTblTemp;Filter(TblMaterial_Excel;Id_Calculado < 1));;
Navigate(TelaExito;ScreenTransition.Cover)