Pre-filling Column Values in a SharePoint Form

ByMarc D Anderson

Christian Ståhl left me a good question in another post:

Have you tried to ‘formfill’ a SharePoint column for date and time with ‘today’s date’, that would be cool if its possibly?

Of course it’s possible!  You can pre-fill any columns value based on rules which you decide.  There are probably a few common situations:

  • Passing values on the Query String to pre-fill column values.  I have a post which talks a little about this scenarios already entitled Using Query String Variables to Populate SharePoint Form Fields.  That post was fairly specific to a problem I was trying to solve, but it should give you the basic steps for other situations.
  • Pre-filling based on some existing data (like the today’s date question above) . Let’s go through this scenario here.

The easiest way to set a column to today’s date and time is by using JavaScript.  Here’s an example of how this can work.  I had a client ask me to set a Date/Time column’s time to be 11 PM on NewForm.aspx (it caused some downstream issues if the time was the default 12 AM), and the code to do this is below.  The column’s name was ‘Request End Time’, and its form field label was ff2.

<script type="text/javascript">    
    _spBodyOnLoadFunctionNames.push("setRequestEndTime"); 

    // Set the Request End Time to '11 PM'    
    function setRequestEndTime() {     
        //alert('setRequestEndTime');     
        var tags = document.getElementsByTagName('select');     
        for (var i=0; i < tags.length; i++) {     
            //alert(' tags&#91;' + i + '&#93;.id=' + tags&#91;i&#93;.id);     
            if(tags&#91;i&#93;.id.indexOf('ff2_new') > 0 && tags[i].id.indexOf('DateTimeFieldDateHours') > 0) {     
                //alert('HIT tags[' + i + '].id=' + tags[i].id);     
                innerspans = tags[i].getElementsByTagName('option');     
                //alert(innerspans.length);     
                for (var j=0; j < innerspans.length; j++) {     
                    //alert('innerspans&#91;' + j + '&#93;.value=' + innerspans&#91;j&#93;.value);     
                    if (innerspans&#91;j&#93;.value == '11 PM') {     
                        innerspans&#91;j&#93;.selected = true;     
                        break;     
                    }     
                }     
            }     
        }     
    }     
</script>

The _spBodyOnLoadFunctionNames.push function lets you run any JavaScript you’d like when the form page loads.  In this case, I just have it run setRequestEndTime().  The JavaScript may look a little messy, but I’ve left in my debugging alerts in case you want to see how things happen.  The basic idea is that you find the appropriate HTML objects in the DOM in the JavaScript and then set the value you’d like.  Note that the HTML objects will be generated based on the controls which you have on the page.  You’ll want to use the Internet Explorer Developer Toolbar (IE7 and below) or the Developer Tools (IE8) to see what is actually rendered on the page for each control.

To get back to Christian’s actual question, we just need to tweak the JavaScript above a little bit to set the column’s value to today’s date.  In fact, setting the date is even simpler than setting the time:

<script type="text/javascript">   
    _spBodyOnLoadFunctionNames.push("setRequestEndTime"); 

    // Set the Request End Time to today's date   
    function setRequestEndTime() {    
        today = new Date();    
        todayDay = today.getDate();    
        todayMon = today.getMonth() + 1;    
        todayYear = today.getYear();    
        //alert('setRequestEndTime');    
        var tags = document.getElementsByTagName('input');    
        for (var i=0; i < tags.length; i++) {    
            //alert(' tags&#91;' + i + '&#93;.id=' + tags&#91;i&#93;.id);    
            if(tags&#91;i&#93;.id.indexOf('ff2_new') > 0) {    
                //alert('HIT tags[' + i + '].id=' + tags[i].id);    
                tags[i].value = todayMon + "/" + todayDay + "/" + todayYear;    
            }    
        }    
    }    
</script>

Note that we need to add one to todayMon because the month values start at 0 for January.  I’m also building up the value for today’s date in 1033 locale format because I live in the US – you would want to build this up in your locale’s format.

Similar Posts

