Is there anyway to reset the default ID number that SharePoint gives to an item back to "1"?

2013-05-02

Question

_{Thursday, May 2, 2013 7:45 PM}

I created a list and added a couple test items to verify that everything was working correctly.

I'm using the ID number (which SharePoint automatically creates for every item in a list), so for example, SharePoint set the first test item to have an ID of "1" and the second to have an ID of "2."

I've deleted the two test items, but ID of next item will be "3."

Is there anyway to reset that counter back to "1" right before I take the list live--even if it's just a one time reset?

All replies (12)

_{Thursday, May 2, 2013 7:52 PM ✅Answered | 2 votes}

Hi mrthetooth,

Save the list as a template and restore it from there.

Every time you restore that list, the ID will start back at 1. If you want to restore it in the same site, then delete the existing list (after you save it list as a template) and then recreate it from the template.

Note: if you have custom permissions and/or lookup-fields in that list, you'll need to restore them.

HTH,

Chris

_{Thursday, May 2, 2013 7:49 PM | 1 vote}

Not without deleting the list and recreating it from scratch.

Paul Stork SharePoint Server MVP
Principal Architect: Blue Chip Consulting Group
Blog: http://dontpapanic.com/blog
Twitter: Follow @pstork
Please remember to mark your question as "answered" if this solves your problem.

_{Thursday, May 2, 2013 8:00 PM}

Thanks I figured the answer was pretty much "not without recreating the list."

It's strange, on that site I have the ability to delete the list, but not the ability to create a new list.

I guess as a terribly hacky solution, I could always create a calculated column that subtracts 2 from ID and use that calculated column instead.

_{Friday, May 3, 2013 2:07 AM | 1 vote}

You cannot reference the ID of a row for a newly inserted row in the calculated column. The ID does not yet exist when the calculation is performed.

Read "Using column references in a formula" in http://office.microsoft.com/en-us/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx

Thanks

_{Friday, May 3, 2013 5:14 PM}

Ah, good point senthilekn. I'll have to wrap the calculation in an IF formula, and the value will only be visible once the entry has been made.

It turns out that we'll also want the ID to be six digits, so I'll have to create two calculated columns after all.

ID subtract column:

=IF([ID]>0, [ID]-2,"")

Six-digit ID column:

=IF([ID subtract]>0, IF(LEN([ID subtract]) = 1, "00000"&[ID subtract], IF(LEN([ID subtract]) = 2, "0000"&[ID subtract],IF(LEN([ID subtract]) = 3, "000"&[ID subtract], IF(LEN([ID subtract]) = 4, "00"&[ID subtract], IF(LEN([ID subtract]) = 5, "0"&[ID subtract], [ID subtract]))))), "")

Wish I could think of a more graceful way to handle the six-digit column...

_{Friday, May 3, 2013 5:26 PM}

The one problem with that plan is that calculated columns are only processed when you create an item or update an item. So since the ID isn't there when the item is first created the calculated column would be '0'. It wouldn't update until the first time you edit and save it. Viewing it won't update the calculated column.

_{Friday, May 3, 2013 5:48 PM | 1 vote}

Workflow/Event receiver is the only way to achieve the functionality.

Use Item Added event if you are planning to use ID to create string to update in another field in the list for a newly added item.

Thanks

_{Friday, May 3, 2013 11:10 PM}

Workflow is a good idea, thanks for the suggestion.

_{Tuesday, December 30, 2014 3:28 PM | 1 vote}

Do it in the SQL DB??

From the backend content database we can reset this value for any list:

UPDATE <Content DB>.dbo.AllListsAux set NextAvailableId=1 where ListID='<GUID>'

Here you can see a table called AllListsAux. This list maintans Item count and Id details for all lists.

Please note we have to be very careful while doing any opertation in backend.

OR, yes, save as a template and recreate, although i personally do not like this option.

_{Wednesday, December 31, 2014 2:35 PM}

You should never update the database directly. Doing so will Void your service and support agreement with Microsoft.

_{Wednesday, July 1, 2015 7:25 PM}

Azonicds0,

you just saved my day!

Thank you!

Ricardo Fuentes

_{Thursday, July 2, 2015 6:45 AM}

You are welcome Ricardo! glad I could help.

Share via

Is there anyway to reset the default ID number that SharePoint gives to an item back to "1"?

Question

All replies (12)

Additional resources