Unanswered

Creating a Unique ID number based on an answer to a form question

(0) Share

Report

Posted on by AB-09051044-0

Hi,

I have a flow set up with the trigger when a new response is submitted within Forms. From this, I want to create a unique form ID number that increments within its own site based on which site is selected in a question, between site 1-6.

So for example, the first form response is submitted for site 1, I want their code to be Site1-2425-1. Then the second form response is submitted for site 4, I want their code to be Site4-2425-1 as this is the first response for their site.

The rest of the flow I have working as expected, there is a switch which determines who should be emailed based on which site the form was submitted for and then the email is sent. The email pulls this unique form ID into the content as this is to be used as a reference number so I just need to tighten up how to generate the IDs to work as expected.

I'm pretty new to power automate so apologies if this is obvious.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

rzaneti 4,241 Super User 2025 Season 2 on at

Like (1)

Report

Hi @AB-09051044-0,

You can use a few expressions to control the IDs, but you would need to store it somewhere (a SharePoint List, an Excel table, a Dataverse table....) or at least work with an environment variable. Do you already have any later step in your flow to populate some dataset with the Form responses data? If yes, we can leverage it

Note: No apologies needed. That's a great use case and it will probably help other users with the same issue in the future. Thanks for asking in the forum :)

Was this reply helpful? Yes No
AB-09051044-0 11 on at

Like (0)

Report

@rzaneti The form response data is currently just going to the spreadsheet automatically with the sync it does to excel when a form is stored in a Team, but I do want to be able to write the generated ID number into the spreadsheet too - when I first tried to set up a general ID number with the prefix code and the actual form ID number the writeback to the spreadsheet was throwing an error but I'm assuming as this was because forms didn't have a chance to writeback to the excel spreadsheet and adding a delay action in the flow would overcome this..

I was experimenting using a sharepoint list, but I feel like it being stored in the spreadsheet would make more sense but I'm happy to do it either way!

Was this reply helpful? Yes No
rzaneti 4,241 Super User 2025 Season 2 on at

Like (0)

Report

Hi @AB-09051044-0,

I'm not sure if it's safe to to change the structure of the synched spreadsheet to your Forms. I didn't find anything official, but I already got issues with that in the past (the sync stops when you add new columns) and found this user reporting a similar problem in the Tech Community: https://techcommunity.microsoft.com/discussions/microsoftforms/microsoft-forms---editing-of-associated-excel-sheet/3835490

Question for you: have you considered to use Power Automate to also populate this Excel table with the Form responses? If the Form questions don't change much, it can be a good approach, with the advantage to create a few calculated columns, including your custom ID. If you don't know how to do it, refer to this article: https://digitalmill.net/2024/11/04/populating-an-excel-table-from-a-microsoft-forms-submission/

If you think this approach can fit you needs, refer to this step-by-step to implement the Excel table populated from Power Automate. Let me know if this approach works for you, and I will be glad to help you to monitor the custom ID.

Was this reply helpful? Yes No
AB-09051044-0 11 on at

Like (0)

Report

Hi @rzaneti

Hmm I have added additional columns and formulas / pivots to a synced spreadsheet previously and it didn't seem to affect the auto-sync, although maybe I'm misunderstanding.

If it will make generating the custom ID a smoother process though I'm happy to use a flow to to populate a separate excel document with the responses however.

Was this reply helpful? Yes No