Monday, February 13, 2012

Cascading dropdown (or) Filtered Values in Lookup Columns in SharePoint

To 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:
// 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.

89 comments:

  1. I am getting the error 'There was a problem with the request.'
    Upon looking through the javascript file, found that http_request.status is 500 (internal server error), what could be the reason for this error?
    Thanks!!

    ReplyDelete
  2. Hi,

    There are two reasons, you get this error "There was a problem with the request"
    1. When the relationship bewteen the lists are not properly created
    2. Or when the relationship in the javscript is not properly given (example)in the below code ie.,
    var ccd1 = new cascadeDropdowns("Country", "State", "Country", "States", "Title");

    Hope this helps you!

    ReplyDelete
  3. Hello.
    I'm having trouble using this tutorial.
    When I go to the toolpane to put the source code, I can only see one textarea that says Content Editor, when it was supposed to say Source Editor.
    The toolpane shows:

    Content Editor
    To add content, type the HTML into the text box.
    Content Link
    To link to a text file, type a URL.

    Do you have any idea what might be hapenning?
    Thank you.

    ReplyDelete
    Replies
    1. Hi Luis Forquesato,
      From your question, I can understand that you are trying this tutorial in SP 2010.
      Following are steps to put your source code in Content Editor WebPart:
      Once you navigate to the ToolPaneView, you will be in the Edit mode -> Clcik inside the content editor webpart -> In the ribbon -> Editing Tools -> Format Text -> Click on the HTML option in the Markup section -> Select Edit HTML source from the two options -> In the HTML source popup window -> Insert your source code.

      Hope this helps you!

      Delete
    2. Hi Luis Forquesato,
      I have again checked your requirement deeply and able to understand your query.
      I have updated the article about inserting the web part without designer. Have a look at this.
      http://sharepoint-works.blogspot.in/2012/03/inserting-web-part-into-sharepoint-page.html
      Hope this will help you in fixing the issue!

      Delete
    3. Hello.
      Actually, I had two problems.
      First, I was using a different browser, not IE. It didn't work.
      And I had to put the webpart in the end of the page, like shauncw09 said.
      Everything is working now, good job.
      Thank you!

      Delete
  4. Very nice and useful article!

    ReplyDelete
  5. Folks,

    You need to make sure that the WebPart entry is BELOW the data entry fields. We've been going round in circles trying to fix this. Here's the reference that put us straight and got this working for us:
    http://spcd.codeplex.com/discussions/233714

    Thanks to Micchy and MikeMan on the CodePlex forum for this great suggestion.

    And thanks to Maruthachalam Krishnamurthy for this great article.
    Very Cool!!!

    ReplyDelete
  6. I am getting an error "'undefined'is null or not an object" when my child lookup have multiple selection.

    ReplyDelete
  7. Hi,

    This looks like it is exactly what I'm looking for - but have been a bit stumped by this instruction (I'm a 10-minute old sharepoint user):

    click on new
    and then insert a content editor web part and add the above code to achieve
    the result

    Can you please elaborate on this?

    Thanks!

    ReplyDelete
    Replies
    1. Hi Anonymous,
      Here, I mean to say, Click on New <-> Click on New for adding a new value in the Cascading lookup Demo list created.
      Mean While, I have updated this article for clear understanding with the screen shots. Check it out!
      Hope this helps you!

      Delete
  8. Hello again.
    One suggestion for improving your code (I am probably going to implement it my way, just saying so that more people might need it):

    When a field has "" (blank), the code doesn't recognize it, and so it bugs: When it should show nothing, it actually shows string;# as an item on the dropdown.

    Let me explain better: I have a calculated field, that it's like IF(something,Title,""). When i want to use this calculated field for my cascading dropdown (as a child), it shows string;#, instead of just ignoring it.

    Hope I made myself clear. It should be an easy bug fix.
    Thank you again.

    ReplyDelete
    Replies
    1. Hi Luis
      I will look into this issue and will test my code. If anything, I will update the article.

      Delete
    2. Hi Luis
      It took time for me to update the article.First of all thanks for letting know about the empty string issue.
      Everything is fixed and I have updated the article very clearly with screen shots and now the code works for empty strings also.
      Happy Coding!

      Delete
  9. Hi

    I am trying to add several variable but it won't work. Here is my code below. Can you tell me what is wrong with it? Many thanks

    P.S..I have used @@@@@ in place of 'Scr!pt'

    <@@@@@ type="text/java@@@@@" src=/sites/cmrcl/JS%20Query%20Library/spcd.js>
    <@@@@@@ type="text/java@@@@@@">
    var ccd1 = new cascadeDropdowns("Category", "Category Sign Type", "Category", "Framework Items", "Title");
    var ccd2 = new cascadeDropdowns("Category Sign Type", "Sign Item", "Sign Type", "Framework Item types”, "Title");
    var ccd3 = new cascadeDropdowns("Sign Item", "Colour or Descr", "Sign Item", "Framework Category Colours/Desc", "Title");
    var ccd4 = new cascadeDropdowns("Colour or Descr", "Size", "Colour/Desc", "Framework signs Sizes", "Title");




    Cx

    ReplyDelete
    Replies
    1. Hi
      For your requirement, I have updated the article with 3 values where the filtering in the values are resulting properly.
      Let me know if you have any issues in achieving the solution.

      Delete
  10. Hi there,

    Thanks for the code, but I have 1 issue.

    I have 2 columns (parent, child). When I choose the parent, the child column show the right amount of options, but not visible. I checked the code and it's ok. Any idea how to fix it? Thanks in advance.

    ReplyDelete
    Replies
    1. Hi
      Please check with the updated code.
      Make sure the relationship between the lists are created correctly and the java script is properly used.
      Hope this helps you!

      Delete
  11. I have followed the instructions for the tutorial word for word. This script does absolutely nothing for me. It doesn't filter anything at all. Perhaps my path is wrong for the js file?

    ReplyDelete
    Replies
    1. Hi
      Please check with the updated code.Let me know if you face any issues.

      Delete
  12. I appreciate the fast reply, and the rework of the instructions. My problem was that the path to my js file was wrong, and then my relationships were not created right in the first place. I corrected the path, and recreated my lists and it works perfectly!!! Thank you for this!!

    ReplyDelete
    Replies
    1. Hi,
      Thanks for your comment and glad that it helped you!

      Delete
  13. All of your code examples are missing.

    ReplyDelete
    Replies
    1. It is present and visible properly. Check again by loading the page.

      Delete
    2. I also cannot see the code examples... i have to view the source to do so. I have tried in ie9, firefox and chrome and still no code examples.

      Delete
  14. Very Nice Post and functionality but I have one problem.

    Everything works fine on Firefox, Chrome and Safari but on IE, the filtered dropdown has the correct number of items in it, but they are not visible until I
    click on the Dropdown arrow. On all other browsers they are.

    Any idea what the problem might be?

    Thank you

    ReplyDelete
    Replies
    1. Thanks for your comment.
      I have tested this functionality in IE and Chrome and it works well.
      Here in your comment, I am not clear about this line (but they are not visible until I click on the Dropdown arrow).
      When the number of items filtered is correct, then the functionality works as expected. But Clicking on the drop down only the items will be shown.
      Could you explain in detail about the issue so that I can give my ideas.
      Thanks.

      Delete
  15. Thanks for the great article. I have managed to use it to populate one parent to many children dropdowns. I have one question I need to further filter by another field an active field which is set to Yes/No so in your example you use countries with a lookup to continents. If Countries had an extra field called Active, that I could set to No to say that country is no longer active and so does not appear in the drop down. Is there a way to do this? Thanks

    ReplyDelete
    Replies
    1. Hi
      Thanks for your comment!
      Hope you can achieve this by using calculated columns to display a field, if it is active or do not display if it is not active. Then use the java script to achieve the cascading drop down.
      Hope it is bit complicated but can achieve it.

      Delete
  16. I am utterly shocked that this worked on my very first try - thank you SO much for doing the heavy lifting for me!

    ReplyDelete
    Replies
    1. Hi Sharon,
      Glad that it helped you. Thank you for your comment.

      Delete
  17. I feel like I've seen this reported somewhere elsewhere, so hopefully you can direct me to the solution? This all works perfectly for an initial entry. But if I try to edit an entry and change a parent field, the subsequent child fields are not re-filtering to match the new parent.

    ReplyDelete
    Replies
    1. Hi Sharon,
      Try to follow the same procedure for adding the Content Editor web part in the EditForm.aspx and then add the same Java script in it. Then it will work in Edit forms also.
      Let me know if you face any troubles.

      Delete
  18. I can used with SharePoint wss 3.0??

    ReplyDelete
    Replies
    1. Yes. It is tested in WSS 3.0. It will work.

      Delete
    2. Thank you so much this has got me super kudos at work.

      However I too have the same issue, if I go into the list item again and try to update or change the drop down items I see them all they are no longer filtered at all??

      This is in either of the 3 fields that I have set up have you got a solution to this please.

      Delete
    3. Hi Nath,
      I will work on that and will post the solution for the same.

      Delete
    4. Hi Maruthachalam

      Can you confirm the status of the code to edit existing entries that's the final piece of this puzzle. so Far 2 thumbs up though.

      Delete
    5. I've worked it out! If you follow the same principles as creating a new item dropdown as you do when editing one it works!!!!

      so as above copy and paste the same content from the new content editor webpart and paste it somewhere for a sec.

      Then go to edit an item it should say similar /Lists/test/EditForm.aspx?
      All you need to do is add Toolpaneview=2 to the end add your content editor web part as previous and then paste in your j query and it will work. following the above steps.

      I've done it on mine and it now works when creating and editing existing ones.

      Delete
    6. Hi Nath,
      Thank you very much for pointing this. I am bit busy with my works and hence not able to work on this issue as of now.
      But I had written an article for inserting the content editor web part in the EditForm.aspx http://sharepoint-works.blogspot.in/2012/01/how-to-insert-web-part-in-newform.html
      Thanks again for this solution. It will help other users to fix their problems.
      Happy Sharing!

      Delete
  19. Hi,

    Does it work for SharePoint 2007 as well? I have tried it and it didnt!!!

    Many thank,
    Hamish

    ReplyDelete
    Replies
    1. Yes. It will work. It is checked in 2007 & 2010 also.

      Delete
  20. This works perfectly for an initial entry. But if I try to edit an entry and change a parent field, the subsequent child fields are not re-filtering to match the new parent.

    ReplyDelete
    Replies
    1. Hi Sharon,
      I will work on that and I will post the solution for the same.

      Delete
    2. Hi Sharon,
      Try to follow the same procedure for adding the Content Editor web part in the EditForm.aspx and then add the same Java script in it. Then it will work in Edit forms also.
      Let me know if you face any troubles.

      Delete
  21. Maruthachalam,

    Great article! Great work! Exactly what I needed! Works perfectly! Thank you!

    I was wondering if the the Master Relationship List could be located outside of the site that it is being used? For example I have a subsite that uses 2 sets of cascading dropdowns and the master lists are located within that subsite. What I would like to do is move those master lists to either the root site or another subsite with the intention to use those lists in other subsites that require the same common information (department, job title, race, etc). That way the Master List is maintained in one location but used where ever it is needed without recreating it in each subsite. I am using Bamboo Lookup Selector to lookup lists accross the site collection. Is this possible? How would I put this in relationshipList?

    Thank you
    DAL

    ReplyDelete
  22. Thanks so much for the outstanding article! Saved me a ton of time and allowed me to rid of a crappy infopath form.

    ReplyDelete
    Replies
    1. Thank you for the comment. Glad that it helped you!

      Delete
  23. Hello
    I am having trouble with this solution. It works fine in Firefox but in IE, values are not displayed. It filters down to countries but not states. Any ideas?
    Thanks
    G

    ReplyDelete
    Replies
    1. Hi,
      Please check the browser settings for JavaScript. It works as expected in IE and I have tested it as well.

      Delete
  24. Thanks A lot,It help me to solve my problem.
    Very good post.

    ReplyDelete
  25. Hi,

    Thanks for the very good post
    it helped me a lot.

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. Hello,

    How can I achieve AND connection between two dropdowns who are cascading third dropdown? For example, i'd like to get the functionality to filter cars (in the 3rd dropdown) who are red(1st dropdown) AND have two doors(2nd dd).

    Thanks!

    ReplyDelete
  28. This solution is working firn for me in IE, but its not comming in chrome or firefox.
    I have only two columns for cascading dropdowns. but till i have no idea why its failing?
    Do i need to change some settings in firefox?

    ReplyDelete
    Replies
    1. I have tested in IE, Chrome. It works fine. I haven't tested it in the Firefox. Check the Javascript and also check if you are getting any errors while using in chrome or firefox.

      Delete
  29. My three relationship lookup lists are located in the root site. Three root-level site columns were then created that referenced the three root-level site lookup relationship lists. A root-level site content type was created using the three root-level site columns. That root-level site content type was added to a custom list at a sub site.

    The example shows the relationship lookup lists existing at the same level as the list where you apply the jQuery.

    How do you reference the relationship lists when they live a couple of sites up in the root?

    ReplyDelete
    Replies
    1. I have this same question also. Did you find a solution?

      Delete
  30. Thank you for the instructions provided! I know there is hope for me :)

    That said, I haven't been able to make this work yet. Would there be a difference in the scripting if my lookups for set as multiple selections?

    Thanks!

    ReplyDelete
  31. I got this to work, thank you so much!! I know there are some limitations (e.g. Will not work in Datasheet view).

    That said, is it possible to make this work from the "Edit Item" form since this uses browser functionality like the "New" option?

    ReplyDelete
  32. HI I'm trying to a use something like this. I'm trying to create a simple order entry list where an Employee can order an item. The items are in different list with a list price. However when I try and use look-up to these items I cannot perform calculations using the price information from the item list. The items have options and depending on you selection the price will change. But when I go to multiply qty against price I Can't get to price. Any suggestions? Great article BTW.

    ReplyDelete
  33. Hi,

    that was what i have been looking for, but i did not getting it on work. All is fine, but the children lookup is not filtered. What have i done wrong?

    Can you help me please.

    Thanks

    ReplyDelete
    Replies
    1. Hello,
      Check your Java script path and check whether the code elements (JS methods)are correctly defined.
      Let me know if you face any troubles.

      Delete
  34. Hi,
    Solution is working thanks but at initial state all option values in child drop down are shown. How can i change this? I don't want to see any value until i select an option from parent

    Thanks

    ReplyDelete
  35. Hi

    thanx for that solution. Everthing works fine.

    But one question/problem.
    When i edit an item in the worklist, the dropdown selections are ok. when i click on the dependent dropdown at first the list showed is not filtered. I think this depends on the "onchange" event that is not fired bevor because i have not made any changes to the parent column.
    I tryed to fire the event manually, but nothing happens. Have you any suggestion to solve this problem?

    thanx
    Jo

    ReplyDelete
    Replies
    1. Hi Jo,
      Glad that it helped you!
      Yes.It depends upon the OnChange event,the child values are filtered. I will try to fix the same.

      Delete
  36. Hi, I tried the method which you have mentioned but m getting unfiltered list.

    Steps followed.
    1. Created 4 list and columns
    List name Column name
    Continents Continent
    Countries Country
    Continent
    States State
    Country
    Cities City
    State

    Then created a cascadingdemo list with the column names.
    Continent
    Country
    State
    City

    In the new form, added the web content editor and gave the same code changing the jquery file path

    Please let me know if something m missing. I tried this in sharepoint 2007.

    ReplyDelete
    Replies
    1. Check if you have created the relationship between lists in the java script properly. If your relationship in the java script is wrong, it won't filter the values. Let me know if you still face any issues.
      Also, it will work in versions.

      Delete
  37. Useful article. Thanks.

    ReplyDelete
  38. Hi Maruthachalam,

    Thanks for great article got this up and running with your clear and well laid out instructions.

    Would this script still work if I had all the information in four columns in one list, rather than four separate lists?

    I get a list of information (report) in one spreadsheet of which there are four columns. If I separate them into the four lists (continent, country, state, city) it works.

    But periodically I have to update the data. At moment I am looking at using Access Append Query to do this, but would be much simpler if this code would work or could be adapted slightly.

    Thanks!
    Paul

    ReplyDelete
    Replies
    1. Hi Paul,
      Glad that it worked...

      I haven't tried this scenario. I will update you, once I try any solutions for this scenario.

      Delete
  39. I'm running into the same issue. Any solution for this?

    ReplyDelete
  40. Thanks, this works great. I'm using it for an asset tracking database I have. I'm using Hardware, Make, and Model fields. Problem I have is that if I have a Hardware type with multiple makes the models will not be unique. For example I have HP laptops, desktops, and printers. If I choose the hardware type as laptop and the make as HP the model dropdown will display models for laptops, desktops, and printers. Logically, I know this can be fixed by the code comparing the hardware and make type and filtering only models that match that criteria, applying this is a diffent challenge.

    Also, for those having issues make sure that all the lookup fields are text fields. The only lookup field would be on the final form that point to the colums in the other list.

    ReplyDelete
  41. do you have a newer version of this entire setup. It does not work for me. I have followed the steps carefully.

    ReplyDelete
  42. can you explain the fields below? i think this is where the confusion comes from


    ReplyDelete
  43. Hi, Thanks For Your Effort....
    Does it work with SP2013?

    ReplyDelete
  44. Still seems to be working in 2013 fyi. This is the easiest (free) solution I have found. Thanks.

    ReplyDelete
  45. ^should mention using current jquery and jquery-spservices

    ReplyDelete
  46. Hi!
    Sorry, but where ist the Code?
    I can only see:
    -------
    5.
    General Code to be added:
    blank
    Description of the code....

    Code in Our case:
    blank
    6.
    --------

    6.



    ReplyDelete
    Replies
    1. Hi,
      There are some problems with the browser and the JS I have added. Hence I have uploaded the code sample as a image. Please check and hope it helps you!

      Delete
  47. Hey Friend, i tried the code, everything went fine, but, i m getting an error at end,
    Error in function
    SPServices.SPCascadeDropdowns
    Parameter
    parentColumn: Continent
    Message
    Column not found on page

    Click to continue

    may i know why this happens..?

    ReplyDelete
  48. Hi

    Firstly I think this is reall awesome...thanks for sharing. I would like to find out if this can be used in Document Library list in SharePoint 2010?

    Thanks

    ReplyDelete
  49. Hi,

    I had implemented this solution - http://spcd.codeplex.com/ - but it does not seem to allow me create two filter lines. If I had two, on the second line will filter.

    So, I thought I would try your solution. I cannot actually get mine to work at all. I created three lists -1. Continents - 2. Country - 3. testing list with lookups to these two lists - as per your example. I have downloaded and add the .js files to my SiteAssets folder. I've then created a txt file and used content editor to point to it in the Site Assets Folder. Code is:

    // Execute the following JavaScript after the page has fully loaded, when it's ".ready"
    $(document).ready(function(){
    $().SPServices.SPCascadeDropdowns({
    relationshipList: "Countries",
    relationshipListParentColumn: "Continent",
    relationshipListChildColumn: "Country",
    parentColumn: "Continent",
    childColumn: "Country"
    });

    });

    Any advice on what I may be missing?

    ReplyDelete
    Replies
    1. *Bump* Anybody got any clues? I tried again from scratch and still nothing. I must be missing something? (I've changed the word script to ***** as it wouldn't' allow me to paste it in here)

      <***** scr="/AssetMovement/SiteAssets/jquery-1.4.2.min.js"
      type="text/javascript">


      <***** scr="/AssetMovement/SiteAssets/jquery.SPServices-0.5.3%20min.js"
      type="text/java*****">


      <***** type="text/java*****">
      // Execute the following Javapt after the page has fully loaded, when it's ".ready"
      $(document).ready(function(){

      $().SPServices.SPCascadeDropdowns({
      relationshipList:"Countries",
      relationshipListParentColumn:"Continent",
      relationshipListChildColumn:"Country",
      parentColumn:"Continent",
      childColumn:"Country"
      });

      });

      Delete

Link WithIn

Related Posts Plugin for WordPress, Blogger...