Click here to Skip to main content
16,020,565 members
Articles / Programming Languages / Javascript

Cascading drop-downs with Cross-site Lookup for SharePoint 2013 and Office 365

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
7 Aug 2013CPOL2 min read 32K   2   2
Cascading drop-downs with Cross-site Lookup for SharePoint 2013 and Office 365.

In the previous entries I showed how to configure Cross-site Lookup column and how to modify the template of the lookup result set. Now I'm going to demonstrate how to use Cross-site Lookup with Forms Designer and build dependent columns and cascading dropdown in particular with this complex solution. Please, note, Cross-site Lookup is supported by Forms Designer starting from version 2.6.4930.

Let's imagine that our company has offices in multiple countries and we have to create a ticket system. In the ticket form users have to specify their office. In order to simplify this task we can also add a country field to the ticket form. So, when the user chooses a country in the form the dropdown of offices is automatically filtered by the selected country. This behavior is called cascading dropdowns.

Ok, I will use lists from my previous articles: Countries and Target. I added a new list called Offices to the same site where Countries list is located. Then I created a simple lookup field in Offices and set Countries as its source list. So, now we can specify the country for each office. Here is a new Office form:

SharePoint 2013 lookup

Let's imagine that our Target list is the list of tickets from my previous example. We have already created Cross-site Lookup column for country in Target list. Now we have to create Office lookup field that is linked to Offices list. The both lookups do not allow multiple values. Now we have to use Forms Designer to place both of these fields into the form. Forms Designer is required in this scenario because we will use its JavaScript framework.

Here is a new Target form:

SharePoint 2013 cascading lookups

Now let's open Cross-site Lookup management window and select Office field there. Expand Additional settings. Here we have to modify 'Request items' template to load only those offices which belong to the selected country. Here is my template:

JavaScript
function (term, page) {
  // Getting the selected country
  var countryId = fd.field('Country').control().value();
  if (!countryId) {
    countryId = 0;
  }
  
  // Filtering by the selected country
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id," + 
      "{LookupField},Country/Id&$orderby=Created desc&$expand" + 
      "=Country/Id&$filter=Country/Id eq " + countryId + "&$top=10";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id," + 
    "{LookupField},Country/Id&$orderby={LookupField}&$expand=Country/" + 
    "Id&$filter=startswith({LookupField}, '" + 
    term + "') and Country/Id eq " + countryId + "&$top=10";
}

First I get the selected country field with Forms Designer's JavaScript framework. Then I use it in requests as additional filter. Pay attention to the requests. Firstly, to use a field in the filter you have to add it into the select part of the query. Secondly, if you use lookup column in the filter you have to use required $expand operator. You can find detailed info on using lookup column in SharePoint 2013 REST API in the following article: http://www.andrewconnell.com/blog/Applying-Filters-to-Lookup-Fields-with-the-SP2013-REST-API

Now, when we choose a country in the form, the drop down list of offices is automatically filtered by the selected country:

Cascading lookups in SharePoint 2013 and Office 365

The last thing we have to do is to clear Office field when Country is changed. Open Forms Designer and go to its JS-editor. Put the following code there and save the layout for edit and new forms:

JavaScript
fd.field('Country').control().change(function() {
  fd.field('Office').control().value(null);
});

As you can see Forms Designer's JavaScript framework supports Cross-site Lookup field like any standard field. So, you can dynamically get and set values, handle value changing events, make selection controls readonly and etc.

This article was originally posted at http://formsdesigner.blogspot.com/feeds/posts/default

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Plumsail
Russian Federation Russian Federation
Expert in SharePoint
The leader of SharePoint Forms Designer Team: http://spform.com
Co-founder of Plumsail: http://plumsail.com
My blog: http://formsdesigner.blogspot.com

Comments and Discussions

 
Questionazu Pin
damii24-Feb-14 3:47
damii24-Feb-14 3:47 
Questiontry this one Pin
damii15-Oct-13 19:11
damii15-Oct-13 19:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.