Filtering Cross-site Lookup

In the previous article, we described how you can configure Cascading Cross-site Lookup fields. But this is not the limit to the functionality of Cross-site Lookup and you can configure results filtering based on almost any fields on the form or simply use your own JavaScript variables to filter the results.

In this article, I will show you how you can filter Cross-site Lookup based on Choice and Yes/No field, but these are just examples. You can filter your Cross-site Lookups by text fields, other lookups and many other fields as well. Using OData URI request schema, you can configure filtering for your own needs.

First I will create a List for our Lookup to point to. It’s a simple list with Title, Choice and Yes/No fields. I will also populate this list with some basic Items:
Next, I will create a new List which will contain same fields, plus a Cross-site Lookup field. Once the List is created and Lookup is converted to Cross-site Lookup, I will design a simple Form for it:

Now, we’ll need to open configuration for my Cross-site Lookup. In advanced settings, I will setup Request Items to get values from my Choice and Check fields, convert them to appropriate formats and use these values to filter requested items.

There are three important steps here – retrieving data, converting it and modifying OData URI request to use this data. Retrieving items is an easy step, but make sure to follow our guidelines for every type of the field, on how to correctly get the data from them.

Converting data to correct format is a harder step, but one that must be done. For example, when retrieving a string value, it is not enough to simply pass it into request, it needs to be between two single quotation marks. Yes/No fields are even stricter in their formatting as the request will only support values 1 for “Yes” and 0 for “No”, even though the field itself returns true or false values. It needs to be converted using JavaScript.

Other fields use other formats, so make sure to research the one you need prior to writing the code. Numbers and Lookups are the easiest fields to filter by, as they don’t require formatting at all.

Finally, we need to compose a proper request to retrieve the items. We are only interested in filtering, so we only need to add filter part. In it, I will add column names from the Data Source list and set them equal to our values, so only the items that have matching values will be retrieved. Both requests will be connected with and between them, so both fields are required to match.

This code goes to Advanced Settings -> Request items section of Cross-site Lookup configuration:

function (term, page) {
  // Getting the selected choice
  var choice = fd.field('Choice').value();
  // Checking the checkbox
  var check = fd.field('Check').value() ? 1 : 0;
  
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&$filter=CheckLP eq " + check + " and ChoiceLP eq '" + choice + "' &$top=10";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "') and CheckLP eq " + check + " and ChoiceLP eq '" + choice + "' &$top=10";
}

These requests can get much more complicated and you can use a large number of conditions here. Conditions can be grouped by using round parentheses, and you can use variety of comparisons – equal, not equal, greater than, lesser than or equal, etc. You can read more about filtering configuration here.

Finally, we’ll go back to our Form and make sure that once one of the fields changes, Lookup value is removed. This is necessary to make sure that the value selected in the Lookup fits our criteria. Place this code inside JavaScript editor on the Form:

fd.field('Choice').change(function() {
  fd.field('Lookup').value("");
});

fd.field('Check').change(function(){
  fd.field('Lookup').value("");
});

This is it, that’s enough to configure filtering for a Cross-site Lookup field. Let’s open the Form in the browser and see the results. Here it is:

Hopefully this article will help you configure Cross-site Lookup filltering to your needs. If you need extra information about OData Requests in general, you can find it here.