cancel
Showing results for 
Search instead for 
Did you mean: 

Ensure Unique Reference On New Item

paul_crawford
Nintex Newbie
3 9 3,542

Prompted by Cassy Freeman‌ and this question raised by Chintan Desai‌, 2 workflow instance executes at same time‌, I thought I would share how I got around this very problem.

My scenario is slightly different as I wanted the new reference number displayed on the new form for users info even before the record saved. Initially i thought us JavaScript to get the next number from the GenerateID list and then use workflow on save to increment the next number.

This actually made Murphy's law (read the rest of the comments on the question!!) more likely to happen which was pointed out to me by Cassy Freeman‌ in a workshop so i had to come up with something else.

Solution:

When the new form is loaded use Javascript to get the next number and then increment by one and then write back to the GenerateID list immediately. This will ensure that the next number will be different and therefore no chance of Murphy getting his way!!!

The fun part:

Title field set to have a Client ID JavaScipt variable of varReference.

Title field has a rule on it to be disable when populated using the formula not(isNullOrEmpty({Self})).

Second field on the form set to have a Client ID JavaScipt variable of varSecondField. (Change as required)

The really fun part (!?!?!):

Custom JavaScript to do the rest.

//Ensure form is initialised
NWF.FormFiller.Events.RegisterAfterReady(function () {
    NWF$(document).ready(function () {
        //Ensure required files loaded
        ExecuteOrDelayUntilScriptLoaded(GetNextNumber, 'sp.js');
    });
});

function GetNextNumber() {
    //Get reference value
    var reference = NWF$('#' + varReference).val();
    //Ensure current reference is empty (new item)
    if (reference == '') {
        jQuery('#errorMsg').empty();
        jQuery('#outputData').empty();
        //Get Next Number from GenerateID list
        var requestUri = _spPageContextInfo.webAbsoluteUrl + '/_api/Web/Lists/getByTitle(\'GenerateID\')/items?$select=Id,Title';
        try {
            jQuery.ajax({
                url: requestUri,
                type: 'GET',
                headers: { 'ACCEPT': 'application/json;odata=verbose' },
                success: ReferenceGetSuccess,
                error: ReferenceGetError
            });
        }
        catch (err) {
            jQuery('#errorMsg').html('getListData Error: ' + err);
        }
    }
}

function ReferenceGetSuccess(data) {
    //Ensure data returned
    if (data.d.results.length > 0) {
        //Update Title column to the Next Number
        NWF$('#' + varReference).val(data.d.results[0].Title);
        //Give Title column focus
        NWF$('#' + varReference).focus();
        //Give second field focus to invoke the rule on the Title field to disable if populated
        NWF$('#' + varSecondField).focus();
        //Convert next number value to a number
        var nextNumber = parseInt(data.d.results[0].Title);
        //Increment the number by 1
        nextNumber = nextNumber + 1;
        //Get current context
        var ctx = new SP.ClientContext(_spPageContextInfo.webAbsoluteUrl);
        //Get the GenerateID list
        var oList = ctx.get_web().get_lists().getByTitle('GenerateID');
        //Get the item by id
        var oListItem = oList.getItemById(1);
        //Udpdate the item to the incemented number
        oListItem.set_item('Title', nextNumber);
        //Commit the update
        oListItem.update();
        ctx.executeQueryAsync(Function.createDelegate(this, onQuerySucceeded), Function.createDelegate(this, onQueryFailed));
    }
}

function ReferenceGetError() {
    alert('Failed to load Lookup Items');
}

function onQuerySucceeded() {
    //Do post processing if required
}

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

Now you have an immediately incrementing number so duplicates can be avoided.

Video: Writing Custom JavaScript In Nintex Forms for SharePoint

Hope this helps.

9 Comments
emha
Nintex Newbie

Hi Paul Crawford‌,

nice work out.

one basic question, though.

let's have current value of counter in GenerateID list to be 1.

let's have 3 users/clients, that each opens 3 forms that queries for unique reference number as you described, in parallel at about the same time.

what will be actual counter's value in GenerateID list after they are done with their work?

paul_crawford
Nintex Newbie

Hi

The counter would be set to 4.

User 1: query = 1, update = 2

User 2: query = 2, update = 3

User 3: query = 3 update = 4

Next user therefore, query = 4

emha
Nintex Newbie

and if each user opened 3 forms for himself? (as I've originally asked)

paul_crawford
Nintex Newbie

Apologies, misunderstood your question

The counter would be set to 10

User 1: query = 1, update = 2

User 1: query = 2, update = 3

User 1: query = 3 update = 4

User 2: query = 4, update = 5

User 2: query = 5, update = 6

User 2: query = 6 update = 7

User 3: query = 7, update = 8

User 3: query = 8, update = 9

User 3: query = 9 update = 10

I have had the forms open on my computer and get the above experience.

If you are suggesting that this could still create duplicates then potentially yes, if the update has not completed before the next query, but its the best way i have found of implementing this solution and in all the applications in my business that use this, i have not experienced a single duplicate.

emha
Nintex Newbie

yes, this is exactly where I was heading to!.

your solution doesn't deal with concurrency anyhow, so can not avoid duplicates.

I believe it might be sufficient solution in environments/applications with no or low level of concurrency.

but it can not be taken as reliable source of unique identifiers in general at all!

btw, you have a typo on line 49 in list name

paul_crawford
Nintex Newbie

Thanks, i have updated the typo.

Hopefully my solution can help someone. If it doesn't fit the requirement then there are other ways.

mlauer
Nintex Newbie

I agree with Marian Hatala. The solution from Paul Crawford  can't guarantee generating of unique IDs, duplicates are possible! There is no usage of a semaphore to control access to the critical common resource.

You can get a unique ID by creating a new list item and using list item ID not by updating a counter in a list item.

By creating a new list item you get a 1-based integer ID, which is one greater than the ID of the item that was previously added. If the item is deleted, its ID is not reused (see https://msdn.microsoft.com/EN-US/library/microsoft.sharepoint.splistitem.id.aspx ).

Kind regards

Manfred

MegaJerk
Nintex Newbie

I made a post about that very thing over here   

I don't know if it'll help, but it might. 

prashanth_hamse
Nintex Newbie

I am also trying to utlize some concepts from what paul has mentioned but we have different requirement , auto generate unique number & increment by 100 and store as string with 5 digits start from 00100 , Example : 00100 , 00200, 00300,

Hopefully we use javascript function to retrieve last Unique_ID = 00100 and increment!