How can I change the next auto ID field in a sharepoint list?

dan_glos
dan_glos used Ask the Experts™
on
I am entering some old data into a SharePoint list and I have the ID column visible in the default view.

As it is a new list I create testing data, I get ID 4...5...6..., etc. The new data I will input doesn't quite have ID's associated with it, but I would rather not start at about number 6, I would like the next ID to be 100, 101, 102, etc.

How can I bump the next ID without creating and deleting 95 more test items? Thanks :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Not without manipulating the DB directly which is a no-no. You have a couple choices (and I'm sure more, but I'm only doing 2):

1. Just add/delete the 95 more items.  You can use datasheet view to speed this process along.

2. Don't use the ID column for your views.  Hide it and create a new one and input whatever you like.  You can even setup an even handler to make it work as an autonumber for future entry if necessary.
hide the ID column from your view. Create a number column called projectID or whatever and make it a calculated column and in the value field put [ID]+95
or whatever the math is to get the starting number that you want

Author

Commented:
Those are both pretty naff solutions, is there anything more robust?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Hmmm, naff as in the British slang?

I'm not sure what extra robustness you're hoping for since you cannot change the item ID in any acceptable manner.


Yea maybe you could tell us what you would like to do then and we could provide an answer. You can't modify the ID column and stay within a supported environment. I provided you a very easy simple way of getting exactly what you wanted in a supported way without any extra work. Not really sure what more you are looking for
For PAQ quality I will point out that using the ID field in a calculated column is not recommended since the calculated field is generated before the ID is created.  If you really want to go this route (perpetually adding 95) then using an event handler or workflow would be required.
Actually GreatGerm the calculated column wouldn't be calculated until after the row is created the ID column is populated once the row is created and therefore there is no issue with the formula above. You could also do it with event handler or workflow but would be extra work to solve a simple problem.
Actually, this is a known issue and I'm not sure why you would suggest otherwise. A quick search of google or EE will show this issue and the workaround suggested.
http://www.google.com/m/search?oe=UTF-8&hl=en&q=sharepoint+calculated+column+Id

Author

Commented:
Apologies for not replying quicker.

In an effort to just get it working, I attempted to create a additional calculated column as suggested. This worked on existing items, but as the ID is assigned post-creation, it does not work for newly created items.

I will investigate that workaround or event handlers and reply asap.
Thanks as always TheLearnedOne.

I believe that 24904151 answered the question.
24904465 is an answer as well
I would not consider 24904465 due to the reasoning above.
but you would agree that the same theory works perfectly fine in a workflow or event handler no?
Meant to post screen shot with last question as I mocked up the example quick. I also use this on numerous lists.
ID-Number.JPG
Of course it works fine in an event handler or workflow as I suggested, but will now work correctly in a calculated column as you suggested and would not want the PAQ to event hint that a calculated column would be the solution.  
screen shot is showing it with a calculated column. I was also suggesting a workflow. You recommended manually adding 90+ more items and deleting them or an event handler although you didn't provide any detail as to how to create or use that event handler. I agree both of those would work as well.

the problem with a calculated column is solved with workflow or event handler however if items are in place all you have to do is add the calculated column with formula and it will show in the desired format.
Thanks CoolLeoMod,

Recommend 24904151 for reasons stated above.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial