Power Platform Community Forum Thread Details

Working with large SharePoint List

I have a table in SAP containing customer number and name information. My goal is to enable users in my organization who do not have access to SAP to view this customer data and associated documents in SharePoint.

I successfully created a flow that transfers all the SAP data to a SharePoint list without any problems.

Now, I'm looking to establish another flow that runs every evening to compare the SAP table with the SharePoint list. I have access to all the necessary data from SAP, and I've constructed an HTTP request to fetch all SharePoint items (54,000 in total).

The challenging aspect arises during the comparison process. When I use a condition to verify if the SAP customer number matches the SharePoint list title, the flow takes days to complete.

I'm seeking an efficient method to quickly check for changes and either update existing items or add new ones to the SharePoint list.

All the help is welcome.

This is how my flow looks like.

What you're asking is possible but quite complex using OOB Flow actions. You could try using Office Script actions to perform the comparison operations.

If you want to try standard actions, the process may resemble:

1. Collect all the data from SAP and from SharePoint into 2 arrays

2. Two more arrays will hold the unique identifiers (from SAP)

3. Using Filter action, you can check for newly created items:

3.1. Filter From: SAP IDs - @body('SAPItemIDs')

3.2 Filter Query: SharePoint IDs does not contain item - @not(contains(body('SharePointItemIDs'), item()))

4. With the list of new IDs you can now create the new items in SharePoint

4.1 Filter the SAP items array for ids that are contained in the result array of 3.2 - @contains(body('NewItemIDs'), item())

4.2 If a large number of items is created daily, you can use the Batch Create process as documented by @Paulie78 here: Power Automate Flow to batch create SharePoint List Items (tachytelic.net), for a lower scale you can simply iterate and create the items in a Apply to each loop (remember to apply Concurrency to allow parallel creation of items and improve performance)

5. Same logics for deleted items (simply reverse the SharePoint and SAP arrays)

5.1. With the filtered SharePoint items (by the result array of the deleted items), either use the Bulk delete process or again, iterate the deleted items and delete in a concurrency-enabled Apply to each loop

6. Lastly the changed items - there it gets even more complicated, so I won't elaborate too much here, but the concept is creating two more arrays of the SharePoint and SAP items filtered so that the SAP IDs exist in both arrays with the tracked fields concatenated - for example: concat(item()?['SAPID'],',',item()?['Title'],...). With the two arrays at hand, you can now use a similar approach to check for items in the SAP array that are not contained in the SharePoint array - these are the Modified items! Lastly, you can either iterate the changed items, locate them in SharePoint by ID and update them in Apply to each loop (concurrent..), or you can use dictionaries (documented here: Power Automate Dictionary – EPM Dev Blog (wordpress.com)) to collect the changes and SharePoint IDs and use the Batch Update process.

For performance tuning of your flows I recommend reading @mdevaney 's excellent post: Power Automate Standards: Performance Optimization (matthewdevaney.com).

That's it,

Hope this helps you and others

Quick Links