cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Incremental list based on the value in the last row of the first column

Hi all

 

I have created an automated flow that's triggered when a new response is received for a Microsoft Form.
It adds a new row to an existing Excel sheet that's stored in Onedrive.

 

However, before I add the row to the table, I would like to add an incremental ID based on the last RFP ID that exists in the spreadsheet . 

 

Flow.PNG


This cannot be entered manually using Forms/Excel because

a) Access to the Excel sheet is limited to 2 users

b) Forms are used by multiple users who will have no way to track (apart from asking the 2 users) on what was the last RFP ID

 

 

Capture 3.PNG

 

So far, I'm able to get the last row and get its output in Compose but unable to extract RFP ID and increment it to be added in the newly created record. I also note that RFP_ID will probably be required to be trimmed/converted to another format to be allowed to be incremented?

 

The expression used in Compose is last(body('List_rows_present_in_a_table')?['value'])

 

 

Any help will be greatly appreciated!

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Incremental list based on the value in the last row of the first column

@Ash_R 

last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'] should get you the specific column of the last row.  Test that first.
 
If that works, we want to split that on the underscore and grab everything after:
 
Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1]     (the [1] is saying 'grab the second element of the array we created by splitting the value).  Test that next.
 
Now, we want to convince Flow/Power Automate that the thing is a number, so we wrap the whole thing in an INT()...so: 
 
INT(Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1])  Test that part.
 
And finally, we want to add 1 to get the next increment.
 
ADD(Int(Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1]),1)
 
Putting all that in your Compose, should give you the desired result.  We can do that with less Functions, if you want, too.  Happy to help either way.
 
Keep us posted.
-Ed-
 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

 
 

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Incremental list based on the value in the last row of the first column

@Ash_R 

last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'] should get you the specific column of the last row.  Test that first.
 
If that works, we want to split that on the underscore and grab everything after:
 
Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1]     (the [1] is saying 'grab the second element of the array we created by splitting the value).  Test that next.
 
Now, we want to convince Flow/Power Automate that the thing is a number, so we wrap the whole thing in an INT()...so: 
 
INT(Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1])  Test that part.
 
And finally, we want to add 1 to get the next increment.
 
ADD(Int(Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1]),1)
 
Putting all that in your Compose, should give you the desired result.  We can do that with less Functions, if you want, too.  Happy to help either way.
 
Keep us posted.
-Ed-
 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

 
 

View solution in original post

Highlighted
Frequent Visitor

Re: Incremental list based on the value in the last row of the first column

Thanks @edgonzales!

 

This solution works!

 

It'll be definitely interesting to know the alternative that you suggested i.e. by using less functions
(if you have the time, of course) 😀😋

Highlighted
Super User
Super User

Re: Incremental list based on the value in the last row of the first column

@Ash_R 

Of course!  It really only saves the last part, and also means setting up a variable.

 

In the beginning, you would initialize an integer variable, and then set it to be this part:   INT(Split(last(body('List_rows_present_in_a_table')?['value'])['RPF_ID'],'_')[1]

 

Then you could add the action step "Increment Variable" to add 1.

 

Not super helpful, but is a little bit closer to the 'no-code' stuff.

-Ed-

 

 

Highlighted
Frequent Visitor

Re: Incremental list based on the value in the last row of the first column

Thanks for helping out @edgonzales , greatly appreciated!

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
Top Kudoed Authors
Users online (8,608)