Pre-filling Column Values in a SharePoint Form
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[' + i + '].id=' + tags[i].id);
if(tags[i].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[' + j + '].value=' + innerspans[j].value);
if (innerspans[j].value == '11 PM') {
innerspans[j].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[' + i + '].id=' + tags[i].id);
if(tags[i].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.
Hello
Is it possible to change the value of one field in a form based on a previous field in the same form?
For example: I have a field in my form which allows a user to pick a date using the Date picker. I have another field in the same form which should auto populate with the next month name. So if I picked 25/11/2011, the other field should auto populate with December as the value.
Thanks for any feedback.
Regards,
Yoshi
Yoshi:
Of course. You need to hook into the change event for column A, calculate the value for column B, and insert it into the DOM.
M.
Hi Marc,
I am trying to create a duplicate button on a NewItem, so that when the button is clicked, the item is created and a new NewItem form pops up with the same information in each column as the last one. Would the query string be the best way to go about doing this, or is there a more efficient way?
Thanks for your time!
John
John:
When you submit the NewForm, you don’t yet know the ID of the newly created item. I have a function in SPServices called SPRedirectWithID that can help here. If you use that function, you can get the ID of the newly created item and the use a call to GetListItems to populate the NewForm with the same values.
M.
Marc,
I have the ID, but im not sure how to use GetListItems to get the values of the specific item that was just created.
John
John:
Have you checked the documentation on the Codeplex site? There are several examples there. If you need more help, please post your code in the Discussions and I’ll see if I can help.
M.
Sorry for taking so long to respond, I finally learned how to use CAML! But now it seems that only the “required” fields are being brought over to the new form. Is this a problem with my code or is that normal?
John:
That’s not “normal” unless that’s what you’ve asked for. By default GetListItems will only return the columns in the default view. If you set CAMLRowLimit to 0, you’re telling SharePoint to ignore the default view.
M.
Its working now! I was not specifying all the fields I wanted correctly. Thanks for all your help it is greatly appreciated
Hi Marc, I am trying to pull all the dates value in “Date” column and days names in “Weekday” column automatically when a week is selected using datepicker.
I have created a custom NewForm in SharePoint Foundation 2010 with a single column “Select Week”( takes the week as Mon-Sun).When I select a week from the date picker, it fills select week field with Monday’s date of selected date.Once I click on Save button, in “AllItems” page “Date” and “Weekday” columns must filled in automatically from Monday to Sunday depending on selected week.
Would you please suggest me how to proceed to get the solution?
Thanks in Advance.
Hi Marc ,
I have multiple select lookup column in a sharepoint list created using three level cascading dropdown using ur spservices.. As you know it is control with two select options and two buttons “Add” & “Remove” . I want some values to be populated by default in the right hand side select option ( these values are retrieved from the previous selects ) . Please advise how to achieve this.
var options = $(“[title=’Responsibility selected values’]”);
options.append($(“”).val().text(“test”));
but it is throwing error. Please advise
Ranjani:
The way I do this in SPServices is to select the values I want to set in the left box and the call GipAddSelectedItems(master). Using the existing SharePoint script functions ensures that all of the right JavaScript variables are set as well as the selection working.
M.
Marc, even today this blog continues to be useful to newcomers like me. Currently working in SP2007 and migrating to SP2010, I need to create task forms that are differ in the information required from the submitter. I can create the pre-filled list field based on the form type but it looks like an input field to the submitter. How can I convert the field to be just a display or hide it altogether. The field is required and used to group tasks.
Anne:
You can disable and/or hide the fields with script. However, you’ll need to re-enable them in the PreSaveAction or the commit may not be successful, especially if any of the pre-filled columns are required.
M.