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.

Solution:

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 Countries and look up the values from Country 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() {

$().SPServices.SPCascadeDropdowns({

relationshipList: “States”,

relationshipListParentColumn: “Country”,

relationshipListChildColumn: “Title”,

parentColumn: “Country”,

childColumn: “State”

});

$().SPServices.SPCascadeDropdowns({

relationshipList: “Cities”,

relationshipListParentColumn: “State”,

relationshipListChildColumn: “Title”,

parentColumn: “State”,

childColumn: “City”,

relationshipListSortColumn: “ID”,

CAMLQuery: " < Eq >< FieldRef Name='Countries'/ ><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='Countries'/ ><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 visiting my blog!