Use SPServices to Get Recurring Events as Distinct Items


You may also be interested in: SharePoint Hosting by Fpweb.net


 

Editor’s note: Contributor Jim Bob Howard is a Senior Solutions Engineer at Juniper Strategy. Follow him @jbhoward

There are reasons why you might want to get distinct calendar events, rather than leaving all of the grunt work to a Calendar list. If you’re working in the front-end, of course, SPServices is the way to go. But, how to get what you want isn’t exactly clear without a lot of trial and error. I’ve already done that, so you can build from what I’ve learned.

Assumptions: You’ve worked with SPServices (http://spservices.codeplex.com). If not, spend some time getting to know this incredible, revolutionary library before attempting the following.

Using GetListItems
In SPServices, when we want to pull data from a list, we use GetListItems, and this situation is no different. It will look very much like this:


$().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "My Calendar",
.
.
.

Next come three object properties: CAMLViewFields, CAMLQuery, and the less-often used CAMLQueryOptions. Let’s start with the CAMLQueryOptions, since that’s probably new to most people.

If you don’t add the right CAMLQueryOptions and don’t include specific nodes to the CAMLViewFields and CAMLQuery, recurring dates will be returned as a single item with an EventDate ("Start Date") of the start date/time of the first occurrence, and an EndDate ("End Date") of the end date/time of the last occurrence. This will have the effect of making the recurring event be one long, multi-date event.

CAMLQueryOptions

In the CAML, we have to pass the QueryOptions to get all Recurring Events to be returned as individual instance nodes, including those in an "older" [e.g. v3] format. (Yes, in SharePoint, some [not all, mind you] of the events you create will actually be in the v3 format.) We also need the RecurrenceData and fRecurrence fields returned in order to get everything we need.

We get all of the above by including the QueryOptions node in the CAMLQueryOptions property:


CAMLQueryOptions: "<QueryOptions>" +
        // Query Options
    "</QueryOptions>",

The query options we need to add are:
CalendarDate (optional, if only looking at [Today]): set to a start date, explained below;
RecurrencePatternXMLVersion: set to ‘v3′ to get the "older" format mentioned above, and
ExpandRecurrence: set to "TRUE," meaning that we want them expanded

So our CAMLQueryOptions now looks like this:


CAMLQueryOptions: "<QueryOptions>" +
        "<CalendarDate>" + startDate + "</CalendarDate>" +
        "<RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>" +
        "<ExpandRecurrence>TRUE</ExpandRecurrence>" +
    "</QueryOptions>",

(We’ll look at the startDate variable below when we discuss how the dates overlap.)

CAMLViewFields

The above Query Options will only return expanded recurrences if you also include fRecurrence and RecurrenceData in your ViewFields:


CAMLViewFields: "<ViewFields>" +
        "<FieldRef Name='Title' />" +
        "<FieldRef Name='EventDate' />" +
        "<FieldRef Name='EndDate' />" +
        "<FieldRef Name='Location' />" +
        "<FieldRef Name='Description' />" +
        "<FieldRef Name='fRecurrence' />" +
        "<FieldRef Name='RecurrenceData' />" +
        "<FieldRef Name='fAllDayEvent' />" +
    "</ViewFields>",

In the above, the EventDate is the same as the Start Date. If calendar data is not expanded, this will be the starting date/time of the first occurrence, and the EndDate will be ending date/time of the last occurrence. That’s obviously not what we want, which is why we’re expanding the recurrence data.

NOTE: If you leave out the fRecurrence and RecurrenceData from the ViewFields, even if you include the CAMLQueryOptions listed above, recurring dates will not be expanded.

CAMLQuery

Now, here comes the meat of the CAML Query… and some unexpected behavior.

When pulling recurrent dates, you’ll most likely want to get a range of dates, say for a month, a year, or maybe just the events for Today. So, of course, we tell CAML (like SQL) what range we want in the WHERE clause, using the DateRangesOverlap node, like the following:


CAMLQuery: "<Query>" +
        "<Where>" +
            "<DateRangesOverlap>" +
                "<FieldRef Name='EventDate' />" +
                "<FieldRef Name='EndDate' />" +
                "<FieldRef Name='RecurrenceID' />" +
                "<Value Type='DateTime'>" +
                    "<Month />" +
                "</Value>" +
            "</DateRangesOverlap>" +
        "</Where>" +
        "<OrderBy>" +
            "<FieldRef Name='EventDate' />" +
        "</OrderBy>" +
    "<Query>",

Within the DateRangesOverlap node, the RecurrenceID FieldRef tells which occurrence item in the recurring sequence that we’re dealing with. Again, if this FieldRef isn’t included, the expansion doesn’t work correctly.

DateRangesOverlap Value Type

I don’t know if any back-end developers have run into this same issue when working with the SPQuery to pull expanded recurrence date, but from here, not all DateRangesOverlap Value types work the way they should. And they’re cryptically-documented, where mentioned at all. The basics are that you can return dates that "overlap" a given scenario. The overlapping varies depending on scenario, as follows:

<Year />
This is supposed to get all events within:
(a) Today’s year (if no CalendarDate is passed), or
(b) the date passed in QueryOptions as CalendarDate.
           
Actually, it returns all dates from [Today] into the future. It ignores:
(a) the CalendarDate, and
(b) the end of the current year

<Month />
Performs consistently as expected, with the exception that its definition of "month" is more of a "month view." A month view consists of all of the weeks which contain days in that month. In other words, the month view for May 2012 begins on April 29, 2012, because May 1 is on a Tuesday and the "month view" consists of full weeks. Likewise, the May 2012 month view ends on June 5, 2012. This means that pulling all of the dates for May 2012 will return all events for April 29 – June 5.

However, even though April 29 is the beginning of the May month view, when passed as the CalendarDate, it will return the April month view. See below for how this affects date calculations based on pulling data for a given month.

<Week />
Performs exactly like <Month />, but only returns the corresponding week for:
(a) Today, or
(b) CalendarDate

<Today />
Returns just the events for Today.

<Now />
Contrary to most documentation, <Now /> doesn’t "work" but rather defaults to the same results as <Year />.

In fact, <Year /> is the default. If you leave off the entire WHERE clause, but include the RecurrenceDate and fRecurrence fields and the QueryOptions (CalendarDate will be ignored), you get the same results as <Year />.

CalendarDate

In QueryOptions, if the CalendarDate is set, it needs the date to be a string in the format:
            yyyy-mm-ddThh:mm:ssZ

In most cases, setting the CalendarDatewhen needed) to the real date on which you want to base your range will work just fine, as long as you set the time in the Z-notation to noon. The reason for the noontime setting is to make allowance for all-day events within the given range. These tend to get messed up when viewing is based on a midnight or early morning time. Basing the range on noon alleviates the issue. (At least it does for US time zones; I confess that I haven’t tested them all.)

