Filtered Lookup Column For Cascading Drop-Down List

Posted: November 19, 2010 in SharePoint
Tags: , , , ,
Toy Car

This is an example how to use the SPServices JQuery library to filter a SharePoint hierarchical drop down list (cascading lookup column) on an additional column.

For a better understanding, first read the documentation for the Cascading Drop Down List function of the SPServices.

http://spservices.codeplex.com/wikipage?title=%24().SPServices.SPCascadeDropdowns&referringTitle=Documentation

In this example, I am using –

I recently created a ‘Multi Project Tracking’ site template to handle the tracking of multiple projects and the associated tasks and releases associated with the projects.
The highest level list is the ‘Projects’ list. The ‘Projects’ list has a title and a few other columns.

A ‘Release’ list was created to track future releases of a project and a roadmap could be realized. A status column was added (Open, Locked, Closed) to let the team members know if task level issues could be assigned to this release.

An ‘Issues’ list was created to track tasks related to the projects/releases. In the ‘Issues’ list, we need to know which project and release this task is being associated with so a lookup column was created to lookup the project and the release.
In an evironment where there are only a few projects with 1 or 2 releases each, this would be acceptable. In my case, there may be 30 projects actively being tracked with a constant closing and re-opening  of future projects. You can imagine the lookup column list could get very confusing, especially if a ‘release’ name for one project was the same as a ‘release’ name for another project.
That is where the cascading drop down list function saves the day. See the link at the top to understand how to implement the cascade (hierarchical) drop-down lists.

Now that we have a trimmed lookup column based on a hierarchy there is another issue we need to solve…
Let’s suppose you release a version of your project (tracked in the Release List) and want to lock the next version so no new issues can be assigned to it.
You can change the status to ‘Locked’ in the ‘Release’ List, but the users will still be able to choose the release when adding a new itme in the ‘Issues’ list. The answer – add an additional clause to the CAML Query in the cascading drop down list function.

By adding an additional clause in the CAML query, we can filter out the items that do not have an ‘Open’ status.
This code should look familiar if you’ve already implemented the cascading drop down lists.

 <script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "Release",
   relationshipListParentColumn: "ProjectID",
   relationshipListChildColumn: "Title",
   parentColumn: "ProjectID",
   childColumn: "Release",
   CAMLQuery: "<Eq><FieldRef Name='Status'/><Value Type='Text'>Open</Value></Eq>",
   debug: true
  });
 });
</script>

 

The following line is the key to filtering the data –

   CAMLQuery: "<Eq><FieldRef Name='Status'/><Value Type='Text'>Open</Value></Eq>", 

The CAMLQuery property allows you to create additional where clauses in the query.
In the example above, I am selecting all the records where the column ‘Status’ is equal to ‘Open’
Read more about the CAML Query schema here http://msdn.microsoft.com/en-us/library/ms467521.aspx

There is another tool that helps you build a CAMLQuery. I haven’t used it but it looks interesting… It would probably also help you learn the CAML syntax faster.
http://www.u2u.net/res/Tools/CamlQueryBuilder.aspx

Advertisements

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