The SharePoint Hillbilly

Creating a SharePoint List Parent / Child Relationship - Out of the Box

<update>

Be sure to check out the new video blog post based on this entry. It may be easier to follow and has a couple of upgrades from this post:  Creating a SharePoint List Parent/Child Relationship – VIDEO REMIX

</update>

First Things First

Okay, first off, my development blogs will fall into one of two categories “Out of the Box” or “Custom”.  My definition for “Out of the Box” is anything that can be done in SharePoint, SharePoint Designer, or InfoPath.  Another way to look at it is if you can do it “Out of the Box” you can do it remotely without having to use a VM or any sort of Remote Desktop Connection.  Of course, this may be everyone’s definition of “Out of the Box”, but I just want to make sure we are all on the same page when reading my Blogs. 

The Impetus

When we first started our SharePoint development efforts we had a requirement to write an application to track issues and projects as well as log time against them.  All of our development had to be Out of the Box because of the development constraints with Custom development and well, because at the time we didn’t really know what we were doing.  However, a couple of things came out of this development effort which I think will actually come in handy.  One of these was creating a Parent / Child relationship between lists so that we could track hours for a project or issue.   Unless there is some functionality in SharePoint that I’m not aware of (and there is a TON) you cannot create a list and have it automatically have any parent identifying field automatically set?  If I’m wrong, I’d love to know it.

Is There A Better Way?

One of the joys of SharePoint is that there are a lot of different ways to accomplish the same thing.  Is this the BEST method?  I doubt it.  This is what I was able to figure out and is pretty straightforward.  In fact, I just looked looked at some existing SharePoint templates to see how they did it.  This isn’t even my idea!  But I figure one of the benefits of a Blog is that it is a good place to document some of the things I’ve done and can easily go back later and find it.  So, this is more about me helping me than me helping you.  Yes, it’s out there.  I’m as selfish as my wife says I am. 

Blah… Blah… Blah… Just Show Me How You Did It

Okay! Keep your pants on.   First lets think of a real world scenario.  How about a time tracking application to track time against issues?  What a great idea!  Here are the steps we’ll take.

1. Create Issue List
2. Create Time Log Entry List
3. Show time log entries for an Issue on an Issue View
4. Create link on Issue View that passes Issue List ID to a page that creates a new Time Log Entry
5. Create a page for creating a new Time Log Entry and store parent’s ID

Pretty easy eh?  Let’s get started. I’ll show you step-by-step in case you are as dense I am.

CREATE ISSUE LIST

Follow these steps from your SharePoint site (or watch the quick video, it always helps me to actually see it done)

1. Click “Site Actions”
2. Click “Site Settings”
3. Click “Site Libraries and Lists”
4. Click “Create new Content”
5. Click “Custom List”
6. Enter the name of the List (“Issue” in this example)
7. Click “Create”

You now have a custom list created called “Issue”.  This list only has a couple of columns (Title, Created By, Modified By, and other auto-generated columns).  If you want to add more columns click on “Settings” then “Create Column” to add whatever columns you need.

CREATE TIME LOG ENTRY LIST

Follow the same steps above and create another list called “Time”. 

After the “Time” list is created, create a column for this list called “IssueID”.  Use the settings in the screen shot to the right.  In addition to make this application more realistic add a couple of more fields.  Create a number field called “Hours” and a Date field called “Entry Date”. So when we are done we will have these fields:

SHOW TIME LOG ENTRIES FOR AN ISSUE ON AN ISSUE VIEW

Okay, now this is where the fun starts.  Start up SharePoint Designer and open your site.  I suppose the same results could be achieved by editing the aspx pages directly, but again, I’m not that ambitious.  Open up the Issue Display form (DispForm.aspx).  I’ve heard some people say that you should not modify the default aspx files generated by SharePoint and that’s probably sound advice.  For the purposes of this tutorial I am going to use these files.  If you need help creating a new file shoot me an email.

Drag and drop the “Time” List to the bottom of the Issue DispForm.aspx file.

Let’s filter the Time list WebPart so that it will only show Time entries that belong to the Issue. This is done with the following Steps (or again, watch the video):

1. Open up the “Common Data View Tasks” for Time List.
2. Click “Filter”.
3. Click “Click here to add new clause…”.
4. Select “IssueID” for Field Name.
5. Select “Create a new parameter…” for Value.
6. Give the parameter a Name of “IssueID”
7. Select “Query String” as the Parameter Source
8. Enter “ID” for Query String Variable

CREATE LINK ON ISSUE VIEW THAT PASSES ISSUE LIST ID TO A PAGE THAT CREATES A NEW TIME LOG ENTRY

We now need to pass the ID of the current Issue as a query string variable to the aspx page responsible for creating a new Time entry.  Edit the Display page for Issue, and insert the following “<xsl:otherwise></xsl:otherwise>” code block within the <xsl:template> tag at the end of the Time list. :

