cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNI
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

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

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

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

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
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (54,423)