Flow - auto increment a certain column of an added row

[Anonymous]

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!

[ccc333ab · Accepted solution]

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. 

[Anonymous]

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!

[Trabajanus]

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

[ccc333ab]

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]

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]

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: 

[Trabajanus]

Thank you very much , that helped 

[martii20]

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

[ccc333ab]

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)

[martii20]

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. 

[ccc333ab]

OK. The expression looks correct.

On your run, can you show me a snippet of what the results for the first Compose statement is? 

[martii20]

First Compose Statement:

last(outputs('List_rows_present_in_a_table')?['body/value'])

Input: 

{

  "@odata.etag": "",

  "ItemInternalId": "15a8cdb5-e54f-4f1f-a8a2-6956478ee279",

  "MOC ID": "1",

  "Timestamp": "45161",

  "Submitter Email": "EMAIL",

  "Project Description": "test",

  "MOC Process": "Line/Plant Transfer",

  "Affected Plants": ""

}

Output:

{

  "@odata.etag": "",

  "ItemInternalId": "15a8cdb5-e54f-4f1f-a8a2-6956478ee279",

  "MOC ID": "1",

  "Timestamp": "45161",

  "Submitter Email": "EMAIL",

  "Project Description": "test",

  "MOC Process": "Line/Plant Transfer",

  "Affected Plants": ""

}

[ccc333ab]

Looks like your spreadsheet has the MOC column named with a space. Your expression uses an underscore. 

So you would need to ensure the fields match. 

[martii20]

I updated the column/equation to match and the flow "succeeded" this time, but the ID column did not populate with a new number. I'm getting the "Invalid Template" error again:

[ccc333ab]

A little baffled by the fact the flow succeeded, yet you are getting an error...

Anyway, can you again send me the results of the request before this step, and the expression used in this step? 

[martii20]

I should've clarified - the flow "succeeded" but the "ID" cell did not populate with the +1 value.

I then tried editing the flow again to fix it and that's when the error occurred.

    

[ccc333ab]

OK. 

Can you get it back to where the flow was successful again. In the second compose, go back to your original statement of 
   add(int(outputs('Compose')?[MOC???],1)  
         Note: I put in MOC??? as I don't know what your Excel file has in it anymore. Make sure it matches.

Then to troubleshoot the fact your excel file isn't increasing send me (or you can just check) the following: 

• Make sure your Compose2 is returning the MOCID value +1 that you are looking for 
• In your Add a row into a table action, the expression for MOCID is using Outputs('Compose_2')

[martii20]

It's working! I can't say I'm sure what changed exactly between this attempt and the last one, but rewriting the compose statements seemed to do the trick.

This is a copy of my flow, in case it helps anyone else:

Thanks @ccc333ab!