cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Updating Items or Creating New Items from MySQL Table to Sharepoint List

Hello,

 

I have a MySQL Table (~2000 rows w/ 50 columns) that gets dumped and refreshed every hour.  I need to look at this table and compare it to a Sharepoint List.  If an item (based on Order Number) from MySQL is already in Sharepoint, I need to update some of those 50 columns in my Sharepoint List.  If it is not in Sharepoint, I need to create a new item in the Sharepoint list based on the Order Number in MySQL.  Each Order Number in Sharepoint has a unique ID, but each Order Number's ID in MySQL gets changed on ever refresh.

 

Therefore, I started off by using an 'Apply to Each' loop to look at each item (Order Number) in the MySQL table and compare it to each item (Order Number) in another 'Apply to Each' loop for the Sharepoint list.  If the Order Number matched, then update the item in Sharepoint.  If not, then create a new item.  The problem I ran into was that my run time was 2 hours.  I believe this was because I had a nested 'Apply to Each' loop.  Is there a way to reduce this run time, or am I doing this incorrectly?  Is there another way to do it faster?

 

I have been trying to put the Order Numbers from by MySQL and Sharepoint into separate arrays and then look at the intersection of the arrays.  The new output array would be the Order Numbers that are the same, so I would know what to Order Numbers to Update.  However, I do not have the Sharepoint ID that would go with Order Number, thus not being able to update as efficiently as I would like.

 

If you have any thoughts or recommendations, I would greatly appreciate it.

Thank you,

CNI

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: Updating Items or Creating New Items from MySQL Table to Sharepoint List

Have you considered using the concurrency control setting in the apply for each loop?

 

I used it in the following post to speed up sorting of items.

https://sharepains.com/2020/03/05/sort-an-array-in-power-automate/

 

In your case this should reduce the run time down to about 4 minutes.

View solution in original post

Highlighted
Frequent Visitor

Re: Updating Items or Creating New Items from MySQL Table to Sharepoint List

Peter,

 

Thank you for your reply and support.  I checked out your sorting post and it was helpful.  I had a global variable outside, as well as within the 'Apply to each' loop, therefore, I could not run concurrently.  (I tried and was recommended to set the concurrency to 1.)  Therefore I did two things:

 

(1) I changed my variable to a data operations (compose) action.  Compose will allow concurrency, whereas using variables within concurrency can cause chaos within your program.

(2) To get rid of the 'Apply to each' loop within another 'Apply to each' loop, I took my data and through it into a string.  Then I get the index of my data within that string and modify it that way.  It allows my run time to drop dramatically and it is working quite well.

 

Thank you for your help.  I tried to accept your solution, but it looks like you have already performed that action.  Again, thank you.

CNI

View solution in original post

2 REPLIES 2
Highlighted

Re: Updating Items or Creating New Items from MySQL Table to Sharepoint List

Have you considered using the concurrency control setting in the apply for each loop?

 

I used it in the following post to speed up sorting of items.

https://sharepains.com/2020/03/05/sort-an-array-in-power-automate/

 

In your case this should reduce the run time down to about 4 minutes.

View solution in original post

Highlighted
Frequent Visitor

Re: Updating Items or Creating New Items from MySQL Table to Sharepoint List

Peter,

 

Thank you for your reply and support.  I checked out your sorting post and it was helpful.  I had a global variable outside, as well as within the 'Apply to each' loop, therefore, I could not run concurrently.  (I tried and was recommended to set the concurrency to 1.)  Therefore I did two things:

 

(1) I changed my variable to a data operations (compose) action.  Compose will allow concurrency, whereas using variables within concurrency can cause chaos within your program.

(2) To get rid of the 'Apply to each' loop within another 'Apply to each' loop, I took my data and through it into a string.  Then I get the index of my data within that string and modify it that way.  It allows my run time to drop dramatically and it is working quite well.

 

Thank you for your help.  I tried to accept your solution, but it looks like you have already performed that action.  Again, thank you.

CNI

View solution in original post

Helpful resources

Announcements
firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (8,285)