Forum Discussion
Create reference number in SharePoint lists with each new entry
Migrating an Excel table into SP lists, so I can existing reference numbering system, so with each new entry I need this to increase by one. Let's say the current ref number is R5687 how to I ensure...
Option 1: Using the SharePoint ID Column (Recommended)
The simplest approach is to use the built-in ID column and generate the reference number from it.
If your current reference number is R5687 and the target SharePoint list is empty, the first item created will have an ID of 1.
Create a Calculated Column called Reference Number and use the following formula:
="R" & (ID + 5687)
This will generate:
| ID | Reference Number |
|---|---|
| 1 | R5688 |
| 2 | R5689 |
| 3 | R5690 |
This approach is simple, reliable, and avoids duplicate numbers when multiple users create items at the same time.
Option 2: Power Automate (Most Flexible)
If you need more control over the numbering scheme, you can use Power Automate.
Create a flow that runs when a new item is created:
- Trigger: When an item is created
- Get the latest/highest reference number.
- Extract the numeric portion (e.g., 5687).
- Increment the value by 1.
- Update the new item with the generated reference number (e.g., R5688).
This option is useful when:
- The reference number must be independent of the SharePoint ID.
- The numbering sequence must continue across multiple lists.