Generate DocNumber - fill in MS form, linked to Excel - Users choose first 3 fields, last item a sequential number. Then update Excel file with generated number.
Hello! I'm pretty new to Power Automate and I am not able to find exactly what I'm looking to do with a template.
I want users to be able to generate a document number. Ideally, I want to use a shared MS Form - linked to an excel file that is filed on SharePoint/Teams.
Users will fill out the form, choosing the first 3 fields from a drop down, the last field will be a sequential number, the sequence will depend on the fields chosen from the (first three) drop downs in the form.
After the number is assigned - I would like users to receive an email with the generated number and then have the Excel file update a new row, with the new number.
Field 1 - Project
Field 2 - Doc Type
Field 3 - Discipline
Sequential number - xxxxx (00001)
Say we already have the following number assigned in the excel sheet: ABC-REP-DA-00001 - Assuming the next user chose the same Project, Doc Type and Discipline, the next sequential number to be auto assigned would be ABC-REP-DA-00002. The Excel file already contains the first 3 fields - we have been manually assigning the 4th field (sequential number) 00001, 00002, by filtering.
Is it possible to build a flow to make this happen? Any help is greatly appreciated.
It's definitely possible. This may not be the best way to do it, but my first thought is that you would need a condition for each possible outcome of the selections. That could get pretty long, but once built it would work fine.
What I mean is, you would use the condition action and have 3 conditions. Let's use your example to design the first one.
Field 1 is equal to Project
Field 2 is equal to Doc Type
Field 3 is equal to Discipline
If all the the conditions are met, then in the If Yes of the condition we would want to increment a variable that we would initialize at the beginning of the Flow by 1. We would just leave the If No blank.
Again you would need to create the condition for each potential combination of values if you were to do it this way. Also, I am not quite sure how you would manage the formatting of the number to be 00001, 00011, etc.