Pull on-call information from calendar

10.06.2011 Update: I forgot to separate out the FieldInternalNames for the start and end date. See updated code.


This solution is an answer to a request from Colin Blake:

Hey Alexander,

I’ve been browsing through your blog (amazing!) and have not been able
to come up with anything yet using your posted solutions so I was
hoping you could help me or get me pointed in the right direction. I
have a calender that holds our “On-Call” information. I have added a
custom column to the calender that holds a text value(the name of the
on call person). What I would like to do is is on another Web Part
Page is have the text value from the custom “on-call” column for the
current week displayed in a CEWP Web Part. Is this something that
could be easily done?

Thanks,
Colin Blake

This solution is designed to be put directly into a CEWP and will insert the name of the person “on call” in a placeholder <div>.

Note to SharePoint 2010 users:
Add this code to a text file and put it in a document library, then use the content link option on the CEWP to link to this code. This is necessary to overcome a “bug” in the CEWP handling when editing a SP2010 page. If you put a script that generates HTML directly into a CEWP, the HTML is accumulated when editing the page.

CEWP code:

&lt;div id=&quot;insertOnCallNameHere&quot;&gt;&lt;/div&gt;
&lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;

// Define list name or GUID
var calendarName = &quot;OnCallCalendar&quot;;
// Define the FieldInternalName to pull in the value from
var fieldToReturn = 'OnCallPerson';
// Define the start and end date fields
var startDateFIN = 'EventDate';
var endDateFIN = 'EndDate';
// Call the cunction
var onCallArr = getOnCallInfo(calendarName,fieldToReturn,startDateFIN,endDateFIN);

