NewForm.aspx filter Column based on another column -Cascade Drop Down using Content Editor Web Part and JavaScript.[Like category - sub category]

Modify NewForm.aspx to filter one SharePoint Column based on another column -Cascade Drop Down using Content Editor Web Part and JavaScript.[Like category - sub category/State - City]


Step 1: Kindly follow my intital post to understand how java script can be used in Content Editor Web Part.

Step 2: Here we will be using State -City relationship. City will be filtered based on State.

Step 3: Create 3 lists.

List called "State" : Custom list with a column "Title" with all states.

List called "CityState" : Custom list with a column 'City' and one for its State. State Column will be lookup to the previous list "State". Here I have renamed "Title" column as "City".

 City will hold column "City" information. State will be a dropdown that loads as a lookup.

List called "Data" : Custom list with a column to have Company name, a column for State[ lookup to the column "Title" in first list "State"], a column for city[ lookup to "City" column of second list "CityState"]

Now State is a lookup column and brings all the value from the first list "State".

Now City is a lookup column and brings all the value from the column "City" in the list "CityState" . So far no filtering is happening.

Step 3: Edit the page. You can modify the URL to edit NewForm.aspx. To the end of the URL append PageView=Shared&ToolPaneView=2. This will open the form in edit mode.

your URL must be like this http://sample.com/sites/MySPSite/Lists/Data/NewForm.aspx?PageView=Shared&ToolPaneView=2

Step 4: Add Content Editor Web Part.

Step 5: Write the following code in a text. save as html. upload into SharePoint.Link this html page to content editor web part or Write code in Web Part's text editor directly. SPSErvices JS File  helps us with the SPCascadeDropDown function.

<SCRIPT type=text/javascript src="http://mysite.com/sites/MySite/Documents/jquery-1.4.2.min.js"></SCRIPT>
<SCRIPT type=text/javascript src="http://mysite.com/sites/MySite/Documents/jquery.SPServices-0.5.6.min.js"></SCRIPT>
<SCRIPT type=text/javascript>

$(document).ready(function(){

        $().SPServices.SPCascadeDropdowns({
        relationshipList:"CityState",
        relationshipListParentColumn:"State",
        relationshipListChildColumn:"Title",
        parentColumn:"State",
        childColumn:"City"
      });
  
});

</SCRIPT>

Step 6: After the content editor web part is saved and you exit the page from edit mode, City gets filtered based on the State.

Step 7: To remove the identity that the code was generated from Content Editor Web Part, within the Web Part Setting, Under Appearance, For Chrome Type select "None". Click OK. Click Apply. Publish the Page.

Step 8: Now instead of showing all cities depending upon the selected state, you need to show only a particular set of cities based on a CAML query, then that also can be achieved.

Step 9: Create a column of type "Yes\No" in second list "CityData" called "Visible".

Step 10: Now add the CAML query to the SPServices.SPCascadeDropDown function. Notice the comma after the ChildColumn.

 $(document).ready(function(){

        $().SPServices.SPCascadeDropdowns({
        relationshipList:"CityState",
        relationshipListParentColumn:"State",
        relationshipListChildColumn:"Title",
        parentColumn:"State",
        childColumn:"City",
        CAMLQuery: "<Eq><FieldRef Name='Visible' /><Value Type='Boolean'>1</Value></Eq>"
      
      });
  
});

Step 11:  Now for a selected State, Its relevant Cities with Visible ="True" only gets displayed.

Leave your comments below.