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.
[...] http://www.uccorner.com/54/sharepoint/cascading-drop-down/ [...]
Hi,
I am using the code above and inserted the script file as well, but I am opening the page to add new values to the vaccation list, it gives me a blank values in place of country.
This is code from my site
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “Cities”,
relationshipListParentColumn: “Country”,
relationshipListChildColumn: “Title”,
parentColumn: “Country”,
childColumn: “City”,
debug: true
});
});
Hi,
are you using a non-english version of Sharepoint? Check my post regarding multiple values in non-english languages. Also, check that the URL to the Javascript-files works when trying them in the browser.
I think you have an error in the tutorial’s code. I followed it to the letter, and had trouble, then I changed the script and BINGO!, it worked. This tutorial was SO much more intelligible than the official documentation, thank you so much!!!
Here is my fix to your code above:
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “VacationPlans”,
relationshipListParentColumn: “Country”,
relationshipListChildColumn: “City”,
parentColumn: “Country”,
childColumn: “City”,
debug: true
});
});
Ignore what I said about your code being wrong. It was wrong when I done it because of case-sensitivity which I was getting wrong. Ace work!
Very good post! Thanks Marc Anderson too!
Hi Marc,
I went through my code and made some changes in the lists as per my requirments. I have a list which has the names of the people and the other one which has some processes names linked to the people names in the first list..Now what I am trying is similar to what you did in your example
code from my site is
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “Processes”,
relationshipListParentColumn: “Delivery Leader”,
relationshipListChildColumn: “Title”,
parentColumn: “Delivery Leader”,
childColumn: “Process”,
debug: true
});
But it is giving me error when I am selecting the any value from Delivery Leader column. Since I am unable to see clearly the message in the error display box..I don’t know where the problem is..Please help
Thanks for the post
It works very fine for 2 columns
My requirment is for 3 columns ( Continent,Country,City)
How do I achieve this here ?
Can you please tell me ?
Thanks
Harish
Rakesh
parentColumn: “Delivery Leader”,
childColumn: “Process”,
This two columns are your present List Columns
Hi, I have not written a tutorial for three columns yet. However, take a look here: http://spservices.codeplex.com/wikipage?title=%24%28%29.SPServices.SPCascadeDropdowns&referringTitle=Documentation
Here Marc D. Anderson, the author of the Jquery file, has an example of a set up with three columns.
@Rakesh:
My name is not Marc:)
For troubleshooting, I would recommend starting with the exact same set up procedure as I have documented here. When this is working, try changing the set up according to your needs, and you will see if some (and which) of your changes are creating problems.
Thanks Solheim
I have tried this It is working fine.
In My requirement The 3rd coloum can be blank In this case it should not allow user to accept the form.It should throw an message
Here If it is blank by default it is picking first value from the list.
Thanks
Harish
I think i’m dooing something wrong.
the dropdown that you have are they all dropdown or lookup fields.
if have tested it with a few, but i get now result?
thanks
Hi, this is explained in the article:)
Hi Audun,
Thanks for the very usuful article.
I have a complicated requirement. I have three multiselect lists and initially I want all lists to be populated from the look up lists. The relationship is many to many between the three columns. Depending on which value is selected (any of the three) other two should be populated. So in your example initially I need to show all countries, regions, cities. If user selects one of the region forst, then automaticlly correct country should be selected and corrosponding cities should be populated in the multiselection box. Will this be possible using the SPCascadeDropdowns?
Thanks.
Hi,
unfortunately I don’t know if this is possible. My best advice is to check out the Jquery code to see if you can implement it with existing functionality, or check out the guides on the web pages to the script’s author.
Hi,
I’m using your script and have uploaded the latest jscript 4.2 and services 5.4. I’ve set the relationshiplist up for the AccessAreas list which has two columns: 1 – Title (Child); 2 – AAType (Parent) which is a lookup field to another list call access area types. Here’s my script.
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “AccessAreas”,
relationshipListParentColumn: “AAType”,
relationshipListChildColumn: “Title”,
parentColumn: “Access Area Type”,
childColumn: “Access Items Requested”,
debug: true
});
});
When I open the form “NewItem” for the list Access Requests, it pops up:
Error in function
SPServices.SPCascadeDropdowns
Parameter
relationshipListParentColumn: AAType or
relationshipListChildColomn: Title
Message
Not found in relationshipListAccessAreas
I’ve triple checked everything and it should work. Do you have any further direction on this? It is the same problem if my “Access Itmes Requested” field in the Access Requests list is single or multiple value.
Thanks,
Phil
Hi,
your code looks correct to me, but obviously there is an error somewhere, it looks like the AAType or Title column cannot be found in your AccessAreas-list. I would triple-check all the spelling, if it still doesn’t work, try to set up a test version using the excat set-up in my example, preferrable with the exact Jquery-versions in my example.
IT works very well with formfields default, but when i insert DVDropDownList i have an error: the parentcolumn name not exists in page…. what is the name i use in this situation????
The best regards,
Hi,
I’m not sure what you mean by DVDropDownlist.
I removed the combobox and i inserted a sharepoint combo list, the code is:DVDropDownList
With this control it works if i put the parameter “Tittle=”ParentColumn or ChildColumn” in the combo list. but now it not saves in the list..
looking for a soluton aswell using the DVDropDownList.. i guess you are using it for avoiding the ugly textfields used on IE for 20+ items.
i was able to make it work in update mode aswell, just make sure every field is populated with the correct value, id etc
but tafter that, i got another problem reopening the saved items, the list get wrongly positioned at the first element, instead of the right one.
anyone found a solution for this ?
for you info, i opened a thread on the developers site
http://spservices.codeplex.com/Thread/View.aspx?ThreadId=211968
Love this!just a query though, what would you do if you had 3 categories? such as Country , State, City?
Hi,
thanks for the feedback. I have not used DVDropDownList myself with this solution, but perhaps the developer can fix it.
Hi,
three levels is possible. I haven’t made a step-by-step tutorial for this though, so you’ll have to dive into the documentation yourself:)
Has anyone tried in Sharepooint 2010? everything works well except that the second drop down (City in the above example) is not being filtered by the Country dropdown. Any clue as to what is wrong
Thanks
Hi Sucheta,
I have the similar requirement.
Did you get the solution for your requirement using javascript or jquery?
Thanks
hk
I tried to implement everything as explained but when I open the page where the ‘Vacation Plans’ list is placed I get the an Error window saying:
Error in function
SPServices.SPCascadeDropdowns
Parameter
parentColumn: Country
Message
Column not found on page
Where is mistake?
I’m having the same issue as Phil Reese reported on 04/22
I have a list called Direct Cause – which is the lookup source for the DIRECT_CAUSE field on my main form – these values are considered the parent.
The RelationshipList is called Root Cause Category, with two columns (parent)DirectCause and (child) RootCauseCategory
The main list has two columns:
DIRECT_CAUSE – lookup to Direct Cause list (parent)
ROOT_CAUSE_CATEGORY – (child)
The code I am using is:
$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: “Root Cause Category”,
relationshipListParentColumn: “DirectCause”,
relationshipListChildColumn: “RootCauseCategory”,
parentColumn: “DIRECT_CAUSE”,
childColumn: “ROOT_CAUSE_CATEGORY”,
debug: true
});
});
When loading the form I get the following error:
Error in function
SPServices.SPCascadeDropdowns
Parameter
relationshipListParentColumn: DirectCause or relationshipListChildColumn: RootCauseCategory
Message
Not found in relationshipList Root Cause Category
And the ROOT_CAUSE_CATEGORY has “Choose ROOT_CAUSE_CATEGORY…” in the dropdown.
Disregard my earlier post…I figured it out. relationshipListChildColumn was originally called Title…I change my code to reflect it and it works now. Thanks!
Hank
Wonderful Post .. works like charm .. appreciate your work
everything works well except that the second drop down (City in the above example) is not being filtered by the Country dropdown.It needs to filter by state and country. Any clue as to how to get it working
Thanks great post works fine!!!
Can you please tell me how to modify the codes if i have 2 cascading dropdown.Thanks
Hi,
it’s possible to have three levels (I assume that’s what you want – 3 drop-downs). As mentioned earlier, I have not set up a step-by-step guide of how to to this, you’ll have to dive into the documentation yourself:-)
Great work.
Ihave an issue,please help me out to resolve this issue.
I created 2 look up columns Frequency and Frequency Value.Frequency drop down values are Quarterly,Monthly and Weekly.
If I select Quarterly,I should get Quarter1,Quarter2,Quarter3 and Quarter4 drop down values in Frquency Value column.For this i do not have any issue.
If I select Monthly,I should get January,February…….Dec drop down values in Frequency Value column but I am getting drop down values in alphbetical order……April,August,December,February,January,July,June,March,May,November,October and September.
If I select Weekly in Frequency,I should get Week1,Week2,Week3……………Week52 drop down values in Frequency Value column but I am getting values like this:IWeek1,Week10,Week11,Week12….Week2,Week20……….Week5,Week50,Week51Week52
Any suggestions would be appreciated.
and one more question….I created cascading drop downs in standard view,does it works in datasheet view?
I haven’t tried, but I assume this will not work in datasheet view since this renders the view in a completely different way and uses the Access components.
I haven’t set a site with this set up right now, but I would guess you could adjust the javascript to change to sorting.
Hi, Can the “City” column in “Vacation Plans” list be a text box? I have unique value for each relationship and I want to display it as a single line text field.
Hi, this should be possible. First you would have to edit the form in Designer so that “City” is shown as a label for example. Then you would have to populate the label based on the Country field, I haven’t tried this but you might have to dive into the Jquery file to check how you would implement it and if you have to change come things in the Jquery.
Thanks! It works
Hello,
It is working when there is mapping found for a country and respective cities.But when there is no city for the related country then. It given javascript error in file jquery.SPServices-0.4.8.min.js as arr(..) is null.Is it problem with javascript or anything else. how can i sort this out.
For anyone who gets an error similiar to Hank:
Error in function
SPServices.SPCascadeDropdowns
Parameter
relationshipListParentColumn: DirectCause or relationshipListChildColumn: RootCauseCategory
Message
Not found in relationshipList Root Cause Category
I had the same issue and solved it by finding the “actual” name of my parent column in the relationship list. (I assume you can do the same if it’s your child column that’s causing the issue.) I had named my column “Business Area” but its actual name according to SharePoint was “Business_x0020_Area”. The error stopped once I put “Business_x0020_Area” as the name of the Parent Column.
You can find the actual name of the column by opening the relationship list in Internet Explorer and hitting View > Source. Then Edit > Find and type the name of your column (I typed “Business Area” without quotes) to find something like DisplayName=”Business Area”. Then a bit before it in the script you’ll see Name=”actual column name here” (mine said Name=“Business_x0020_Area”). This is the actual name of your column. Hope that helps someone else…
Allison
Okay, so I’m at the end of my rope. I’ve been trying to get this to work for a couple of days to no avail and no matter what I do I get the same 3 errors when opening the form that contains the cascading dropdown lists. Couple of caveats: I’m fairly new at Sharepoint development and this is my first attempt at modifying a sharepoint form and adding jscript. I’m not a newbie developer, have been doing .net development for about 10 years, just new to sharepoint development.
Here are the 3 errors I’m getting:
Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; InfoPath.2; .NET4.0C; .NET4.0E)
Timestamp: Mon, 29 Nov 2010 21:27:42 UTC
Message: Syntax error
Line: 2
Char: 1
Code: 0
URI: http://*********/jsFiles/Forms/AllItems.aspx/jquery-1.3.2.min.js
Message: Syntax error
Line: 2
Char: 1
Code: 0
URI: http://*********/jsFiles/Forms/AllItems.aspx/jquery.SPServices-0.5.8.min.js
Message: Object expected
Line: 675
Char: 2
Code: 0
URI: http://*********/CasDDLTest/Forms/TestCasDDLEdit.aspx?Mode=Upload&CheckInComment=&ID=11&RootFolder=%2FCasDDLTest&IsDlg=1
Any help would be greatly greatly appreciated to save what little hair I have left
)
Thanks for sharing Allison!
Thanks alot,
your post help me to impliment casecading in document library.
I think the column name(relationshipListChildColumn: “Title”)
is a internal name.
Thanks,
Faheem Ahamed
Thanks Allison, this was driving me crazy!
For anyone having trouble implementing this cascading drop down that is using column names with *** spaces in the name *** this is the solution!
I follow your instruction but it give me the following error although I check the column and list name.
Error in function
SPServices.SPCascadeDropdowns
Parameter
relationshipListParentColumn: Country or relationshipListChildColumn: Title
Message
Not found in relationshipList Cities
Click to continue
Can you please advise