Unanswered

Flow - auto increment a certain column of an added row

(0) Share

Report

Posted on by Community Power Pla...

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!

Categories:

Building flows

I have the same question (0)

All responses (18)

Answers (1)

Verified answer

ccc333ab 1,144 on at

Like (2)

Report

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.
2. I then use a LAST statement to get the very last row of this spreadsheet.
3. And then I add 1 to the column called DisciplineID that outputs from my compose statement.

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Trabajanus 7 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ccc333ab 1,144 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Trabajanus 7 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ccc333ab 1,144 on at

Like (1)

Report

Try this. Here are my two tables.
Table 1: FamilyWithID

Table 2: FamilyToAdd (doesn't have IDs)

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).

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

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

4. Now list the rows present in your table without the IDs
4. Now you'll want to loop through each row you want to add.

5. Increment your variable to the next ID number
6. And then add your data into the original spreadsheet, using the variable as the ID.
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:

Was this reply helpful? Yes No
Trabajanus 7 on at

Like (0)

Report

Thank you very much , that helped

Was this reply helpful? Yes No
martii20 27 on at

Like (0)

Report

I tried coping these steps to my flow and I am getting an "Invalid Template" error message on the second function (compose add one to get new id)

Error Message

Function

Does anyone have any tips for fixing this? Thanks

Was this reply helpful? Yes No
ccc333ab 1,144 on at

Like (0)

Report

Guessing as you didn't actually show your initial COMPOSE action (the one that you set your MOC value in), but I believe your equation should be:

add(int(outputs('Compose')),1)

Was this reply helpful? Yes No
martii20 27 on at

Like (0)

Report

Okay, I tried that and it didn't work. Here's my whole flow:

I am trying to accomplish the same thing as the original post - when a new response is submitted, response details are collected in an Excel file and assigned an "MOC ID," which is just +1 from the previous row.

Was this reply helpful? Yes No