0

We have a SharePoint document library which contain around 3,000 customers subfolders inside the root folder, and total of 2 million files and sub-folders inside the 3,000 customer sub-folders.

Now we added a custom column named "CustomerFilesLastModifed" , which should be equal to the latest date any file inside the related customer sub-folder was modified.. for example let say we have this case:-

  1. Customer sub-folder named "CustomerA"

  2. Inside the "CustomerA" sub-folder we have 2 files:-

  • Expenses.xlsx was modified on 12 Feb 2022

  • Profile.docx was modified on 10 March 2023

  1. then the "CustomerFilesLastModifed" date for "CustomerA" sub-folder should be equal to 10 March 2023 (the latest modified date for the files inside the sub-folder).. and so on for the other customers sub-folders.

so for this to work i will need to create an instant flow which can loop through all the 2 million documents.. so does Power automate support this type of operations? or i need to look into creating an azure function or power shell script?

Thanks

2 Answers 2

3

As @Derek Gusoff mentioned, doing such a large bulk operation is tricky and will likely run into throttling issues, and while I would agree with the comment to push back on the idea of doing it all at once, you can still do something towards reaching the requirement.

You could set up a flow like this:

  • Trigger on item updated
  • If the item is a file:
  • Find the parent folder and set the CustomerFilesLastModified field
  • (It isn't clear from your question if you only need to set the field on the immediate parent subfolder, but you could also have the flow continue to climb up the folder hierarchy, setting the field on each parent folder, until you reach the first level of the original 3000 customer folders, if that is your requirement)

While this wouldn't have all folders immediately populated with the correct data, it would allow for the data to build up over time without any intense resource usage or heavy bulk operations.

This is a step in the right direction, and probably what you want as a long term solution anyway. If you still have a requirement to get the data populated on all folders ASAP, you at least have something working for you making incremental progress while you take the time to figure out how to do the bulk operation.

4
  • 1
    this is definitely better than what the OP had in mind. Jul 7, 2023 at 16:30
  • @Dylan Cristy thanks for the reply, yes i am planning to create an automate flow which runs when a document is created or modified, and this flow will update the related document parent customer folder's CustomerFilesLastModified field. but since we already have 2 million files and folders categorized under 3,000 customer sub-folders, so i need to run this giant script or flow atleast for one time only to get the CustomerFilesLastModified field updated, then it will be the responsibility of the automate flow to update the CustomerFilesLastModified field for the related customer sub-folder.. Jul 7, 2023 at 17:39
  • @Dylan Cristy so for this one-time script is it better to develop it using powershell or azure function rather than using power automate. keeping in mind that i might run this one-time script once per month to make sure the CustomerFilesLastModified field is updated successfully incase the automate flow failed for a reason or another .. what do you think? Thanks Jul 7, 2023 at 17:41
  • If you absolutely have to do the bulk job, I would stay away from Power Automate and do something with Powershell or Azure Functions, yes, possibly involving Azure Queue as @DerekGusoff mentioned. Definitely read through the the throttling article he linked to, especially the part about how to handle throttling. And rather than develop the bulk job with the idea to run it as a monthly clean-up in case of failures, I would avoid that and try to put error handling in the created/updated flow that will alert you on failures. You want to do the bulk job only once if possible. Jul 7, 2023 at 18:15
2

You are going to run into throttling issues no matter what approach you try. In a database, updating two million records is no problem, but updating that much data in SharePoint is a problem. It just isn't designed for that kind of volume.

What's worked for me in the past for bulk operation is: Azure Queue backed by an Azure Function with queue trigger. The reason I like this is failure mitigation is baked into the architecture. When you hit throttling errors the items are re-queued and can be processed when the throttling has passed.

3
  • 1
    but, taking a closer look at your requirement, I would push back on doing this. I'd find another way to solve the problem of, when was any document modified for this customer. You're going to have continual headaches over such a heavy solution to solve such a small requirement. Jul 7, 2023 at 15:54
  • Thanks for your reply and info. I need to run this for one time only as currently we have 2 million files and sub-folder without any data inside the CustomerFilesLastModified field.. after having this initial run, I will create another power automate flow that run when a document is created or modified, and it will update its parent customer folder's CustomerFilesLastModified field.. so my main concern is about running this flow or azure function for the first time. Jul 7, 2023 at 17:34
  • ----I might schedule this to run once a week or once per month, just to make sure the CustomerFilesLastModified field reflects the real dates just in case the automate flow fail for a reason or another. what do you think should i stay away of doing so inside power automate and have this done inside a scheduled azure function ? Jul 7, 2023 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.