<xsl:template name="dvt_1.rowinsert">
        <xsl:param name="IsInsertMode" />
        <xsl:variable name="Pos">_new</xsl:variable>
        <tr>
            <xsl:choose>
                <xsl:when test="$IsInsertMode = '1'">

                     .

                     .

                     .
                </xsl:when>
                <xsl:otherwise>
                    <td class="ms-vb" colspan="99">
                         <a href="../Time/NewForm.aspx?IssueID={$IssueID}" onclick="javascript:this.href = unescapeProperly(escape(this.href)); GoToLink(this); return false;" target="_self">Create a new Time Log Entry...</a>
                   </td>
              </xsl:otherwise>

The “{$IssueID}" is the parameter that we created above and will contain the ID of the current Issue.  The above code will create a link that looks like the following:

When a user clicks on the “Create a new Time Log Entry…” link they will be taken to the page to create a new Time list entry.  Save your changes and let’s make changes to the “NewForm.aspx” file for the Time list so that it stores the IssueID.

*UPDATE 3/4/09 ISSUES WITH MISSING XSL:TEMPLATE CODE*

**************************************************************************************

It appears as though I may have missed a step, or in some instances an additional step is needed in order to get the XSL code mentioned above.  If you are missing the aforementioned “xsl:template” code, follow these steps:

1. Open up the “Common Data View Tasks” for Time List.
2. Click “Date View Properties…”.
3. Click on the “Editing” tab.
4. Check the “Show inset item link” checkbox
5. Press the “OK” button

    

At this point you should be able to find the “<xsl:otherwise>” code block described above and make the necessary modifications.  I hope this helps clear up any confusion.  Now… back to our regularly scheduled blog…

**************************************************************************************

CREATE A PAGE FOR CREATING A NEW TIME LOG ENTRY AND STORE PARENT’S ID

Probably the least intuitive part of this whole process is writing the code in the “NewForm.aspx” page so that it stores the ID of the parent Issue into the IssueID field of the “Time” list.  We have the parent ID of the Issue passed to the page in the “IssueID” query string.  To do this, simply add the following JavaScript to your “NewForm.aspx” page for a new Time list entry:

<script type="text/javascript">
var qs = location.search.substring(1, location.search.length);
var args = qs.split("&");
var vals = new Object();

for (var i=0; i < args.length; i++) {
    var nameVal = args[i].split("=");
    var temp = unescape(nameVal[1]).split('+');
    nameVal[1] = temp.join(' ');
    vals[nameVal[0]] = nameVal[1];
} 
setValueForFieldName("IssueID", vals["IssueID"]);
function setValueForFieldName(fieldName, value) {
  if (value == undefined) return;
    var theInput = getTagFromIdentifierAndTitle("input","",fieldName);
    theInput.value = value;
}
function getTagFromIdentifierAndTitle(tagName, identifier, title) {
  var len = identifier.length;
  var tags = document.getElementsByTagName(tagName);
  for (var i=0; i < tags.length; i++) {
    var tempString = tags[i].id;
    if (tags[i].title == title && (identifier == "" || tempString.indexOf(identifier) == tempString.length - len)) {
      return tags[i];
    }
  }
  return null;
}
</script>

I have found that the location of the script does matter.  I generally place it within the “PlaceHolderBodyAreaClass” ContentPlaceHolderId content tags. 

Conclusions?

There you have it, when you use the link on the Issue DispForm.aspx to create a new Time log entry it will create a Time entry with the IssueID set to the ID of its corresponding Issue.  When you view an Issue you will see a list of all Time entries corresponding to that Issue.  This also comes in handy for reporting on the data.  In our production environment I disabled allowing users to go directly to the NewForm.aspx page for a Time log entry so that there were no orphan Time entries out there.  Nothing spectacular, but it gets the job done.  If you’ve seen a better way of doing this, please post a comment with a link, I’m sure others would benefit as well. 

There’s quite a bit you can do from here.  To make this work more real world you would need to modify the “NewForm.aspx” page to hide the IssueID field and you could add some quick and dirty Workflows from SharePoint Designer to do things like sum up hours worked and store it in the Issue.

I’m more than happy to keep this application moving forward in future blogs if there is any interest, adding the above features and any others you might want to see?  Just let me know.

Also, if you found this blog completely worthless I’d like to know that as well.  I don’t want to make your trips here a total waste of your time, but I do offer a money back guarantee. 

<UPDATE>

There seems to be lots of interest out there in sending more than one value from the parent list to the NewForm.aspx page of the child list.  I finally got around to writing a blog explaining how to do this as a follow up to this blog.  You can find it here:

Passing Multiple Query String Variables Using SPD – Follow Up on Creating Parent / Child List Relationships

Enjoy!

</UPDATE>

<ANOTHER UPDATE>

Are you finding the JavaScript confusing?  I wrote a quick blog post where I break the JavaScript down into more detail.  Hopefully it will make a little more sense after you read it.  The blog is located here:

Setting SharePoint Form Fields Using JavaScript

</ANOTHER UPDATE>

posted @ Wednesday, December 24, 2008 12:34 AM