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:
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.
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.
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.
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.
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…