SharePoint Calendar Rest query (Items vs GetItems) date filtering issue

I normally use “Items” for SharePoint Rest query but with Calendar some issues.

var requestUri = _spPageContextInfo.webAbsoluteUrl
+”/_api/Web/lists/getbytitle(‘” + glbCalendarDisplayName + “‘)/Items”
+ “?” + querySelectOption
+ “&” + querySortingOption
+ “&” + queryExpandOption
+ “&” + queryFilterOption
+ “&” + queryPagingOption

* Have to use GET type

Filter with EventDate/EndDate not allowed.

  • Error : “The field ‘EventDate’ of type ‘DateTime’ cannot be used in the query filter expression.”
  • But if I add new date type field into the Calendar and filter with the date field it works fine.

* Error for filtering with SharePoint Calendar OOTB date field EventDate/EndDate
var queryFilterOption = “$filter=EventDate le datetime'”+endDateString
+ “‘ and EndDate ge datetime'”+startDateString+”‘”;

* No error for filtering with new added date field
var queryFilterOption = “$filter=NewAddedFieldDate le datetime'”+endDateString+”‘”;

So how about let’s use Calm Query into other Rest endpoint GetItems

var requestUri = _spPageContextInfo.webAbsoluteUrl
+ “/_api/Web/lists/getbytitle(‘”+glbCalendarDisplayName+”‘)/GetItems(query=@v1)”
+ “?” + querySelectOption
+ “&” + queryPagingOption
//+ “&” + queryExpandOption  //expand not working.
//+ “&” + querySortingOption //sorting not working.
+ “&@v1=” + JSON.stringify(viewXml)

* Have to use POST type

* in the headers need “X-RequestDigest”: jQuery(“#__REQUESTDIGEST”).val()

First the Calm query applied and then other parameter passing option applied.

Disadvantage for GetItems :

  1. can’t use expand such as Author.
  2. Returned date/time type is without UTC designator(“Z”) so it’s local time format with site settings/reginonal settings.
    • ISO 8601 UTC designator format : “2017-08-31T16:00:00Z”
    • ISO 8601 without time zone offset : “2017-09-01T00:00:00”
  3. So in the CAML query when you filter the date should use ISO8601 without time zone offset to get accurate data. (this is only for GetItems tricky solution. in case of Items you can use with ~Z type.)

var startDateString = “2017-09-01T00:00:00.000”; //’2017-08-31T16:00:00.000Z’ will get unwanted result

var endDateString = “2017-09-30T23:59:59.999″; //’2017-09-30T15:59:59.999Z’ will get unwanted result

var viewXml = {var viewXml = {ViewXml:”<View>” +”<Query>” +//”<OrderBy><FieldRef Name=’EventDate’ Ascending=’True’ />” + “</OrderBy>” +”<Where><And>” +”<Geq>” +”<FieldRef Name=’EndDate’ /><Value Type=’DateTime’ IncludeTimeValue=’TRUE’>” + startDateString + “</Value>” +”</Geq>” +”<Leq>” +”<FieldRef Name=’EventDate’ /><Value Type=’DateTime’ IncludeTimeValue=’TRUE’>” + endDateString +”</Value>” +”</Leq>” +”</And></Where>” +”</Query>” +”<RowLimit>” + viewPageSize + “</RowLimit>” +/*”<ViewFields>” +”<FieldRef Name=’AuthorId’ />” +”<FieldRef Name=’Title’ />” +”<FieldRef Name=’Category’ />” +”<FieldRef Name=’EventDate’ />” +”<FieldRef Name=’EndDate’ />” +”<FieldRef Name=’fRecurrence’ />” +”<FieldRef Name=’fAllDayEvent’ />” +”<FieldRef Name=’EventType’ />” +”<FieldRef Name=’RecurrenceData’ />” +”<FieldRef Name=’MasterSeriesItemID’ />” +”<FieldRef Name=’Duration’ />” +”<FieldRef Name=’RecurrenceID’ />” +”<FieldRef Name=’TimeZone’ />” +”<FieldRef Name=’UID’ />” +”<FieldRef Name=’XMLTZone’ />” +”</ViewFields>” +*/”</View>”};

Recurrence information select

by default the recurrence information is not select so have to define the select option manually.

var querySelectOption = “$select=Title,EventDate, EndDate, AuthorId,RecurrenceData,MasterSeriesItemID,EventType,Duration,RecurrenceID,TimeZone,UID,XMLTZone”;

EventRecurrenceInformation

 

 

 

There is 2010 style web service call as well to filter the event date.

But the field name should be StartTime and EndTime

var queryFilterOption = "$filter=StartTime le datetime'"+endDateString
+ "' and EndTime ge datetime'"+startDateString+"'";
var requestUri = _spPageContextInfo.webAbsoluteUrl+"/_vti_bin/ListData.svc/"+ glbCalendarDisplayName
+ "?" + querySelectOption
+ "&" + queryFilterOption

disadvantage :

  1.  can’t select recurrence data.
  2. the returned date is millisecond format so have to convert and have to apply timezone offset
    • for example : “/Date(1506384000000)/”
    • new Date(parseInt(RESTReturn[i].StartTime.match(/\/Date\(([0-9]+)(?:.*)\)\//)[1]) – 8*60*60*1000)

 

References)

https://fatalfrenchy.wordpress.com/2010/07/16/sharepoint-recurrence-data-schema/

https://github.com/SharePoint/PnP/tree/master/Samples/Core.DisplayCalendarEvents

http://www.hubfly.com/blog/solutions/how-to-get-sharepoint-event-recurrence-data-using-rest-api/

http://itblog.wolthaus.net/2011/12/rest-filter-datetime/

https://kartooz.wordpress.com/2009/02/20/querying-sharepoint-calendar-for-recurring-events-using-caml-query/

https://sharepoint.stackexchange.com/questions/146085/submitting-recurrence-data-with-spservice

https://social.msdn.microsoft.com/Forums/office/en-US/5e63a4e9-5b62-402d-beaf-57bb98a376d7/recurrence-events-via-rest?forum=sharepointcustomization

https://social.technet.microsoft.com/Forums/en-US/d4e87de2-4ad1-4b7c-9bbc-891ec10bf4d9/handling-recurring-events-using-sharepoint-2013-rest-api?forum=sharepointdevelopment

https://social.technet.microsoft.com/wiki/contents/articles/32133.sharepoint-2013-retrieve-recurrence-data-programmatically-from-a-calendar-list.aspx

https://sharepoint.stackexchange.com/questions/136482/sharepoint-rest-api-using-caml-query-and-select-expand-to-get-lookup-data

SharePoint REST API: Retrieve List Items Comparison

SharePoint REST API: Retrieve List Items Comparison

https://threewill.com/using-the-expand-odata-query-option-in-sharepoint-2013/

https://msdn.microsoft.com/en-us/library/office/dn531433.aspx#bk_ListItemCollectionEndpoint

https://social.msdn.microsoft.com/Forums/office/en-US/e9952029-a1b1-4560-ac94-f27b67af1aed/sharepoint-app-rest-api-expand-not-working-with-getitems?forum=appsforsharepoint

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: