Pages

05 April, 2012

Sharepoint 2010 and Cascade Lookups

Here I am for another great tip. One of the things that lack in Sharepoint 2010 is the ability to create Cascade Lookup columns. This is, to filter the options of a lookup column based on the value of another.

Around the web you can find many solutions, I used one hosted in Codeplex http://spcd.codeplex.com. The documentation needs some tweak, here you might see how I managed to put it to work.
Also, it has a little problem with Chrome, that I solved.



The problem
Supose the following, you have 3 lists.

List1 > Column1


List2 > Column2 and Column3

Column3 is a lookup connected to Column1 in List1

List3 > Column4, Column5 and Column6

Column4 is a lookup connected to Column1 in List1
Column5 is a lookup connected to Column2 in List2

Now the goal is to filter the options of the lookup field Column5 based on the value of Column4.

The solution
1. Download spcd.js 
There is a version of spcd.js in Codeplex, but that version does not work in Google Chrome. I have created a new version which can be downloaded frm here.

2. Upload spcd.js to your Sharepoint site
Now that you have spcd.js script, just upload it into your Sharepoint site. You can upload it to a Library or use Sharepoint Desinger to upload it into a Folder. Up to you, pick the one you like more.

3. Create the lists in Sharepoint
If you have not created the lists, now it is the time. Create the lists and connect them with Lookup columns.

4. Create a new item custom form and make it default form
Use Sharepoint Desinger to create a new custom form, in your list with the two fields (mine List3). Don't forget to mark it as the Default one.

5. Add a Content Editor web part to the new form
Now, edit the newly created form and add a Content Editor web part after the form webpart (the script will not work if you place the Content Editor before the form webpart)

6. Configure the Content Editor web part
Last, edit the Content Editor web part in HTML and add the following code to it:

<script src="<script_location/spcd.js" type="text/javascript"></script>
<script type="text/javascript">
var ccd1 = new cascadeDropdowns("Column4", "Column5", "Column3", "List2", "Column2");
</script>

where:

script_location: is the folder or library path where your uploaded spcd.js. Use relative path, if you place the full http:/server/library/scd.js, the script will not work.
Column4: Is the display name of the Parent column in List3
Column5: Is the display name of the Child column in List3
Column3: Is the internal name of the column in List2 linked to List1
List2: Is the display name of List2
Column2: Is the internal name of the column in List2 that feed the Child column (Column5)

PS: When creating column, avoid special characters in the name, as the internal name will be a mess because of it. It will work, but the column is a bit more ugly.

7. Try it!!
Ok, now that you put it all together just try to add a new item into List3. You should be getting something like this:

If not, well e-mail me pedro.rosae@gmail.com, I will help you out.

Want a shorcut, download a site template fully configured here. The language of this template is English, so if you are using a non-english Sharepoint installation don't forget to install the English Language Pack, check how to do it at Installing Sharepoint Foundation 2010 Language Pack - Windows 7.

11 comments:

  1. Great article! But one thing not sure if it is a bug. By default the parent lookup have (none) value and the child dropdownlist contains all child list value. Did you find solution for this?

    ReplyDelete
    Replies
    1. Indeed, still trying to solve it :(.

      Delete
    2. Actually, I think that is the program approach but not a bug. Whereas, is there any solution to display multi-fields from the tables? For example, getting the Staff ID to display relevant information such as department, position etc.

      Delete
    3. Well without trying and would create a calculated column to display the info of the pretended field and use it as the Display column. But give me a few to try it.

      Delete
    4. Hi,

      IE will show (none) as the first record in the list if the lookup list in the dropdown contains more than 20 items.

      Chrome should not give this bug.

      Delete
  2. I tried this without success, then set it up with the exact same names for lists and columns just to make sure I didn't misunderstand anything and it still doesn't work: the HMTL shows on the New Item screen below the Save and Cancel buttons, but that's it. Also the link to the spcd.js shows there, and when I click it, it takes me to the JS alrigth, so no mistake there either. I'm on SP2010 and Internet Explorer 8.

    Any suggestions would be greatly appreciated

    ReplyDelete
    Replies
    1. Hi again, I'm bit confused with the description of your problem. Can you send me an e-mail with some screenshots. Would be a pleasure to help you.

      Regards,
      Pedro

      Delete
    2. I'd love to send an email, but to what address ?

      And I meant to say that the JS shows of course, but I also tried an alternative way: instead of a CEWP I added a HTML Form WP with the same JS in it. Unfortunately there I get "Error on page"

      Delete
  3. Hello,

    I am also having trouble getting this to work. I have the CEWP setup with the html and the url to the .js file. When I select my first drop down item I get an error. Can you help me sort this out? If so, my email is michellekorb@outlook.com.

    Thank you so much

    ReplyDelete
  4. E-mail sent. If you don't want your e-mail address public you might now delete the comment, or I can do it for you.

    ReplyDelete
  5. Also having trouble... I am novice, so may be something simple. Followed all instructions, but get no modification to the normal behavior of the Lookup fields (i.e. no apparent connection between the two). Also first lookup (the filter) does not display the , but sets to 1st value in the lookup list, if that is a clue to something. i noticed several examples where you must select from the drop down.

    I made all script mods to the "Source Editor" of the CEWP, and placed below the List form.

    *scratching my head*

    ReplyDelete