Unanswered

When a form is submitted, create a new excel row with a unique ID that follows a naming convention and save the file with that IDd

(0) Share

Report

Posted on by TCrompton3

Hi,

First time positing. So I am new to Powerapps and Power Automate, I've looked through several tutorials that touch part of what I want to do but don't seem to mesh together. Hoping someone can help.

I want to create a flow so that when someone submits a Form (that I previously made and is linked into Teams) it will create a new Log entry in the Excel file I have that follows the naming convention we use, but I am having trouble having it detect the numbering sequence and then creating a new one. It is NDA[yy]-[###]

Additionally, I also want to generate a word document that incorporates the answer provided in the form and saves that document with the ID in the title... but that may be for another day.

I've attached the type of sequence I want to follow and the flow I have so far.

Screenshot 2020-09-17 171322.png

Screenshot 2020-09-17 171208.png

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

efialttes 14,756 on at

Like (1)

Report
Hi!

"I am having trouble having it detect the numbering sequence and then creating a new one. It is NDA[yy]-[###]"

Can you elaborate a bit more the rules for generating a new numbering sequence?

Shall the year be generated from today's date?

IF so you can use the following WDL expression

concat('NDA',utcNow('yy'),'-')

Shall the index be generated by incrementing the last one available in your excel?

THanx!

Was this reply helpful? Yes No
TCrompton3 7 on at

Like (0)

Report

Thanks for the response!

Yes, the first two are generated from the year, and the last three are generated by detecting the last used number in the excel so (let's say it was "NDA20-009") the flow would retrieve the list, find the last used number or next available, then generate the ID and create a row and put that ID in the cell.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Hi!

One possible approach is to configure 'Order by' on your 'List rows present in a table', indicating its output shall present rows ordered by your target column, in descending order

So you just need to add an ODATA expression for that purpose

TargetInternalColumnName desc

This way you ensure the higher index is on the top of it, then you just need to read this first row by means of first() based expression

Let's assume the highest one is NDA20-009, and you store it in a variable called 'myHiguestIndex' of type string

You can read the index by using the following expression:

first(split(variables('myHighestIndex'),'-'))?['TargetInternalColumnName']

So in the example below, the TargetInternalColumnName I used was 'MANUFACTURER'

...and, I believe you can increment it by one by means of the following expression

add(int(last(split(variables('myHighestIndex'),'-'))),1)

Hope this helps

Was this reply helpful? Yes No
TCrompton3 7 on at

Like (0)

Report

Ok, I think I get it. So what are the other flow items I need to insert?

I guess I need to get a better grasp on the process here on what's happening behind the scenes of the flow. So:
Step 1: Trigger is that a form is submitted
Step 2: Retrieve the response details
Step 3: store those details in an excel row ("FormResp" table)
Step 4: List rows in Log "NDATable" so that I can work with those rows/variables? And order then so that it can detect the next available number
Step 5: Create a unique identifying NDA ID using the expression you first showed me, and use the next sequence of NDAyy-xxx.

Is that right?
Step 6:

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report

Great analysis!

Please let us know your progress on your new flow design

Cheers!

Was this reply helpful? Yes No