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.

Setting up the view of results in Cross-site Lookup column for SharePoint 2013 and Office 365

CodeProject

In the previous entry I demonstrated how to create Cross-site Lookup Columns. Now I will show how to use Advanced section fields of Cross-site Lookup column management dialog and how to modify the template of the result set in particular. With our Cross-site Lookup you can add extra fields to the result set to simplify search and navigation through the large lists. Here is illustration from the official website:

I will use the same environment that I created in the previous entry: Countries list in the root site of the root site collection and Target list in the nested site collection.

Here I will show how to place the flag near the country name in the lookup result set. First, I added Hyperlink or Picture column and called it Flag. Next, I set flags for all the countries in my list:

Now, go to Target list and open Cross-site lookup management window. Select Country field and expand Advanced settings at the bottom of the dialog.

Here you can find two templates. The first one 'Request items' is used to request items from the source list through SharePoint 2013 rest service. Here we can define sorting, additional filtering and searching criteria for result sets. We have to insert our Flag column into select statement to retrieve it from the server and build it into the item template which is used in the result set.

Here is my 'Request Items' template:

function (term, page) {
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Flag&$orderby=Created desc&$top=10";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Flag&$orderby={LookupField}&$filter=startswith({LookupField}, '" + term + "')&$top=10";
}

The second template 'Item format' defines the view of an item in the result set. Here we will use our Flag field that will be returned from the server by the request defined in the first template. Here is my 'Item format' template:

function(item) {
  return '
' + item["{LookupField}"] + '
';
}

Now let's see what our Country field looks like in the forms:

Excellent! In the following article I will show how to use Cross-site Lookup with Forms Designer and create dependent columns including cascading dropdowns.

Cross-site Lookup column for SharePoint 2013 and SharePoint Online in Office 365

CodeProject

In this article I would like to present our new solution Cross-site Lookup for SharePoint 2013 and SharePoint Online in Office 365. With help of this feature you can create lookup columns with quick search and asynchronous loading of results for lists and libraries to link data from any sites within a site collection.

First, I would like to cover the technical aspects of this solution. Our lookup is not a custom field because custom fields can be deployed only as part of farm solutions which are not available for Office 365. So, we made it as a number of JavaScript templates (view, edit, new and display) for standard Lookup field. We used JSLink property of SPField class to inject these templates and Select2 control with some modifications as control for searching and navigating through the source list.

Now I will demonstrate our solution in action. In the root web of the root site collection I created a list of countries. Next I created a new site collection in the same web application and added a custom list to its root site and named it Target. Ok, let's create a new lookup column in Target list which links to Countries list in the root site collection.

Open the view of Target list and click 'Plumsail Lookup ' button in 'List' tab of the ribbon.

If you don't have the existing lookup column in your list you will see a dialog for creating a new lookup field. Enter the name of this field in text box and click Ok.

After the field has been successfully added you can configure it. First, let's change the source list. Click 'Change ' link near the web url to specify the web of your source link. I clear this field because my source list is located in the root site of the root site collection. Ok.

Now select the source list in the next dropdown and the field of the source list which will be shown in the target list. In my case the source list is Countries and the field is Title. Ok.

By checking appropriate options you can enable multiple values for your lookup and add 'New Item' link which allows users to add new items to the source list directly from the form.

If you check Add 'New Item' link you will see the following options:

In the first field you can specify the title of the new item link. The second one is for default content type ID. When the user clicks the new item link they will see a form for the specified content type. If you leave this field empty, users will see a form for the default content type of the source list.

Here is what this link looks like in the form:

I will cover Advanced section fields in the next article. Now, let's see what our lookup field looks like in forms and list views.

Lookup column with a single value:

Lookup column with multiple values:

List view:

Display form:

Looks perfect. In the next articles I will demonstrate how to change the template of the result set in the select box of Cross-site Lookup Column, how to use Lookup and Forms Designer together and create dependent columns and cascading dropdowns.