// Handle the result
var buffer = [];
$.each(onCallArr,function(i,obj){
	// obj.url = hyperlink to profile page
	// obj.name = name
	// obj.userId = user id
	// url and userid is available only when &quot;fieldToReturn&quot; is a people picker.
	buffer.push(&quot;&lt;div&gt;&quot;+obj.url+&quot;&lt;/div&gt;&quot;);
});
$(&quot;#insertOnCallNameHere&quot;).html(buffer.join(''));

// ****************************************************************
//	Do not edit below this line
// ****************************************************************

function getOnCallInfo(listName,fieldNameToReturn,startDateFIN,endDateFIN){
	var result = [];
	var queryBuffer = [];
		queryBuffer.push(&quot;&lt;Where&gt;&quot;);
		queryBuffer.push(&quot;&lt;And&gt;&quot;);
		queryBuffer.push(&quot;&lt;Leq&gt;&lt;FieldRef Name='&quot;+startDateFIN+&quot;' /&gt;&lt;Value Type='DateTime'&gt;&lt;Today /&gt;&lt;/Value&gt;&lt;/Leq&gt;&quot;);
		queryBuffer.push(&quot;&lt;Geq&gt;&lt;FieldRef Name='&quot;+endDateFIN+&quot;' /&gt;&lt;Value Type='DateTime'&gt;&lt;Today /&gt;&lt;/Value&gt;&lt;/Geq&gt;&quot;);
		queryBuffer.push(&quot;&lt;/And&gt;&quot;);
		queryBuffer.push(&quot;&lt;/Where&gt;&quot;);
	var res = spjs_QueryItems({listName:listName,query:queryBuffer.join(''),viewFields:['ID',fieldNameToReturn]});
	$.each(res.items,function(i,item){
		if(item[fieldNameToReturn]!==null){
			var split = item[fieldNameToReturn].split(';#');
			var name = split[1];
			var userId = split[0];
			if(split.length===2&amp;&amp;!isNaN(parseInt(split[0],10))){
			result.push({url:&quot;&lt;a href='&quot;+L_Menu_BaseUrl+&quot;/_layouts/userdisp.aspx?Force=True&amp;ID=&quot;+userId+&quot;' target='_blank'&gt;&quot;+name+&quot;&lt;/a&gt;&quot;,
						 name:name,
						 userId:userId});
			}else{
				if(split.length===2){
					name = split[1];
				}
				result.push({url:'URL: This value is available using a people picker only.',
							 name:name,
							 userId:'userId: This value is available using a people picker only.'});
			}	
		}	
	});
	return result;		
}

function spjs_QueryItems(argObj){
	if(argObj.listBaseUrl==undefined)argObj.listBaseUrl=L_Menu_BaseUrl;
	if(argObj.listName==undefined || (argObj.query==undefined &amp;&amp; argObj.viewName==undefined)){
		alert(&quot;Missing parameters!nnYou must provide a minimum of &quot;listName&quot;, &quot;query&quot; or &quot;viewName&quot; and &quot;viewFields&quot;.&quot;);
		return;
	}
	var content = spjs_wrapQueryContent({'listName':argObj.listName,'query':argObj.query,'viewName':argObj.viewName,'viewFields':argObj.viewFields,'rowLimit':argObj.rowLimit,'pagingInfo':argObj.pagingInfo});
	var result = {'count':-1,'nextPagingInfo':'',items:[]};	
	spjs_wrapSoapRequest(argObj.listBaseUrl + '/_vti_bin/lists.asmx', 'http://schemas.microsoft.com/sharepoint/soap/GetListItems', content, function(data){
		result.count = $(data).find(&quot;[nodeName='rs:data']&quot;).attr('ItemCount');
		result.nextPagingInfo = $(data).find(&quot;[nodeName='rs:data']&quot;).attr('ListItemCollectionPositionNext');
		$(data).find(&quot;[nodeName='z:row']&quot;).each(function(idx, itemData){
			var fieldValObj = {}
			$.each(argObj.viewFields,function(i,field){
				var value = $(itemData).attr('ows_' + field);
				if(value == undefined) value = null;
				fieldValObj[field]=value;
			});
			result.items.push(fieldValObj);
		});
	});
	return result;
}

function spjs_wrapQueryContent(paramObj){
	var result = [];
	result.push('&lt;GetListItems xmlns=&quot;http://schemas.microsoft.com/sharepoint/soap/&quot;&gt;');
	result.push('&lt;listName&gt;' + paramObj.listName + '&lt;/listName&gt;');
	if(paramObj.viewName!=undefined &amp;&amp; paramObj.viewName!=''){
		result.push('&lt;viewName&gt;' + paramObj.viewName + '&lt;/viewName&gt;');
	}
	if(paramObj.query != null &amp;&amp; paramObj.query != ''){
		result.push('&lt;query&gt;&lt;Query xmlns=&quot;&quot;&gt;');
		result.push(paramObj.query);
		result.push('&lt;/Query&gt;&lt;/query&gt;');
	}
	if(paramObj.viewFields!=undefined &amp;&amp; paramObj.viewFields!='' &amp;&amp; paramObj.viewFields.length &gt; 0){
		result.push('&lt;viewFields&gt;&lt;ViewFields xmlns=&quot;&quot;&gt;');
		$.each(paramObj.viewFields, function(idx, field){
			result.push('&lt;FieldRef Name=&quot;' + field + '&quot;/&gt;');
		});
		result.push('&lt;/ViewFields&gt;&lt;/viewFields&gt;');
	}
	// A view overrides the itemlimit
	if(paramObj.viewName==undefined){
		if(paramObj.rowLimit != undefined &amp;&amp; paramObj.rowLimit &gt; 0){
			result.push('&lt;rowLimit&gt;' + paramObj.rowLimit + '&lt;/rowLimit&gt;');
		}else{
		    result.push('&lt;rowLimit&gt;100000&lt;/rowLimit&gt;');
		}
	}
	result.push('&lt;queryOptions&gt;&lt;QueryOptions xmlns=&quot;&quot;&gt;&lt;IncludeMandatoryColumns&gt;FALSE&lt;/IncludeMandatoryColumns&gt;');
	if(paramObj.pagingInfo != undefined &amp;&amp; paramObj.pagingInfo != null &amp;&amp; paramObj.pagingInfo != '')
		result.push('&lt;Paging ListItemCollectionPositionNext=&quot;' + paramObj.pagingInfo.replace(/&amp;/g, '&amp;amp;') + '&quot; /&gt;');
	result.push('&lt;/QueryOptions&gt;&lt;/queryOptions&gt;');
	result.push('&lt;/GetListItems&gt;');
	return result.join('');
}

function spjs_wrapSoapRequest(webserviceUrl,requestHeader,soapBody,successFunc){
	var xmlWrap = [];
		xmlWrap.push(&quot;&lt;?xml version='1.0' encoding='utf-8'?&gt;&quot;);
		xmlWrap.push(&quot;&lt;soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'&gt;&quot;);
		xmlWrap.push(&quot;&lt;soap:Body&gt;&quot;);
		xmlWrap.push(soapBody);
		xmlWrap.push(&quot;&lt;/soap:Body&gt;&quot;);
		xmlWrap.push(&quot;&lt;/soap:Envelope&gt;&quot;);
		xmlWrap = xmlWrap.join('');
	$.ajax({
		async:false,
		type:&quot;POST&quot;,
		url:webserviceUrl,
		contentType:&quot;text/xml; charset=utf-8&quot;,
		processData:false,
		data:xmlWrap,
		dataType:&quot;xml&quot;,
		beforeSend:function(xhr){
			xhr.setRequestHeader('SOAPAction',requestHeader);
		},
		success:successFunc,
		error:function(xhr){
			alert(xhr.status+&quot;n&quot;+xhr.responseText);
		}
	});
}
&lt;/script&gt; 

The parameters

calendarName: The list name or list GUID of the list/calendar.
fieldToReturn: The FieldInternalName of the field to return the value from.
startDateFIN: The FieldInternalName of the start date field.
endDateFIN: The FieldInternalName of the end date field.

The returnvalue

The returnvalue from call to the function “getOnCallInfo” is an array of objects. The object has three properties:
url = a link to the SharePoint user info for the user.
userId = the userId from SharePoints user profile.
name = the name stored in the field.

The property “url” and “userId” is for use with people pickers only.

Hope someone can make use of this.
Alexander

One thought on “Pull on-call information from calendar”

  1. Alexander, this is awesome and VERY helpful. I was wondering (given that I am a non-developer) how I would tweak it to pull from another custom colomn in the calendar that would display a contact number for that person. Any help is appreciated and thanks again!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.