1

I am new to working with SharePoint list. Currently, I am tasked to create a unique Sample ID that will be created by concatenating multiple calculated fields in SharePoint list, but the catch is every year the number at the end needs to reset.

I am having trouble creating an auto incrementing field that resets every year.

Any thoughts? Any help would be greatly appreciated!

Example:

2020PI1
2020PI2
2021PI1
2021PI2

3 Answers 3

0

I'm a huge fan of Flow (PowerAutomate) so if you're up for it, you could:

  1. Trigger flow on list item creation
  2. Get list items & ID the highest value
  3. Use a compose or variable to pull in the current year
  4. Concat & update item

It's a little bit of parsing work, but shouldn't be a huge flow.

0

I assume using Power Automate will be more handy to you. Two flows will be enough handling you task. One is to fill in the counter column with a auto increment variable. One is to reset the counter once a year.

There is type of flow called schedule flow which you can easily set it to run once a year at a certain time so this part is very easy.

Reference:https://docs.microsoft.com/en-us/power-automate/run-scheduled-tasks

And for the auto incremental part, here is an example for you:https://powerusers.microsoft.com/t5/Connecting-To-Data/Flow-PowerAutomate-How-to-auto-increment-the-data-in-sharepoint/td-p/408144

-1

In order to get this to work, you will need to store two numbers:

  • The current value of the auto-increment number
  • What year that auto-increment number is from (because you will need to check - if it's a new year, you need to reset the auto-increment number)

The way that I have gotten this to work most effectively is by storing those numbers as properties on the property bag of the list where I am using those custom ID numbers, and generating the custom ID (and checking those numbers stored on the property bag) using an ItemAdded event receiver. (As this answer states, technically a SPList object does not have a Properties "property bag" property, but the SPFolder that is the SPList.RootFolder does.)

What is a property bag? It's a way we can store key/value pairs internally in SharePoint at various hierarchy levels (Web Application, Site Collection, Web, Folder, etc.), which would be perfect for storing a couple numbers we need to use in code / an internal process, which we do not necessarily want everyday users to see.

I mention all that because you have tagged your question with which implies that you are working with a version of SharePoint on-prem and can therefore use server-side C# code, which all of the above relies on.

If you are comfortable programming C# server-side SharePoint code, and can deploy said custom code to your SharePoint farm, I would look into that.

However, you also state that you are new to working with SharePoint lists, and have also tagged your question with , which leads me to believe you may not be comfortable writing a custom code solution in C#.

If this is the case, there is still a way to get this to work. Unfortunately you can't access any property bags from SharePoint workflows, so you won't be able to use the property bag on the list's root folder. You'll need to figure out some other way of storing those two numbers that you can access and write to from a workflow. Luckily, SharePoint gives you just such a mechanism: lists.

That's right, all you would need to do is create a custom list (perhaps make it hidden from the UI, so that regular users don't mess with it and accidentally edit the values), who's only reason for existence is to store the two numbers you need for your auto-numbering system. I would make the Title field optional, add a column called "Year" and a column called "CurrentID", and create a single list item, and set the "Year" to be this year, and the "CurrentID" to be 0 (zero). (You'll see why in a minute.)

Then set up your workflow to do this:

  • When an item is created, go to your ID numbering tracking list and get the single tracking item there (ID: 1), and get the stored values of the Year and CurrentID columns.
  • Figure out the year the current item (that was just created) was created.
  • If the created year is the same as the stored year, increment the the CurrentID by one and save that in a workflow variable (see - if we initially set that to 0, after incrementing on the first created item we end up with 1).
  • Save that new CurrentID from the workflow variable back to the tracking list.
  • Use the Year and the new incremented CurrentID workflow variable along with whatever other data you need from the current item to generate your custom ID, and set it on the current item.
  • If the created year is not the same as the stored year, increment the Year value, and set the workflow variable that would have been an incremented CurrentID to be 1.
  • Save both of those changed values back to the tracking list.
  • Use the incremented year, the new CurrentID value of 1, and whatever other data you need from the current item to generate your custom ID, and set it on the current item.

So all you need is a list with a single item that will keep getting updated with the "current year" (which will only change once per year) and the "current ID" (which will get updated with every new ID generated), and you can have an auto-incrementing number that resets on each new year.

13
  • You don't need to store the year - you can get that from Today(). You don't need to store the current ID, either - you can lookup the last one, for the current year, from the last item on the list, itself. If there are no items for the current year, ID = 1 (and do the prepending of the year and "PI" for proper syntax). This is more complicated than it needs to be and doesn't give the formulas needed for setting this up.
    – vapcguy
    Dec 26, 2023 at 16:50
  • @vapcguy What happens when the last item in the list gets accidentally deleted (let's say it was 2023PI34), then a new item is created and looks up what is at that point the last item (now 2023PI33), increments one to assign the new item 2023PI34, and then the item that was accidentally deleted gets restored? Now you have 2 items with the same ID of 2023PI34. You can't guarantee uniqueness that way, which is why you need to store the current incrementing ID somewhere and refer to that when assigning new IDs. Dec 27, 2023 at 21:19
  • Doesn't matter if the last one gets deleted because you're only setting this value when you're saving the item as a new item the first time. It won't ever change after that. But even in your scenario of restoring a deleted item-because it's a text field-the admin doing the recovery will know to just increment the value manually-based on the current last value on the list-or they can just manually update the one that was originally auto-assigned the 2023PI34 to whatever the current ID is at the time, instead. The last ID is always the last item on the list-whatever it happens to be at the time.
    – vapcguy
    Jan 3 at 16:04
  • First of all: "the admin doing the recovery will know to just increment the value manually". Will they, though? That's a huge assumption to make. What if the admin who was there when the system was built knows that, but after 4 years, they're gone and there's a new admin who doesn't know the implementation details of that particular list because they are responsible for 30+ site collections across 5 web applications? And it does matter if the last one is deleted, both in the case of accidental deletion or purposeful deletion. If it was accidental, what if the last item got deleted on... Jan 3 at 20:50
  • ...a Monday, and it was a busy week and 100 new items got added before someone realized they had to restore the item. Now the admin who supposedly "knows" they have to edit the IDs has to go through and re-increment the IDs of all 100 new items to keep them in order after re-inserting the recovered item. Unecessary work. And in the case of purposeful deletion - what if there are other lists that reference the IDs, and by deleting an item, it should invalidate any reference relationships? If the last item gets deleted, but a new item is created and gets dynamically assigned the same ID, then... Jan 3 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.