Conclusion

Putting all of the above together, here is a complete block of code that pulls the dates for a month range.


var startDate = "2012-05-12T12:00:00Z"; // Our example will show the month of May 2012
 
$().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "My Calendar",
    CAMLViewFields: "<ViewFields>" +
            "<FieldRef Name='Title' />" +
            "<FieldRef Name='EventDate' />" +
            "<FieldRef Name='EndDate' />" +
            "<FieldRef Name='Location' />" +
            "<FieldRef Name='Description' />" +
            "<FieldRef Name='fRecurrence' />" +
            "<FieldRef Name='RecurrenceData' />" +
            "<FieldRef Name='fAllDayEvent' />" +
        "</ViewFields>",
    CAMLQuery: "<Query>" +
            "<Where>" +
                "<DateRangesOverlap>" +
                    "<FieldRef Name='EventDate' />" +
                    "<FieldRef Name='EndDate' />" +
                    "<FieldRef Name='RecurrenceID' />" +
                    "<Value Type='DateTime'>" +
                        "<Month />" +
                    "</Value>" +
                "</DateRangesOverlap>" +
            "</Where>" +
            "<OrderBy>" +
                "<FieldRef Name='EventDate' />" +
            "</OrderBy>" +
        "</Query>",
    CAMLQueryOptions: "<QueryOptions>" +
            "<CalendarDate>" + startDate + "</CalendarDate>" +
            "<RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>" +
            "<ExpandRecurrence>TRUE</ExpandRecurrence>" +
        "</QueryOptions>",
    completefunc: function (xData, Status) {
        $(xData.responseXML).SPFilterNode("z:row").each(function() {
            var $node = $(this),
                fADE = $node.attr("ows_fAllDayEvent") || 0,
                thisADE = (fADE == 0),
                thisID = $node.attr("ows_ID"),
                sepID = thisID.indexOf(';#'),
                thisTitle = $node.attr("ows_Title"),
                thisRecurrence = $node.attr("ows_fRecurrence"),
                thisDesc = $node.attr("ows_Description");
            if (sepID != -1) thisID = thisID.substring(0,sepID);
            var thisUrl = "DispForm.aspx?ID=" + thisID,
                thisClass = thisTitle.replace(" ","").substr(0,10).replace(",","").replace(" ","") + thisID,
                thisRD = $node.attr("ows_RecurrenceData");                                                        
 
            // if working with FullCalendar or building an events object for another purpose...
            events.push({
                title: thisTitle,
                start: $node.attr("ows_EventDate"),
                end: $node.attr("ows_EndDate"),
                allDay: thisADE,
                url: thisUrl,
                description: $(thisDesc).text()
            });
        });
    }
})


 

