How to Generate a Unique Project Number in SharePoint with Power Automate
Question details
The user wants to create an automated process to generate custom, unique project identifiers (e.g., P220001) in a SharePoint list.
- Product
- SharePoint and Power Automate
- Device & OS
- not provided
- Scenario
- Setting up a SharePoint list where each new project entry requires a strictly unique, formatted ID containing a prefix, the current year, and a sequential number.
- Observed behavior
- The user needs a reliable method to construct and assign this unique project number without encountering concurrency issues common with standard calculated columns.
Ensure you have the necessary permissions to edit the target SharePoint list and create flows in Power Automate. It is recommended to create a dedicated 'ProjectNumber' column in your list before building the flow.
Build a Power Automate Flow to Generate Sequential Identifiers
Use Power Automate to extract the current year, format a sequential number, and combine them into a custom ProjectNumber field.
Relying solely on the default SharePoint ID in calculated columns can lead to duplication errors if multiple users create items simultaneously. A dedicated Power Automate flow offers a more robust solution for strict uniqueness.
In Power Automate, create an automated cloud flow that triggers 'When an item is created' in your target SharePoint list.
Add a 'Compose' action and use the expression formatDateTime(utcNow(),'yy') to extract the two-digit current year.
Retrieve the item's unique SharePoint ID or a custom sequence number, and use an expression or a formatting action to pad it with leading zeros (e.g., creating a 5-digit number).
Add another 'Compose' action to concatenate the prefix, the year, and the sequential number. For example: P@{outputs('Compose')}@{body('Format_number')}.
Add an 'Update item' action to write the newly generated unique string back into the 'ProjectNumber' column of the SharePoint list.
Manage Project Documents Efficiently with WPS Office
While Power Automate and SharePoint handle list automation, you still need a reliable suite for your project documentation. WPS Office is a lightweight, highly compatible alternative to Microsoft Office, perfect for creating project proposals, reports, and spreadsheets without the heavy subscription costs.
- 1. Download and install: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Open your project files: Launch WPS Office and open your existing Microsoft Office project documents directly without any format conversion.
- 3. Edit and collaborate: Use the built-in Writer, Spreadsheet, and Presentation tools to manage your project workflows and save them in standard Office formats.
Frequently Asked Questions
Can I use a SharePoint Calculated Column instead of Power Automate for unique IDs?
While you can use a Calculated Column to append prefixes to the default SharePoint ID, it is not recommended for strict uniqueness. The ID is only assigned after the item is created, which can cause calculation delays or errors during concurrent entries.
How do I add leading zeros to a number in Power Automate?
You can format numbers with leading zeros using the formatNumber function or by using string manipulation expressions like substring(concat('00000', triggerOutputs()?['body/ID']), sub(length(concat('00000', triggerOutputs()?['body/ID'])), 5), 5).
Will the flow trigger an infinite loop when updating the SharePoint item?
It can, if not configured properly. To prevent an infinite loop, add trigger conditions to your flow settings so it only runs when the 'ProjectNumber' column is empty.
