Remove Duplicates from cascade lookup column

  • 3 November 2016
  • 46 replies
  • 212 views

Badge +2

Scenario: We have two lookup column . Column A contains Country and Column B contains Sites.

Based on country selection sites will get filter out .

Issue: Since Sites contains duplicates value , in drop-down also it get visible.

Solution: 1. In nintex form , save client id for the column B dropdown in nintex form. In below code client id is "txtsite".

Then using $("select[id="+txtsite+"]>option") we get all list of values in dropdown .

On "Mousedown" event , create array and check each value of dropdown , if value is already in array remove value otherwise add value to array. Once all check has been done, assign back the array to control value.

 

<script type="text/javascript">

//<![CDATA[

 

NWF$(document).ready(function(){

    NWF$("#"+txtsite).on('mousedown',function(){

var usednames={};

$("select[id="+txtsite+"]>option").each(function(){

if(usednames[this.text]){

                $(this).remove();

            }

            else{

                usednames[this.text]=this.value;

            }

 

 });

 

 });

});//]]>

</script>

<script type="text/javascript" src="//code.jquery.com/jquery-1.11.0.min.js"></script>


46 replies

Badge +2

have you given client ID name for control you are using?are you using lookup column?

Badge +9

So love this small snippet, such a time saver for myself and now not having to explaining to the business they'll need to maintain multiple list!!!!

Hi Warwick,This code is not working for me. Can you please provide screeen shot if possible. Thank You.

Badge +9

Super simple, you must have missed a config step.

1. Paste this code in the Custom Javascript in settings

NWF$(document).ready(function(){

    NWF$("#"+txtsite).on('mousedown',function(){

var usednames={};

$("select[id="+txtsite+"]>option").each(function(){

if(usednames[this.text]){

                $(this).remove();

            }

            else{

                usednames[this.text]=this.value;

            }

 });

 });

});

2. Paste this code in the Javascript Links in settings

//code.jquery.com/jquery-1.11.0.min.js

3. Add a SharePoint List Lookup control and add the value txtSite to the javascript client ID in the control settings

Badge +5

This worked in the previous release.  We migrated to forms 2013 v2.10.0.0 and this no longer works.

Badge +5

I was able to get the mousedown to work by moving it out of the ready to the NWF.FormFiller.Events.RegisterAfterReady.

