Update: 27/06/2017
To know about implementing Cascading drop down or filtered lookup in SharePoint 2013 or SP Online using REST API, read this article: Cascading drop down in SharePoint using REST APIUpdate: 06/07/2017
To know about implementing multi-value lookup column and filtering values, read this article: Multi value Lookup Columns in SharePointTo know about the Basics
of Lookup Columns read this article.Also, to know about the Enhancements
of Lookup columns in SharePoint 2010
read this article.
Problem:
In
SharePoint the cascading or the filtered values in the lookup columns are not
present by default.
In this
article, we will achieve this using the simple JavaScript.
Solution:
Consider
the simple scenario that the user needs to input the values of Continent,
Country, State and City which has to be the filtered values.
Steps:
1. Create
four lists with the following column and corresponding types
Note:
I haven’t used the default title column for any of the lists above
and I have hide it from the default view.
To know how
to hide the default title column from the list read this article.
2. The
screen shots of the four custom lists created in the step 1 are shown below:
3. Now, we
will create another custom list for testing the cascading dropdown in the
SharePoint. Create a custom list with the following column and corresponding
types
The screen
shot of the created list is shown below:
4. When we
click on the dropdowns in continent, country, state or city we will get the
unfiltered values as shown below:
5. Now we
add the java script to achieve the solution
General Code to be added:
1
2
3
4
5
6
7
8
9
10
| // Execute the following JavaScript after the page has fully loaded, when it's ".ready"
$(document).ready(function(){
$().SPServices.SPCascadeDropdowns({
relationshipList: "Display Name of Master List",
relationshipListParentColumn: "Parent Column Internal Name from Master List",
relationshipListChildColumn: "Child Column Internal Name from Master List",
parentColumn: "Parent Column Display Name from List/Library",
childColumn: "Child Column Display Name from List/Library"
});
});
|
Description
of the code
Note:
To know how to get the Internal Name of a column in a list in SharePoint, read this article.
Download of
JQuery referred in the code can be found from jquery.com and codeplex.com
For ease of
download for the readers, I have uploaded the script in the below links. Click to download the jquery-1.4.2.min.js and jquery.SPServices-0.5.3
min.js
Code in our case:
6. Now, we will insert this JQuery and achieve the cascading dropdown.
7. Click on New in the created custom list (Cascading Lookup Demo) as shown below:
8. Now, we
will insert a Content Editor Web Part in the
newform.aspx to place the JavaScript code in
it.
To know how to insert
a web part in the NewForm.aspx without using SharePoint Designer read
this article.
9. After
inserting the web part, paste the code and save the web part.
Note:
Place Content Editor Web Part below the list form
or else the Java Script added won’t work.
10. We are
done! Now, we will check the cascading drop down. So, when the continent is selected only
countries with respect to the continent should be displayed (Filtered
values) and then the state, city in the same
case.
The results
of various scenarios are shown below:
I have
checked for the empty values scenario also. What if the child column is empty or does not contain values. Then after
inserting the value, it should add empty values in the column as shown below:
After inserting the values the list is as follows: 
Advantages:
Ø
We
can simply achieve the cascading drop down in lookup columns in SharePoint
without using any third party tools
Ø
No
need of SharePoint designer to achieve this result
Disadvantages:
Ø
This
won’t work in the Data Sheet View since we
are inserting the J query in the aspx page only.
Ø
When
the number of items in a look up column is greater than 20 then your drop down
will change as follows:
We had more than 20 states in the
States List (28 items with lookup values)
This
happens only in the IE and not in the Chrome or any browsers.
Have a look
at the page in the Chrome Browser:
The reason
for this variations and the fix is explained well in this article. Below is the
link
Hope you
have read a nice article.
Please free to comment.
Always, your comments help me to write more.
Share this post to others if this helps you!
Note:
I have updated the code of this article on 08.06.2012 since
readers of this article requires the following:
1. What is
the case if there are more than 2 values (4 cascading or filtered values –
using more number of variables)?
2. What is
the case if there is an empty string in the child value?
3. To
explain the solution with more details for ease of reading and understanding
for the readers.
Update: 27/06/2017To know about implementing Cascading drop down or filtered lookup in SharePoint 2013 or SP Online using REST API, read this article: Cascading drop down in SharePoint using REST APIUpdate: 06/07/2017To know about implementing multi-value lookup column and filtering values, read this article: Multi value Lookup Columns in SharePoint
