So, I had an issue that I’ve a customer who wanted to have some items look up against a large list. (In fact, it was a large document library). This large list had more than 5000 items. This is a little unfortunate, due to a painful and annoying quirk in SharePoint’s design.
Sadly, someone designed the Lookup column in SharePoint in a pretty foolish way. With a small number of items, it appears as a drop down list. With a larger number of items, well, it becomes a type-ahead field (sometimes) or stays as a drop-down list (sometimes). In either state, this means the page has to load all the available options up-front, and this leads to the following:
Yup, it’s the query throttle exception:
This is a lookup column that displays data from another list that currently exceeds the List View Threshold defined by the administrator (5000).
Now, in a normal, on-premise SharePoint farm I’d start writing my own custom form for this instead, perhaps using the ListFieldIterator – which is probably a series of posts in itself – but I couldn’t do that this time, as it’s an Office 365 project. I can’t deploy any C#.
Instead, I started to wonder about all JavaScript alternatives. What if I used a Hyperlink field to refer to the document instead? That would actually work in my project – indeed, it might be more useful to link directly to the document rather than the list item – but the user interface isn’t exactly great:
Two fields to fill in? Users having to copy and paste URLs? That isn’t good.
Instead, I decided to use a little jQuery and the REST APIs in SharePoint. The jQuery Autocomplete widget seemed a sensible place to start. First, I’d need to hide the existing URL fields. Then, add an input box, and make it autocompleting. Finally, when someone selects an item we need to store the value. I wrote the following:
$(document).ready(function() {
var u = "[url to system]";
//Find and hide the Text and URL fields
var linkText = $("table[class='ms-formtable']").find("input[id^='Document_']:last");
var linkValue = $("table[class='ms-formtable']").find("input[id^='Document_']:first");
var td = linkText.closest("td");
td.find("span[dir='none']").hide();
//Add an input for the Autocomplete.
td.append("<input class="ms-long" id="docRef" type="text" maxlength="255" value="" />");
var newInput = $('#docRef');
newInput.autocomplete({
source: function( request, response ) {
$.ajax({
cache: true,
type: "GET",
async: false,
dataType: "json",
url: u + "_vti_bin/listdata.svc/Documents?$filter=startswith(Title,'" + request.term + "')&$select=Title,Id&$orderby=Title",
success: function (data) {
var results = data.d.results;
response( $.map( results, function( item ) {
return {
label: item.Title,
value: item.Id
}
}));
}
});
},
minLength: 3,
select: function (event, ui) {
//When someone selects an item, populate the hidden form fields
newInput.val(ui.item.label);
linkText.val(ui.item.label);
linkValue.val(u + "Shared%20Documents/Forms/DispForm.aspx?ID=" + ui.item.value);
return false;
}
});
});
Things to note above:
- There is a hack around the URL to the selected item, but I’m sure enterprising readers will find alternatives than hard-coding the library into the ‘select’ function. Also, for this example, I did like to the display form, rather than the document itself.
- We’re matching using the REST web service with a ‘StartsWith’ function as ‘StartsWith’ will use a column’s index. ‘Contains’ does not, and will result in a query throttle exception.
This provides, then a form looking like:
which allows autocompletion of the field values on lists of more than 5000 items:
And that’s it.
Andy Burns' Blog 
Andy:
Gotta love the jQueryUI autocomplete function.
I’m curious about the StartsWith thing using the index. Do you have any references on which queries trigger use of the index?
M.
Um, I did, but I’ve lost them. It was something I’d read, and it makes sense that StartsWith would be able to use the index still. I did test it though, and yes, it was as I’d read – StartsWith works when Contains doesn’t ‘cos contains can’t use an index.
Unfortunately, in my testing on an on-premises installation, this works wonderfully during the large query window, but breaks after the window closes.
You will still need an index on any columns your are querying by. It does not circumvent the list query throttle, but rather prevents the form from trying to load all 5000 items (and therefore, causing a throttle exception) when merely rendering the form.
Any queries you run will still have to obey the throttle, and from your description, my guess is that yours isn’t.
If you are using this for type-ahead, make sure you don’t lookup until the user has typed a few letters – or you could still end up trying to retrieve more than 5000 items.
And don’t forget, you can’t use ‘Contains’ as contains does not use indexes.
You were right that I had forgotten to index the column I was querying on. It worked perfectly as soon as I did that.
I know this article is a couple of years old. Has anyone been able to get this to work with the current version of SharePoint Online? It looks like the select control for the lookup is no longer rendered and therefore cannot be hidden/replaced by the auto complete control. I love this solution and it works great for lookups that don’t exceed the threshold.
Mike… Did you ever find a resolution for this?
I still couldn’t get the value to write back because, as far as I can tell, the input control for the lookup is never rendered. I ended up creating a second field on which I added the auto lookup using Andy’s code. Then, I use a workflow to set the actual lookup value. I hate doing this because workflows are so unreliable but it was the only way I could get it to work.
Mike… I got this working… Works great at handling the missing control…
Great solution Andy… Works like a charm… You officially have saved my hide…
Is your SharePoint online or self-hosted? Did you have to do any tweaks to Andy’s code to get it to work? I spent a ton of time trying to get this working and finally gave up.
This is in SharePoint Online… Current version as of today…. Simply put, I added another text field to my list (content type actually) and used it to grab the lookup value with the autoComplete code. I appended the actual ID to the value of the text field on the PreSaveAction of the form…
On the back end, I then would update the value of the lookup column from the value of the text field…
I also set the lookup column to .ShowInDisplayForm = true, .ShowInEditFor = false, .ShowInNewForm = false. Then, I did the exact reverse for the corresponding text field.
Hey Andy… I’m using this approach here successfully, with the exception of when the filter value contains a single quote…. The ajax throws an error. Works fine without the single quote. I have tried a bunch of different approaches to resolve by replacing the single quote with different values (i.e. escaped) but still not having any luck…
This is the problem section (the url)…
$.ajax({
cache: true,
type: “GET”,
async: false,
dataType: “json”,
url: urlAbsolute + “/_vti_bin/listdata.svc/Clients?$filter=startswith(Title,'” + request.term + “‘)&$select=Title,Id&$orderby=’Title’&$top=2000”,
success: function(data){
var results = data.d.results;
response($.map(results, function(item){
return {
label: item.Title,
value: item.Id
}
}));
},
error: function (result) {
alert(“Due to unexpected errors, data could not be loaded”);
Any ideas?
Ok… I got it resolved…
Before the ajax request is submitted, I added the following bit of code and used the encodedTerm variable in my call…
var term = request.term;
var encodedTerm = encodeURIComponent(term);
if (encodedTerm.indexOf(“‘”) >=0)
{
encodedTerm = encodedTerm.replace(/’/g, “””);
}