Friday, July 29, 2016

Cross-site Lookup to a SharePoint list with more than 5000 items

Today, I wanted to demonstrate how to adjust Plumsail Cross-site Lookup fields for a list with more than 5000 items. As you might know, SharePoint has a constraint on a view that does not allow you to filter or sort a view containing more than 5000 items. This constraint prevents users from searching items in Cross-site Lookup drop-down. Now I will show you how to work around this restriction.

First, you need to create indices for all columns of the source list by which you're going to filter items in the Cross-site Lookup drop-down. By default, the only field that requires an index is the field that you've picked in the 'Field' drop-down of the Plumsail Lookup Manager.

A display field in Cross-site Lookup Manager

Go to the source list's settings and click 'Indexed columns' under the list of columns. Create an index for each column that requires it.

Next, open the Lookup Manager and expand Advanced settings section. In the 'Request items' snippet, you have to remove 'orderby' clause from the first request because SharePoint does not allow you to sort a view containing more than 5000 items even by an indexed column. The result snippet will be:

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

And here you go:

Searching in a list containing more than 5000 items with Plumsail Cross-site Lookup

Please, note that if your filtering criteria is not concrete, say, you typed just a single letter, the result may contain more than 5000 items and you will get an error in the drop-down. Just continue typing to decrease the number of filtered items and you will get the result.