Unanswered

Creating a unique, sequential Proposal Number on the first column/in the first cell on each row

(0) Share

Report

Posted on by PC-10011053-0

Hi, I am a new user of Power Automate. We are a company where we receive requests for fee proposals for work we carry out. I have created a form with various drop down answers for our team to use when they receive a request for a proposal. I have used Power Automate to populate a row on an Excel sheet with the answers from that form, each subsequent form populates the next row down and so forth.

Is there a way that each time a form is submitted that a unique, sequential Proposal number is automatically entered into column A when the rest of the data appears in each row. For example, if in the first row the Proposal number was P17750, then when the next form is submitted and the row has been populated from cell B2 to Z2, the flow adds in the unique Proposal number to cell A2?

Whilst a proposal number could be added manually, where there will be a number of staff adding proposal forms at the same time there is a danger of mistakes happening with manually created proposal numbers (ie same number used twice, not sequential numbers used etc)

My flow looks like this currently and works fine for the form submission

Thank you in advance

Categories:

Building flows

All responses (2)

Answers (0)

Sort by

Expiscornovus 33,997 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @PC-10011053-0,

How frequent are proposals going to be added? Based on the frequency you might be able to generate a unique number based on the date (converted to ticks).

Below is an example (which generates a unique number for each second, assuming that is sufficient for your use case)
concat('P', string(div(ticks(utcNow()),10000000)))
PC-10011053-0 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Expiscornovus, ideally it would be a sequential number, following on from the previous P (Proposal) number. I joined the company a few months ago, they have an existing spreadsheet dating back to 2018 and they want to keep the same P number format - the current spreadsheet was not populated using Microsoft forms so they just put a bunch of P numbers in column A in ascending order like this -

As everything was entered directly on to the worksheet it was no problem. However if I try that on my new worksheet, then submit a form, it adds the row with the form data under where the last P number is as it thinks that row is already populated. So if you have something like you created above but would simply create the next sequential P number...that would be amazing!

Alternatively, is there a way to lock column A - I could generate the P numbers all the way down the column, however I would need Forms and the Power Automate to ignore/pretend Column A is there.

So if a2 had P17647, then a3 would be populated by P17648 and the rest of the data from the form along the remainder of the same row, so it looks similar to this (black fill used to hide client details!) -