Side bar note if you’re working with the FullCalendar library:

 

The events function in the FullCalendar library gets the true month view start date for start and the true month view end date for end (e.g. for May 2012, start = "April 29, 2012" and end = "June 5, 2012). As stated in the article, passing April 29 for the CalendarDate when looking at a <Month /> will yield the dates for April 2012 (April 1 – May 5).
 
So we’ll need to do some calculations to pass the correct date value to CalendarDate.

var startDate = start.getYear() + '-', // returns the year as YYYY
    monthAdj = 1, // for adjusting the month, if needed
    dayAdj = 0; // for adjusting the day, if needed

Since start is the beginning of the month view, it will either be the 1st of this month or within the last week of the previous month. Therefore, if it’s over 10, we know our view is starting in the previous month: we’ll add an extra month to our calculations, and subtract 10 days. So, if start is 4/29, we’ll make it 5/19 to make sure it’s fully in the month we want to view.

if(start.getDate()*1 > 10) { monthAdj++; dayAdj = -10 };

getMonth() returns the zero-based number of the month with no leading zeroes, so we’ll add 1 at least, but will add 2 if our view starts the previous month. And if it’s < 10, we’ll add a leading ‘0’ to the string.

if((start.getMonth()*1+monthAdj) < 10) {startDate += "0";}
startDate += (start.getMonth()*1+monthAdj) + '-';

getDate() returns the number of the days with no leading zeroes, so if it’s < 10 we’ll add a leading ‘0’

if (start.getDate()*1 < 10) {startDate += "0";}

For good measure we’ll set the time at noon to ensure we’re fully into the day we’re basing things on

startDate += (start.getDate()*1+dayAdj) + "T12:00:00Z";

Use this startDate in the code provided in the accompanying article.

 

Note: Jim Bob is a featured author in SharePoint 2010 at Work: Tips, Tricks, and Bold Opinions by EndUserSharePoint and O’Reilly Media. Use discount MILLERSP for 40% off the print edition or 50% off of the ebook.

One thought on “Use SPServices to Get Recurring Events as Distinct Items”

Comments are closed.