cancel
Showing results for 
Search instead for 
Did you mean: 

Remove Duplicates from cascade lookup column

aartishaw
Nintex Newbie
12 44 12.1K

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>

Tags (1)
44 Comments
Not applicable

It does not work for me. it still showing duplicates. Can you please provide some screenshot. thanks

aartishaw
Nintex Newbie

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

Warwick
Nintex Newbie

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!!!!

reddy1
Nintex Newbie

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

Warwick
Nintex Newbie

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

joseph_cervi
Nintex Newbie

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

joseph_cervi
Nintex Newbie

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

gbaschkier
Nintex Newbie

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.

lotus222
Nintex Newbie

It is not working for me. 

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

lotus222
Nintex Newbie

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

renatois
Nintex Newbie

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

nvang1985
Nintex Newbie

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!

brittanyruth
Nintex Newbie

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! 

brittanyruth
Nintex Newbie

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..  

chaddavis
Nintex Newbie

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.

brittanyruth
Nintex Newbie

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

chaddavis
Nintex Newbie

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. 

brittanyruth
Nintex Newbie

Sorry I should have added "responsive" form. 

chaddavis
Nintex Newbie

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.

chaddavis
Nintex Newbie

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

karmaq
Nintex Newbie

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.  

chaddavis
Nintex Newbie

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.

karmaq
Nintex Newbie

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

chaddavis
Nintex Newbie

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.

karmaq
Nintex Newbie

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!

chaddavis
Nintex Newbie

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.

karmaq
Nintex Newbie

OK, 2 of the items were my fault - some days I'm a "genius."  The control was working as planned and filtering them out because I'd changed a setting while testing another workflow.  

But the last item is one I manually added to the list. It shows up in the Active Projects view on the list (which the control is being filtered by), but not as an option in the form.

Active View (that the Resource control is filtered through):

For good measure, sample project details.  All required items are filled out.
I

I wouldn't ordinarily worry about it, but once these items go live, I will still need to test things from time in case something breaks, and I will need to enter "dummy" data to do that so I don't affect live projects or the processes related to them.

chaddavis
Nintex Newbie

Unfortunately, I cannot think of any reason a value would be appearing in the view on the list, but not coming through in the List Lookup using that view. Perhaps you can try removing the view filter in the List Control, removing or commenting out the JavaScript, and simply have the List Lookup return absolutely everything just to see if it comes through without any filters anywhere. If it still doesn't come through, I'm really not sure what to tell you. However, if it does, then you know it's somehow being caught by a filter somewhere.

karmaq
Nintex Newbie

Thanks Chad.

I will try that and a few other things. Made no sense to me either. But at least the other part was a user error and not an actual problem.

Thanks again. The java script will help with this solution tremendously.

karmaq
Nintex Newbie

Solved the problem. To see all possible values in that field, I had to leave the Source View blank and have Filter set to Display all items.  Then, in the connected Project ID control, I can apply the Source view (to only show Active Projects) and have it filter based on the Resource control.

I'm guessing, I can either filter that control using java to remove the duplicates or via Source view.  But it doesn't like the combination, which is fine since the Source view can be applied to the Project ID control to produce the same effect:  Showing only Active project IDs for a specific person.

Thanks so much for all your help.

chaddavis
Nintex Newbie

I'm glad you solved the problem!

brittanyruth
Nintex Newbie

I'm guessing I missed a step in your solution (I switched to classic from responsive!). 

I added the script to the form settings:

Then I updated the control css class on the drop down(s):

Any idea what I am doing wrong? Duplicates continue to show up. 

chaddavis
Nintex Newbie

You'll want to put it in the Custom JavaScript section, not in the Advanced -> Custom JavaScript Includes section:

However, yours looks like it jumps from Advanced to Print to PDF. Do you have these other sections?

brittanyruth
Nintex Newbie

Ahh okay. Yes I put it in the correct place. But now it won't show anything. When I select Product, the other drop downs don't update. 

chaddavis
Nintex Newbie

I'd need a lot more information about how your form is setup. I don't know how your filters are setup, or how your drop downs are populated.

dnes24
Nintex Newbie

Great post..Chad Davis

I need a small help.

 

In my ListA, I have 3 lookup Columns coming from the same list.

Parent List Name: Location Details

Columns: Region, Country and City

 

I need a cascaded dropdown with these 3 fields on ListA.

Doesnt want to create multiple list to achieve it,

So I have applied filter on Country Dropdown to filter it by Region control and in the same way applied filter on City Dropdown to filter it by Country control.

 

Your code works perfect initially to remove duplicates on all these 3 dropdowns, but on change of Region, its filtering the values in Country and pulls all duplicate values again.

Could you please suggest a best way to filter it and remove the duplicates appropriately .

workflower
Nintex Newbie

Hello Davis 

Thank you for this nice snippet.

 

For me, it works only in the Form-Preview, when I save and publish the Form and create an New Item, the duplicate Entries are no removing.

 

Its very interesting that the Code snippet only worked in the Form-Preview Mode, do you have any idea what could be the issue?

chaddavis
Nintex Newbie

I apologize for not getting back to you sooner. I don't remember receiving a notification for your question.

To answer your question, since the lookup fields are being repopulated from each other, the code isn't being rerun each time. The code currently only runs on page load. If you'd like to have it run every time, you'll have to add in another type of listener.

I would suggest an on click or on change listener of some sort. I don't have time to create an example for you right now, but I will post one so it can help others too.

One really nice feature of the code is that it's in a function so, you can call it as many times and from as many different areas of your code as you need.

chaddavis
Nintex Newbie

I'll be honest, I'm stumped by this one. If it works in preview, it should work in the published form as well.

One suggestion you might try:

For some reason, I have had a couple situations where I had to replace each $ with NWF$ in the code. I have no idea if this is what's happening for you too, since in my situations it didn't work in the preview or publish. However, it's worth a try.

Aside from that, I would suggest posting your code and giving screenshots of your form, its controls, and maybe even the list to help troubleshoot.

Another suggestion would be to add the console.log code I suggested above. You could even tweak it to show additional information as the form loads. I find the console in Chrome to be a huge help in discovering why code isn't working as it should.

chanteldup
Nintex Newbie

Love it!! but sadly it does not work on Filtered lookup fields... 

workflower
Nintex Newbie

Hello Davis

Thanks for the Feedback, I found the solution.

To put NWF before the $ was one thing, but in my case I had a .js File linked with the Form. 

After I putted your code in the js File and corrected the $, the duplicates were gone .

Thank You!!

chaddavis
Nintex Newbie

I'm very glad to hear you were able to get it to work. 
In the past, I was able to use the $ by itself, but now it seems NWF$ has to replace it whether you use a Nintex JavaScript variable or not. I'm not sure if that has changed in a recent update.

chaddavis
Nintex Newbie

I'm surprised to hear it isn't working on filtered lookup fields. The code shouldn't care how the data gets populated to the control. It simply loops through whatever options it has, confirms each option doesn't already exist, and removes it if it does.

Can you add the console.log lines to the code that I suggested above? This should help you in debugging the issue.

Arianeb
Nintex Newbie

I can't get rid of the duplicates in a lookup dropdown field. Any help would be appreciated. I've added "duplicates" to control CSS class and the code into Custom Javascript in Settings.3.JPG2.JPG1.JPG