17

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" enter image description here

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. enter image description here

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.

9

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.

enter image description here

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

enter image description here

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 :

enter image description here

  • Woohoo! Happy to see my library being put to good use. Since there is a small risk that people could be filling out the form at the same time, I chose to lookup to the next ID using PreSaveAction. However, it gets a lot more complicated with the async calls so what you have here works great too. – Kit Menke Mar 14 '14 at 13:47
5

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/

  • 1
    I wish I could mark this as an answer, it helped me a lot and it doesn't involve coding, just by using OOTB functionality, up you go. – Yugo Aug 24 '17 at 20:47
1

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. :)

0

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/

  • But how do you show the ID column. I cannot find where to add it to the custom list. – zeeple Jul 16 '15 at 5:58
  • look in the list configuration. then once in the list settings page update the current view which is usually the default view where you can specify to show the ID column. – Ali Jafer Jul 16 '15 at 7:04
0

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

0

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

protected by Community Oct 19 '17 at 6:00

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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