3 level Cascading Dropdowns in SharePoint – Remove Duplicate Records

One of the most demanding requirements in recent times is to have the cascading dropdown columns in SharePoint. There are many useful articles available over Internet to achieve this functionality, each using different methodologies and technologies.

The one I prefer uses Jquery, thanks to the Marc D Anderson for this wonderful blog http://sympmarc.com/2009/07/19/cascading-dropdown-columns-in-a-sharepoint-form-part-2/

This is one of the easiest ways to implement it. However, I need something more than what is explained in the above blog.

Issue Description:

The above blog does explain how to get Cascading dropdowns work. It also highlights the 3 level cascading dropdowns, but what if the third dropdown values are duplicated that is if the first dropdown has Countries, the second has States and the third one holds the Cities. Now consider an instance where different Countries may have identical City Names then we get duplicate records in the Cities dropdown. So in very Simple words, all the dropdowns should contain unique values.


This is not a difficult task, we can get this done just by making little modifications to the existing SharePoint List and also to the Jquery provided by Marc Anderson. Lets walk through the steps to achieve this functionality:

1) Follow all the steps as per Marc Anderson’s blog (link provided above).
2) In the Cities List add one more Lookup Column named Country and look up the values from Countries List
3) When we add items to the Cities List along with the States also select Country values.
4) Now, we are done with the SharePoint List modifications, all we are left with is Jquery updates. Open the file “CascadingDropdowns.js” and look for the function “.SPServices.SPCascadeDropdowns” in this file

5) This function accepts many parameters, one of them is the CAML Query parameter. So we may feel that by adding the Query here we can filter based on first dropdown as well. But unfortunately the answer is NO as it doesn’t accept dynamic values in other words the value would be an hard coded value and hence we cannot pass the first dropdown value in this. The updated “CascadingDropdowns.js” file looks as below:

$(document).ready(function() {
relationshipList: “States”,
relationshipListParentColumn: “Country”,
relationshipListChildColumn: “Title”,
parentColumn: “Country”,
childColumn: “State”
relationshipList: “Cities”,
relationshipListParentColumn: “State”,
relationshipListChildColumn: “Title”,
parentColumn: “State”,
childColumn: “City”,
relationshipListSortColumn: “ID”,
CAMLQuery: ” < Eq >< FieldRef Name=’Country’/ ><Value Type=’Lookup’ >” +document.getElementById(‘ControlId‘).options[document.getElementById(‘ControlId‘).selectedIndex].text+ “< /Value >< /Eq >”
6) So lets modify the code from another file named “jquery.SPServices-0.5.4.min.js
7) Open the file and find for var R=”<Query><OrderBy> from here we find sequence of steps which builds query based on condition so just before the Where Condition ends we need to add one more criteria as below
if(U.CAMLQuery.length>0){R+= ” < Eq >< FieldRef Name=’Country’/ ><Value Type=’Lookup’ >” +document.getElementById(‘ControlId‘).options[document.getElementById(‘ControlId‘).selectedIndex].text+ “< /Value >< /Eq >”+”< /AND > “}
8) Save this file and we are all set to use the cascading dropdowns, with unique values, most importantly filters the third dropdown (Cities) values not just based on the second control (States) but also the first one (Countries)
9) If you wish to have single click on the lookup columns with more than 20 items then refer to previous blog

Note: This modifications are with respect to the files from Marc Anderson’s blog so its mandate that we have those files before making these changes.

Thank you for visiting my blog!
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to 3 level Cascading Dropdowns in SharePoint – Remove Duplicate Records

  1. I am not a developer, but have been able to get the cascading drop-downs working. I need to implement your solution for removing duplicates, however cannot work out the location to insert your code from your comment ‘just before the Where Condition ends’.

    I am using jquery.SPSERVICES-0.7.2.min.js. Would it be possible to advise exactly where to insert the code.

    Appreciate your help in advance.

    • Venkat Karri says:

      HI Jackmakesfive,

      I haven’t looked at the latest version “jquery.SPSERVICES-0.7.2.min.js”. As per the SPServices JQuery used earlier, please find for “var R=”” and from here check for the ending tag of “Where” and add the follow the steps discussed in the article.

      Not sure, if anything has changed for this logic in the latest SPServices, I’ll look into it and update the post accordingly, if required

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s