Skip Ribbon Commands
Skip to main content

:

> Posts > Creating a SharePoint Parent/Child List Relationship–No SPD Version
January 19
Creating a SharePoint Parent/Child List Relationship–No SPD Version
So, over the years by far my most read blog posts are the ones detailing how to create Parent/Child list relationships in SharePoint and automatically populating the Parent (Lookup) ID of the Child list. Those posts being:

Creating a SharePoint List Parent / Child Relationship - Out of the Box (yes, I know the videos are dead on this one. The VIDEO REMIX post is better anyway)
Passing Multiple Query String Variables Using SPD – Follow Up on Creating Parent / Child List Relationships
Creating a SharePoint List Parent/Child Relationship – VIDEO REMIX
Creating A SharePoint Parent/Child List Relationship– SharePoint 2010 Edition

You’d think at some point I’d stop kicking that dead horse, but no… These blog posts are all still quite active and occasionally spawn further questions. Which brings me to this blog post. A couple of weeks ago I had an email from a gentlemen who wanted to create this Parent/Child list relationship, but his organization decided to not allow the use of SharePoint Designer (SPD). Hey, did you know it’s free? Well, my previous posts had always utilized SPD as an integral tool for creating these relationships. However, it’s very possible to create the same functionality using only JavaScript/jQuery.
 
So, Why don’t we bring that leg back one more time and give that horse a smack while I show you how to create a Parent/Child list relationship in SharePoint 2010 without using SharePoint Designer.

Before we get started

Before we get started, make sure you are familiar with jQuery basics including deploying jQuery, linking scripts to Content Editor Web Parts, and setting SharePoint Form Fields.  Luckily I have a couple of blog posts to help you out with that:

A Dummies Guide to SharePoint and jQuery–Getting Started
A Dummies Guide to SharePoint and jQuery–Getting & Setting SharePoint Form Fields

I’m going to assume you have the document library “SiteAssets” at the root of your Web Application and the jQuery library named “jQuery.min.js” in that document library. We will also be storing any scripts we write for this blog in that same document library.
 
Also, make sure you have the lists that we’ll be using. Just like every previous example we’ll be using the following list structure:

Parent List: “Issue”

Field Name Field Type
All we need is default “Title” field for our needs  
 

Child List: “Time”

Field Name Field Type
IssueID Lookup Field Type on ID field of “Issue” list
Hours Numeric Field Type
 
You can also refer to my video from my earlier blog post on this topic for more step-by-step instructions on how to create these lists. In fact, I’ll just link the video for creating the lists here. (You're Welcome)

Creating Issue and Time List in SharePoint 2012

Lastly, I would like to point out that this method will work in both SharePoint 2007 and 2010, although certain aspects are different in 2007 such as editing default pages (append “&ToolPaneView=2” to the page’s url to get into edit mode in 2007).
 

So, what’s the plan?

So, here are the steps we are going to take to create the list relationship functionality. For those of you comfortable with SharePoint and JavaScript/jQuery maybe this is all you need, for the rest I’ll go through the step-by-step “how” below.
 
1) Edit the default Display Form for the Issue list
a) Add Time list to the page
b) Set up web part connection between the Issue Display Form and the Time list
c) Write a script that overwrites the “Add new item” link and passes in the Issue’s ID as a Query String parameter
2) Modify the default New Item Form for the Time list and add a script that:
a) Sets the value of the IssueID lookup field
b) hides the “Issue ID” field to stop meddling users and their darn dogs from changing it
That’s all there is to it. Not too hairy I hope? let’s get started and I’ll walk you through it step by step.

Edit the Default Display Form for the Issue List

Now that you have the requisite lists created, let’s make the necessary modifications to the default Display Form for the Issue List.
First thing we are going to do is add the Time list to the Default Display form of the Issue list. Then we are going to add a Web Part Connection between the Issue Display Form Web Part and the Time list Web Part using the “ID” from the Issue list and the “IssueID” from the Time list as filter members. After this is complete, you will see a list of all associated Time entries for an Issue whenever you view an issue.
 
Edit Default Display Form for Issue, Add Time List, Create Web Part Connection to Filter Time


The next step is to write a script that will overwrite the “Add new item” link of the Time list so that we can call the SharePoint “NewItem2” function to bring up the modal window for a new Time entry as well as pass the parent Issue’s ID to the New Item form as a Query String variable. The following script will be used to accomplish this task. 
 
<!--
    Name: DisplayIssue.js 
-->

<script type="text/javascript" src="/SiteAssets/jquery.min.js"></script>


<script type="text/javascript">

jQuery(document).ready(function($) {

    //get the ID for the Issue from the Query String
    var issueID = getParameterByName("ID");

    //find the element with the "Add new item" link.
    //note that if you have more than one list on your page, this just finds the first one
    var anchorElement = $("a:contains('Add new item')");
    
    //modify the "Add new item" link to call the "NewItem2" function and pass in the Issue ID. 
   //Be sure to put the path to your site below. You can use relative URL to the web application or the FQDN
    $(anchorElement).attr("href","javascript:NewItem2(event,'http://PATH_TO_YOUR_SITE/Lists/Time/NewForm.aspx?IssueID="  + issueID + "');");
    //remove the "onclick" attribute from the anchor element as we aren't using it anymore
    $(anchorElement).removeAttr("onclick");

});

// The following function should really be put into a utility library
// with all of your commonly called functions
//
// no, I didn't write this function from scratch, I found it at
// http://stackoverflow.com/questions/901115/get-query-string-values-in-javascript
function getParameterByName(name)
{
  name = name.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
  var regexS = "[\\?&]" + name + "=([^&#]*)";
  var regex = new RegExp(regexS);
  var results = regex.exec(window.location.href);
  if(results == null)
    return "";
  else
    return decodeURIComponent(results[1].replace(/\+/g, " "));
}

</script>
 
Again, you should really create a utility library and put commonly used functions like “getParameterByName” in it instead of copying the same functions over and over again in your scripts like I’m doing here… and in the next script… Do as I say!! Not as I do!
 
Now that we have our script written and uploaded to our SiteAssets document library, let’s add it to the Issue Display form.
 
Insert DisplayIssue.js script into Issue Display Form

Okay, so at this point we have modified our Issue Display form so that it only shows Time entries associated with the current issue and we’ve added a script that allows us to pass in the ID of the Issue to the Time New Item Form as the Query String parameter “IssueID”.  Which brings us to the next step in the process.
 
Modify the Default New Item Form for the Time List
 
If you haven't fallen asleep yet and have followed all the previous steps, the New Item Form for the Time list now has the ID for the Issue being passed to it in the Query String Parameter “IssueID”. Yes, I know you can’t “see” it because of the modal window, but trust me, it’s there. Have I ever knowingly lied to you before?
 
The next thing we need is a script that can read the “IssueID” Query String parameter value, and then store that value in the “IssueID” Lookup field on the New Time form. The Script below does just that. There are a couple of things I would like to point out about the script before you go pasting it into your solutions:
 
1) Again, you REALLY should create a utility library for “getParamterByName”. I toyed with doing that for you in this blog post, but I really wanted to keep it simple as I know many of you don’t want to be bogged down with the extra details.
 
2) SharePoint has a “feature” with lookup fields in IE where if you have more than 20 items it changes the “select” element into an “input” element. I actually blogged about that if you want to know more at: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items). I bring this up because I have to take this into account when setting and hiding the IssueID in the script below. You’ll see what I mean.
<!--
    Name: NewTime.js 
-->

<script type="text/javascript" src="/SiteAssets/jquery.min.js"></script>

<script type="text/javascript">

jQuery(document).ready(function($) {

    //get the Issue ID from they query string
    var issueID = getParameterByName("IssueID");

    //Set the corresponding Lookup field value to the Issue ID
    //and hide the field row
    SetLookup("IssueID",issueID);
    
});


// The following functions should really be put into a utility library
// with all of your commonly called functions

//Sets the value of a lookup field whether it is an input or select field. 
function SetLookup( fieldTitle, lookupVal)
{
    //Set default value for lookups with less that 20 items
    if ( $("select[title='" +fieldTitle+ "']").html() !== null)
    {
       $("select[title='"+ fieldTitle +"']").val(lookupVal);    
       //hide the field row
       $("select[title='IssueID']").closest("tr").hide();    

    }
    else
    {
        choices = $("input[title='" +fieldTitle +"']").attr("choices");
        hiddenInput = $("input[title='" +fieldTitle +"']").attr("optHid");
        $("input[id='" +hiddenInput +"']").attr("value",lookupVal)
        
        choiceArray = choices.split("|");
        for (index = 1; index < choiceArray.length; index = index + 2)
        {
            if (choiceArray[index] == lookupVal){
                $("input[title='" +fieldTitle +"']").val(choiceArray[index - 1]);    
                //hide thie field row
                $("input[title='IssueID']").closest("tr").hide();
            }
        }
    }
}


// no, I didn't write this function from scratch, I found it at
// http://stackoverflow.com/questions/901115/get-query-string-values-in-javascript
function getParameterByName(name)
{
  name = name.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
  var regexS = "[\\?&]" + name + "=([^&#]*)";
  var regex = new RegExp(regexS);
  var results = regex.exec(window.location.href);
  if(results == null)
    return "";
  else
    return decodeURIComponent(results[1].replace(/\+/g, " "));
}

</script>
Alright, let’s add the script to the Time list New Item form and make sure it all works. Again, unless I broke something the script is going to read the Query String parameter “IssueID” and set the value of the “IssueID” lookup field to the corresponding value. The script will then hide the IssueID field from the New Time form completing our magical journey into the world of SharePoint for this evening.
 
Insert NewTime.js script into Time New Item Form

Tada… that’s really all there is to it.

And in Conclusion

There you go, setting up a Parent/Child list relationship in SharePoint 2010 without using SPD. I think I may actually like this method best from a maintenance standpoint. Have a better idea on how to accomplish this? Please share with the rest of the class, the comments section isn’t there for spammers to hock their wares…
 
Hopefully most of your questions have been answered? If you’re lucky you will not have to endure another one of these posts from me until vNext. As always, please let me know if I was not clear about something and I’ll see what I can do to explain myself better.
 
One last note, You can build upon this example and pass multiple values from the Parent Display form in the Query String and set multiple fields in the Child’s New Item form, just modify the script to pass more values in the “NewItem2” function, and modify the other script to set more fields. Refer to my previously mentioned blog post A Dummies Guide to SharePoint and jQuery–Getting & Setting SharePoint Form Fields for more information on how you might read and set SharePoint form field values.
 
Thanks again for stopping by, I hope maybe you learned a trick or two.

Comments

Cannot get it to work on a subsite

From my understanding the changed "Add new item link" in DisplayIssue.js does not work on a subsite as it calls the newitem form of the list on the root site.

I have tried to use the COM to get the siteurl of the subsite to append to the link, but it fails because sp.js is not yet loaded.

I will greatly appreciate if you could post a fix as I am a newbie in this area.
 on 1/23/2012 2:47 PM

Works fine in subsites

Sorry you are having difficulty...

This solution works fine with subsites, you just need to change the relative path in the call to the newitem2 function to point to the correct NewItem form:

$(anchorElement).attr("href","javascript:NewItem2(event,'[PATH TO YOUR NEW ITEM FORM]/NewForm.aspx?IssueID="  + issueID + "');");

I have added clarifying comments to the script to prevent other users from having this problem. 

Thanks for the feedback.
Mark
EMS\markrackleyNo presence information on 1/23/2012 3:20 PM

When used as part of a site template

Sorry, I have not explained clearly what my intention was with using your solution.

I ultimately want to use your solution as part of a site template.  In that case the [PATH TO YOUR NEW ITEM FORM] is unknown until the site is created.  So the question is can you at runtime determine the [PATH TO YOUR NEW ITEM FORM] to plug it into the url.

Thanks again for your feedback.
 on 1/23/2012 3:35 PM

Ahhhh...

Yeah.. might have helped to have that info. ;)  No worries...

You can use straight JavaScript to get the New Form.  I cobbled together the function below. It assumes you will call this function from the dispform or some other page where "Lists" is part of the path. 

function GetNewForm(listName)
{
//window.location.pathname returns relative url
var pathParts = window.location.pathname.split("/");
var newFormPath = "";
for (part in pathParts)
{
newFormPath += pathParts[part] + "/";
if (pathParts[part] == "Lists")
{
break;
}
}
newFormPath += listName + "/NewForm.aspx";

return newFormPath;
}

Just call this function and stuff the returned value into the NewItem2 function appropriately...

One possiblity would be:

var newForm = GetNewForm("Time");
$(anchorElement).attr("href","javascript:NewItem2(event,'" + newForm + "?IssueID="  + issueID + "');");
EMS\markrackleyNo presence information on 1/23/2012 4:12 PM

I have two lists on the page

Hi

DisplayIssue.js  :

I have 2 related lists on the parent (DisplayForm.aspx) page.

How can I link it to the second list add new item.

------------------------------------------------------------------------
----------------------------------------------------------------------
//note that if you have more than one list on your page, this just finds the first one
    var anchorElement = $("a:contains('Add new item')");
-----------------------------------------------------------------------
------------------------------------------------------------------------

Hope u can help me out, really appreciatable.

Thanks
 on 1/30/2012 11:32 PM

Web Part Connections

Mark, thanks for this and your past presentations on this subject!! The more you learn about the internal workings of SharePoint, it seems much of the information you have to gleen from the etherworld or through osmisis. That always stymies (sp?) me.

Anyway, my question on this post is the connectivity of the two web parts you showed in the video. I'm using MOSS2007 for the next little while but was attempting this without SPD so/but the connection/get filter from choices are greyed out. Should I use your older method of modifying the dispform.aspx page away from site definition and add a dataview instead of a drag and drop web part for the child list? Or did I misinterpret something?

Cheers and Thanks!
 on 3/10/2012 10:59 AM

Thanks

This is a great overview. I appreciate you continuing to evolve this solution to meet everyone's needs while making it easier and more accessible.  I used your Parent/Child tutorials for MOSS 2007, and they have been extremely helpful.
 on 3/16/2012 12:27 PM

Same Functionality in a Document Library

Hello Mark, thank you for this great post is really good, can you tell us how to do the same thing with document libraries,i followed your instructions in this blog and i did some little changes to make it work with a document library but when i get to this :

$(anchorElement).attr("href","javascript:NewItem2(event,'http://PATH_TO_YOUR_SITE/Lists/Time/NewForm.aspx?IssueID="  + issueID + "');");

i realize that there is no NewForm.aspx for document libraries, but there is an EditForm but this page is called only after you uploaded the document, so Mark how can i pass the parameter IssueID to the Upload form and then to the  Edit Form, or there is another, or is there another workarround for this using javascript and jquery also?.

thanks in advance, jonathan

 on 3/21/2012 11:05 AM

great stuff!

one question, how do i hide the IndexID field from the edit form?  got it out of the new item form ok.
 on 5/3/2012 4:08 PM

Include Files

Whats the best way to combine the common functions into one js file and then call them from another js file ?

(this solution is fantastic by the way)
 on 7/19/2012 4:58 AM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Name *


Who are you?

Email *


How do I find you?

captcha *


Attachments


SharePoint Architect, Developer, Blogger, Speaker, Author, and general pain in the butt.
 
Thoughts, views, and opinions expressed in this blog are solely my own.
 
Remember, the Impossible just takes longer.
​​​​
 

 Blogs you should be reading