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:
<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, '&amp;') + '" />'); 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>
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