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

CodeProject

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:

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:

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:

function (term, page) {
  // Getting the selected country
  var countryId = fd.field('Country').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:

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:

fd.field('Country').change(function() {
  fd.field('Office').value(0);
});

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.