Cascading Drop-down Lists in a parent, child relationship

April 30, 2008 at 5:52 pm 10 comments

I see this topic come up over and over again in SharePoint. I am going to elaborate on this post when I have a chance.

I am creating this post as a reminder to myself of what I did to make it work.

i started by downloading and installing an add-on from the following site:

http://datacogs.com/datablogs/archive/2007/08/26/641.aspx

Now when I create a new column – I have the option to create a column type of parent drop-down list or child drop-down list.

The selection from the parent will filter the results in the child drop-down

I had to enable Auto Postback on the drop-downs to make it work properly.

If anyone needs this immediately, let me know and I’ll make is a priority.

UPDATE

I am finally able to get around to documenting this procedure. I needed to get it done, because I’m pretty sure that I would have a hard time remembering what I did if I had to start again from scratch. Here is my scenario for the cascading (parent-child) drop-down lists:

I have an application that tracks the progress of programming tickets through the company’s specification review process. The initial programming request contains two drop-downs; one for Project and another for Program. My users wanted the Program list to be filtered to only show programs that belong to the selected Project. The user would select a Project from the first drop-down list and it would filter and refresh the options in the second list.

Setup

After installing the Datacogs program, I created a list containing the Projects. The list contains one column called Project Name.

For Example:

  • Project 1
  • Project 2
  • Project 3
  • etc. 

Next, I created the Programs list. The list contains two columns; one with Program Name and the other a lookup to the Project list. Entries in the Program list are associated to the Project list.

For Example:

  • Program 1 – Project 2
  • Program 2 – Project 2
  • Program 3 – Project 1
  • Program 4 – Project 3
  • etc.

Note: Both of these lists can be created in SharePoint WITHOUT the Datacogs add-in.

Connect the dots

Now comes the fun part. In my new list, the Specification Tracker, I need to perform the cascading drop-down. When I create a new column, with the Datacogs add-in installed, I have an option to create the column with a column type of Parent Drop Down list.

I create a column called Project and set the type to Parent Drop down list. The Parent drop-down list contains a new option:

Parent Drop Down List Details

 
 
 

Enter a SharePoint site URL (in this Farm)then click the Load Lists button. Select a List and Column in the remaining dropdowns
Site URL

List Name

Column For Value

Column For Text

I have to specify the site that contains the list (to perform the lookup on). I really like this feature as I should be able to select lists from any of the SharePoint sites on the same collection. I haven’t tested the feature yet, as both times I’ve used it, the lists were always on the same site.

Once you select the site, click Load Lists. Now you can select the List Name from the drop-down. I selected the Project list I created earlier. Then I have to select the Column for Value (that’s what the selection will pass to the child list). Finally select the column for Text – which is what will display on the screen in the drop-down.

Follow the same steps for the Child drop-down list, except that you have one more selection to make. There is an additional drop-down for Column to Join to Parent. In my case, I selected the Project column in the Program List as the column to join.

Presto, you are done. If you open the newform.aspz for the list, and select a Project, the options available in the Program list will be filtered to only show the ones that belong to that project.

Advertisements

Entry filed under: SharePoint Designer.

Create a drop-down list with a lookup to another site SharePoint Contact Lists play nicely with Outlook

10 Comments Add your own

  • 1. ThiNg  |  May 16, 2008 at 2:41 pm

    I have continued researching this issue and have found a tiny problem with the solution – you cannot use the cascading drop-down in the Edit in Datasheet mode. I don’t allow my users to use that mode so I didn’t realise there was a limitation.

    Reply
  • 2. PJ  |  September 19, 2008 at 1:52 pm

    Nice article.

    I would like to know if I will be able to acheive the following through your example. Please let me know.

    I have a requirement wherein within a calendar entry form, user will need to select a STATE (coming from an external sql table) and then associated ‘cities’ (coming from an external sql table) for the previous selected STATE. Here for one STATE user can select one or more cities.

    After this USER will continue to select more STATES (and then for each selected STATE cities again). This goes in a loop and it can be 3 or 4 or 2 or 10 STATES.

    I see this as master-detail where I do not know how many STATES would be selected ( and how many CITIES for each STATE).

    Is this possible in WSS ? If not in MOSS (with Infopath ?)

    Appreciate your help.

    Reply
  • 3. SharePoint Developer  |  September 29, 2008 at 3:21 pm

    I understand that this is a valid solution for WSS 3.0 where custom field types are supported. Are you aware of similar solution that can be implemented in WSS 2.0 and SPS 2003?

    Reply
  • 4. ThiNg  |  September 29, 2008 at 3:26 pm

    At SharePoint Developer,

    I don’t have any experience with WSS 2.0 and only started in Sharepoint 2007. If there is another reader who knows the answer please post it.

    At PJ,

    I am looking to test the situation you are describing but I get the feeling that you are into more of the development side where you would need a programmer. I programming skills are limited to Javascript and PHP and I haven’t fiddled with .NET at all (except for the tiny customisations you see in my blogs).

    Sorry I couldnt be of more help!

    Reply
  • 5. astatke  |  October 9, 2008 at 6:00 am

    Where can I get the addon so that I can download and use it.

    Reply
  • 6. ThiNg  |  October 9, 2008 at 11:58 am

    The link is in the article. Make sure you read their instructions and limitations. I didn’t create this so I can’t offer any support or anything.

    Reply
  • 7. Candice  |  November 3, 2008 at 9:58 am

    This controls works perfectly when using it in 1 environment. As soon as I backup and restore from DEV -> QA / PROD server, the new item / edit item form cannot be accessed and the parent/child field setting cannot be accessed either. The error shows that its looking for the URL where the drop downs are looking up its information from.

    Have anyone encountered something like this? If so, did you find a solution to it? Something like an automatic refresh after a backup and restore is done?

    Thanx alot,
    Candice

    Reply
  • 8. mike  |  February 12, 2009 at 2:47 am

    i receive object not set to reference after i create the parent drop down and go to newform.aspx.
    I created projects list and included 5 projects under the title column.
    in the parent field i selected projects for the list name. then title for the value and text. What did I do wrong? thanks

    Reply
  • 9. Lan  |  April 23, 2009 at 5:21 pm

    How can I get this to work in the Office 2007 Document Information Panel (DIP) or Edit in Datasheet view? The cascading works fine, but when I open up the document, the DIP gray out the value and said “Edit in server property”. How can get the value to show in the DIP. Could someone please help me.

    Thanks

    Reply
  • 10. roopa  |  February 9, 2010 at 12:16 pm

    Gr8 work but when i try to make changes to the parent column in list through code, it throws the following error:

    Field type ParentDropDownList is not installed properly. Go to the list settings page to delete this field.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed



%d bloggers like this: