The request for Cascading Drop Down, filtered drop down, dependent drop down or what you call them is one of the most common requirements from customers. This rather basic functionality is surprisingly not implemented in either WSS 3.0 or MOSS 2007.
Update: This also works in Sharepoint Foundation (2010). I have created a new post for 2010 here.
What is Cascading Drop Down?
You have two columns in your Sharepoint list, Country and City. Both are lookup/drop down columns. When you choose a country in the Country Drop Down, you only want to see the cities in this country available in the City Drop Down column. This of course makes life easier for the end user, and improves data quality.
Solutions en masse
There are however lots of solutions for this. Datacogs’ Cascading Drop Down is well-known, and various improved versions like SharePoint Cascading Drop Down List (With Filter) Field Type and Query Based Lookup Field Control (Custom field controls), both on Codeplex. These are all free, you can also find solutions out there you have to pay for. I have tried several of these, however good work these guys are doing making this for free, there are some issues that keeps me from using them, like annoying page refresh, creating lots of versions of the list element and the general problems you can get with restore/moving/upgrading data that is not standard Sharepoint.
The solution
Instead, I have used this solution with success. The list is still standard Sharepoint, which gives great flexibility and stability. This way, the filtering is done in the forms for creating and editing elements. Because of this, you don’t even have access to the server to implement this. I have used this solution both in document libraries and in regular Sharepoint lists.
1. Add the Jacascript
Download the Jquery file jquery-1.3.2.min.js by Jquery.com. Download jquery.SPServices-0.4.8.zip from the Jquery Library for Sharepoint Codeplex site (newer version is probably also OK). Upload jquery-1.3.2.min.js and jquery.SPServices-0.4.8.min.js (from the zip file) to a document library that everyone accessing your site has access to (a library on the top level site can be wise).
2. Create your relationship lists
Make two lists, one for the parent (Country) and one for the child (City):
Countries list:
Cities list:
The Country column can be a look up from the Countries list, but you can just as well use a standard single line of text column. This can theoretically give better performance, but you must of course be precise when filling out the County column so that the values here matches the ones in the Countries list.
3. Create the list using the Drop Down
Vacation Plans list:
This is the list where I want my Cascading Drop Downs. Country and City are lookup columns against Countries and Cities lists. The lookups goes against the title field in the respective columns.
4. Make the magic happen
In Sharepoint Designer, create your own NewForm.aspx (or EditForm.aspx, you’ll need both if you want it to work when editing items). If you don’t know how to do this, I’ll probably post a tutorial later. Tip: don’t edit the existing forms, but copy them and assign them as default forms.
Just below <asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">
insert this code:
<script language="javascript" type="text/javascript" src="/address to document library/jquery-1.3.2.min.js"></script>
<script language="javascript" type="text/javascript" src="/address to document library/jquery.SPServices-0.4.8.min.js"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: "Cities",
relationshipListParentColumn: "Country",
relationshipListChildColumn: "Title",
parentColumn: "Country",
childColumn: "City",
debug: true
});
});
</script>
Save and create a new item using your new form:
Voila!
5.Explained:
relationshipList: “Cities” : this is the name of the relationsship list = the list containing parent and child
relationshipListParentColumn: “Country” : Column name from the Cities list
relationshipListChildColumn: “Title” : Column name from the Cities list
parentColumn: “Country”: Column name from the list where the drop down is = Vacation Plans
childColumn: “City”: Column name from the list where the drop down is = Vacation Plans
You have lost of other options explained at the project site on Codeplex like sorting, multiple levels and so on.
Thanks to Marc D. Anderson for making this available.
Updated: The Jquery files is now updated by Marc D. Anderson to support also multiple select in non-english languages without having to tweak anything. Thanks to Jasmine Garry and Marc D. Anderson for this update.
Hi,
from the error message it seems you have configured something wrong, e.g. mixed up lists or column names.
Try to follow the guide exactly and see if this works, then make adjustments to suit your envirmonment.
I am trying this on SharePoint 2010 Foundation.
I followed the instructions exactly – but am using jquery.SPServices-0.6.1.min.js and jquery-1.3.2.min.js
Should it work with these versions?
Thanks
Hi Krystal,
it should work. I will see if I can test this myself on Foundation.
I haven’t testet with your exact file versions, but I’ll try with the newest versions.
Thanks – I really appreciate it!
Hi,
Is there a way in SP Foundation to use this cascading drop down principle for 2 Data view drop down list linked to a SQLdatasource which I find in DataBase Connection. So I have a manager_id (source from the external table Managers) and I want to populate this other drop down list with the agent_id (source from external table Agents), the agents corresponding to this manager_id..
Thank you in advance.
hi,
I studied this post and tried to implement it into a list but it doesn’t work (with new or previous js versions). I’m not SW developper and would appreciate your help on this issue.
Many thanks in advance!
Here below the details:
My lists are as below (list name => what is – list’s columns)
- Country => for parent list – column Title
- City => for child list – column Title and Country
- Test => 3rd list in which I want to see the cascading drop-down – Title, Country(lookup to Country list – Title column)and City (lookup to City list – Title column)
I’ve updated the code for NewForm.aspx in Test list using SharePoint Designer 2007.
The code in that page is pasted just below
Here is the code:
$(document).ready(function()
{
$().SPServices.SPCascadeDropdowns
(
{
relationshipList: “City”,
relationshipListParentColumn: “Country”,
relationshipListChildColumn: “Title”,
parentColumn: “Country”,
childColumn: “City”,
debug: true
}
);
}
);
Hello,
Thanks for the post. I tried doing exactly as outlined and this is the problem I am encountering,
I have Countries list, Cities list and the join list =CountryCity. When I choose a country the corresponding City dropdown only shows “Choose City” and doesnt pop up filtered values.
Please help!
I followed the same steps but its not reflecting in Child column.instead i am getting my both columns as it is lookup values and no filtering happens. I am not sure even my script is running as even i am not getting any error.
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “OU”,
relationshipListParentColumn: “OG”,
relationshipListChildColumn: “Title”,
parentColumn: “OG”,
childColumn: “OU”
});
});
Parent List : OG- Title column (lookup column) and values coming from another list
Child List :OU – Title col value and OG col value coming from another list as lookup value.
Document Library- added those 3 columns Title, OG,OU and OG column associated with Parents OG list “title” column and OU column associated with Child OU List Title column as lookup columns.
I have uploaded both jquery and jquery service script in another different document library at same site collection where my this document library exists and i have full admin access.
Please help
Thanks
I follow your instruction but if i select Country column corresponding cities are not filtering in city lookup column.
Please help me
If you’re having errors related to not being able to find the target columns in the child list, try using the field internal names instead of the display names.
Hi ,
Greatest , best post have ever seen !! works like a magic cool job dude
but i am facing a new problem wen i click Ok button
Server Error in ‘/’ Application.
Invalid postback or callback argument. Event validation is enabled using in configuration or in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Invalid postback or callback argument. Event validation is enabled using in configuration or in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[ArgumentException: Invalid postback or callback argument. Event validation is enabled using in configuration or in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.]
System.Web.UI.ClientScriptManager.ValidateEvent(String uniqueId, String argument) +8621721
System.Web.UI.Control.ValidateEvent(String uniqueID, String eventArgument) +72
System.Web.UI.WebControls.DropDownList.LoadPostData(String postDataKey, NameValueCollection postCollection) +53
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, NameValueCollection postCollection) +13
System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad) +346
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1743
now how to proceed with this issue , your help will be greatly appreciated . thanks you
I followed your instructions.It works perfectly fine.
Thanks for the great post.
Really cool solution!!! I have been able to make it work but I have one little problem! I am trying to implement the solution in a document library in SP 2007, I can add the code to the aspx of the edit & upload forms and works just fine, but I am not sure where to add the code so it works one someone clicks on the ‘New Document’. Any help will be greatly appreciated!!!!
Thank you!!!
Josi
[...] Read more about Cascading Drop Down in my previous post for Sharepoint 2007. [...]
For all of you that want to use this in Sharepoint 2010, I have written a new blog post about it. The procedure is basically the same.
this worked great for me; is there a way to set the default to be blank (or not default value I guess)?
Hi Paul, I am not sure what you mean. Before I select anything when creating a new Vacation Plan, it says “(None)” in the Country drop down. The City drop down shows “Choose City”. After selecting a Country, City drop down still shows “Choose City” by default.
What to you want to show in the drop down, and which drop down?
Hi, I manage to create cascading dropdown list like this using jQuery in NewForm.aspx & EditForm.aspx, I create 1st dropdown list to trigger filter into 2nd & 3rd dropdown list & it’s work. But when I try to do the same thing in my custom form, it will trigger filter on the 2nd dropdown list only & the 3rd dropdown list is remain as default containing all the value(not filtered)…How to fix this?
Hi, are you using three levels of drop downs (like Country, State and City)? I am not sure I understood your question completely.
No…just exactly like I said before…
I got this list ; BizzCentre, CreditOfficer & SalesOfficer.
BizzCentre list
Title
Centre A
Centre B
Centre C
CreditOfficer list
Title Officer Name
Centre A
James
Centre B
Michael
Centre C
Deric
Centre A
Bob
Centre B
Shery
Centre C
Clark
Centre A
Justin
Centre B
Maria
Centre C
Donald
SalesOfficer list
Title Officer Name
Centre A
Billy
Centre B
Jenny
Centre C
Armstrong
Centre A
Ramirez
Centre B
John
Centre C
Kelly
Centre A
Robert
Centre B
Percy
Centre C
Phillip
Can you see the table…it just 2 level only but the 1st dropdown list will trigger filter in two dropdown list…
Hi Haziz, I understand that you want to filter two times in the same form. From what I understand you choose “BizzCentre” one time in your form, and should then be able to see only the related CreditOfficers and SalesOfficers (in two separate drop-downs).
I haven’t tried this myself, but I guess you’d have to add most of the code twice in your form with the corresponding settings.
Hello,
thank you for the nice tutorial. It worked very well out of the box.
One point I have with the usage of it. I have a list with docments which are going to be grouped by section (the entries of the list). Now I have the problem, if a user is clicking on the Name of the Listentry, than the list appears to be edited. That’s what I would like to prevent.
Bye
Hi Boardys,
I am not sure I entirely get what you mean. Do you mean that if you group by a lookup coloumn, and click the group title in the list, you are taken to the lookup list? If so, this can be prevented with javascript. If your question is directly related to the Cascading Drop-Down, please elaborate and I will see if I understand your question.
Hello,
you’ve got it right, that clicking on the lookup column the I was brought to the title in the list. But I’ve found in the meanwhile how to erease the link (with js).
One other problem is the layout of the boxes.
I have finally following code:
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “DocSectionList”,
relationshipListParentColumn: “DocViewL”,
relationshipListChildColumn: “LinkTitle”,
parentColumn: “DocView”,
childColumn: “DocSection”,
debug: true
});
$().SPServices.SPCascadeDropdowns({
relationshipList: “DocTypeList”,
relationshipListParentColumn: “DocSection”,
relationshipListChildColumn: “LinkTitle”,
parentColumn: “DocSection”,
childColumn: “DocType”,
debug: true
});
});
the curious part is, that the first box looks like the common one, but the second and third looks different (look at here: http://imageshack.us/photo/my-images/641/20110830134146.png/)
The lists are created on the same way.
What can be wrong?
I try what you say in the tutorial and it’s great, but i’d like to know if i can do the cascading using a text box not a lookup field, is it possible??? I can´t but i would like to know…
Thanks!
Yes you can!
can you tell me how?
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “Ciudades_”,
relationshipListParentColumn: “Pais”,
relationshipListChildColumn: “Title”,
parentColumn: “Pais_”,
childColumn: “Ciudades_”,
debug: true
});
This is the code you publish with the name of my columns. I want to get the information from a Text Box Column which have a default value…
I need to take this default value and filter by it.
});
I cannot seem to get this to work for my list. Here is my script:
$(document).ready(function()
{
$().SPServices.SPCascadeDropdowns(
{
relationshipList: “Purpose”,
relationshipListParentColumn: “TypeofIssue”,
relationshipListChildColumn: “Title”,
parentColumn: “TypeofIssue”,
childColumn: “Purpose”,
debug: true
}
);
}
);
When I select what would be your “country” option, I get ALL of the “cities” returned instead of just the cities for the selected “country”. Any thoughts on what I could be doing wrong? Thank you
Hi! Your post is the most useful post I’ve ever read regarding this issue. And I’m ready grateful for this =)
I just have an inquiry. Is there a way that we can add a filter for the child selection? E.g. display all record where status = “active”.
Again, thank you for the post!
Hello,
Thanks a lot for the great codes. its working absolutely fine. however i am not getting “Choose City” Option in the City Dropdown. could u plz guide?
[...] Referências para utilização desta biblioteca: http://www.uccorner.com/54/sharepoint/cascading-drop-down/ [...]
Hi,
I have tried as per your instruction. but second option(cities) is not getting sorted on my form.
Please help me out.
Regards,
Jayashri