Wednesday, July 10, 2013

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:

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:

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:

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.

28 comments:

  1. The clear Office field when Country is changed does not work. The Office is not cleared when the Country is changed using the posted JavaScript. Is there a syntax error in the posted JavaScript?:

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

    It looks like the problem is in the change function. Can you help by reviewing the JavaScript and posting any corrections?

    ReplyDelete
    Replies
    1. I figured it out. Change the line that sets the 'Office' drop down from null (which does not work) to a blank value like this:

      fd.field('Office').control().value(" ");

      Delete
  2. Hello,

    I am not sure what i am doing wrong.... i followed your instructions (created same lists) but after i select the country field, in the office field i get "searching..."
    The 2 lists are custom type. Offices list has a lookup column to country called 'country'. Any ideas?
    thanks,
    Katerina

    ReplyDelete
  3. I just found it. The lookup field in the offices list has to be a cross-site lookup (not the out of the box look up of SP) in order to work as a cascaded field in the 3rd list.
    Katerina

    ReplyDelete
  4. sorry for multiple posts, but it seems to be working on & off, i got again 'searching' ...

    ReplyDelete
    Replies
    1. Hello Katerina,
      Please, open the browser console and check if there're JS-errors or notifications. Seems, you should correct the Request Items code.

      Delete
  5. Hi Dmitry,
    Thank you for your feedback. As i mentioned i used your example to see how it works, and hence copied your code for the requests items code.... Cant figure out what i am doing wrong.....

    I opened the console as u suggested and got this, which doesn't tell me much.... i goggled them but didn't get back anything useful.....

    ---Could not read chrome manifest 'file:///C:/Program%20Files%20(x86)/Mozilla%20Firefox/chrome.manifest'.
    mutating the [[Prototype]] of an object will cause your code to run very slowly; instead create the object with the correct initial [[Prototype]] value using Object.create Preferences.jsm:378
    ---Could not read chrome manifest 'file:///C:/Program%20Files%20(x86)/Mozilla%20Firefox/browser/extensions/%7B972ce4c6-7e08-4474-a285-3208198ce6fd%7D/chrome.manifest'.

    ---[Exception... "Component returned failure code: 0x80570018 (NS_ERROR_XPC_BAD_IID) [nsIJSCID.getService]" nsresult: "0x80570018 (NS_ERROR_XPC_BAD_IID)" location: "JS frame :: resource://cck-dawson/cckModule.jsm :: observe :: line 46" data: no] cckModule.jsm:46

    ---Error: couldn't open library scriptff.dll scriptff.js:3
    1407245106600 Services.HealthReport.HealthReporter WARN No prefs data found.

    Thank for all your help
    Katerina

    ReplyDelete
    Replies
    1. Thanks for the additional info. Have you tried to open the browser console and expand the Cross-site Lookup drop-down list? Do any messages appear in the console?

      Delete
    2. Thank you for helping me debug this. Yes it did, so i thought that instead of digging any further i deleted and recreated the lists. Then voila it worked....Thanks again
      Katerina

      Delete
  6. Hello, is it possible to filter the returned results by anything other than &$filter=startswith - i.e. contains

    Example - the lookup field contains a company name 'The Body Shop'. User types in 'Body' and would expect to get 'The Body Shop' as an option to select.

    ReplyDelete
    Replies
    1. Hi Joseph,
      Sure, please, use substringof operator:
      substringof('" + encodeURIComponent(term) + "', {LookupField})

      https://msdn.microsoft.com/en-us/library/office/fp142385.aspx

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi, I'm having a similar issue to Katerina. I've recreated your solution with the same lists, and when I open the target list and select a country, the office list just shows "Searching...". Looking at the console, it tells me "Script5009: 'fd is undefined', and points to the variable declaration var countryId = fd.field('Country').value();

    ReplyDelete
    Replies
    1. Hi Nigel,

      The form must be designed in Forms Designer as described in this post.

      Delete
    2. Hi Dmitry, yes - it's in forms designer, using '/Lists/Target/fd_Item_New.aspx'

      I'll try delete and recreate all the lists again and see. When viewing the source, the /_layouts/15/formsdesigner/plumsail.fd.* js files are there, so it's puzzling why it isn't working.

      Delete
    3. Probably, you have other JS-errors on this page before this one. Could you check the browser console?

      Delete
    4. I did manage to get this working fortunately; I'm not sure what the error was, but after retracting and re-deploying the solutions, it worked fine. Thanks for the help.

      Delete
  9. Is there a way to filter the cascading dropdown with multiple values defined? I have a site where I need to define:

    Facility (Single Value)
    Equipment (Multiple Values)
    Components (Multiple values, filtered based on choices defined in Equipment)

    ReplyDelete
    Replies
    1. Hi Jesse,
      Unfortunately, multi-value lookup fields are not supported in REST-queries, so you cannot neither filter by them nor specify them as a display columns.

      Delete
    2. Thanks for the response Dmitry. Is there a simple way to disable/hide the 'component' form field if more than one choice is made in the equipment field in my form then? Simply restricting the user to either choose multiple pieces of equipment with no components defined, or one piece of equipment, with multiple components defined.

      Delete
    3. Hi Jesse,

      It seems that I understood you improperly. Please, confirm that the 'Components' list contains a single-value lookup to the 'Equipment' list. If so, sure, you can filter the components by multiple values from the Equipment because REST-queries support 'and', 'or' operators:
      https://msdn.microsoft.com/en-us/library/office/fp142385.aspx

      So, you need to expand the Advanced settings section and modify Request items template accordingly. Our support team can help you with the task, just send the detailed requirements to support@spform.com and we will estimate them.

      Delete
  10. Thanks for a brilliant solution, as a complete novice I managed to replicate your steps.

    What would I need to change in the "request items" to have different lists, lets say countries equates to Cities and Offices are Streets?

    ta

    Lee

    ReplyDelete
    Replies
    1. its ok, I sorted it. I was having issues with the form rather than the request items

      ta again

      Lee

      Delete
  11. hi, i'm trying to apply this cascading lookup using two cross-site lookup fields, so mapping your example to my case, the country is the client, and the office is the contact, however in my case both fields are plumsail cross site lookup fields, and it didn't work, so here's my code:

    //place in lookup column request
    function (term, page) {
    // Getting the selected account
    var accountId = fd.field('Client').value();
    if (!accountId) {
    accountId = 0;
    }

    // Filtering by the selected account
    if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Client/Id&$orderby=Created desc&$expand=Client/Id&$filter=Client/Id eq " + accountId + "&$top=10";
    }
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Client/Id&$orderby={LookupField}&$expand=Client/Id&$filter=startswith({LookupField}, '" + term + "') and Client/Id eq " + accountId + "&$top=10";
    }

    //place in form js
    fd.field('Client').change(function() {
    fd.field('Contact').value(0);
    });

    ReplyDelete
    Replies
    1. I sorted it out, i was referencing a wrong field, sorry for bothering, please ignore, it works fine.

      Delete
    2. Hi Mohamed, can you post the correct, working code? I can't get mine to work and fear it's a similar issue.

      Delete
  12. If country column contain more than 10 character it gives issue.For example
    1) if we select county field and
    2) we kept office field open(not selected)
    3) again when we select country field it will not auto refresh or close opened office field.Office field remained open.
    If county field contain less than 10 character it works fine.

    ReplyDelete