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

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

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.

 

TC

 

Screenshot 2020-09-17 171322.png

Screenshot 2020-09-17 171208.png

  

5 REPLIES 5
Highlighted
Dual Super User III
Dual Super User III

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!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

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.

 

 

Highlighted

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'

Flow_getHighestIndex.png

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

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

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: 

 

Capture.PNG

Highlighted

Great analysis!

Please let us know your progress on your new flow design

Cheers!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (12,795)