33 Comments

  1. Pingback: WSS 3.0 & MOSS: Recopilatorio de enlaces interesantes (XXIX)! - Blog del CIIN

  2. Hi Marc, many thanks for your answere and your inspiring blogposts! Have now (a bit delayed) read this JS and try it, i now understand that this one seems to work fine with, as you say ‘form fields’. Typical in my case i want to use this technic in a SharePointWebControls:DateTimeField, a custom column for date & time that i use in a PageLayout. Do you think that would work with in some way in the same manner?
    Thanx dude!

    Reply

    1. Christian:

      I would think it would work the same or require just a little tweaking. All this JavaScript does is look into the DOM for the right HTML objects and change their value. If your SharePointWebControls:DateTimeField has a different wrapper around it, you should be able to find it with the Internet Explorer Developer Toolbar.

      M.

      Reply

  3. Hi Marc,

    I am new to sharepoint development. Thank you for this useful information. How do you set value in more than one column? For example, I want the Priority column to be “High,” (in a list of choices), and Category to be “PC Safety”, and the Department to be “IT.”

    Thank you!

    Xixi

    Reply

    1. Xixi:

      Take a look at this other post:
      http://sympmarc.com/2008/07/11/using-query-string-variables-to-populate-sharepoint-form-fields/

      Assuming that you are passing multiple values on the Query String, like so:

      http://servername/sitename/listname/EditForm.aspx?Priority=High&Category=PC%20Safety&Department=IT

      you can just add more lines into the JavaScript, like this:

      setLookupFromFieldName(“Priority”, vals[“Priority”]);
      setLookupFromFieldName(“Category”, vals[“Category”]);
      setLookupFromFieldName(“Department”, vals[“Department”]);

      You may need to adapt the JavaScript a bit depending on the types of columns you are setting.

      M.

      Reply

  4. Thanks for your suggestions.

    I was trying to set some text in a multi-line input field by following your example. It didn’t populate the text automatically in the form. Is there anything I am doing wrong?

    _spBodyOnLoadFunctionNames.push(“setDesc”);
    function setDesc() {
    var tags = document.getElementsByTagName(‘input’);
    for (var i=0; i 0) {
    tags[i].value = “Hello”;
    }
    }
    }

    Thank you, Xixi

    Reply

  5. Oops I am missing an “If” line.
    _spBodyOnLoadFunctionNames.push(“setDesc”);

    function setDesc() {
    var tags = document.getElementsByTagName(‘input’);
    for (var i=0; i 0) {
    tags[i].value = “Hello”;
    }
    }
    }

    Reply

    1. Xixi:

      Hard to tell what’s going wrong. As you noticed, WordPress doesn’t allow much code in comments. If you’d like, you can send me your script by email at marc period anderson at sympraxisconsulting period com.

      M.

      Reply

  7. Hi Marc,

    Thanks for the post.
    I am looking to do something similar for my “NewItem” form.
    But, I want to set the values in “NewItem” form for a particular ID.
    That is, I am providing a link for each item that is created using NewItem and when user clicks on that link, it will provide him with a NewItem form for that particular ID, with all pre-filled values.
    The user can than change the values or use that to create a new request from that template form.
    Please provide me any suggestions or queries.
    Let me know if you have any questions.

    Thanks,
    Vaishnavi

    Reply

    1. Well, if the initial values are fixed, you can just make them the default values for the columns in the list. If they are variable, then you can pass them in on the Query String.

      M.

      Reply

      1. Thanks for your quick response.
        What do you mean by passing then in Query string?

        What i am exactly trying to accomplish is, creating a Template for the NewItem form with prefilled values based on the ID.

        So if the list has a bunch of items with ID 1,2,3,4…
        When user wants to create “newitem” which is same as ID#2, he can directly use an option or a link which will direct him to “NewItemTemplate” page with all values filled for that particular ID#2.
        He can than change the values on that form or just use it to create New Item in that list.

        Thanks,
        Vaishnavi

        Reply

        1. So it’s more of a cloning exercise. I think what I’d do is pass the ID of the item you want to clone into the new item form and grab it from the Query String. Then use the SharePoint Lists Web Services, GetListItems operation, to grab the values you want from that item to prepopulate the new items form. Take a look at my jQuery Library for SharePoint Web Services for an easy way to use the Web Services.

          M.

          Reply

          1. Hi Marc,

            I am new to SharePoint and am not quite sure how to perform this.
            Also i the ID of the item is not known.
            It could be any item ID from the list.
            If you could please explain it using real world code, i would appreciate it.

            Thanks.

            Reply

            1. To be honest, based on your questions, I’m not sure I could guide you through it very easily. This isn’t level 100 stuff, but more involved. It might be a half day or a day of work for me, when all is said and done.

              I don’t know if you have a development background or not, but you’re saying that you’re new to SharePoint.

              M.

              Reply

  8. Okay, so I’m trying to create a newform.aspx page with multiple lookup columns. These lookup columns have multiple fields. When I do a lookup column to the List it only allows me to select one aspect of the list’s columns, per column that I create. I need to be able to populate all of the list’s fields in one lookup column.

    ie. I have a “Credits” list, under credits newform.aspx are three columns, “Name” “Style Number” “Category” all are INPUT text fields except “Category” is a choice field with 3 choice drop downs. I have another list “Editorials” this list needs to have a Credits lookup column, where if need be, in the Editorials Newform.aspx the user can add items to the Credits list and it’s respective 3 columns “Name” “Style Number” and “Category”. Any Ideas?

    Cheers!

    Joshua

    Reply

    1. Joshua:

      Forms in SharePoint can only interact with a single list unless you pretty much write them from scratch. However, there are tricks you can use to help make the user expereince feel more united. My jQuery Library for SharePoint Web Services can help with this, specifically (in your case) the $().SPServices.SPDisplayRelatedInfo and $().SPServices.SPLookupAddNew functions. Take a look and post questions, if you have them, in the Discussions area on the Codeplex site.

      M.

      Reply

  9. Marc,

    thanks for the links, I might have to try out the link to list solution. Otherwise I’m thinking I’m just going to add all the columns so that it’s possible to populate and just make it look like it’s coming from one source with DVWP customizations. I didn’t want them to have to leave the page to get the content on there… but it might be the easiest way at this point.

    Joshua

    Reply

  10. Very helpful info. Thanks!

    I think you should be using today.getFullYear(); when getting the year for making the date. today.getYear(); returns 111 where as today.getFullYear(); returns 2011.

    Regards!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.