Create Cascaded (related) Lookup Dropdown fields easy with no server deployment

February 17th, 2010 | Categories: JavaScript, SharePoint, SharePoint Lookup field

I’ve added a new section on my page dedicated to JavaScript solutions that I’ve published. The latest one is intended for creating a Cascaded Lookup Dropdown fields in a SharePoint New or Edit Form.

What is a Cascaded dropdown? It’s a combination of two dropdown lists. By selecting an option in the first dropdown (parent) you filter the options in a second dropdown (child).

Using this simple-to-deploy and configure solution you can create multiple cascaded relations in the same form. It works in MOSS 2007 / WSS 3.0 and in MOS 2010 / WSF 4.0

You can visit the project page here or download the JavaScript solution from CodePlex.

  1. Noel Dsouza
    February 19th, 2010 at 18:54
    Reply | Quote | #1

    why is it that it only supports upto 19 items in the drop-down.

  2. Boris Gomiunik
    February 21st, 2010 at 12:43
    Reply | Quote | #2

    If you have more than 19 items in the dropdown, the dropdown list changes to input. More info can be found here:
    http://www.sharepointboris.net/2009/10/sharepoint-lookup-field-how-does-it-work-and-how-to-add-javascript-event-handler-function-to-it/
    The script is prepared and tested with lookup with up to 19 and also over 20 items.

    • Prayag Mahant
      June 23rd, 2011 at 04:03
      Reply | Quote | #3

      Hi Boris,
      I really appreciate the work that you are doing. I am learning Sharepoint. I followed your post about the Cascaded dropdown lookups, but I was not able to execute the instructions properly as the images on the site do not appear. They are blank. I tried on different browser but the problem persists. I checked other posts as well. Most of the posts have missing images. Please upload the Images again.

      Thank you.

  3. March 9th, 2010 at 18:03
    Reply | Quote | #4

    Awesome feature. Unfortunately, I cannot edit “Add new entry”-forms, even with websitecollection-admin rights. Is there any way to make this possible?

  4. March 9th, 2010 at 18:19
    Reply | Quote | #5

    another bummer. Found out your infopage on how to Insert CEWP and managing code. For those as blind as myself:
    http://www.sharepointboris.net/the-power-of-content-editor-webpart/inserting-cewp-and-managing-code/

  5. MarcvanEijk
    March 12th, 2010 at 00:00
    Reply | Quote | #6

    Hey Boris, the script is a real lifesaver. Its working great with 19- items. When i then add two more items (so it’s 20+) it no longer works. I’m i missing something?

  6. MarcvanEijk
    March 12th, 2010 at 00:11
    Reply | Quote | #7

    I troubleshooted some more. You are right (of course ;-) . The functionality is working with 20+ on the lists (parent and child).
    What i did was to set the Parent lookup to required and then it will stop working. When i took the requirement off it worked again.
    I think it probably has something to do with the deafult value of the lookup.
    Any idea’s how to get the lookups required without breaking your script?

  7. Boris Gomiunik
    March 12th, 2010 at 08:33
    Reply | Quote | #8

    I didn’t consider that. Thanks for poionting the issue out. I’ll take a look into it.

    • Gunter
      July 19th, 2010 at 17:06
      Reply | Quote | #9

      I also have some trouble with required fields. When the child lookup field is set to required it returns an empty child list when no child exists for the selected parent (fine so far). However, selecting another (valid) parent does not populate the child list again – it remains empty. The cascaded dropdown mechanism breaks if the user selects a parent that does not have a child. The situation is even worse if the first item in the parent list does not have a child. In this case its never possible to get a populated child list. The comment above helped me to find a work around – thanks for that. It works fine if I allow empty child values (but that’s not what I want). By the way, my parent list has more than 19 entries – the child lists are small). Do you have any solution yet?

  8. vlas
    April 12th, 2010 at 12:43

    i have treid to do the same with a script on my own.
    i retrieved the data from a specifiek list with JQuery.
    i can put the results from the query into the dropdown.
    “it is a dropdown created in the document library settings.”
    when i run my editpage in sharepoint an i select a value from the dropdown, than i click on the “OK” botton for saving those value’s.
    i recieve a error “an unexpected error has occurred”.

    control1 = getTagFromIdentifierAndTitle(“select”,”DropDownChoice”,”dropdown1″);
    data for recieving the items.
    *******
    for (var i=0; i<rows.length; i++)
    {
    var item = rows[i];
    var myOption=new Option();
    myOption.text = item.getAttribute('ows_Title');
    myOption.value = i;
    control1.options[i] = myOption;
    }

    i looked at your code: the following line wont work with my.
    i think with a dropdown iets a different value than "childLookup".
    var choices = CascadingDropdown.childLookup.Object.choices;
    i have allsow tried to do something like this:
    control2.options[y](attributeValue(resultNodes[y], "ows_" + CascadingDropdown.childLookupTargetField) + "|" + attributeValue(resultNodes[y], "ows_ID"));

    what im a dooing whrong?
    thanks

  9. Vaibhavi
    June 8th, 2010 at 14:48

    I have to create cascading drop down in my list under my sharepoint site.
    There are two drop down country and state……

    I did the following steps….

    1. Create country list where title field specifies the country names.
    2. Create state lsit where title field specifies the state name and country field specifies the country name corresponding to state name. In this list country is lookup field for country list.
    3. Then i craete a country_state list where i added country and state column,both are lookup field for country and state list.
    4. After then i craete a new form in SPD under my country_state list and added the following script below

    and also added jquery.SPServices-0.5.5.js to my document library.

    $(document).ready(function() {
    $().SPServices.SPCascadeDropdowns({
    relationshipWebURL:”link of my sharepoint site”,
    relationshipList: “State_List”,
    relationshipListParentColumn: “Country”,
    relationshipListChildColumn: “Title”,
    parentColumn: “Country”,
    childColumn: “State”,
    debug: true
    });

    });

    But its not working

    When i open my country_state list and select any one country in drop down, states for that country will not be appear in the state drop down. Please solve my problem :(
    Waiting for reply.

  10. Boris Gomiunik
    July 18th, 2010 at 16:12

    Why don’t you use the script provided here instead of jquery?

  11. Dhawal
    August 5th, 2010 at 08:47

    Hi Boris,

    When we are using this we are getting a javascript error saying “‘CascadingDropdowns[...].parentLookup.Opthid’ is null or not an object”

    Any help on this will be highly appreciate.

    Thanks,
    Dhawal

  12. syloo
    August 17th, 2010 at 01:02

    Hi,

    Exactly the same issue as Dhawal “‘CascadingDropdowns[...].parentLookup.Opthid’ is null or not an object”. I follow the demo, but it doesnt wok.
    Help will be appreciate :-)

  13. Doc
    August 24th, 2010 at 15:16

    Hi,

    I have exactly the same problem as Dhawal & syloo. Is there a reason for the script not working?

    • Jop
      October 29th, 2010 at 01:41

      Same error here, as Dhawal, Syloo and Doc, Why is not working?…Thanks

  14. Heather
    September 1st, 2010 at 15:59

    Hi,

    I’ve got the cascading dropdowns working fine on my form, but what I want to do is add them onto the landing page of my site as 2 dropdown boxes…..I still need them to be on the form, but also independantly on the landing page but referencing the original list.

    Any ideas?!?

    Thanks

  15. kevers
    October 12th, 2010 at 00:55

    Hi Boris,

    I am reciving this error and I have checked permission and field names. Any other ideas?

    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.3; .NET4.0C; .NET4.0E; MS-RTC LM 8) Timestamp: Mon, 11 Oct 2010 21:45:58 UTC

    Message: ‘CascadingDropdowns[...].parentLookup.Opthid’ is null or not an object
    Line: 17
    Char: 4
    Code: 0
    URI: http://sharepoint01/Documents/spcd.js

  16. KK
    October 29th, 2010 at 04:40

    I have exactly the same problem as Dhawal & syloo. Is there a reason for the script not working?

  17. Kate
    November 3rd, 2010 at 09:12

    I have the same problem.

    Сообщение: ‘CascadingDropdowns[...].parentLookup.Opthid’ – есть null или не является объектом
    Строка: 17
    Символ: 4
    Код: 0
    URI-код: http://sp/KatyaTest/DocLib/spcd.js

    P.S. It’s russian SPF..

    • Kate
      November 10th, 2010 at 13:19

      You should interchange web parts. Put Context Editor below DataFormWebPart/

      • January 24th, 2012 at 09:04

        Hi Kate,
        U rock!

        Thanks for the help! It helped me in fixing the issue.

  18. Micchy
    November 6th, 2010 at 07:11

    Hi Guys!

    I’ve encountered the same problem, to fix this move the cascading webpart after the entry fields webpart.

    Good luck!

  19. November 11th, 2010 at 20:21

    “‘CascadingDropdowns[...].parentLookup.Opthid’ is null or not an object”

    You MUST put the webpart with javascript code below the form.

  20. essined
    January 14th, 2011 at 18:10

    is this possible for DataSheet view?

    • Boris Gomiunik
      May 25th, 2011 at 06:48

      Unfortunately not. The datasheet view is an ActiveX control and I doubt it has javascript access.

  21. March 17th, 2011 at 23:02

    This is awesome. Works like a charm!!!
    Is it possible to have two lists affect one child list. I choose from list A and it shrinks list C. I choose from list B and it shrinks list C to only items that match both List A and List B?

    I Hope that makes sense. Thanks for any input.

  22. Geno
    March 28th, 2011 at 20:52

    Hi Boris, Excellent work on the cascading dropdowns. One issue: When the child field is set to Required, it stops working. Will you be addressing this?

  23. Vince Dalton
    April 18th, 2011 at 07:41

    I have the content editor at the bottom of the page, but am still getting the same error. Has this problem been resolved?

    Thanks!

  24. JustinK
    May 10th, 2011 at 17:43

    I am having an issue with the parameters part. Here are my tables/fields, would like to get some assistance in getting the right values.

    Business Units (/Lists/Business%20Units/AllItems.aspx)
    Title Field=Title

    Department/Division (/Lists/DepartmentDivision/AllItems.aspx)
    Business Unit Field=Business%5Fx0020%5FUnit
    Department/Division Field=Title

    Part A – Initiation (5.1.2) (/Lists/Part A Initiation 512/AllItems.aspx)
    Initiating Business Unit (5.1.3) Field=state
    Dept/Division Field=Dept%5Fx002f%5FDivision

    * Part A has more fields but these are the ones that will be involved in the lookup process, BU on part A will determine what is in dept drop down.

  25. June 15th, 2011 at 14:10

    Great script!
    I am having a problem getting the script to work within an ascx control using a field iterator.

    Any idea why it would not work outside of a standard webpart?

  26. Francois Wahl
    August 8th, 2011 at 17:09

    Gunter :I also have some trouble with required fields. When the child lookup field is set to required it returns an empty child list when no child exists for the selected parent (fine so far). However, selecting another (valid) parent does not populate the child list again – it remains empty. The cascaded dropdown mechanism breaks if the user selects a parent that does not have a child. The situation is even worse if the first item in the parent list does not have a child. In this case its never possible to get a populated child list. The comment above helped me to find a work around – thanks for that. It works fine if I allow empty child values (but that’s not what I want). By the way, my parent list has more than 19 entries – the child lists are small). Do you have any solution yet?

    I had the same issue still a year later. I have checked the script and noticed the following line in function “filterChildLookup”

    if(CascadingDropdown.childLookup.Object.options[0].value == 0) startAt= 1;

    The above line of code is executed before any items are added to the child list.
    The problem here is that if you had a parent selected which had no child items the actual list was not initialised as the “” field is not added in a required field.

    Thus the line of code fails/crashes (without error off course!!) and the code following will never execute.

    To fix this I simply added a check to see if the list has been initialised by checking it’s length first.

    Replace line:
    if(CascadingDropdown.childLookup.Object.options[0].value == 0) startAt= 1;

    With:
    if(CascadingDropdown.childLookup.Object.options.length > 0)
    {
    if(CascadingDropdown.childLookup.Object.options[0].value == 0) startAt= 1;
    }

    That fixed it for me and got it to work with required and optional dropdown lists.

    Hope this helps.

    • Francois Wahl
      August 8th, 2011 at 17:10

      adding a missing note above. It is the “”none” default item which is not added to a required field, hence the list never gets initialised.

      • Boris
        April 17th, 2012 at 22:26

        @Francous: Thanks for the tip.

  27. Reggie
    August 26th, 2011 at 15:20

    Hi,

    I am getting a the follwoing javascript error on page load itself.
    ‘null’ is null not an object

    What could be the problem

    Thanks
    Reggie

    • Boris
      April 17th, 2012 at 22:24

      @Reggie: looks like you’re trying to refference a field that doesn’t exist. Can you verify that you have proper display name of the field?

  28. Paul
    October 11th, 2011 at 19:00

    This solution works until I hit 21 rows in the child list. any ideas???

  29. Corinne
    December 6th, 2011 at 17:53

    I followed everything exactly, when I click the Parent dropdown I am getting “There was a problem with the request.” Any ideas?

    • Boris
      April 17th, 2012 at 22:10

      There is a problem with the request means an error with your async query. Probably some faulty naming of fields or lists.

  30. February 14th, 2012 at 07:43

    Hi Dave,
    Here in this link, you will find the step by step explaination for creating two filtered values in lookup columns in SharePoint.
    http://sharepoint-works.blogspot.com/2012/02/cascading-dropdown-or-filtered-values.html
    Hope this helps you!