enforce unique value, but it triggers error message \Duplicate ID\" in the edit mode"


Badge +4

Hi everyone,

I would like to know how to set the "enforce unique value" rule apply to the new entry item only.

in my case, I created a list for everyone to come to log the bug they found, so, firstly,  I set the column to "enforce unique value" n SharePoint, the purpose is to prevent people to report the duplicate issue. It works in that way, but it fails in another side, which when someone want to open the item to give the feedback, it doesn't allow to save the answer, as the "enforce unique value" has set in one of the fields to prevent duplicate entry.

thus, I change the  "enforce unique value" to No on SharePoint, and edit the configuration on Nintex, added an extra rule as below, but, however, this makes it worse, the result go totally opposite, now the user can create new item with duplicate item ID, and can't open the item to edit or save the feedback due to "save" action will trigger duplicate checking....

185448_pastedImage_0.png185449_pastedImage_1.png

any idea?

Thanks,

Zoe


11 replies

Badge +16

Paul Crawford​ surely you have some javascript to the rescue here?

Userlevel 4
Badge +7

Hi Zoe

This is how i have got around this issue.

A bit of background:

We have a custom site provisioning process and we use a form to capture the information about the site that is to be created. In the process of entering the data, the form will check to ensure the site url does not already exist, prompting the user that this is the case so the url can be changed.

Firstly, add a calculated control on to your form and set the formula to Is New Mode:

Capture.PNG

Now you can check this value and ensure the code will only execute if its a new record. Add a Javascript Client ID of varFormMode.

Next, add a list lookup on to your form configure to look back into the list you are working on and set to return the column you wish to enforce unique values on. The list this form exist on is the Site Requests List (the list where i want to enforce unique values) and the List Column Name is the Site URL column (the column i want to enforce unique values on). In my case i added a javascript variable of siteRequestLookupWeb.

Capture.PNG

Now configure the column to enforce unique values on to use custom validation and set a javascript variable name

Capture.PNG

Now you can add the following javascript:

function ValidateSiteUrl(source, arguments) {     
     var valid = true;    
     //checks if new mode    
     var formMode = NWF$('#' + varFormMode).val();    
     if (formMode == "true") {        
          //gets site title and trims and sets to lowercase        
          var siteTitle = arguments.Value.trim().toLowerCase();        
          //removes spaces        
          siteTitle = siteTitle.replace(/s+/g, '');        
          var currSiteTitle;        
          //loops through each option in the list lookup        
          NWF$("#" + siteRequestLookupWeb + "> option").each(function () {            
               //gets option and trims and sets to lowercase            
               currSiteTitle = this.text.trim().toLowerCase();            
               //removes spaces            
               currSiteTitle = currSiteTitle.replace(/s+/g, '');            
               if (siteTitle == currSiteTitle) {                
                    alert('Site Url is already being used.');                
                    valid = false;            
               }        
          });        
          arguments.IsValid = valid;    
     }
}

This should now enforce unique values on your column

Let me know how you get on

Paul

Badge +4

Hi Paul,

sorry for my late reply. I follow your instruction to do the same setting, and go back to the list to manfully remove all the existing duplicated IDs, and then turn on the "enforce unique value" in the column setting again. Now, it works, thanks a lot!! happy.png

the only little problem is when someone enters duplicated ID and saved, the form will just closed without returning an error message. Even though I have done the following setting. Do I miss anything to enable the error message?

Thanks!

Zoe

185669_pastedImage_0.png

Badge +4

Hi Frank,

thanks for following up. it works well, just now I need to find the way to enable the error message to notice the user that the entry is failed due to it is a duplicated ID. more details can be found in my reply to Paul above, if you know any solution, please share with me, thanks a lot in advance!!

Zoe

Userlevel 4
Badge +7

Hi Zoe,

In my example, i didn't change the default settings on the column. All of the validation is handled through the javascript code. Can you try reverting the column settings to the default settings, to not enforce unique values, and try this again. if you could screenshot the settings on the controls as per my example above, the list lookup and the itemID single line textbox and the code you are using and i will see if i can find out what is not working.

Look forward to your reply

Paul

Badge +4

Hi Paul,

when I turn off "enforce unique values", and it can't work, so I tried to turn it on, and found it works but the problem is there is no error message.

here is my setting:

I found I missed on setting, which I don't know how to Add a Javascript Client ID of varFormMode.

185679_pastedImage_0.png

the rest steps should be the same....(I hope so...silly.png)

185680_pastedImage_1.png

185687_pastedImage_2.png

function ValidateItemID(source, arguments) {    

     var valid = true;    

     //checks if new mode    

     var formMode = NWF$('#' + varFormMode).val();    

     if (formMode == "true") {        

          //gets site title and trims and sets to lowercase        

          var itemID = arguments.Value.trim().toLowerCase();        

          //removes spaces        

          itemID = itemID.replace(/s+/g, '');        

          var curritemID;        

          //loops through each option in the list lookup        

          NWF$("#" + ReportContentIssueLookup + "> option").each(function () {            

               //gets option and trims and sets to lowercase            

               currItemID = this.text.trim().toLowerCase();            

               //removes spaces            

               currItemID = currItemID.replace(/s+/g, '');            

               if (itemID == currItemID) {                

                    alert('Site Url is already being used.');                

                    valid = false;            

               }        

          });        

          arguments.IsValid = valid;    

     }

}

Badge +4

and here is the place I add the JavaScript...hope this is right....

185690_pastedImage_0.png

Userlevel 4
Badge +7

Hi Zoe,

I think i see your issue. Under the appearance of the calculated control, can you check these are both set to yes. If visible is set to no you will not be able to store the Client ID in a JavaScript variable. (I will explain how we can hide it later).

I think the code is executing but if, as i think, the control is set to visible = no, this line of code:

if(formMode == "true") will never be true because the control is not storing the value so the rest of the code will be skipped.

Try this and let me know. (i would recommend changing the column settings to turn off enforce unique values)

To hide the calculated control, add a new CSS class to the calculated control as below:

Capture1.PNG

and then add the following CSS to the form settings

Capture.PNG

Hopefully this should sort your issue.

Let me know

Paul

Badge +4

Hi Paul,

I turn off the enforce unique values and do the same as you told, "nf-hidden" doesn't seem to work, other than that it works perfectly!!!

I have also created my own error message happy.png

185677_pastedImage_1.png

Can you tell me how can I make the "nf-hidden" works? Just type in "nf-hidden", or I need to select this function from the inline functions?

185696_pastedImage_2.png

Big Thanks!

Zoe

Badge +4

sorry!! I forgot to change the custom CSS! after edited, it is totally PERFECT now!!

million thanks!laugh.png

Badge +4

Thanks Frank! To be honest, so far, keep asking questions and mark the correct answer are the only two things I can do here...grin.png,  without you guys here to helping people like me who  has 0 background in IT and know only very little about coding, I believe I have already gave up on this. 

I appropriate everyone who come to help me or even just come to read my question and tell me that they are facing the same issue, either way encourage me to forward bit by bit and learn more.

Cheers,

Zoe

Reply