Forum Discussion
Auto increasing reference number
Hello,
I need to set up my reference number column in my list so that it automatically increases every time I add a new list item.
The reference number needs to be the following format:
MI-TEC-23-0001
where MI-TEC- is text, 23 is the last two digits of the current year, and 0001 is a unique number which increases every time a new enquiry is added.
Is there a way to achieve this?
I thought I found a solution using PowerApps but unfortunately the reference number of an existing item is overwritten with an increasing reference number every time I go to edit it.
Any help would be greatly appreciated, thank you!
Hi sp16879,
you can use a combination of calculated columns and workflows. Here's a step-by-step approach to setting it up:
1. Create two columns in your SharePoint list:
- "Year": Set the data type as "Number" and set a default value to the formula `=YEAR([Created])`.
- "Sequence": Set the data type as "Number" and set the default value to 1.2. Create a calculated column named "Reference Number" with the following formula:
="MI-TEC-" & RIGHT(TEXT([Year],"00"),2) & "-" & TEXT([Sequence],"0000")This formula combines the static text "MI-TEC-", the last two digits of the "Year" column, and the "Sequence" column value formatted with leading zeros.
3. Save the list settings to apply the calculated column.
4. Create a SharePoint Designer workflow to increment the "Sequence" column every time a new item is added to the list. Here's an example:
- Set the workflow to trigger on item creation.
- Add an "Update List Item" action to the workflow.
- Set the "Field" to "Sequence" and the "Value" to "Current Item:Sequence" plus 1.
- Save and publish the workflow.Now, when you add a new item to the list, the "Sequence" column will automatically increment, and the "Reference Number" column will be updated accordingly with the format "MI-TEC-YY-XXXX", where YY represents the last two digits of the current year, and XXXX is the unique number.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
1 Reply
Hi sp16879,
you can use a combination of calculated columns and workflows. Here's a step-by-step approach to setting it up:
1. Create two columns in your SharePoint list:
- "Year": Set the data type as "Number" and set a default value to the formula `=YEAR([Created])`.
- "Sequence": Set the data type as "Number" and set the default value to 1.2. Create a calculated column named "Reference Number" with the following formula:
="MI-TEC-" & RIGHT(TEXT([Year],"00"),2) & "-" & TEXT([Sequence],"0000")This formula combines the static text "MI-TEC-", the last two digits of the "Year" column, and the "Sequence" column value formatted with leading zeros.
3. Save the list settings to apply the calculated column.
4. Create a SharePoint Designer workflow to increment the "Sequence" column every time a new item is added to the list. Here's an example:
- Set the workflow to trigger on item creation.
- Add an "Update List Item" action to the workflow.
- Set the "Field" to "Sequence" and the "Value" to "Current Item:Sequence" plus 1.
- Save and publish the workflow.Now, when you add a new item to the list, the "Sequence" column will automatically increment, and the "Reference Number" column will be updated accordingly with the format "MI-TEC-YY-XXXX", where YY represents the last two digits of the current year, and XXXX is the unique number.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic