Tuesday, April 10, 2012

How to Implement Cascaded Dropdown List in SharePoint List

Introduction


This article explains step by step procedure to implement cascaded dropdown list in SharePoint list.

Background


I was recently working on large list with many fields were in these fields are dependent on other list fields as well as fields has to be populated based on the selection of previous field value. SharePoint doesn’t support cascaded dropdown list field as out of the box. But this can be implemented in 3 ways. They are given below.

1.      JavaScript
2.      JQuery
3.      InfoPath Forms

In this article, I will explain how to implement cascaded dropdown list in SharePoint list using JQuery. I will be creating 3 custom lists in SharePoint 2010 site. Below steps explains how to implement cascaded dropdown list in SharePoint list.

1.      Create Countries Custom List – Parent List
2.      Create Cities Custom List – Child List
3.      Create Vacation Plans Custom List – Cascaded functionality implemented
4.      JQuery Information
5.      Add JQuery Script to NewForm.aspx and EditForm.aspx file using SharePoint Designer


Create Countries Custom List

This section explains creation of Countries custom list. This list acts as parent list and will store list of countries. Follow the below steps to create Countries custom list.

   v  Go to SharePoint 2010 site.
   
   v  Click on ‘List’ link in the Left Navigation bar. Click on ‘Create’ link. See the below image for more information.

       

   v  This will open list creation pop-up window. In the list creation pop-up window, select ‘List’ option in the Left Side and select Custom List as template.
   
   v  Enter list name as ‘Countries’ and select ‘Create’ button. See the below image for more information.



   v  After creating Countries custom list, add items to list. See the below image for more information.







Create Cities Custom List

This section explains creation of Cities custom list. This list acts as child list and will store list of cities. Follow the below steps to create Cities custom list.

  v  Go to SharePoint 2010 site.

  v  Create custom list as Cities. Follow the steps explained in Countries list creation section.

  v  Create 'Country' column with below information.

o    Column Name : Country
o    Type : Lookup
o    Require that this column contains information : Yes
o    Enforce Unique value : No
o    Get Information from : Select ‘Countries’ from dropdown list
o    In this column : Select ‘Title’ from dropdown list
o    Click OK to create Country column.

  v  Add items to Cities custom list. See the below image for more information.


Create Vacation Plans Custom List

This section explains creation of ‘Vacation Plans’ custom list. This list implements cascaded dropdown lists functionality i.e. based on selection of ‘Country’, cities will be populated to dropdown list. Follow the below steps to create ‘Vacation Plans’ custom list.

  v  Go to SharePoint 2010 site.
  
  v  Create custom list as ‘Vacation Plans’. Follow the steps explained in Countries list creation section.
  
  v  Create Country column with below information.

o    Column Name : Country
o    Type : Lookup
o    Require that this column contains information : Yes
o    Enforce Unique value : No
o    Get Information from : Select ‘Countries’ from dropdown list
o    In this column : Select ‘Title’ from dropdown list
o    Click OK to create Country column.

  v  Create City column with below information.

o    Column Name : City
o    Type : Lookup
o    Require that this column contains information : Yes
o    Enforce Unique value : No
o    Get Information from : Select ‘Cities’ from dropdown list
o    In this column : Select ‘Title’ from dropdown list
o    Click OK to create City column.




JQuery Information

To implement cascaded dropdown list, we need to download below jquery files from Jquery site.

  v  Jquery.SPServices-0.6.2.min.js
  v  Jquery-1.6.2.min.js

Upload these two files to any document library where everyone has access to it. I would suggest to upload these files to document library of top level site. See the below image for more information.


Add JQuery Script to NewForm.aspx and EditForm.aspx using SharePoint Designer 2010

Follow the below steps to add jquery scripts to NewForm.aspx and EditForm.aspx file using SharePoint Designer 2010.

  v  Open ‘Vacation Plans’ list in SharePoint Designer 2010.

  v  Open NewForm.aspx and EditForm.aspx in advanced mode. See the below image for more information.



  v  Add the below script in both NewForm.aspx and EditForm.aspx page.

Insert the script below <asp:Content ContentPlaceHolderId=”PlaceHolderMain” runat=”server”> this tag.

See the below image for the script.



  v  Save the files and check-in.



Verification



1.      Go to ‘Vacation Plans’ list and click on ‘Add a new Item’ option.

2.      Select ‘India’ as Country. You could be able to see all Cities under India are populated to City dropdown list.





Comments


Provide your valuable feedback if this article is useful to you. Also don't forgot to post your queries/issues if any.



24 comments:

  1. Thanks ... works like a charm.

    I added a third list, and duplicated the SPCascadeDropdowns section with the new listname and column names ... this also worked great!

    Thanks again.
    Steve

    ReplyDelete
  2. Where do I download the jquery files from

    ReplyDelete
  3. You can download it from the JQuery site itself or search it in the google you will get lot of references.

    Regards,
    Nagendra Gunaga

    ReplyDelete
    Replies
    1. You can download files from : http://cdiscportal.digitalinfuzion.com/CT/Scripts/Forms/AllItems.aspx

      Delete
  4. any video to refer?coz i try but doesn't work

    ReplyDelete
  5. Hi Lin,

    I have not recorded any video. Please follow steps and let me know for any issues.

    Thanks,
    Nagendra

    ReplyDelete
  6. hey Nagendra...inka lalbagh, Cubbon Park ni Marchipoledha...
    epppudaina Brigade Road vellu... :)
    Just Kidding

    Thx bhaiya...Nice Example...

    ReplyDelete
  7. Better solution over look up columns
    www.spcascade.org

    ReplyDelete
  8. What if I need to use this across different sites? Do I need to make the same lists in all the sites?
    Moreover there is no good way you can copy lists with lookup columns from one site to another.

    ReplyDelete
  9. HI,
    for cascading we are depend on Jquery (it means download from other site and then upload,you know it makes dependency)

    ReplyDelete
  10. Hi,

    I'm using SharePoint 2013 & I am getting the following error:
    Error in function
    SPServices.SPCascadeDropdowns
    Parameter
    parentColumn: Building
    Message
    Column not found on page

    My scenario is the following: Building has multiple spaces. Ticket is created for a space to be cleaned. Therefore as part of creating ticket the user must select building & based on that, space.

    I created a list called Building, then Space (which contained Building as lookup) and finally a list called Ticket which contained both. I have not used the title column anywhere because it leads to confusion. My alteration of your script is the following:

    $(document).ready(function(){
    $().SPServices.SPCascadeDropdowns({
    relationshipList: "Space",
    relationshipListParentColumn: "Building",
    relationshipListChildColumn: "Space",
    parentColumn: "Building",
    childColumn: "Space",
    debug: true
    });
    });

    ReplyDelete
  11. Thanks Nagendra.....Nice post

    ReplyDelete
  12. 2010 Sharepoint designer, I get the following error:

    This web part does not have a valid xslt stylesheet: error: required white space was missing

    ReplyDelete
  13. Hi ,

    I am using sharepoint 2013,
    I have followed same steps but it's not working either not giving any error.
    My city is not filtered on the basis of country.On city dropdown all countries are populaed.

    ReplyDelete
  14. I am using SharePoint 2010 and the same thing happened to me as happened to Gupta. All cities are populated no matter what country I select.

    ReplyDelete
  15. Same here. It's not working All cities are populated.

    ReplyDelete
  16. Hi Nagendra Gunaga,

    I want to insert SPCascadeDropdowns code and 2 link js(jquery and jquery.SPService) into MasterPage. But after I insert them, code not working. So, may you help me this problem? Thanks so much :)

    ReplyDelete
  17. Hi Nagendra Gunaga,
    "Error in function
    SPServices.SPCascadeDropdowns
    Parameter
    parentColumn: Country
    Message
    Column not found on page

    Click to continue"

    Any idea?

    Regards,
    Praveen.

    ReplyDelete
  18. I was trying the same. but not getting the required result. then i realise i change the Title of Column in Last List i.e in parentColumn: & childColumn:. then i put the Title instead of Internal name and is work :).

    Old Code:
    parentColumn: "City",
    childColumn: "Match", //Internal name not working.

    Changed Code:
    parentColumn: "City",
    childColumn: "Match In City", // Title working.

    ReplyDelete
  19. Can you please explain how can we do this from infoPath

    ReplyDelete
  20. Thanks a lot, working Beautifully...

    ReplyDelete
  21. Gave this a try today and received the following error message; which from reading the other posts...a couple of others have received it as well

    "Error in function
    SPServices.SPCascadeDropdowns
    Parameter
    parentColumn: Country
    Message
    Column not found on page

    Click to continue"

    Can anyone please offer me some insight on what I'm perhaps missing?

    ReplyDelete
  22. Hi Nagendra Gunaga! thank you for post!
    I am using sharepoint 2013,
    I have followed same steps but it's not working either not giving any error.
    My city is not filtered on the basis of country.On city dropdown all countries are populaed.
    Any idea?
    Thank!

    ReplyDelete