NWF.FormFiller.Events.RegisterAfterReady(function () {

     NWF$(document).ready(function(){  
          console.log(" in ready");         
     });
 
     console.log("outside of ready");

Add code HERE!

Also, I the JavaScript variable now contains the _hid pointing to the Input not the select control.  You have to remove the _hid from the JavaScript Variable to bind the mousedown function.

var myvariable_without_hid = myvariable.replace('_hid',"");
NWF$("#"+myvariable_without_hid).on('mousedown', function(){ DO SOMETHING });   

What I have also found is that this only works for Lookups that pull back non-filtered data.  If you filter the data the bind goes away.  Not sure what caused this to break the bind.

Hope this helps others.-JC

Badge +1

Hi, do you know if someone else could get this working with Forms 2013 ver 2.10.0.0? I tried this snippet with no luck.

Badge +3

It is not working for me. 

I have the lookup control and txtsite is added under javascript client ID

Badge +3

I am using lookup column and also Client ID. it is not working in Nintex Form 2010

Badge +3

Hi, it scares me that Nintex didnt create a checkbox in the control's settings "Remove duplicates" or similar for the dropdown control. 

The is a simple solution:

1 - Create a third list with the countries, you can call it "Countries":

France

Australia

Brazil

2 - In both lists "Country" and "Sites" you add a lookup column that will read from "Countries"

Than you apply the solution of cascading dropdowns from Emily Billing‌ Cascading drop downs and Nintex Forms for SharePoint 

cheers,

Renato

Badge +1

Thanks to JC,

That helped a bunch. Follow same steps as original solution but instead of using the original code, paste the following code into Custom JavaScript section:

NWF.FormFiller.Events.RegisterAfterReady(function(){
 NWF$(document).ready(function(){   
 });  

 var txtsite_var = txtsite.replace("_hid", "");
 NWF$("#"+txtsite_var).on('mousedown', function(){
 var usednames={};
 NWF$("select[id="+txtsite_var+"]>option").each(function(){
 if(usednames[this.text]){                
 NWF$(this).remove();           
 }           
 else{               
 usednames[this.text]=this.value;           
 }  

 });
 });
});

Thanks again, JC!

Badge +3

I am getting hung up on this part:  In nintex form , save client id for the column B dropdown in nintex form. In below code client id is "txtsite".

How do you save a client ID for the dropdown? I looked in the setting of the column B drop down, and I don't see an option to save the client ID. I am using Nintex Forms for Office 365. If anyone has step by step instructions, I would greatly appreciate it! Thanks! 

Badge +3

I should also add that this form is not within a workflow.. it's a form that initiates the workflow... probably why I can't save the client id..  

Badge +7

Automatically Remove Duplicates From All Drop-Downs on Page Load

Working off of what AS initially posted, and WW, JC, and NV have expanded upon above, I've put together the following process. It works to automatically remove all duplicates on all drop-downs with a specific class throughout the form on form load, thus no longer requiring mousedown, the jquery inclusion, or the need to call out each field in the code. 

Disclaimer: This has only been tested on SharePoint 2013 On-Prem with Nintex Forms version 2.11.1.10.

JavaScript

  1. Click the form's Settings button in the ribbon and expand the Advanced section.
  2. Expand the Custom JavaScript section and paste the following code into the the text field:
    NWF.FormFiller.Events.RegisterAfterReady(function(){
         $('.duplicates').each(function(){Update(this.id);});
    });
    function Update(control){
         var usednames={};
         $("select[id="+control+"]>option").each(function(){
              if(usednames[this.text]){$(this).remove();}           
              else{usednames[this.text]=this.value;}  
         });
    }‍‍‍‍‍‍‍‍‍‍
  3. It should look like this:


    Field(s)
  4. Now go to the individual drop-down field on the form and open its settings screen.
  5. Expand the Formatting section and enter "duplicates" into the Control Css class field (without the quotes)
    1. You can replace the "duplicates" term with anything you'd like (without spaces), but it must match the term in the second line of code in step 2: 
      $('.duplicates')
  6. Repeat steps 4-5 for each and every drop-down on the form that may return duplicates.

Explanation

The key to this enhancement is this line of code:

$('.duplicates').each(function(){Update(this.id);});

It searches the form for all controls with a class of 'duplicates' and runs the Update function for each control by passing through its ID. This means you can have as many fields on the form as you'd like, and as long as you add the specific class to each one, this line of code will find and run the code on them all.

The Update function is simply what AS initially posted (thanks AS), but by removing the mousedown trigger, this can now run immediately on form load using RegisterAfterReady (thanks JC).

Also, JC was correct about needing to remove the "_hid" from the ID when using mousedown so, keep that in mind if your solution requires that feature. Technically, you could add that line back in, and it wouldn't affect anything if the string isn't found.

I do want to mention that I did not test this on filtered data, as JC mentioned was an issue. I don't know if this solution will work in that scenario. If you have the chance to test this, please reply with your results.

Note

For those of you unfamiliar with JavaScript and how Nintex formats data within its fields, when you paste the code into the Custom JavaScript field, save it, and then go back in to view it, the code may look all run together like this:

This is okay. The code will still work so, don't feel you need to re-paste the code every time you go into look at the settings.

I hope this can help others, as the initial post and responses greatly helped me. Feel free to reply with any questions or comments, and if you find a way to make this even more efficient, please share it so we all may benefit.

Badge +3

Any options for people who are working with Nintex Forms outside of a workflow? 

Badge +7

I'm using this on a form that does not require a workflow. However, it should also work on a Start Form inside of a workflow. 

Badge +3

Sorry I should have added "responsive" form. 

Badge +7

Ah! Well, in that case, unfortunately, I do not have an answer for you. I haven't played with any responsive forms yet.

After a quick glance, it looks like we're not able to add custom JavaScript solutions to the responsive forms. Is that correct?

If it is, I suppose I would ask if the form absolutely has to be made in the responsive format. You could create it in the classic format, and then add in the additional device forms you need. That way you would have the option to add in this custom JavaScript solution.

Badge +7

Brittany Matheson‌, I found this post with a workaround that might help you out in regard to adding JavaScript to Responsive Designed forms: Unable to find the Custom Javascript text area in Nintex Responsive forms

Badge +3

Chad Davis‌ Thanks so much for this.  It "almost" worked like charm on my Nintex 2013 Classic form.  I say almost because it removed the duplicates, but also removed three names that otherwise appeared only one time in the list.  Any ideas on how to prevent it from purging the single occurrence list items?

Thanks in advance.  

Badge +7

That's very odd that it would be removing single occurrence items.


This portion of the code is specifically meant to only look for duplicates:

var usednames={};
$("select[id="+control+"]>option").each(function(){
     if(usednames[this.text]){$(this).remove();}
     else{usednames[this.text]=this.value;}
});

Line 1 declares the new empty array variable for the current lookup control.

Line 2 uses jQuery to find each of the <option> elements that are children of a <select> element with the ID of the current lookup control.

Line 3 checks the usednames array for a key equal to the text of the current <option> element. If it exists, it removes the <option> element. 

Line 4 is if it doesn't exist, at which point it adds the current <option> element text as a key in the array, and sets the <option> element's value as the key's value.

In a lookup control, the ID of the list item is the value so, in this example:

<select id="ctl00_PlaceHolderMain_formFiller_FormView_ctl90_ctl264_8bd97981_baab_4ea2_80dd_9c4fb78f09e5" 
          name="ctl00$PlaceHolderMain$formFiller$FormView$ctl90$ctl264$8bd97981_baab_4ea2_80dd_9c4fb78f09e5"
          class=" nf-client-control "
          data-use-attribute-as-value="data-nfChoiceValue"
          style
="width: 100%;"

          formcontrolid="56c123ab-c618-4e18-b039-443f3b2d5bbf">

     <option value="**SelectValue**" data-nfchoicevalue="">Select Score</option>
     <option value="52" data-nfchoicevalue="52;#High">High</option>
     <option value="53" data-nfchoicevalue="53;#Med">Med</option>
     <option value="54" data-nfchoicevalue="54;#Low">Low</option>
</select>

the usednames array will look like this:

{Select Score: "**SelectValue**", High: "52", Med: "53", Low: "54"}

They only way it would be removing an <option> element (one of the lookup values) is if it already exists in the usednames array.

I would suggest maybe adding these two lines

console.log(this.text + ": " + this.value);
console.log(usednames);

to your code just before the if statement, and then while previewing your form, press F12 or right click the form and select Inspect to view the console.

I use Chrome and it should look like this (with your values of course):

You'll notice the first line is the first <option> element's text and value from the HTML above.

The second line is an empty array because nothing has been added to it yet.

The third line is the second <option>, but now the fourth line shows the previous text and value stored in the array.

This continues for each <option> element.

The last array output doesn't show "Low: 54" because it's being added after this output.

By doing this, you should be able to see what the <option> element text and value is before it gets removed, and whether or not is already in the array variable.

Can you please try this and let me know what you find?

Also, it wouldn't hurt to post your code here to see if we can find anything that could be causing the issue.

Badge +3

Chad Davis‌:  Thanks so much.  Testing now with your adjustments.  But in re-examining this form, I think there may just be a problem with those 3 list items.  They don't seem to be pulling into the form with or without your code.  

More soon - and thanks again for the detailed response.

~ Rae Ann

Badge +7

I'm happy to help. When using a Lookup Control, you can add a filter to the control itself or simply reference a view already on the list. The view might be a simpler answer and easier to test, plus it's easier to modify in the future if needed. However, if you're already using that, maybe try the other option.

Badge +3

Chad Davis‌  OK, Added the console.log snippet and checked in Chrome - it's not your code, which will significantly improve the user experience for what I'm doing.  There must be something wrong with the 3 entries not getting pulled from the lookup list.

I'll see what offering I must make to the SharePoint gods to sort out the issue with those particular list items.

Thanks again!

Badge +7

Can you post the details of those three items, your filter, and any other details that might help determine the issue?

If you create a view equivalent to the filter in the Lookup Control, do those three items show up in the view?

If so, can you use that view in your Lookup Control to replace its filter?

Like this:

Set the Source view field to the name of your view, and set the filtering to Display all items. 

But if the items don't show in the view, they won't show in this Lookup Control.

Of course, if they do show up in the view, but not in the control, there's something else entirely happening, and I'm not sure what that would be.

Reply