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 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_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, 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.