Formatted auto number in NewForm.aspx or EditForm.aspx using the itemID

Change log
November 24. 2013
Updated the code examples to support lists with folders. I have also added a code example for use with DFFS (update DFFS to 3.26 or above). You must update spjs-utility to the latest version.

This solution let you use the item ID in a calculated column as part of a formatted “autonumber”.

Please note that if you apply the number in NewForm you cannot guarantee that the ID it retrieves is correct because someone other than you can “steal” the ID in a scenario where two or more users submit to the same list or library at the exact same time. In this case you can end up with a duplicated ID.

This is the price you must pay to have this number added in NewForm. If you can wait to add the number in EditForm, you are guaranteed a correct result. You can also use the code in both NewForm AND EditForm to correct any wrong ID added in NewForm.

How to set it up

Add a new column of type “Single line of text”, and the name “_ID” to the list. Add a HTML form web part to the NewForm / EditForm of the list, and add the code supplied below.

Please note that the column “_ID” must be included in all content types as it must be present in the form. The code will hide the field from view.

NewForm.aspx (list)

This code kicks in when you hit the save button, the function “PreSaveItem” queries the current list for the last item added. It gets the item ID, and increment the number by 1, before writing it to “_ID”.

This _ID column can now be used in a calculated column to create a number format of your liking.

<script type="text/javascript" src="/Scripts/jquery-1.10.2.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript">

// spjs-utility.js version check
if(spjs.utility.version < 1.178){
	alert("You must upgrade spjs-utility.js to v1.178 or above.");
}

$(document).ready(function(){
	// Hide the field
	$("input[title='_ID']").parents("tr:first").hide();
});

function PreSaveItem(){	
	var listGuid, listBaseUrl, query, res;
	listGuid = _spPageContextInfo.pageListId;
	listBaseUrl = _spPageContextInfo.siteServerRelativeUrl !== "/" ? _spPageContextInfo.siteServerRelativeUrl : "";	
	query = "<Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where><OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
	res = spjs_QueryItems({"listName":listGuid,"listBaseUrl":listBaseUrl,"query":query,"viewFields":["ID"],"rowLimit":"1","scope":"Recursive"});
	if(res.count === 0){
		$("input[title='_ID']").val("1");
	}else{
		$("input[title='_ID']").val(parseInt(res.items[0].ID,10)+1);
	}
	return true;
}
</script>

The PreSaveItem function will automatically execute when the list item is saved, and it coexists with any instances of PreSaveAction.

Use this code with DFFS

If you want to use this code with Dynamic Forms for SharePoint you must do the following:

  • Update DFFS to v3.26 or above
  • Update spjs-utility.js to v1.178 or above
  • Replace the function PreSaveItem with this function:
    function dffs_PreSaveAction(){	
    	var listGuid, listBaseUrl, query, res;
    	listGuid = _spPageContextInfo.pageListId;
    	listBaseUrl = _spPageContextInfo.siteServerRelativeUrl !== "/" ? _spPageContextInfo.siteServerRelativeUrl : "";	
    	query = "";
    	res = spjs_QueryItems({"listName":listGuid,"listBaseUrl":listBaseUrl,"query":query,"viewFields":["ID"],"rowLimit":"1","scope":"Recursive"});
    	if(res.count === 0){
    		$("input[title='_ID']").val("1");
    	}else{
    		$("input[title='_ID']").val(parseInt(res.items[0].ID,10)+1);
    	}
    }
    

Please note that this code example is for SP2010 and 2013. If you are using SP2007 you must change the variables "listGuid" and "listBaseUrl" like this:

listGuid = "GUID or display name of the list";
listBaseUrl = L_Menu_BaseUrl;

You find jQuery here, and spjs-utility.js here.

EdiForm.aspx (list or document library)
<script type="text/javascript" src="/Scripts/jquery-1.10.2.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	// Hide the field
	$("input[title='_ID']").parents("tr:first").hide();
});

function PreSaveItem(){	
	$("input[title='_ID']").val(GetUrlKeyValue("ID"));
	return true;
}
</script>

This code pulls the ID from the URL and writes it to "_ID".

Use this code with DFFS

If you want to use this code with Dynamic Forms for SharePoint you must update DFFS to v3.26 or above, and replace the function PreSaveItem with this function:

function dffs_PreSaveAction(){	
	$("input[title='_ID']").val(GetUrlKeyValue("ID"));
}
Hide the field in DispForm.aspx (list or document library)
<script type="text/javascript" src="/Scripts/jquery-1.10.2.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	// Hide the field
	$("a[name='SPBookmark__ID']").parents("tr:first").hide();
});
</script>
Example

I have a choice column named "Category", with the following options:
A_Something
B_Something
C_Something

I then add a calculated column with this formula:

