Autonumber list items with DFFS

[avala]

Curious if creating a auto number feature in DFFS is something you’d be interested in. We currently use a Nintex workflow to query a list and auto-increment list items. Typical naming conventions start at FDC 1,001, FDC 1,002, etc to properly sort in a SharePoint list.

[Alexander Bautz]

Hi,
This is no problem to make as a custom function in DFFS, but doing this in JavaScript means it will not trigger if you use datasheet view to create these items.

Also, using the list ID as basis for the number when using JavaScript means it cannot be properly numbered from NewForm as the ID has not been set yet. The code could be applied successfully in EditForm though.

Is the only purpose to properly sort the items ascending or descending in a list view, or do you plan to use the number as an ID for the items?

Alexander

[avala]

Thanks for looking into this.

We’re not worried about the datasheet view as we would direct users to the Newform directly.

We don’t have a need to reference the SP ID specifically, but if we needed to we could redirect from the NewForm to the EditForm.

The purpose of this feautre is twofold. One, sequence a form request in sequence without numerical gaps. The SP ID creates gaps when items are created during testing, maintenance, etc. Secondly, we need to sort the list without SharePoint’s quirky numbering system in a list view.

• This reply was modified 7 years, 8 months ago by avala.

[Alexander Bautz]

If you only need a number to sort the list items by, you can create a single line of text field in your list, and use this code to fill it (in Custom JS):

setFieldValue("The_Name_Of_Your_Column",new Date().valueOf());

If you want a running number, you can use something like this to read the ItemCount from the list schema:

function getItemCount(a){
	if(a === undefined){
		a = _spPageContextInfo.pageListId;
	}
	var b = [], iCount = "-1";
	b.push('<GetList xmlns="http://schemas.microsoft.com/sharepoint/soap/">');
	b.push('<listName>'+a+'</listName>');
	b.push('</GetList>');		
	spjs.utility.wrapSoap(_spPageContextInfo.webServerRelativeUrl + '/_vti_bin/lists.asmx', 'http://schemas.microsoft.com/sharepoint/soap/GetList', b.join(''), function(data){
		iCount = Number(jQuery(data).find("List").attr("ItemCount"));
	},function(err){
		//console.log(err);
	});
	return iCount;
}

function dffs_PreSaveAction(){
    var currItemCount = getItemCount();
    var nextNum = currItemCount + 1;
    setFieldValue("RunningNumber",nextNum);
}

Put the code in the custom js and change the field “RunningNumber” to your field.

You can call the “getItemCount” function with a list GUID or display name, but if you omit this parameter the current list is used.

I’m not sure how this will behave with heavy load on the list – the ItemCount property might have a delayed update, but I’m not sure if this is the case.

Please note that this will only take the total number of items in the list. If you add a list item that gets the number 100, and then go to delete one previous item form the list, the next item added will get a duplicate value of 100 as the total number of items in the list is still 100.

Hope you can use this code example.

Best regards,
Alexander

[MikeS]

Alexander,

How would you go about using a DFFS Rule to auto-increment a sequential number not based on the ID? The numbers should appear as follows (leading zeros based on next number to be used). This New Form rule would be triggered by membership in a SharePoint Permission Group. This allows other list adds to bypass this rule (dual function list).

ROM-0001
….
ROM-0099
….
ROM-0100
….
ROM-1000
….
ROM-9999
etc.

Solutions I have used in the past involve a SPD WF and an external list that maintains the Last Number so as to assign the Next Number. This is not optimal for this context. Hopefully DFFS or a JS solution might be useful.

Thanks
Mike

[Alexander Bautz]

Using a separate list to keep track of the number series is not a great idea if you ask me. I have tried this in the past with varying results.

If you for example add an item in quick-edit it will not get an ID, and if you have two or more users saving a new item at the same time, you might get a save conflict in the number-tracker-list, or you might get duplicate numbers because both users query the list to get the next number at the same time and therefore get the same number.

Not to forget two extra queries to the server to get the next ID and then to save the new next ID.

I would recommend using any other method – either a random unique number, or maybe a timestamp of some sort – for example like this:

function getNumber(prefix){
    var now = new Date();
    var num = String(now.getFullYear()).substring(2) + String(now.getMonth() + 1).padStart(2,"0") + String(now.getDate()).padStart(2,"0")+ String(now.getHours()) + String(now.getMinutes()) + String(now.getSeconds() + String(now.getMilliseconds()));
    return prefix + num;
}

This code will build a number using year-month-day-hour-minute-second-millisecond.

Call it like this with the prefix you want to use:

var number = getNumber("ROM-");
// This will return a string like this: ROM-20080716529661

And you can set a field in this item like this:

setFieldValue("Your_number_field", number);

Please note that this will not work in quick-edit.

Alexander

[MikeS]

How would I go about sequentially numbering (starting at ROM-0001 and correctly inserting leading zeros up to ROM-9999) regardless of a list item being deleted? Seems like I would need to keep an external count somewhere or run into the problem you noted in your August 9, 2016, post above.

Thanks
Mike

[Alexander Bautz]

You can do it like this.

1. Add a list named “DFFS_Autonumber_master” (you can change this, but must also change the code snippet below).
2. Add one list item to this list and type in 0 in the Title filed.
3. Add a single line of text field named “SequentialNumber” to the current list (not the one you created above).
4. Add the below function to the Custom JS in your current list (not the one you created above) NewForm

function dffs_PreSaveAction() {
    var item = spjs.utility.getItemByID({
        "listName": "DFFS_Autonumber_master",
        "id": "1", // this is the list item id of the placeholder for the counter
        "viewFields": ["Title"]
    });
    if (item !== null) {
        var currNum = Number(item.Title);
        var nextNum = currNum += 1;
        // update counter item withe new number
        var res = spjs.utility.updateItem({
            "listName": "DFFS_Autonumber_master",
            "id": "1",
            "data": {"Title": nextNum}
        });
        if(res.success){
            // Write number to field 
            setFieldValue("SequentialNumber", "ROM-" + String(nextNum).padStart(4,"0"));
        }else{
            alert("Error updating the DFFS_Autonumber_master list: " + res.errorText);
            // Prevent save
            return false;
        }
    } else {
        alert("No item with ID 1 was found in the list DFFS_Autonumber_master. Please add one list item and type in 0 in the Title field to get started.");
    }
    return true;
}

Please note all the possible pitfalls mentioned in my previous post so you know the risks of doing it like this.

Alexander

• This reply was modified 3 years, 8 months ago by Alexander Bautz.

[MikeS]

Works great Alexander! Thank you.
Mike

[Author]

Posts