cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arpad_Szerzo
Regular Visitor

Flow - auto increment a certain column of an added row

Hi!

 

I am building a more complex flow in which everytime a form is submitted, a new excel row is generated. What I'm struggling with is that I want the first cell of the row to be an auto number (the previous row value +1).

 

I've looked for solutions online, I tried declaring a variable and then increment it by one, but it's the same problem, I din't know how to declare a variable starting value, so I'm getting the same number every time. Any ideas? Is there an expression which would yield the value of the previous row, same column + 1?

 

Thanks!

 

Screenshot.png

1 ACCEPTED SOLUTION

Accepted Solutions
ccc333ab
Solution Sage
Solution Sage

Here is an approach: 

 

1. Get the rows from your excel first, so you can find out the ID's that have been used. In my excel file I have a column called DisciplineID which is a counter like yours. I also order by DisciplineID in case your ID's are out of order. 

ccc333ab_0-1625841938783.png

2. I then use a LAST statement to get the very last row of this spreadsheet. 

ccc333ab_1-1625842026614.png

3. And then I add 1 to the column called DisciplineID that outputs from my compose statement. 

ccc333ab_2-1625842076009.png

 

Now you can just use the output of this compose statement for adding in the new ID in your "Add a row into a table" action. 

 

 

View solution in original post

7 REPLIES 7
ccc333ab
Solution Sage
Solution Sage

Here is an approach: 

 

1. Get the rows from your excel first, so you can find out the ID's that have been used. In my excel file I have a column called DisciplineID which is a counter like yours. I also order by DisciplineID in case your ID's are out of order. 

ccc333ab_0-1625841938783.png

2. I then use a LAST statement to get the very last row of this spreadsheet. 

ccc333ab_1-1625842026614.png

3. And then I add 1 to the column called DisciplineID that outputs from my compose statement. 

ccc333ab_2-1625842076009.png

 

Now you can just use the output of this compose statement for adding in the new ID in your "Add a row into a table" action. 

 

 

Hey! Thank you so much for your kind response, it worked perfectly, not only that, but I also managed to extract the id and append it to the linked file name, which is extremely helpful to my company 🙂

 

Cheers!

How can I adapt this in order to Update a row with consecutive ID's for rows that don't have them ?

ccc333ab
Solution Sage
Solution Sage

Not 100% sure what you are asking. 

 

Do you have an existing spreadsheet with a blank column and you want to have an ID added to it? Or does the existing spreadsheet have a column that has SOME IDs in it and you want to fill in the missing IDs? 

Trabajanus
Regular Visitor

I do have a spreadsheet , that contains ID's already , and a flow that will continue to fill in information from another table but without the ID, what I want is to fill in the ID for the rows that get added  if that is possible 

ccc333ab
Solution Sage
Solution Sage

Try this. Here are my two tables. 

Table 1: FamilyWithID   

ccc333ab_0-1643135225014.png

 

Table 2: FamilyToAdd (doesn't have IDs)

ccc333ab_1-1643135340498.png

 

The flow would be: 

1. List the rows in the table that has your IDs in it. Make sure you "ORDER BY" to ensure your IDs are in order as the code relies on that. (My ID field is called TestID).

ccc333ab_8-1643136622896.png

 

2. Then get the last row of your table with IDs. 

ccc333ab_9-1643136681086.png

 

3. Now initialize a variable with the last ID used. 

ccc333ab_10-1643136712669.png

 

4. Now list the rows present in your table without the IDs

ccc333ab_4-1643135857285.png

4. Now you'll want to loop through each row you want to add. 

ccc333ab_5-1643135927655.png

 

5. Increment your variable to the next ID number

ccc333ab_6-1643135974151.png

6. And then add your data into the original spreadsheet, using the variable as the ID.

ccc333ab_7-1643136075924.png

Now, this assumes you clear out the spreadsheet without IDs for every run. If you don't you'll need to put some checking in there to determine if the row has been added before or not. 

Final Result: 

ccc333ab_11-1643136910671.png

 

Trabajanus
Regular Visitor

Thank you very much , that helped 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,087)