How can I set the default value in a SharePoint list field, based on the value in another field?

Question

Hi all,

I have a custom list in SharePoint (specifically, MOSS 2007.) One field is a yes/no checkbox titled "Any defects?" Another field is "Closed by" and names the person who has closed the ticket.

If there are no defects then I want the ticket to be auto-closed. If there are, then the "Closed by" field ought to be filled in later on.

I figured I could set a calculated default value for "Closed by" like this:

=IF([Any defects?],"",[Me])

but SharePoint complains I have referenced a field. I suppose this makes sense; the default values fire when the new list item is first opened for entry and there are no values in any fields yet.

I understand it is possible to make a calculated field based on a column value but in that case the field cannot be edited later.

Does anyone have any advice how to achieve what I am trying to do?

Is it possible to have a "OnSubmit" type event that allows me to execute some code at the point the list item is saved?

Thank you.

Answer 1

Include a content editor web part in the page (newform.aspx / editform.aspx) and use jQuery (or just plain javascript) to handle the setting of default values.

Edit: some example code:

In the lists newform.aspx, include a reference to jquery. If you look at the html code, you can see that each input tag gets an id based on the field's GUID, and a title that's set to the fields display name.

now, using jquery we can get at these fields using the jQuery selector like this:

By title:

$("input[title='DISPLAYNAMEOFFIELD']");

by id (if you know the field's internal guid, the dashes will ahve to be replaced by underscores:

// example field id, notice the guid and the underscores in the guid ctl00_m_g_054db6a0_0028_412d_bdc1_f2522ac3922e_ctl00_ctl04_ctl15_ctl00_ctl00_ctl04_ctl00_ctl00_TextField

$("input[id*='GUID']"); //this will get all input elements of which the id contains the specified GUID, i.e. 1 element

We wrap this in the ready() function of jQuery, so all calls will only be made when the document has fully loaded:

$(document).ready(function(){
 // enter code here, will be executed immediately after page has been loaded
});

By combining these 2 we could now set your dropdown's onchange event to the following

$(document).ready(function(){
 $("input[title='DISPLAYNAMEOFFIELD']").change(function() 
 {
      //do something to other field here 
 });
});

Answer 2

The Use jQuery to Set A Text Field’s Value on a SharePoint Form article on EndUserSharePoint.com shows you how to set a default value for a field using JavaScript/jQuery.

They also have a whole series of articles on 'taming calculated columns' that will show you many more powerful options you have for calculated fields with the use of jQuery.

One thing to be aware of when inserting JavaScript into a SharePoint page and modifying the DOM is support. There is a small chance that a future service pack will break the functionality you add, and it is quite likely that the next version of SharePoint will break it. Keeping this mind however, I believe it's a good solution at this time.

Answer 3

I've got a walk through with sample code that may help

• Setting a default duration for new calendar events

It sets the End Time/Date fields to Start Time + 1.5 hours when you create a new event.

Its complicated a little by the steps need to do the time/date work, but you'll see examples of how to find the elements on the form and also one way to get your script onto the newform.aspx without using SPD.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"&gt;
</script>
<script type="text/javascript">

  // Set the hours to add - can be over 24
  var hoursToAdd = 1;
  // Mins must be 0 or div by 5, e.g. 0, 5, 10, 15 ...
  var minutesToAdd = 30;

  // JavaScript assumes dates in US format (MM/DD/YYYY)
  // Set to true to use dates in format DD/MM/YYYY
  var bUseDDMMYYYYformat = false;

  $(function() {

    // Find the start and end time/minutes dropdowns by first finding the
    // labels then using the for attribute to find the id's
    // NOTE - You will have to change this if your form uses non-standard
    // labels and/or non-english language packs
    var cboStartHours = $("#" + $("label:contains('Start Time Hours')").attr("for"));
    var cboEndHours = $("#" + $("label:contains('End Time Hours')").attr("for"));
    var cboEndMinutes = $("#" + $("label:contains('End Time Minutes')").attr("for"));

    // Set Hour
    var endHour = cboStartHours.attr("selectedIndex") + hoursToAdd;
    cboEndHours.attr("selectedIndex",endHour % 24);

    // If we have gone over the end of a day then change date
    if ((endHour / 24)>=1)
    {
     var txtEndDate = $("input[title='End Time']");
     var dtEndDate = dtParseDate(txtEndDate.val());
     if (!isNaN(dtEndDate))
     {
      dtEndDate.setDate( dtEndDate.getDate() + (endHour / 24));
      txtEndDate.val(formatDate(dtEndDate));
     }
    }

    // Setting minutes is easy!
    cboEndMinutes.val(minutesToAdd); 

});

// Some utility functions for parsing and formatting - could use a library
// such as www.datejs.com instead of this
function dtParseDate(sDate)
{
    if (bUseDDMMYYYYformat)
    {
     var A = sDate.split(/[\\\/]/);
     A = [A[1],A[0],A[2]];
     return new Date(A.join('/'));
    }
    else
     return new Date(sDate);
}

function formatDate(dtDate)
{
    if (bUseDDMMYYYYformat)
     return dtDate.getDate() + "/" + dtDate.getMonth()+1 + "/" + dtDate.getFullYear();
    else
     return dtDate.getMonth()+1 + "/" + dtDate.getDate() + "/" + dtDate.getFullYear();
}

</script>