cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

How to get rid of second apply to each

Hello,

 

I am retrieving list from sql and list from sharepoint and comparing a key, if they match then i update the sharepoint.

The logic is fine but the apply to each runs for hours and hours. I figure if I get rid of one of the apply to each, then my flow might run faster. How do I change my logic?

 

Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Kudo Collector
Kudo Collector

Re: How to get rid of second apply to each

You can actually place it anywhere. 

 

What happens is that Flow sometimes does not immediately pick up schema updates. 

But when you add a new action, it does look for updates (in my experience).

 

Once you select that table in the newly inserted action, it should pick up the new schema. Actions below this new SQL get rows should be able to see the new column. 

 

After you do that once, the updated schema will be available in the Flow, and even old Get rows actions will be able to see it. You can safely delete the newly inserted SQL get rows, the flow now sees the updated schema.

It's weird and fiddly, but it's my experience. 

 

As for the performance optimization, I would try this blueprint, check this mockup: 

 

flow_help.png

View solution in original post

26 REPLIES 26
Highlighted
Super User
Super User

Re: How to get rid of second apply to each

Hi,

 

Are you looking to do full list updates every single run? Can't use use when an item is created or modified trigger of SQL or SharePoint?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@abm Thanks for reply,

 

Each item gets updated every night by a sql job... So unfortunately, the value of each item needs to be updated

Highlighted
Super User
Super User

Re: How to get rid of second apply to each

So why can't you use the SQL trigger? Then look to retrieve the only SharePoint record and update.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@abm Thanks for reply.


The SQL trigger sounds good in theory, in reality, its way too inconsistent for us. Some items don't trigger at all.

We tried the trigger several times and the draw back of it and its inconsistency made us reconsider it for Get Rows V2 instead.

 

I guess where I am trying to go is, can I break out of my loop when I find a match? Say at key 2, I match the SharePoint list at Key 4, I see that apply to each still looks for all the items after key 4 anyways. There is no way there is going to be another match after key 4, why is apply to each working that way? Is there something I can do?

 

 

Highlighted
Super User
Super User

Re: How to get rid of second apply to each

Hi @mykelboachie 

 

So in that case start with SQL (Get rows) then filter SharePoint by value (Get Items). This way you only get one record for SharePoint.

 

If you need further help in this let me know.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@abm Thanks

 

So i followed the procedure and tried this way

 

Capture.PNG

 

Unfortunately, I can see that it is failing, the sharepoint items I have as a test is only 10 items so its easy to debug. Did I do something wrong from your suggestion?

Highlighted
Super User
Super User

Re: How to get rid of second apply to each

Hi @mykelboachie 

 

