How to Automatically Generate Sequential Codes After Form Submissions
Question details
Automating the creation of sequential tracking codes following a survey or form submission, while distinguishing between brand new requests and re-registrations.
- Product
- Power Automate
- Device & OS
- not provided
- Scenario
- A user needs a workflow that processes incoming form responses, assigns a new auto-incremented number (e.g., 2022-204) for new entries, or appends a suffix (e.g., -R23) for re-registrations.
- Observed behavior
- The goal is to automatically calculate and assign the correct sequential code dynamically without relying on heavy external software, simply using standard tracking tools.
Ensure you have access to Microsoft Power Automate and have prepared a central data source, such as a SharePoint list or Excel spreadsheet, to store and track your existing codes.
Build a Sequential Code Generation Flow in Power Automate
Set up a cloud flow triggered by your form submission that conditionally checks the request type and interacts with your spreadsheet or database to calculate the correct code.
This automated process relies on keeping an updated record of the highest assigned numbers in a centralized file. Power Automate will read this file, perform a basic calculation depending on whether it is a new entry or a repeat entry, and write the new code back to the system.
Create an Excel table stored in OneDrive or a SharePoint list. Add columns for 'Request Type', 'Base Code', and 'Latest Suffix' so the flow can retrieve the most recent tracking data.
Open Power Automate and create an 'Automated cloud flow'. Choose 'When a new response is submitted' (Microsoft Forms) as your trigger, and select your target survey or form.
Insert a 'Condition' action to evaluate a specific answer from your form. Configure it to check if the submission is marked as a 'New Request' or 'Re-registration'.
In the 'If yes' branch for re-registrations, use the 'Get item' action to fetch the original code. Use the 'Compose' action with a formula to append your specific review suffix (e.g., changing 2019-111 to 2019-111-R23).
In the 'If no' branch, retrieve the highest existing base code from your data source. Use a 'Compose' action with an `add()` function to increment the sequence number by 1 (e.g., stepping up from 2022-203 to 2022-204).
Add a final action such as 'Add a row into a table' or 'Update item' to write the newly generated sequential code back into your central data source for future reference.
Manage Your Form Data Efficiently with WPS Office
While automated code generation flows often require the Microsoft Power Automate ecosystem, WPS Office provides an exceptional, free alternative for manually managing, tracking, and formatting your data source spreadsheets.
- 1. Download and Install: Download the free WPS Office suite from the official website and install it on your computer.
- 2. Open Your Tracking File: Launch WPS Spreadsheets and directly open your existing Excel data source files containing your sequential codes.
- 3. Organize Your Data: Utilize built-in spreadsheet formulas and conditional formatting to manually update or audit your form tracking sequences.
Frequently Asked Questions
What data sources work best with Power Automate for sequential tracking?
SharePoint lists, Excel files formatted as tables (stored in OneDrive or SharePoint), and Microsoft Dataverse are the most reliable data sources for storing and retrieving sequential codes in a Power Automate flow.
Can I generate sequential codes using just Excel formulas without Power Automate?
Yes, for basic tracking, you can use formulas like =MAX(A:A)+1 combined with IF statements in your spreadsheet. However, this method requires manual data entry and will not automatically trigger when an external form is submitted.
How do I add the current year as a prefix to my auto-generated codes?
You can use the concat() expression in Power Automate to merge dynamic dates and numbers. For example, concat(formatDateTime(utcNow(), 'yyyy'), '-', variables('NextNumber')) will output a code like 2023-105.
Why did my flow generate duplicate sequence numbers?
This happens when two users submit a form at the exact same time, causing the flow to read the same base number before updating it. You must enable 'Concurrency Control' in the trigger settings and restrict it to 1 to process submissions one at a time.