=LEFT(Category,1)&"-"&IF(VALUE(_ID)<10,"000"&_ID,IF(VALUE(_ID)<100,"00"&_ID,IF(VALUE(_ID)<1000,"0"&_ID,_ID)))

This code pads the number up to a four digits with leading 0 like this:
A-0001
A-0002
...
A-0012
A-0013
...
A-0123
A-0124
...
A-1234
A-1235

Let me know if you have any questions,
Alexander

18 thoughts on “Formatted auto number in NewForm.aspx or EditForm.aspx using the itemID”

  1. Hi
    to avoid duplicated ID values, I recommend to add a workflow to the list which will update the new column ( when a new item Is added ) with a value based on item’s real ID

  2. Hi Alexander,From NewForm.aspx, I’m trying to modify your javascript to display ID number + 1383 (this is for an incremental position number in a list)

    In your code, I tried changing the ELSE line from +1 to +1383 but it gave me number 1887 and the ID Column is 1066.
    So it didn’t add up at all.
    Then if I create another new item, I get 1887 again.
    If I change the +1 to a +5 the ID=1069 and the _ID column = 509.

    Can you please let me know what needs to be changed in your code for this to work?

      1. Hi, I did have it working originally but I edited your code and I don’t know what I did, but each time I create a new Item, the _ID column enters a value of 505.

        The result from Chrome javascript console for the Alert is: Uncaught Reference Error: res is not defined(anonymous function) @ VM1920:2InjectedScript._evaluateOn @ VM1919:883InjectedScript._evaluateAndWrap @ VM1919:816InjectedScript.evaluate @ VM1919:682

        I’m going to test it in a different list and see if I can get it working.

  3. Hi Alexander,
    Can I please get a slight adjustment to your code?
    I’m trying to append “CP-20140903-0” to the ID number, eg: “CP-20140903-0864” so the ID=864.
    I can use a calculated column but it’s for an existing list where the Title column needs to be populated with the new generated string rather than the user typing it in manually each time.
    I’m sure I need to add a line of code there and I could probably hack it over a couple days of change, save, refresh but if you could help me out, I would be much appreciated.
    Thanks.

    1. Hi,
      You can change this line:

      $("input[title='_ID']").val(GetUrlKeyValue("ID"));

      something like this:

      setFieldValue("Title","CP-20140903-0"+GetUrlKeyValue("ID"));

      You might want to dynamically pad the ID number by adding it to a variable and checking the length – something like this:

      var theID = GetUrlKeyValue("ID"); // This is a string
      switch(theID.length){
      case 1:
      theID = "000"+theID;
      break;
      case 2:
      theID = "00"+theID;
      break;
      case 3:
      theID = "0"+theID;
      break;
      }
      setFieldValue("Title","CP-20140903-"+theID);

      Alexander

  4. Thanks for replying to this Alexander.
    I used your code above but was getting an error:
    SCRIPT5007: The value of the property ‘setFieldValue’ is null or undefined, not a Function object.
    So I replaced setFieldValue line with:
    $(“input[title=’Title’]”).val(“CP-20140903-“+theID);
    And it works perfectly, many thanks for helping with this.

    1. I’m glad you figured it out. The setFieldValue function is part of the SPJS-utility.js script package so this must be referred in the CEWP – like in the code example in the top of this page.

      Please note that the Title attribute is different in SP2013 as it appends “Required field” if the field is set as required in the list settings.

      Alexander

  5. Hi Alexander,
    The above code works perfectly for EditForm but for NewForm, I’ve got the following working for the _ID column. (once I have it working for _ID, I’ll change it to Title column)
    var theID = parseInt(res.items[0].ID,10)+1;
    setFieldValue(“_ID”,”CP-MM-1409-” + theID);

    My big burning question is:
    How do I add or subject a number, from the ‘theID’ variable?
    eg: var newID = theID – 42
    So I’m subtracting 42 from the theID variable.
    If the ID column is 1052, the answer = 1010.
    I have many tried many variations, combinations and have no clue in how this is done.
    If I place an alert(“theID”); I just get an empty value returned.
    If it can’t be done, I’ll go with that but any ideas you can give would be great.
    Thanks again 🙂

    1. The variable “theID” is a string, and to properly add or subtract you need to change it to an integer:

      var theID = GetUrlKeyValue("ID"); // This is a string
      theID = parseInt(theID,10); // Now it's a number
      // To change it back to a string, use this code:
      theID = String(theID);

      Hope this helps,
      Alexander

  6. Love the scripts.
    I was able to get the script working in my environment. One change I would like to make is if there is a way to incorporate the Year in the calculated column:
    =LEFT(Category,1)&”-“&IF(VALUE(_ID)<10,"000"&_ID,IF(VALUE(_ID)<100,"00"&_ID,IF(VALUE(_ID)<1000,"0"&_ID,_ID)))
    The result I am looking for is Year-0001 example for the first entry it would be 2017-0001

    thanks
    Ray

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.