cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

For each loop inside Do loop

I am using get rows SQL to get set of lines from my database and insert that into another table. Which is working fine if I use for each loop but in the collected data I have two fields (Start year and End year) so according to the year difference between two years set of lines should repeat adding on the table.

 

In below example: I want four carlines to be added two times. one set is with 2018 in year field and another with 2019 in year field.

 

Before flow:

 

Carline                                                   Start year    End year
BMW:BMW:1-Series:F20 [0-3.5 PC]      2018             2019
BMW:BMW:1-Series:U01 [0-3.5 PC]      2018             2019
BMW:BMW:2-Series:G42 [0-3.5 PC]      2018             2019
BMW:BMW:2-Series:G42(2) [0-3.5 PC]  2018             2019

 

After flow (output should be):

 

Carline                                                   Start year    End year      Year
BMW:BMW:1-Series:F20 [0-3.5 PC]      2018            2019            2018
BMW:BMW:1-Series:U01 [0-3.5 PC]      2018            2019            2018
BMW:BMW:2-Series:G42 [0-3.5 PC]      2018            2019            2018
BMW:BMW:2-Series:G42(2) [0-3.5 PC]  2018            2019            2018
BMW:BMW:1-Series:F20 [0-3.5 PC]       2018            2019            2019
BMW:BMW:1-Series:U01 [0-3.5 PC]      2018             2019            2019
BMW:BMW:2-Series:G42 [0-3.5 PC]      2018             2019            2019
BMW:BMW:2-Series:G42(2) [0-3.5 PC]  2018            2019             2019

4 REPLIES 4
Community Support Team
Community Support Team

Re: For each loop inside Do loop

Hi @Gopi,

 

I have made a test on my side and the flow works well. Please take a try with the following workaround:

  • Choose a proper trigger, here I use Flow button trigger.
  • Add “Get rows” action, specify Table name.
  • Add variable->”Initialize variable” action, Name set to InputArray, Type set to Array and set Value to null.
  • Add “Apply to each”, input parameter set to output of “Get rows” action.
  • Within “Apply to each”, add Compose action, Input set to following formula:

 

createArray(item()?['StartYear'],item()?['EndYear'])

 

  • Add “Set variable” action, Name set to InputArray, Value set to output of Compose action.
  • Add “Do until”, condition set to following formula:

 

@empty(variables('InputArray'))

 

  • Within “Do until”, add “Insert row” action, Year field set to following formula:

 

first(variables('InputArray'))

 

  • Add Compose 2 action, Input set to following formula:

 

skip(variables('InputArray'),1)

 

  • Add Set variables 2 action, Name set to InputArray, Value set to output of Compose 2 action.

Image reference:4.JPG

 

5.JPG

 

6.JPG

 

7.JPG

The flow works successfully as below:

 

8.JPG

 

9.JPG

Note: You can upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading. 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: For each loop inside Do loop

Hi Kris,

 

I followed all the steps but my year value is null in output and also only two lines are being added when I choose SOP as 2020 and EOP as 2022. For ex: I need three same carlines with year as 2020,2021 and 2022.

 

Capture.JPGCapture1.JPGCapture2.JPGCapture3.JPGCapture4.JPG

Community Support Team
Community Support Team

Re: For each loop inside Do loop

Hi @Gopi,

 

Could you please show a bit more about the data type of Year field in your SQL table?

On my side, the data type of Year field is varchar.

The InputArray only contains two items(item()?[‘SOP’] and item()?[‘EOP’]), so the “Insert row” action would run twice based on the two items. If you want to create three carlines, the InputArray should contains three items, for example:

createArray(item()?[‘StartYear’],item()?[‘MiddleYear’],item()?[‘EndYear’])

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: For each loop inside Do loop

Data type for start and end year is int.. so if start date is 2018 and end date is 2020 then each carline should repeat 3 times filled with year 2018, 2019 and 2020... Difference between start and end date can be anything.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 75 members 3,771 guests
Please welcome our newest community members: