How to auto-populate a list column with a sequential number?

Question

I am using a SharePoint list as a database for logging accidents/incidents at work. Each incident is assigned a number. As there are multiple people making use of this list, I'd really like the "Occurrence Number" column to auto-populate with the next sequential number. Below is an image of what it currently looks like. In this case, when a user selects "new item," the first field should automatically be "14-0002"

What I want to happen is that when people create "new item," the first field is already filled in with the next # like the pic below.

Is any of this even possible? I really appreciate any help I can get in this matter. It's a huge roadblock to my company moving forward with our use of SharePoint 2013.

Answer 1

You can do this in JavaScript by using the SPUtility.js library

PS, in this exemple I have used SharePoint 2010, but it's the same with SP 2013

As example I have created a simple list 'sequential number' with:

• Title (simple text).
• Occurrence # (simple text), this is our auto increment id

---

Every list has three default forms to add, display and edit items, we are going to edit this forms to achieve our goal.

Before beginning the modification, download the SPUtility.js and the JQuery than upload the two file to SharePoint for example on the Shared Documents Library.

Edit the ‘Default New Form’ and add a Content Editor Web part

Edit the Html content the web part and past the code below :

​<script src="/Shared%20Documents/jquery-1.10.2.min.js"></script>
<script src="/Shared%20Documents/sputility.min.js"></script>
<script>

    // Get the current Site
    var siteUrl = '/';

    function retrieveListItems() {

        var clientContext = new SP.ClientContext(siteUrl);
        // Get the liste instance
        var oList = clientContext.get_web().get_lists().getByTitle('sequential number');

        var camlQuery = new SP.CamlQuery();

        // Get only the last element
        camlQuery.set_viewXml('<View><Query><OrderBy><FieldRef Name=\'ID\' Ascending=\'False\' /></OrderBy></Query><RowLimit>1</RowLimit></View>');
        this.collListItem = oList.getItems(camlQuery);

        clientContext.load(collListItem);

        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));        

    }

    function onQuerySucceeded(sender, args) {

        var listItemInfo = '';

        var listItemEnumerator = collListItem.getEnumerator();

        while (listItemEnumerator.moveNext()) {
            var oListItem = listItemEnumerator.get_current();

            var listItemInfovalue = oListItem.get_item('Occurrence_x0020__x0023_');


            // Based in you request the id is : 14-00001

            // Split the first
            var res = listItemInfovalue.split("-");

            console.log(res[1]);

            // increment the index
            var newId = parseInt(res[1])+1;

            // create the new id
            SPUtility.GetSPField('Occurrence #').SetValue(res[0] + '-' + pad(newId, 5) );
        }

        console.log(listItemInfo.toString());
    }

    function onQueryFailed(sender, args) {
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace()); 
    }   


    // Create new id with fixed size : 
    // exp : 00001, 00001

    // num : is the number
    // size : is the number size
    function pad(num, size) {
        var s = num+"";
        while (s.length < size) s = "0" + s;
        return s;
    }


$(document).ready(function(){
    ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js"); 
    });

</script>

Now the result :

Answer 2

Usually the list item ID number is used to do this. I'm not sure how different this is in 2013, but 2010 you would need to edit the form used to add items to the list and add a data connection of type retrieve data from a list, select you list, then select the ID field.

Then create a rule to query for the data using the "Query for data" action and select the data query you just created, then set the value of your occurrence number using something like:

concat("14-", max(ID))

Which would give you 14-1 for the first document 14-10 for the tenth etc.

if you want the leading zero's (like we wanted on ours) the fastest "workaround" I found was to make 4 rules the first checking if max(ID) was less than 10, and adding "000" to the concat statement, then the next rule only running if max(ID) is less than 100 and adding "00" to the concat and so forth. Making sure that the box on the rule for "don't run remaining rules if the condition is met" is ticked. There may be a nicer way to do this but my hack job works for us.

Be aware there is no value for max(ID) is the list is empty and you'll get a NaN value the first time this is called, but it should work fine after that.

This link was listed in my favourites as the solution I used, but my IT department in their infinite wisdom have decided that WordPress is now a restricted site so I cannot check it:

http://claytoncobb.wordpress.com/2009/06/20/auto-generating-filenames-for-infopath-forms/

Answer 3

Another common way of doing it is to create a separate column called "OccurrenceID". Then have a workflow set the value based on a setting list value.

Create a separate custom list called "Occurrence Settings." Create a new column called Value (set to single line of text).

Create an item (Title: NextOccurenceID, Value: 1)

Create a sharepoint list workflow on the Occurrence list (not the settings list). Set the Occurrence ID field to the NextOccurrenceID. Then update the Settings list item to equal NextOccurrenceID + 1. Set it to trigger on Create only.

Then after the item is created, it will be assigned a specific identifier of your choosing, and auto-increment. The downside is the value is not set immediately, as per workflow design, it will occur at some point after the item is created.

Hope this helps. :)

Answer 4

sharepoint list items already have a unique id column! Its field name is called "ID".

based on this cant you use this number? or does it have to be in the format of "14-0002".

you can do it through code! you would need to create an event handler. It needs to be on the itemAdding not added event.... when the user goes to create a new item it will run the itemAdding code first. Within the code you need to run through the list and get the latest id and add in your id column the id code.... You cant directly pull the next id that is auto generated because it doesnt exist yet.

for me i wouldnt do that on the item added! but I would use the default id as its fine as it is! but if you need you could not show it on the creation part that the user can see when adding an item... rather you could use the item added event, this occures after the user has submitted the list item so the unique id already exists. Your unique id column would be hidden to adding view so they cant add anything to it but can view it when viewing the item... I would then use the properties varible to get the unique id from the id field that is auto generated and populate your unique column that uses the that id and add it to the list. You could get the last item in the list and get your unique id column and change the last value to the new val and update the list item.... or just have whatever logic you want to add to the id!

if programming through code is too much for you, you could use workflows to do what you wnat! its simple and quick

http://dlairman.wordpress.com/2011/01/10/add-a-unique-auto-incrementing-column-to-a-sharepoint-list/

Answer 5

You could also do a calculated column with the following formula:

="PROJ_ID_"&ID

Based on the row's ID number the result would be, for example the 8th row: PROJ_ID_8

Answer 6

It works fine. I did programatically in SPServices:

$(document).ready(function() {     
    var idquery=""; 
    $().SPServices({
        operation: "GetListItems", 
        async: false, 
        listName: "test", 
        CAMLQuery: idquery, 
        CAMLRowLimit: 1, 
        completefunc: function (xData, Status) 
        { 
            $(xData.responseXML).SPFilterNode("z:row").each(function() { //alert($(this).attr("ows_Title")); 
                var getvalue= parseInt($(this).attr("ows_Title"))+1;//Auto increment value $("input[title='TicketNumber']").val(getvalue);
            });
         } 
    });

1. get value one variable parseInt($(this).attr("ows_Title"))+1;
2. bind textbox TICKET Number

More Details