You are nearly there. What type is 'Key_PLGroup_Item'? If its string then add a single quotes (') after equal filter. 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Kudo Collector
Kudo Collector

Re: How to get rid of second apply to each

Check the types and see if the single quotes as stated. 

Also, if you are already filtering By Key_PLGroupItem in the action Get_items, you might not need the Condition inside the loop. 

 

That is, unless the Condition rule is just a placeholder that will be replaced with more complex checks to skip the update action. 

If you have meaningful checks in this Condition to skip the Update item action though, it will be a good time saver too. 

 

Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@TiagoFreire @abm 

 

Thanks for helping out... I applied the filter and it was still failing. I want you to confirm if I did as you said,

 

Also, I removed the condition as I am not doing anything after that, than just updating the item

 

Capture.PNG

Highlighted
Kudo Collector
Kudo Collector

Re: How to get rid of second apply to each

To be able to help you better it will be helpful to post a screenshot of the actual error message in a test run. 

 

Also, what are the data types of the columns Key_PLGroup_Item in both sides, SQL and Sharepoint? 

 

(as a side note, I would like to point out that I have been using Azure SQL with the 'when a sql row is modified' trigger with great success, provided that the table has a column of type ROWVERSION, which is the requirement for reliable triggering the is modified event.)

Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@TiagoFreire  I am unable to give the failing message because flow is stucked up at item 250 for a while 

 

This is item 249 showing it failed

Capture.PNG

 

This is item 250 being stucked on after 2 mins....Can confirm at this writing, it is timed out for no reason

 

Capture1.PNG

 

The key datatype is a string on both sides

 

Our SQL server is on premesis. I wish it was in the cloud. I think something with flow coming through a gateway makes the trigger event inconsistent

 

Edit: I made it ''Key_PLGroup_Item' eq '@{items('Apply_to_each')?['Key_PLGroup_Item']}' to reflect the datatype...Getting same "stuck" at 250 failing

Highlighted
Super User
Super User

Re: How to get rid of second apply to each

Hi @mykelboachie 

 

Could you please post your error?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@abm It just sits at 250 and times out...Error is a time out

Highlighted
Super User
Super User

Re: How to get rid of second apply to each

Could we have a screen share?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Kudo Collector
Kudo Collector

Re: How to get rid of second apply to each

In this context a time out could be api throttling, which is always a concern. With this in mind, the number of SharePoint calls must be reduced. The changed loop is doing SharePoint calls on every loop, which may have caused this timeout.

 

Let me backtrack then, and look at your original flow

 

You make 2 calls, to SQL and SharePoint once each. This prevents the API throttle by too many calls which is the likely cause for the timeout.

 

Then you have a nested loop that was taking too long and you want to optimize. Let's look at it. 

 

Inside the first loop, have you tried using the Filter_array action? 

With Filter_array you can apply a filter similar to the one in the Condition you just deleted to non-destructively reduce the SharePoint result to the matching elements on each iteration. 

You will still have a nested loop, but the inner loop will operate on a potentially much smaller result set. 

Then reintroduce that Condition to skip Updates, if there is any data pattern that allows for that in some cases, to shave more time. 

 

https://docs.microsoft.com/en-us/flow/data-operations has the description of many data operators including the filter_array. 

I have used it myself to optimize flows and it is a godsend.

 

Try this out and let us know.

 

Cheers!

Highlighted
Community Support
Community Support

Re: How to get rid of second apply to each

 

Hi @mykelboachie ,

 

It seems that there are more rows in the SQL table, so consider traversing the items in the SharePoint list first.

Then filter the rows by Get rows-Filter Query, because it has the same key column, so it will work, so the number of rows that need to traverse the SQL table will be reduced.

In addition to setting Apply to each, I think the execution time of Flow will be reduced.

 

Please check this similar threads:

https://powerusers.microsoft.com/t5/General-Power-Automate/Update-Sharepoint-fields-from-SQL-table/t... 

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@TiagoFreire  Thanks a lot.... I just can't get the filtering to work right. @v-bacao-msft  has been helping me on another thread on getting the filters to work and I couldn't just do it..

 

I decided to go a different route...In your previous reply you said you could trigger SQL database using RV...We use an on premesis database and I was able to use RV on a table to activate the trigger!

 

Thats fantastic and I appreciate it. However, I am running into a weird issue where one column that I update on a row doesn't show up in the sql trigger JSON...I have tried deleting and adding that column and other different ways, that column just doesn't show up...Very weird... Does RV only keep track of 1 change in the row?

Highlighted
Kudo Collector
Kudo Collector

Re: How to get rid of second apply to each

Rowversion does not monitor the row values per se.

It is an autoincremented number that changes every time the SQL DB updates that row. It is this column that is monitored to bubble the trigger event. The event applies to the whole row. 

 

Flow can be fiddly to detect schema changes however.

 

Try adding a new SQL Select Rows action to the flow and fetching some data from that table. It should fetch the new schema, which will then be understood by all other SQL action fetching from the same Connection. Then you can delete the dummy SQL action afterwards.

 

Here is a mockup of the basic structure I would try:

 

flow_help.png

Highlighted
Advocate I
Advocate I

Re: How to get rid of second apply to each

@TiagoFreire  Thanks...


If I understand you correctly, is this where you want me to place the get rows?

 

Capture1.PNG

 

To understand my problem, this is my columns

 

Capture.PNG

 

And see what the flow brought

 

Capture2.PNG

 

Notice how the cost column didn't show up?

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new 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 (6,277)