Guest Blog by Mike Bailey: How to Add a Cascading Drop Down in SharePoint 2007 Via jQuery and jPoint

As far as SharePoint "wish list" items go, one item that seemingly everybody wants is cascading drop downs in good old-fashioned SharePoint forms pages.  I have seen a couple of approaches to this accomplish this, but they have required the use of server-side objects. In this post, I will use JavaScript with no new Web Parts to provide cascading drop downs in SharePoint forms pages.

I do want to add a disclaimer that I don't know the full range of browsers that this solution will work in, as I am working exclusively in an IE7/8-only environment, and haven't tested the solution in other browsers/environments.

If you are familiar with jQuery and jPoint, you may just want to skip to the end and look at the code.  Otherwise, I am going to go step-by-step as much as I can.

 

Pretty cool, right? Select Honda and only see Hondas.  I made a list for cars that has both a make and model field but you could use the same idea for search pages too.

 

          I. SETTING UP THE EXAMPLE

Let's start with the easy stuff first.  I am creating a new site with three lists.

I made a CarMake list with just the default field loaded as shown below:

 

           

Then I made a CarModel list as shown below, and added a second column called CarMake as a lookup to the CarMake list and saved Item.

 

 

And I'll load it:

 

And one final list called Car that has a lookup to both lists.  The lookup to CarMake looks just like the example above and the lookup to CarModel is very simliar:

 

 Okay, let's manually add one record first. I went to the list and hit Create.

 

Nothing too special yet, and there is nothing to keep us from, for example, a Ferrari Civic, but we will get there soon.

 

          II. ADDING THE LIBRARIES

We need two libraries to make the magic work.

   1. jQuery – a great library that extends JavaScript. We need this to make jPoint work, and it also makes selecting, updating, and event binding easy. You can download the library here:

http://jquery.com/

            Or you can just link to Google hosted version, about which you can learn more here:

http://code.google.com/apis/libraries/devguide.html#jquery

   2. jPoint – a library that combines jQuery and SharePoint. We need this to get list information from JavaScript. This library will make calling the Web services of SharePoint easy.

http://jpoint.codeplex.com/

For this demo, I am going to edit the new page.  In the real world, I work on very specific pages, but let's keep the demo simple.

Next I opened Designer, navigated to the cars folder under lists, and added an empty JavaScript file:

 

Note: There are huge conversations on how to add the libraries, but I am trying to keep this simple.  I will do everything in one JavaScript file and drop it on the page with a Content Editor Web Part.

Next I will edit the page, add a Content Editor Web Part, and link our new file:

 

 

You can't see it from the picture above, but I marked the Web Part as hidden.

Save your page, and then let's write JavaScript.

I stole the following code directly from the jPoint site.  For this demo, we are getting the libraries from Google. Add the following so this is the only thing in your js file:

<script type="text/javascript" src="//jqueryjs.googlecode.com/files/jquery-1.3.2.min.js" ></script>
<script type="text/javascript" src="//sharejpoint.googlecode.com/files/jPoint.min.js" ></script>

 

Next let's write a little jQuery. Below the script tags add the following:

<script type = "text/javascript">
    $(document).ready(function()
    {
              alert("jQuery Rocks!");
    });
</script>

 

Save and run the page, and you should see a message box.

For those of you new to jQuery, let me explain this a little. Most of the time, you need to do something once the page is done loading. jQuery gives you the $(document).ready event. You can have as many of these as you like, and jQuery will happily run them all, but not until your page is ready!  The jQuery site has wonderful tutorials if you would like to learn more.

 

          III. WRITING A LITTLE CODE.

One of the best parts of jQuery is its ability to select objects.  SharePoint pages have made this very difficult in that IDs are crazy, and class names are very not unique.  But wait, the control does have a unique, predicable tool tip!

I can easily grab those objects via the tooltip.  They are inputs (form fields) so we use :input and we know the titles, so I can get the items as shown:

var MakeInput = $(":input[title='CarMake']");
var ModelInput = $(":input[title='CarModel']");

 

Okay, we have figured out how to get the HTML controls SharePoint added to the page.  Now we need to do something with them.

Next let's bind the event "change" on the Make dropdown. I made a new function called MakeChanged and set it to the change event of MakeInput.

<script type = "text/javascript">
    $(document).ready(function()
    {
       var MakeInput = $(":input[title='CarMake']");
       var ModelInput = $(":input[title='CarModel']");
       MakeInput.change(MakeChanged);
     });
       function MakeChanged()
    {
              alert("Make Changed");
    }
</script>

 

Save and run it, and you get an alert each time you select a different model.

Let's recap: we made a couple lists with lookups, we added some code libraries, we grabbed the objects we wanted, and we wired the event. So where is the data?

Hold on!

We need to figure out our options.  Our lists are small.  A user can click Acura, then Ferrari, and then Honda very quickly. We could proceed two ways:

  • Get the list of models from the server every time the make is changed.
  • Get the whole list in the beginning and save it, parsing through when make is changed.

I promise I will post details on how to do number 1 another time.  We are going choose number 2 today.  In other words, one call to the list in the beginning and from then on it's just a JavaScript variable.

 

          IV. HERE COMES JPOINT

Now I added a couple lines of pack a lot of magic.

var jPointSite ="/Demo/TestTemplate/Cascade";  //Set this to your site

var qry = "<OrderBy><FieldRef Name='Title' Ascending='TRUE' /></OrderBy>";

CarModelTable= jP.Lists.setSPObject(jPointSite , "CarModel").getSPItemsWithQuery(qry);

 

