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:
[javascript]
<div id="insertOnCallNameHere"></div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js"></script>
<script type="text/javascript">
// Define list name or GUID
var calendarName = "OnCallCalendar";
// 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 "fieldToReturn" is a people picker.
buffer.push("<div>"+obj.url+"</div>");
});
$("#insertOnCallNameHere").html(buffer.join(”));
// ****************************************************************
// Do not edit below this line
// ****************************************************************
function getOnCallInfo(listName,fieldNameToReturn,startDateFIN,endDateFIN){
var result = [];
var queryBuffer = [];
queryBuffer.push("<Where>");
queryBuffer.push("<And>");
queryBuffer.push("<Leq><FieldRef Name=’"+startDateFIN+"’ /><Value Type=’DateTime’><Today /></Value></Leq>");
queryBuffer.push("<Geq><FieldRef Name=’"+endDateFIN+"’ /><Value Type=’DateTime’><Today /></Value></Geq>");
queryBuffer.push("</And>");
queryBuffer.push("</Where>");
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&&!isNaN(parseInt(split[0],10))){
result.push({url:"<a href=’"+L_Menu_BaseUrl+"/_layouts/userdisp.aspx?Force=True&ID="+userId+"’ target=’_blank’>"+name+"</a>",
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 && argObj.viewName==undefined)){
alert("Missing parameters!nnYou must provide a minimum of "listName", "query" or "viewName" and "viewFields".");
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("[nodeName=’rs:data’]").attr(‘ItemCount’);
result.nextPagingInfo = $(data).find("[nodeName=’rs:data’]").attr(‘ListItemCollectionPositionNext’);
$(data).find("[nodeName=’z:row’]").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(‘<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">’);
result.push(‘<listName>’ + paramObj.listName + ‘</listName>’);
if(paramObj.viewName!=undefined && paramObj.viewName!=”){
result.push(‘<viewName>’ + paramObj.viewName + ‘</viewName>’);
}
if(paramObj.query != null && paramObj.query != ”){
result.push(‘<query><Query xmlns="">’);
result.push(paramObj.query);
result.push(‘</Query></query>’);
}
if(paramObj.viewFields!=undefined && paramObj.viewFields!=” && paramObj.viewFields.length > 0){
result.push(‘<viewFields><ViewFields xmlns="">’);
$.each(paramObj.viewFields, function(idx, field){
result.push(‘<FieldRef Name="’ + field + ‘"/>’);
});
result.push(‘</ViewFields></viewFields>’);
}
// A view overrides the itemlimit
if(paramObj.viewName==undefined){
if(paramObj.rowLimit != undefined && paramObj.rowLimit > 0){
result.push(‘<rowLimit>’ + paramObj.rowLimit + ‘</rowLimit>’);
}else{
result.push(‘<rowLimit>100000</rowLimit>’);
}
}
result.push(‘<queryOptions><QueryOptions xmlns=""><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>’);
if(paramObj.pagingInfo != undefined && paramObj.pagingInfo != null && paramObj.pagingInfo != ”)
result.push(‘<Paging ListItemCollectionPositionNext="’ + paramObj.pagingInfo.replace(/&/g, ‘&’) + ‘" />’);
result.push(‘</QueryOptions></queryOptions>’);
result.push(‘</GetListItems>’);
return result.join(”);
}
function spjs_wrapSoapRequest(webserviceUrl,requestHeader,soapBody,successFunc){
var xmlWrap = [];
xmlWrap.push("<?xml version=’1.0′ encoding=’utf-8′?>");
xmlWrap.push("<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/’>");
xmlWrap.push("<soap:Body>");
xmlWrap.push(soapBody);
xmlWrap.push("</soap:Body>");
xmlWrap.push("</soap:Envelope>");
xmlWrap = xmlWrap.join(”);
$.ajax({
async:false,
type:"POST",
url:webserviceUrl,
contentType:"text/xml; charset=utf-8",
processData:false,
data:xmlWrap,
dataType:"xml",
beforeSend:function(xhr){
xhr.setRequestHeader(‘SOAPAction’,requestHeader);
},
success:successFunc,
error:function(xhr){
alert(xhr.status+"n"+xhr.responseText);
}
});
}
</script>
[/javascript]
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