A couple of weeks ago I blogged about automatically setting the lookup field in a new item form when that lookup field exceeded the list view threshold:
Automatic Parent/Child List Relationships for Large Lists in SharePoint
I wanted to follow that up with a “plug and play” script that would allow you to update the value of a lookup field on an Edit Form. Additionally, I wanted this to work for both single selection and multi selection fields.
Now, in an ideal world you would not implement this script, you would have a developer on staff or a third party tool that would allow you to easily create a custom form for SharePoint. Unfortunately, we don’t live in an ideal world. You don’t have a developer on staff, can’t afford to pay a consultant to create a form for you, and you can’t afford a third party tool. Sometimes you just need to get the job done.
Why is this script not ideal?
Well, there’s a couple of reasons this script isn’t ideal for some situations. For one, it partially depends on the current SharePoint default forms to display the currently selected lookup values. If Microsoft decides to do away with the current default forms in favor of the new look someday, this script would not correctly display the values of the currently selected lookup.
Additionally, I’m doing something in this script which makes me cringe a bit, and I hope you heed my advice here. In this script, I’m making multiple REST queries to return all the items for a large list and display them to a user in order to find the value they are looking for. This works really well if you only have 6,000-7,000 items in a list, but if you have a VERY large list it will not be usable. I URGE you, if you use this script for a lookup field that contains tens of thousands of items, that you modify the REST query to return a subset of those items. Otherwise you will experience performance issues, you’ll be potentially hammering your server with requests, and millions of kittens will die.
Got it?? Modify the REST query to return a subset of values that need to appear in the lookup.
Probably the BIGGEST downside of this script, is that when a user selects an item for the lookup field, the script immediately saves that choice and reloads the page. This is necessary because of the FORMDIGEST. If you update a list item with REST in a default SharePoint form and the try to save that same item using the Form’s save button you will get a save conflict error. I spent quite few hours trying to find an elegant workaround for this and even consulted other experts in the field. At the end of the day, I stuck with the simple approach. So, the page will reload after you set the lookup field or delete a previously selected lookup field value. This is critical to understand from a training perspective as other updated values in the form won’t update. Don’t like it? Create a completely custom form. Have a more elegant approach? I’m all ears.
What I DO like about this script
After writing this script and getting it to work, I realized there a few cool tips and tricks you learn from it and help in your everyday development. So, even if you don’t have a need for this specific script, there are concepts in here you can apply elsewhere.
DataTables
If you’ve been to any of my workshops or sessions you know I’m a big fan of DataTables.net. This library makes it super easy to create sortable, searchable list views with just a few lines of code. I use DataTables in this script to display the values of the lookup list and allow users to search for the item they are looking for.
REST Query of a Large List
When you use SharePoint REST to query a list, the REST query returns a “nextLink” which is the REST query to get the NEXT set of items. This allows you to execute successive REST queries to return more than 5,000 items (or whatever your list view threshold is set to). This is cool, if you don’t abuse it and try to return 50,000 items in one call.
jQueryUI draggable
I’m displaying the DataTables view in a dialog that you can drag around on the screen. It’s super easy to do, but most folks don’t realize how easy.
There’s a bug in SharePoint’s JSON Light support
One of the things I uncovered during my testing is that there appears to be a bug in SharePoint’s JSON light support. Using JSON light, you should be able to update a list item without having to specify the ListItemEntityTypeFullName and this works for the most part, EXCEPT multi-select lookup fields. It flat out doesn’t work. I had to use the ListItemEntityTypeFullName value. Don’t fear though, I don’t make you set this value. I query your list to determine it in the script. Again, Mr. Marc Anderson has a blog post about using JSON light to update items at Making Your REST Calls Simpler by Changing the Metadata Setting if you wish to know more.
Blah.. blah.. blah… Just give us the script and video
Geez… fine… here you go.
The Script
This script should work fine in SharePoint 2013 with JSON Light Support, SharePoint 2016, and Office 365
Before you go and plug this script into your environment, you are going to need to set a few global variables so that the script will know which list/fields to work with. Those variables are:
- LIST_NAME – This is the name of the list the form is for. The list that contains the lookup field to the large list
- LOOKUP_TO_LIST – This is the name of the large list
- LOOKUP_TO_LIST_FIELD – This is the INTERNAL name of the field in the large list that you want to display to the user to choose from
- INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD – This is the INTERNAL field name of the lookup field on the list specified for the LIST_NAME variable
- DISPLAY_NAME_OF_LOOKUP_FIELD – This is the display name of the field in the form.
<!--/*
* HillbillyLargeListLookup - Set the value of lookup fields to large lists
* Version 1.0
* @requires jQuery v1.7 or greater
*
* Copyright (c) 2016 Mark Rackley / PAIT Group
* Licensed under the MIT license:
* http://www.opensource.org/licenses/mit-license.php
*/
/**
* @descriptionSet the value of lookup fields to large lists
* @type jQuery
* @name HillbillyLargeListLookup
* @category Plugins/PAITGroup
* @author Mark Rackley / http://www.markrackley.net / http://www.paitgroup.com / mrackley@paitgroup.com
*
*/--><script type="text/javascript" src="//code.jquery.com/jquery-1.12.3.min.js"></script>
<script type="text/javascript" src="//code.jquery.com/ui/1.12.0/jquery-ui.min.js"></script>
<link rel="stylesheet" href="//appsforoffice.microsoft.com/fabric/1.0/fabric.min.css">
<link rel="stylesheet" href="//appsforoffice.microsoft.com/fabric/1.0/fabric.components.min.css"><script type="text/javascript" src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<link type="text/css" rel="stylesheet" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" /><style type="text/css">
.largeListFloatingDiv
{
position: fixed;
top: 10em;
right: 1em;
color: black;
background:white;
border: 3px solid #7D8077;
width: 300px;
padding:5px;
z-index:500;
}</style>
<div id="largeListDiv" class="largeListFloatingDiv">
<span id='LookupTitle' class='ms-font-xl'></span><br>
<table id='largeListTable'><tr><th></th></tr></table>
</div><script type="text/javascript">
////////////////////////////////////////////////////////////////////////
//SET THESE VARIABLES APPROPRIATELY
////////////////////////////////////////////////////////////////////////////////
var LIST_NAME = "Orders"; //list name for the list that the form is for
var LOOKUP_TO_LIST = "Products"; //list name of large list that form has a lookup for
var LOOKUP_TO_LIST_FIELD = "ProductLabel"; //field in large list that users will see on the form
var INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD = "Product"; //INTERNAL field name of lookup field on form
var DISPLAY_NAME_OF_LOOKUP_FIELD = "Product"; //Display name of lookup field on formvar IS_MULTI = false;
GetLargeListItems();
$("#LookupTitle").html(DISPLAY_NAME_OF_LOOKUP_FIELD);jQuery(document).ready(function($) {
DisplaySelectedLargeListItems();
$( "#largeListDiv" ).draggable();$("table.ms-formtable td").each(function(){
if (this.innerHTML.indexOf('FieldInternalName="'+INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD+'"') != -1){
if (this.innerHTML.indexOf("SPFieldLookupMulti") > 0)
{
IS_MULTI = true;
}
$(this).find("span").html("<div id='SelectedLargeListItemes'></div>");
}
});});
var oTable;
var global_results = new Array();function GetLargeListItems(nextlink)
{
//update they query below to return a subset of items from the lookup list in order to achieve the best performance possible
var url = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+LOOKUP_TO_LIST+"')/items?$select=Id,"+LOOKUP_TO_LIST_FIELD+"&$top=5000";
if (nextlink != undefined)
{
url = nextlink;
}
var call = $.ajax({
url: url,
type: "GET",
dataType: "json",
headers: {
Accept: "application/json;odata=nometadata"
}});
call.done(function (data,textStatus, jqXHR){
global_results.push.apply(global_results,data.value);
if (data["odata.nextLink"] != undefined)
{
GetLargeListItems(data["odata.nextLink"]);
} else {
oTable = $("#largeListTable").dataTable({
"bDestroy": true,
"fnDrawCallback": rowClick,
"bProcessing": true,
"aaData": global_results,
"aoColumns": [
{ "mData": LOOKUP_TO_LIST_FIELD }],
"iDisplayLength": 10,
"bLengthChange": false,
"bProcessing": true});
}
});call.fail(function (jqXHR,textStatus,errorThrown){
alert("Error retrieving large list items: " + jqXHR.responseText);
});
}function rowClick()
{$("tr[role='row']").unbind( "click" );
$("tr[role='row']").click(function()
{
event.stopPropagation();
var aPos = oTable.fnGetPosition( this );
// Get the data array for this row
var aData = oTable.fnGetData( aPos );
if (aData[LOOKUP_TO_LIST_FIELD] != undefined)
{
var r = confirm("Add '" + aData[LOOKUP_TO_LIST_FIELD] + "' as a "+DISPLAY_NAME_OF_LOOKUP_FIELD+"?");
if (r == true) {
DisplaySelectedLargeListItems(aData.Id);
}
}
});
}function DisplaySelectedLargeListItems(addSelectedItem)
{
$("#SelectedLargeListItemes").empty();var call = $.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+LIST_NAME+"')/items?$select=Id,"+INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD+"/Id,"+
INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD+"/"+LOOKUP_TO_LIST_FIELD+"&$expand="+INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD+"&$filter= ID eq " +
getParameterByName("ID"),
type: "GET",
dataType: "json",
headers: {
Accept: "application/json;odata=verbose"
}});
call.done(function (data,textStatus, jqXHR){
var choices = new Array();
if (IS_MULTI)
{
for (index in data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].results)
{
$("#SelectedLargeListItemes").append("<span class='selectedLargeListItem' data-id='"+data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].results[index].Id+"'>" +
data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].results[index][LOOKUP_TO_LIST_FIELD]+
"<i class='ms-Icon ms-Icon--x' onclick='$(this).closest(\"span\").remove();RemoveSelectedLargeListItem();' aria-hidden='true'></i></span><br>");
choices.push(data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].results[index].Id);
}
} else if (data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].Id != 0) {
$("#SelectedLargeListItemes").append("<span class='selectedLargeListItem' data-id='"+data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].Id+"'>" +
data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD][LOOKUP_TO_LIST_FIELD]+
"<i class='ms-Icon ms-Icon--x' onclick='$(this).closest(\"span\").remove();RemoveSelectedLargeListItem();' aria-hidden='true'></i></span><br>");
choices.push(data.d.results[0][INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD].Id);
}
if(addSelectedItem != undefined)
{
choices.push(addSelectedItem);
UpdateMetadata(choices);
}
});call.fail(function (jqXHR,textStatus,errorThrown){
alert("Error retrieving selected items: " + jqXHR.responseText);
});
}function RemoveSelectedLargeListItem()
{var choices = new Array();
$(".selectedLargeListItem").each(function()
{
choices.push($(this).attr("data-id"));
});
UpdateMetadata(choices);}
function UpdateMetadata(choices)
{
var call2 = $.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+LIST_NAME+"')",
type: "GET",
dataType: "json",
headers: {
Accept: "application/json;odata=verbose"
}});
call2.done(function (data,textStatus, jqXHR){var field = INTERNAL_FIELD_NAME_OF_LOOKUP_FIELD + "Id";
var itemData = {};
itemData["__metadata"]= { type: data.d.ListItemEntityTypeFullName };
if (IS_MULTI)
{
itemData[field] = {results: choices};
} else {
if (choices.length == 0)
{
itemData[field] = 0;
}else{
itemData[field] = choices[choices.length-1];
}}
var currentItemID = getParameterByName("ID");
var call = jQuery.ajax({
url: _spPageContextInfo.webAbsoluteUrl +
"/_api/Web/Lists/getByTitle('"+LIST_NAME+"')/Items(" + currentItemID+ ")",
type: "POST",
data: JSON.stringify(itemData),
headers: {
"Accept": "application/json",
"content-type": "application/json;odata=verbose",
"X-RequestDigest": document.getElementById("__REQUESTDIGEST").value,
"X-HTTP-Method": "PATCH",
"IF-MATCH": "*"
}
});
call.done(function (data, textStatus, jqXHR) {
window.location = window.location;
});
call.fail(function (jqXHR, textStatus, errorThrown) {
alert("Error updating metadata: " + errorThrown);
});});
call2.fail(function (jqXHR,textStatus,errorThrown){
alert("Error retrieving ListItemEntityTypeFullName: " + jqXHR.responseText);
});}
function getParameterByName(key) {
key = key.replace(/[*+?^$.\[\]{}()|\\\/]/g, "\\$&"); // escape RegEx meta chars
var match = location.search.match(new RegExp("[?&]"+key+"=([^&]+)(&|$)"));
return match && decodeURIComponent(match[1].replace(/\+/g, " "));
}</script>
The Video
In Closing
At the very least this script should get you out of a bind when you reach that 5,001st item in your list until you can find the time to create a custom form or address the issue in another way. As always, I hope you found this script helpful and maybe learned a thing or two.
Until next time…