Also I added the following outside of the function to make a global variable:

var CarModelTable;

 

The site is self-explanatory, but the qry is actually a CAML query.  In this case, I set a sort but I could have done anywhere clause also.  Then all we do is put it to the jPoint method, and our data comes back.

Now I need to figure out what my data looks like. I used IE8's developer tools, started script debugging, set a break point, and run to that point. Awesome stuff, but I can't really show all the steps here.  Instead look at the screenshot:

 

 

I set a watch on my CarModeTable variable and learned a little about my data.  I really only want a few fields though, so watch how I can modify by jPoint call:

CarModelTable= jP.Lists.setSPObject(jPointSite , "CarModel").getSPItemsWithQuery(qry).getItemsFieldData(['LinkTitle','CarMake','ID']);

 

Now the variable I am holding will be pretty light. Here is what it looks like now:

 

          V. Finally, let's use the data in the change event:

I need to find the selected value:

var Make =    $(":input[title='CarMake']").val();

 

Next let's clear the out the old list for models:

var ModelInput = $(":input[title='CarModel']");
var ModelInputId = ModelInput[0].id;
$("#" +  ModelInputId + " > option").remove();

 

The first line gets the ModelInput control, the second gets the ID, and the final line gets all the options under that ID and removes them! Note the [0] gets the classic HTML DOM object that I needed for ID and # is used to get an object by ID.

Now for some more jQuery goodness. Let's use $.each. For each item in our table, we will call a function with "item" representing one data item (model).

  $.each(CarModelTable, function(i, item) {
              alert(item.LinkTitle);
    });

 

This code will display each model.  I do have to deal with make, since it will come back like "3;#Ferrari".  The ID, a semicolon, a number sign, and then the text. This is what SharePoint is working with under the covers so we will split and parse:

var ItemId = item.CarMake.split(";")[0];   

 

Okay, then just compare each item, and if the make IDs match, then create a new option and add it to the list:

$.each(CarModelTable, function(i, item) {

       var ItemId = item.CarMake.split(";")[0];   

       if(MakeId == ItemId )

              ModelInput.append($("<option>").attr("value", item.ID).text(item.LinkTitle));

 

Run it. We now have working cascading drop down without anything other than code!

 

Complete code file:

<script type="text/javascript" src="//jqueryjs.googlecode.com/files/jquery-1.3.2.min.js" ></script>

<script type="text/javascript" src="//sharejpoint.googlecode.com/files/jPoint.min.js" ></script>

 <script type = "text/javascript">

     var CarModelTable;

 $(document).ready(function()

    {

        var MakeInput = $(":input[title='CarMake']"); 

       MakeInput.change(MakeChanged);

       var jPointSite ="/Demo/TestTemplate/Cascade";  //Set this to yours

       var qry = "<OrderBy><FieldRef Name='Title' Ascending='TRUE' /></OrderBy>";

       CarModelTable= jP.Lists.setSPObject(jPointSite , "CarModel").getSPItemsWithQuery(qry).getItemsFieldData(['LinkTitle','CarMake','ID']);

     });

    function MakeChanged()

    {

       var MakeId = $(":input[title='CarMake']").val();

        var ModelInput = $(":input[title='CarModel']");

       var ModelInputId = ModelInput[0].id;

       $("#" +  ModelInputId + " > option").remove();

       $.each(CarModelTable, function(i, item) {

              var ItemId = item.CarMake.split(";")[0];   

              if(MakeId == ItemId )

                     ModelInput.append($("<option>").attr("value", item.ID).text(item.LinkTitle));

    });

    }

</script>

 

I hope you have enjoyed my first post!

SharePoint

Applications

SharePoint apps are stand-alone applications that perform specific tasks on a SharePoint site. Apps can perform functions such as managing a discussion board or knowledge base, performing project management or time tracking tasks, or doing other workflow operations.

SharePoint

Product Suites

Experience greater power and savings by bundling our SharePoint apps and web parts.


Essentials


Essentials Plus


Bamboo Premier


Project Management Suite


Knowledge Management Suite


External User Manager


SharePoint

Web Parts

Extend SharePoint beyond its out-of-the-box capabilities by tailoring it to your requirements with Bamboo Solution’s growing portfolio of Web Parts. Web Parts are the building blocks of pages on a SharePoint site that can be used to customize the user interface and content of a site page. 

SharePoint

Product Suites

Experience greater power and savings by bundling our SharePoint apps and web parts.


Essentials


Essentials Plus


Bamboo Premier


Project Management Suite


Knowledge Management Suite


External User Manager


Office 365

Cloud Parts

Cloud Parts are functional components that extend your SharePoint environment whether it’s hosted, on-premises, or part of Microsoft Office 365. More than mere ports of existing software to the cloud, our Cloud Parts have been built from the ground up to take advantage of the best that the cloud has to offer.

SharePoint

Product Suites

Experience greater power and savings by bundling our SharePoint apps and web parts.


Cloud Parts Suite for O365/SP Onl.


Featured Services

SharePoint Health Check

A SharePoint Health Check will identify the causes of issues and risks associated with your specific environment, and is custom tailored to provide you with the best recommendations to optimize your SharePoint environment.

SQL Health Check

Document recommendations relating to performance, stability, availability, or a specific focus you request of your SQL Server database instances.

My SharePointXperts

The truth is that each SharePoint skill may not be a full time job for many organizations, and it is nearly impossible for one person to do everything you need – so augment your team with SharePointXperts; providing the skill sets you need when you need them!