Tagged: SPQuery

Configuring a Birthday View in SharePoint using a [Today] filter

I was intrigued by a recent post on the Microsoft TechNet forums where the poster outlined the following scenario:

  • Employee information is maintained in a SharePoint list. The information includes each employee’s date of birth in a “Date and Time (Date Only)” field.
  • They would like to have a “Birthdays” view where they can display a list of employees whose birthday is today (i.e., using the [Today] filter on the list).
  • Problem #1: There are some privacy concerns about displaying an employee’s year of birth, and therefore his/her age to everyone.
  • Problem #2: The [Today] filter will not match if the year component of the date value does not equal the current year.

In one sense, I was glad that problems 1 and 2 both existed, because problem 1 led me to a solution that addresses problem 2.

My Proposed Solution

Algorithm: Think of the “Birthday” field as an “Actual Date of the Employee’s Next Birthday” field. This will mitigate both problems outlined above by no longer storing the employee’s year of birth in the list (we’ll assume that HR maintains and protects this data somewhere else) and allowing us to use a [Today] filter to show everyone who has a birthday today.

Solving this problem involves two components: one that initially updates all the Birthday values in the list with the current year or next year as the year component of the date (based on whether or not the employee’s birthday has already occurred this year), then one that updates the list daily to account for the previous day’s birthdays by updating the year component of those date values to next year.

Solution Part 1: Iterate through all items in the list (this will only need to be done once). For each item, based on the month and day component of the item’s “Birthday” value, update the year component to equal this year or next year based on whether the birthday has already occurred this year.

foreach (SPListItem item in birthdayList.Items)
{
  DateTime dtBirthday = DateTime.Parse(item["Birthday"].ToString());
  // Compare month and day components to the same month and day this year
  //  to determine if the birthday has already occurred this year
  DateTime dtBirthdayThisYear = new DateTime(DateTime.Now.Year, dtBirthday.Month, dtBirthday.Day);
  if (dtBirthdayThisYear.CompareTo(DateTime.Now) < 0)
  {
    // The birthday has already occurred, set the field to next year
    item["Birthday"] = new DateTime(DateTime.Now.Year + 1, dtBirthday.Month, dtBirthday.Day);
  }
  else
  {
    // Set the birthday field to this year
    item["Birthday"] = dtBirthdayThisYear;
  }

  item.SystemUpdate();
}

Solution Part 2: Query the list daily for all the birthdays that took place yesterday. Update each matching employee item’s birthday value to reflect his/her next birthday as taking place next year.

SPField birthdayField = birthdayList.Fields["Birthday"];
DateTime dtYesterday = DateTime.Today.AddDays(-1);
SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name='" + birthdayField.InternalName + "' />" +
"<Value Type='DateTime' IncludeTimeValue='False'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtYesterday) +
"</Value></Eq></Where>";

foreach (SPListItem item in birthdayList.GetItems(query))
{
  DateTime dtBirthday = DateTime.Parse(item["Birthday"].ToString());
  item["Birthday"] = new DateTime(DateTime.Now.Year + 1, dtBirthday.Month, dtBirthday.Day);
  item.SystemUpdate();
}

Some Notes on Deployment, Exception handling, etc.: For the sake of clarity and brevity, my code excerpts above do not include appropriate exception handling. You should always include this in your code. Likewise, I have not addressed some higher-level architectural considerations such as whether to deploy Solution Part 2 as a timer job, or a console application that runs as a scheduled task on the server, or something else entirely. I will discuss the relative merits of each of these approaches in a later post. For now, feel free to leave ideas and suggestions in the comments. Thanks!