Compare number against previous entries

Forms SharePoint Online
1

Hi,

I've been asked to generate a unique 4-digit code (separate to the ID) for entries in a list.

I can generate the number, but is there a quick and easy way to check against previous entries to ensure that it is unique?

Regards
Nick

2

Dear @Nick.Jones,
Two options, really. You can simply go to List Settings, select your column and set Enforce unique values option as true:
image

Or you can get List items with pnpjs and filter them by the value - Unable to get List Items with CAML Query - #2 by mnikitina

1 Like
3

Hi Nikita ,

I've implemented the pnp version:
pnp.sp.web.lists.getByTitle("RandomNumber").items.select("Title").filter("Random1 eq " + RNumber).getAll().then(function(allItems){...}

It worked fine, so I tried to put it in a do while loop, while the random number generated is found in previous entries, generate a new one.

do{
    (generate random number RNumber)
    pnp.sp.web.lists.getByTitle("RandomNumber").items.select("Title").filter("Random1 eq " + RNumber).getAll().then(function(allItems){
        if (allItems == "") {
            checkPrev = 0;
        }else{
            checkPrev = 1;
        }
    });
}while (checkPrev>0);

But the pnp line fails to run, it just endlessly loops through the random number generation. Do I need to include some kind of "if ready" for the pnp line?

Thanks
Nick

4

Dear @Nick.Jones,
This is a bit tricky. I would recommend assigning a unique random number each time before save and using Enforce unique value setting:
image

In order to ensure the numbers don't repeat, you can use something like the current time, which will never repeat:

  fd.spBeforeSave(function(){
    fd.field('Title').value = new Date().getTime();
  });

Or you can generate a GUID - javascript - How to create a GUID / UUID - Stack Overflow

5

Hi @Nikita_Kurguzov ,

Generating the number isn't a problem, but it is only 4 digits, so over a year there's likely to be a repeat.

OK, I'll try the enforce unique values method - that only gets checked on save, is that right?

Is there a way to check for the "value already exists" error and automatically run the number generation again?

Thanks
Nick

6

Dear @Nick.Jones,
Here, use this:

fd.spRendered(function () {
    generateUniqueNumber();
    function generateUniqueNumber(){
      var RNumber = Math.floor(1000 + Math.random() * 9000);
      pnp.sp.web.lists.getByTitle('RandomNumber').items.select('Title').filter('Random1 eq ' + RNumber).getAll().then(function(allItems){
            if (allItems.length == 0) {
                fd.field('Random1').value = RNumber;
            }else{
                generateUniqueNumber();
            }
        });
    }
});
1 Like
7

Hi @Nikita_Kurguzov ,

Thanks - that worked perfectly! :smile:

Regards
Nick