Bulk update of Dataverse table records

(0) Share

Report

Posted on by Gary_Eden

375

There are a few threads on this but none quite fit what I'm trying to do.

First off this needs to be carried out from within PowerApps as it needs to be a simple end-user one-click operation.

Problem.

We have a Dataverse table with 150k+ rows of data.

Periodically some records need to be updated, this can be anything from a few hundred to a few thousand rows at a time.

The problem is that the records to be updated can be anywhere within the 150k rows.

For data sets up to 2000 this works fine (although I'd prefer to keep the limit at 500 for performance).

ForAll(

RevisionsTable As Temp, //use temp to avoid conflicts of naming

UpdateIf(

DataverseTable,

ID = Temp.ID,

{

FieldToUpdate1: Temp.FieldToUpdate1,

FieldToUpdate2: Temp.FieldToUpdate2

}

)

The revisions table is an Excel file saved in a SharePoint folder which other users add to as items need updating or via bulk upload from supplied spreadsheet from a third party.

Is there a way to poll through all 150K Dataverse table rows and where there is a matching ID in the revisions table update it and then move on to find the next item to update.

I'm thinking that including Search() as part of the ForAll() loop will do the trick and be relatively fast but haven't been able to figure out the correct point to use it.

Thanks in advance.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

AhmedSalih

6,678 Moderator on at

Like (0)

Report

Hello, @Gary_Eden, ForAll and search combined will take a long processing time if you have couple of hundreds records not to mention few thousand. I would recommend that you use collections as patches and the then combine those collections together into one collection that you can simply patch back to the data source.

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Was this reply helpful? Yes No

Gary_Eden 375 on at

Like (1)

Report
Hi @AhmedSalih
Thanks for the suggestion on using collections, it worked up to a point yesterday. Although bizarrely reopening the app this morning to carry on experimenting what worked yesterday doesn't work today.

More importantly, I'm unable to get beyond working with 2,000 rows.

Is there away to batch process?
Basically, is it possible to:
retrieve the first n rows
check and update where needed
record the result back to the updates table (if updated)
retrieve the next n rows
repeat above until all rows checked

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

Hello, @Gary_Eden, I would recommend that you create an Auto-Number Column and use reload the data to the Dataverse table to have the index column with values from 1 to whatever last records. Then you will use this column to collect data into patches of collections.

Was this reply helpful? Yes No
Gary_Eden 375 on at

Like (1)

Report

Hi @AhmedSalih,
I've found a method using a while-loop as explained here : https://www.dynamicpeople.nl/en/news/while-loop-powerapps/

It's nowhere near as fast as the native loops within PHP but as it checks one row at a time it ignores any delegation limits. I've set the code to also write the result back to the source Excel file which makes life easier when wanting to check if a row was added/updated/excluded. We can now see if a row was skipped over if a match wasn't found.

I've also used the same method to import new records. It's not fast, 17 mins to import 200 rows to Dataverse (including writing the result back to Excel). However, the Dataverse import wizard isn't working at all for me at the moment... so slow is better than none.

Now I have the code working I need to clean it up and refine it. I'll post the final version here when done.

Was this reply helpful? Yes No
Verified answer

Gary_Eden 375 on at

Like (1)

Report

Hi @AhmedSalih
The looping method works but to include multiple expressions I needed to amend the OnTimerEnd code slightly by wrapping the expressions to run inside an IF statement as follows.
//original code
//insert expressions to run
Collect(
colTimer,
{item: "I am item: " & varRuns + 1}
);
UpdateContext({varRuns: varRuns + 1});
If(
varRuns >= varLoopEnd,
UpdateContext({varStartTimer: false})
)

//revised code
If(
//run code if runs less than or equal to required runs
varRuns < varLoopEnd,
//insert expressions
Collect(
colTimer,
{item: "I am item: " & varRuns + 1}
);
//end of expressions
//update counter
UpdateContext({varRuns: varRuns + 1})
);
If(// stop timer if required runs completed
varRuns >= varLoopEnd,
UpdateContext({varStartTimer: false})
)

The slight tweak allows multiple expressions to run inside the loop. If you don't do this it will continue to run (probably indefinitely) if you have a run value greater than 1.

I've included a write back to the Excel update source file to make a note if the item wasn't found. If it is the item is deleted from the update list. This gradually reduces the number of records and over comes the 500 row delegation issue - unless there are more than 500 not founds.

The bit that slows the process down is the write back/delete to the Excel table but so far it seems reliable which at this point is more important than speed and importantly it's simple for a non-techie to use.

Was this reply helpful? Yes No