Automatic Parent/Child List Relationships for Large Lists in SharePoint

August 26, 2016 Mark Rackley Uncategorized 0

Maybe another title for this blog post would be “Setting lookup fields when you get the ‘This is a lookup column that displays data from another list that currently exceeds the List View Threshold defined by the administrator (5000).’ Error”.

Yeah, large lists are a pain in SharePoint. You can be trucking along for months, everything working great, happy users, life is good and then BAM… you create that 5001st entry. List views break, look up fields break, your world becomes painful.

And what’s the right response??

The obvious answer is “don’t create large lists”, but unfortunately we live in the real world. Sometimes you need lists larger than that list view threshold! I’ve blogged previously about ways to create list views for large lists and how you can prepare for them in the following blog posts:

Working Around that Pesky List View Threshold in Office 365 & SharePoint 2013

How to Create List Views for Large Lists in Office 365

Enhancements and improvements have been made for large lists since I’ve posted those two blogs. You can also read Microsoft’s guidance on large lists at:

Manage large lists and libraries in Office 365

Microsoft has made some improvements that you may not have even noticed with features like automated indices on lists. There’s also plans for larger List View Thresholds in SharePoint Online which is good news and all, but what can we do today? How do we set the value of a lookup field?

Perhaps you’ve been using my previous blog post for setting up Parent/Child list relationships in SharePoint but hit that magic List View Threshold and now it’s stopped working?

What can you do?

REST to the rescue… again

Even though we don’t have access to that lookup field in our new and edit forms anymore, we can still set the value of that field using REST. In fact in some ways we can use REST to make the automatic parent/child list relationship easier than the way were doing it in any of my previous solutions. Here are the steps we are going to take:

  1. We will create a script and place it on the display form of our parent item.
  2. In this script we will have a button to create a new child item.
  3. When a user clicks this button it will create a new child item using REST and automatically set the value of our lookup field based upon the ID of the parent.
  4. The user will be redirected to the EDIT form of the newly created child item to finish filling out the metadata.

Sound easy enough? Let’s take a look at this in action.

The Script

Below is the script that we’ll be using. There are a couple of items here that are very important for you to note if you plan on making this work in your environment:

JSON Light

This script is using JSON Light, my good friend Rob Windsor has a YouTube video discussing JSON Light here: https://www.youtube.com/watch?v=NIyNs1vXXJA . Because the script uses JSON Light it means it will only work in SharePoint 2013 On Premises if you have the CU applied for JSON Light support. It will work fine in Office 365 and SharePoint 2016. The concept will work on any version of SharePoint from 2007 above, it’s just the REST query as written will only work if you have JSON Light support. 

Another awesome dude Marc Anderson wrote a blog post about using JSON Light with your REST queries that you may find useful: Making Your REST Calls Simpler by Changing the Metadata Setting

Internal field name + “Id”

You need to know the internal field name of the Lookup field on your Child list. In addition, in the REST query below we append “Id” to the internal field name of this lookup field.

URL to Edit Form

In the script below we are redirecting the user to the URL of the Edit Form of the child list. So, you’ll need to have that URL handy.

Here’s the script:

<!--/*
 * HillbillyCreateChild - Create child items for 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
 */
/**
 * @description Create child items for large lists
 * @type jQuery
 * @name HillbillyCreateChild
 * @category Plugins/PAITGroup
 * @author Mark Rackley / https://www.markrackley.net / http://www.paitgroup.com / [email protected]
 */—>
<script  type="text/javascript" src="//code.jquery.com/jquery-1.11.1.min.js"></script>

<input type="button" value="Create Order" onclick="HillbillyCreateChild();">

<script type="text/javascript">

    function HillbillyCreateChild()
    {
        //get the ID of the parent from the URL
        var id = getParameterByName("ID");
        
        //Create item in Orders list
        var call = jQuery.ajax({
            url: _spPageContextInfo.webAbsoluteUrl +
                "/_api/Web/Lists/getByTitle('Orders')/Items", 
            type: "POST",
            data: JSON.stringify({
                ProductId: id //internal field name of lookup field with "Id" appended at the end
            }),
            headers: {
                Accept: "application/json",
                 "Content-Type": "application/json;odata=nometadata",
                 "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
            }
        });
        call.done(function (data, textStatus, jqXHR) {
            //url to the EDIT form of the child list
            window.location = "https://sphillbilly.sharepoint.com/blog/Lists/Orders/EditForm.aspx?ID=" + data.ID + "&Source=" + window.location;
        });
        call.fail(function (jqXHR, textStatus, errorThrown) {
           alert("Creation Failed, please try again.");
        });
    }

    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>

And here’s a video showing the script in action and how to use it:

Some Final Thoughts

There are a couple of limitations to this solution that I will blog about in future posts. For one, this solution will not work for Document Libraries as you need to upload the file first. Also, if you have a multi-lookup field this solution is not ideal because it supposes there will only be one selection for your lookup. Finally, this solution only works for creating a new child item and does not address updating the lookup field in the edit form.

I have implemented solutions for these scenarios though… so, stay tuned and I’ll get those posted for you.

Thanks again for stopping by!

Families in Germany who are facing divers soundness problem, such persons can buy drugs from the Web without prescription. With the market flooded with divers web-sites selling sundry medicaments, purchasing medicines from th WEB is no longer a trouble for common man. Certain medications are used to treat infections caused by dental abscesses. Of course it isn’t all. If you’re concerned about erectile health problem, you probably know about Xenical and Hoodia. Probably every adult knows about Garcinia. (Read more PhentermineXenical). The symptoms of sexual health problems in men turn on impossibility to have an erection sufficient for sexual functioning. Certain medications may add to sex drive difficulties, so its vital to cooperate with your health care professional so that the prescription can be tailored to your needs. Preparatory to capture Levitra or other medicament, speak to your pharmacist if you have any allergies. Talk to your heartiness care provider for more details. Preparatory to ordering this remedy, tell your physician if you are allergic to anything.