Category Archives: SharePoint 2010

Accessing user profile information in SharePoint with javascript – updated version

I have previously posted a solution for pulling user information from the built-in user information list in SharePoint (found in WSS 3.0, MOSS 2007 and SharePoint 2010 both foundation and server). This is NOT the user profile found in MOSS 2007 and in SharePoint 2010 server. You find the solution here

This solution is an updated version of the script – and should hopefully work better with sites on a managed path.

Insert this code in a CEWP
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" src="/test/EMSKJS/spjs-utility.js"></script>
<script type="text/javascript">

var userInfoObj = getUserInfo_v2(_spUserId);
var name = userInfoObj.Title;
var email = userInfoObj.EMail;
</script>

I have incorporated the new “getUserInfo_v2” in the file “spjs-utility.js”.

The parameter “_spUserId” is provided by SharePoint and represents the current user’s userID.

The function takes one argument which can be a userID or a login name (domainlogin or appname:user). If you use domainuser as a string like this:

var userInfoObj = getUserInfo_v2("contoso\alexander");

Please note the extra “” as it must be escaped in javascript.

The variables “name” and “email” are examples, the full range of “out of the box fields” are:
ID, Name, Title, EMail, Department, JobTitle, Notes, Picture, IsSiteAdmin, Created, Author, Modified, Editor, SipAddress

Download

You can download the file “spjs-utility.js” here. Ensure you pick the version dated 18.09.2011, or newer.

Pull e-mail from people picker and write to a separate textfield – updated version

08.01.2012 I have published an updated version of this script here


I have previously posted a solution for pulling the e-mail address from a people picker and writing it to a separate field when saving the form. You find it here.

This one does the same, but i have updated the scripts and added an “onchange event” that triggers when you enter the name in the input field (and move focus away from the field), or uses the address book to pick a user.

This solution is designed for single selection people picker fields.

For Internet Explorer in SharePoint 2007, and for most browsers in SharePoint 2010, the email is pulled from the picker itself. For all other browsers than IE in SharePoint 2007, the email is retrieved by using a call to the built in “user list” in SharePoint using the function “getUserInfo_v2”. This function is part of “spjs-utility.js” and can be found below.

How to use

Insert a ContentEditorWebPart (CEWP) under the form in NewForm.aspx / EditForm.aspx by switching the page to edit mode. Read here on how to do it in SharePoint 2007. In SharePoint 2010 you can add the CEWP on the list ribbon menu “Form Web Parts”.

CEWP code for both SharePoint 2007 and 2010:

&lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot; src=&quot;/test/EMSKJS/PeoplePickerEmail/spjs-utility.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;

fields = init_fields_v2();
pullEmailFromPickerOnChange({from:'MyPeoplePicker',to:'Title'});

/******************************************************
		Do not change anything below this line
*******************************************************/
function pullEmailFromPickerOnChange(obj){
	var toFind, data;
	$(document).ready(function(){
		toFind = &quot;div.ms-inputuserfield&quot;;
		if(!browseris.ie5up &amp;&amp; typeof(_fV4UI)==='undefined'){
			toFind = &quot;textarea&quot;;
		}
		$(fields[obj.from]).find(toFind).bind('blur',function(){
			data = pullEmailFromPicker(obj.from,obj.to);
			if(data.EMail!==undefined){
				$(fields[obj.to]).find('input').val(data.EMail);
			}
		});
		$(fields[obj.from]).find('img:last').bind('click',function(){
			setTimeout(function(){
				data = pullEmailFromPicker(obj.from,obj.to);
				if(data.EMail!==undefined){			
					$(fields[obj.to]).find('input').val(data.EMail);
				}
			},500);
		});
	});
}

function pullEmailFromPicker(finFrom,finTo){
	var result,	isResolved, data, matchArrRaw, matchArr;
	result = {};
	$(fields[finFrom]).find('.ms-formbody').find(&quot;div[id='divEntityData']&quot;).each(function(){
		isResolved = ($(this).attr('isresolved').toLowerCase()=='true')?true:false;
		if(isResolved){	
			data = $(this).find('div:first').attr('data');
			matchArr = [];
			matchArrRaw = data.split(/&lt;[^&gt;]*&gt;/);
			$.each(matchArrRaw,function(i,val){
				if(val!==''){
					matchArr.push(val);
				}
			});
			if(matchArr.length&gt;1){
				$.each(matchArr,function(i,val){
					if(i%2===0){
						switch(val){
							case'SPUserID':
								val = 'ID';
							break;
							case 'Email':
								val = 'EMail';
							break;
							case 'DisplayName':
								val = 'Title';
							break;
						}
						result[val] = matchArr[i+1];
					}
				});
			}else{ // Non IE in SP2007
				result = getUserInfo_v2($(this).attr('description'));				
			}
		}	
	});
	return result;
}
&lt;/script&gt;

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 possible “bug” in the CEWP handling when editing a SP2010 page where the HTML is accumulated when editing the page.

This tip is also recommended for SharePoint 2007 users, but is not absolutely necessary.

Parameters

You call this function using an object literation like this
pullEmailFromPickerOnChange({from:’MyPeoplePicker’,to:’Title’});

The attribute “from” is the FieldInternalName of the people picker and “to” is the FieldInternalName of the field to write the email to.
Read here on how to find the FieldInternalName

Download

You find the file “spjs-utility.js” here, ensure you grab the version dated 18.09.2010 or newer.


Post any question below.

Alexander

Remove group label in grouped list view

17.09.2011 I have updated the script to support lookup columns and people pickers.


I got a request from Djamel asking for a solution for removing the group label (the field name) in a grouped list view.

Add this code to a Content Editor Web Part (CEWP) in the list view. Adding a CEWP in a SharePoint 2010 list view will unfortunately remove the list view selector.

SharePoint 2007

&lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
$(document).ready(function(){	
	$(&quot;table.ms-listviewtable td[class^='ms-gb']&quot;).each(function(){
		var aLen = $(this).find('a').length;
		switch(aLen){
			case 2:
				var newLabel = $(this).find(&quot;a:last&quot;)[0].nextSibling.nodeValue;
				$(this).find(&quot;a:last&quot;)[0].nextSibling.nodeValue = '';
				$(this).find(&quot;a:last&quot;).text(&quot; &quot;+newLabel.substring(3));
			break;
			case 3:
				var newLabel = $(this).find(&quot;a:last&quot;).text();				
				$(this).find(&quot;a:eq(1)&quot;)[0].nextSibling.nodeValue = '';
				$(this).find(&quot;a:eq(1)&quot;).text(newLabel+&quot; &quot;);
				$(this).find(&quot;a:eq(2)&quot;).remove();
			break;			
			case 4:
				var newLabel = $(this).find(&quot;a:eq(2)&quot;).text();
				$(this).find(&quot;a:eq(1)&quot;)[0].nextSibling.nodeValue = '';
				$(this).find(&quot;a:eq(1)&quot;).text(newLabel+&quot; &quot;);
				$(this).find(&quot;nobr:last&quot;).remove();
			break;
		}
	});	
});
&lt;/script&gt;

SharePoint 2010

&lt;script type=&quot;text/javascript&quot; src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
$(document).ready(function(){
	$(&quot;table.ms-listviewtable td[class^='ms-gb']&quot;).each(function(){
		var newLabel;
		var aLen = $(this).find('a').length;
			switch(aLen){
			case 1:
				newLabel = $(this).find(&quot;a:last&quot;)[0].nextSibling.nodeValue;
				$(this).find(&quot;a:last&quot;)[0].nextSibling.nodeValue = '';
				$(this).find(&quot;img:last&quot;)[0].nextSibling.nodeValue = &quot; &quot;+newLabel.substring(3);
			break;
			case 2:
				newLabel = $(this).find(&quot;a:last&quot;)[0].nextSibling.nodeValue;
				if(newLabel === null){		
					newLabel = $(this).find(&quot;a:last&quot;).text();				
					$(this).find(&quot;a:last&quot;).remove();
					$(this).find(&quot;a:first&quot;)[0].nextSibling.nodeValue = '';
					$(this).find(&quot;img:first&quot;)[0].nextSibling.nodeValue = &quot; &quot;+newLabel;
				}else{
					$(this).find(&quot;a:last&quot;)[0].nextSibling.nodeValue = '';
					$(this).find(&quot;a:last&quot;).text(&quot; &quot;+newLabel.substring(3));
				}
			break;
			case 3:
				newLabel = $(this).find(&quot;nobr:last&quot;).text();
				$(this).find(&quot;nobr:last&quot;).remove();
				$(this).find(&quot;a:eq(0)&quot;)[0].nextSibling.nodeValue = '';
				$(this).find(&quot;img:last&quot;)[0].nextSibling.nodeValue = &quot; &quot;+newLabel;
			break;			
		}
	});	
});
&lt;/script&gt;

Ask if anything is unclear.

Alexander

Charting for SharePoint using Google Visualization API: Update 16.08.2011

26.08.2011 v2.9.3.2 has been released with these changes:
Small fix to allow single quotes in chart options. This is necessary to handle “fontName” in IE – it has to be wrapped in double AND single quotes like this:

{color:&quot;red&quot;,fontName:&quot;'Arial'&quot;,fontSize:14}

24.08.2011 v2.9.3.1 has been released with these changes:

  • For non-site collection administrators on a managed path*, there were a bug with how the baseUrl of the site was found. This led to an error when retrieving the fields for the selected list.
  • For non-site collection administrators, the “Get CAML from selected view” did not work.
  • Due to incompatibility i have removed the possibility to filter the chart on a DateTime column when using “Consume filter from list view web part”.
  • In orgchart there were a blank box added to the top of the “tree”. This is now gone.
  • Better debug – see details below.
  • Added support for a selection handler for use when you want to click the chart and redirect to a filtered list view. See example below.

Managed path

When you are on the root site in your site collection, does the URL look like this:
http://www.contoso.com/default.aspx
or like this:
http://www.contoso.com/sites/mysite/default.aspx

The last one would indicate that you are on a managed path.

Debug
Add this to the CEWP code:

var chartDebug = true;

When you edit the chart, a debug panel will be added to the top of the screen. This might come in handy if you need my help to debug the setup.

Filter example
The script is setup with a “listener” that lets you add a function like this to your CEWP code:

function chartSelectionHandler(data,chart,chartID){
	var selection, item, x, y, z;
	selection = chart.getSelection();
	if(selection.length&gt;0){
		item = selection[0];
		if(item.row!==undefined){
			x = data.getFormattedValue(item.row, 0);
			if(item.column!==undefined){
				z =  data.getFormattedValue(item.row, item.column)
				y = data.getColumnLabel(item.column);
			}
		}
		if(x===undefined &amp;&amp; y===undefined &amp;&amp; z===undefined){
			return;
		}
		// Create your filter
		alert(&quot;These are the variables you can use for your filter:nnx = &quot;+x+&quot;ny = &quot;+y+&quot;nz = &quot;+z);
		if(confirm(&quot;Open filtered list?&quot;)){
			if(chartID==='MyChart1'){	
				window.open('/test/English/Charts/Lists/ByStatus/AllItems.aspx?FilterField1=Status&amp;FilterValue1='+x,'','width=1000');
			}else if(chartID==='MyChart2'){	
				window.open('/test/English/Charts/Lists/Coffee2/AllItems.aspx?FilterField1='+y+'&amp;FilterValue1='+z,'','width=1000');
			}
		}
	}	
}

This is meant as an example for those who want to experiment with this feature.



16.08.2011 I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.9.3. You find it in the download section below.

These are the changes in this release


Save as template:
I have added a new checkbox to the configuration: “Use display name for list and view”. This one will replace the GUID with the display name for the list and view in the chart configuration. By doing this, the chart will “survive” if the site is saved as a template end restored in another location.

Use this option only if you plan to use the site as a template.

If you have a setup with multiple languages and you have translated the list title, the chart will work only for the language used when the template was saved. You can however revert back to using the GUID as soon as the site has been created from the template by removing the check in “Use display name for list and view” and saving the configuration.

Chart options:
Fixed a bugs where some options starting with 0 – like region for charts – were parsed as a number thus removing the leading 0.

Minor bugfixes:
I have done a few small bugfixes that are to small to mention.


Other articles in this series:


Download

You find the code here

If you use the solution, please consider donating a few dollars.

Regards
Alexander

CommentBox for SharePoint

13.08.2011 v1.2 released with these changes to the argObj:

  • paging: Use this in conjunction with the next parameter to set the number of top level comments to display initially.
  • topLevelCommentsLimit: The number of top level comments to display initially.
  • showMoreCommentsText: If there are more comments, this text is used for the link to show the rest of the comments.
  • sortAscending: true or false. Used to control the order of the comments.
  • commentFormVisible: true or false. Used to have the comment box expanded or collapsed initially.

There are changes to these files:

  • CommentBox.js
  • CommentBox.css

And the CEWP-code. See the CEWP-code section below for details.


17.07.2011 v1.1 released with these changes:

  • Anonymous users can read comments, but must sign in to post.
  • Added new parameter “loadLiveProfile” to the CEWP argObj. This defines whether or not to load the profile image and email from the user profile, or to use the values stored in the comment. This will automatically be set to false for anonymous users. To accompany this change i have added some new fields to the configuration list (these will be automatically created when submitting the first comment), and changed the parameters “newCommentText” and “replyText” from string to array.

There are changes to these files:

  • CommentBox.js
  • spjs-webservices.js

And to the CEWP-code.


I got this request from Brett:

Hi Alexander,

Got an awesome request for your mad skills.
Are you able to provide a Javascript/JQuery code for a Comments Box Web Part? So it can be added to any page, list or library?

I’m thinking you could set it like the Poll web part, where the Admin can specify the ID Name for each Comments Web Part instance, which will allow the user comments be allocated to the correct web part.

So it would function exactly like the Comments Web Part in a Blog Site, using the authenticated user credentials and/or anonymous comments if you desire.

There are a few Posts mentioning DVWP’s and SP Designer but I’m hoping to use a Javascript based solution.

I’ve looked everywhere for this function and closest I found was this Post.
http://devgrow.com/simple-threaded-comments-with-jcollapsible/

Many thanks for your time and efforts,

Brett


I thought this would be a useful solution for many users – myself included. It’s not very complicated as all comments are stored in a single list – identified by the pages relative URL.

Of course – as i started building – i incorporated this and that, an all of a sudden i had myself a full featured, multi level comment solution.

Sample thread:

IMG

New comment – plain text:

IMG

New comment – Rich text:

IMG

List of features

  • Supports both SharePoint 2007 and SharePoint 2010
  • Tested in IE7, IE9, Safari 5.0.5 for Windows, Firefox 5.0 and Google Chrome 12.0.742.112
  • Multi level comments
  • You can collapse top level threads
  • Rich text or plain text comments
  • Option to allow the authors to edit their comments
  • Option to let specific users act as “moderators”
  • Can be used in multiple locations within the site as each instance is identified by the relative URL of the page
  • Simple “drop in CEWP-setup”
  • On list holds all comments for all instances within the site
  • This list that holds the comments is automatically created
  • Layout customizable trough separate css-file
  • And more…

How to use

This solution is designed to be put in a CEWP where you want to use it. You will have to download all the files from the download section to a local folder/library where all users have read access, and change the <script src=”… in the CEWP code to reflect your local path.

CEWP code example

<div id="myCommentBox"></div>
<link rel="stylesheet" href="/test/EMSKJS/CommentBox/CommentBox.css">
<script type="text/javascript" src="/test/EMSKJS/CommentBox/jquery-1.6.2.min.js"></script>
<script type="text/javascript" src="/test/EMSKJS/CommentBox/CommentBox.js"></script>
<script type="text/javascript" src="/test/EMSKJS/CommentBox/spjs-webservices.js"></script>
<script type="text/javascript" src="/test/EMSKJS/CommentBox/tiny_mce/tiny_mce.js"></script>
<script type="text/javascript">

var pageID = location.pathname+"?ID="+GetUrlKeyValue('ID');
var argObj = {pageID:pageID,
			 containerID:'myCommentBox',
			 containerWidth:600,
			 replyLevels:2,
			 threadInitiallyCollapsed:false,
			 commentIndent:40,			 
			 commentBoxHeight:100,
			 showProfileImage:true,
			 createdByPrefix:'Posted by ',
			 createdPrefix:' on ',
			 modifiedPrefix:'- Modified: ',
			 modifiedByPrefix:' by ',
			 showUserEmail:true,
			 authorCanEdit:true,
			 editText:'Edit',			 
			 commentBoxRTE:false,
			 expandThread:"<span title='Expand thread'><img style='vertical-align:text-bottom' src='/_layouts/images/tpmax2.gif'  border='0'> Expand</span>",
			 collapseThread:"<span title='Collapse thread'><img style='vertical-align:text-bottom' src='/_layouts/images/tpmin2.gif' border='0'> Collapse</span>",
			 newCommentText:['Leave comment','You must sign in to comment'],
			 replyText:['Reply','<span title="You must be signed in to leave replies">Sign in</span>'],			 
			 submitText:'Submit comment',
			 canceImgHover:"Cancel",
			 deleteThreadText:'Delete comment',
			 moderateText:'Moderate',
			 moderatorID:['15','27'],
			 loadLiveProfile:true,
		 	 paging:false,
			 topLevelCommentsLimit:25,
			 showMoreCommentsText:"More comments",
			 sortAscending:true,
			 commentFormVisible:true
			 };

init_getComments(argObj);
</script>

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.

This tip is also recommended for SharePoint 2007 users, but is not absolutely necessary.

Argument object parameters

  • pageID: The identifier for the unique instance – see separate description below.
  • containerID: The container where the comments are inserted – see separate description below.
  • containerWidth: Width in pixels.
  • replyLevels: The number of nested levels.
  • threadInitiallyCollapsed: true or false to indicate whether the threads are initially collapsed.
  • commentIndent: The number of pixels to indent each nested level of comments.
  • commentBoxHeight: The textbox (for new comments or reply) height in pixels.
  • showProfileImage: true or false to indicate whether or not to display the profile image from the SharePoint user profile.
  • createdByPrefix: The text before the user name of the author.
  • createdPrefix: The text between the author name and the date/time for when the comment is created.
  • modifiedPrefix: The text before the date/time for when the comment is modified.
  • modifiedByPrefix: The text before the name of the editor.
  • showUserEmail: true or false to indicate whether to show the email for the author.
  • authorCanEdit: true or false to indicate whether the author can edit his or hers own comments.
  • editText: The text on the “Edit item link”.
  • commentBoxRTE: true or false to indicate whether to use the TinyMCE rich text editor – see separate description below.
  • expandThread: The text/image of the “Expand thread” link.
  • collapseThread: The text/image of the “Collapse thread” link.
  • newCommentText: Array with the text on the “Leave comment” link for both authenticated and unauthenticated users.
  • replyText: Array with the text on the “Reply” link for both authenticated and unauthenticated users.
  • submitText: The text on the “Submit comment button”.
  • canceImgHover: The mouseover text on the “Cancel comment image”
  • deleteThreadText: The text on the “Delete comment” link.
  • moderateText: The text on the “Moderate item link”.
  • moderatorID: An array of the user ID’s (as string) for the moderators.
  • loadLiveProfile: true or false to indicate whether to load the profile image and email from the user profile, or to use the values stored in the comment.

New in v1.2

  • paging: Use this in conjunction with the next parameter to set the number of top level comments to display initially.
  • topLevelCommentsLimit: The number of top level comments to display initially.
  • showMoreCommentsText: If there are more comments, this text is used for the link to show the rest of the comments.
  • sortAscending: true or false. Used to control the order of the comments.
  • commentFormVisible: true or false. Used to have the comment box expanded or collapsed initially.

Details on “pageID”
For ordinary aspx-pages, use

location.pathname

When using in DispForm/EditForm, use

location.pathname+"?ID="+GetUrlKeyValue('ID')

Details on “containerID”

You can supply a placeholder in the page with the ID corresponding with the parameter “containerID” from the function call. You will want to do this to control the placement of the container, or to supply some custom settings (style or class). If you do not supply a container, it will be created automatically by the script.

Details on “commentBoxRTE”

TinyMCE – Javascript WYSIWYG Editor is a platform independent web based Javascript HTML WYSIWYG editor control released as Open Source under LGPL by Moxiecode Systems AB.

You can change the buttons and plugins for the TinyMCE by modifying the function “init_MCE” at the bottom of the file “CommentBox.js”. Refer the official site for details.

SharePoint 2010 users:
The TinyMCE solution has to be modified to change all “htm” files (and the references in the scripts) to “aspx” as the built in security feature in SharePoint 2010 does not let you open htm-files unless you change the “Browser file handling” from “Strict” to “Permissive”.

I have modified the files used in the default setup for the CommentBox solution. If you change the setup to include more plugins you must change the appropriate files. The modified package can be found in the download section.

Download

You find the files here

Copyright and disclaimer


Please let me know if you find any bugs.
Alexander

If you use this solution, please consider donating a few dollars to keep me motivated.

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

Interactive Charts using Google Visualization API: Examples

This post describes the use of the tool posted here. The latest changes are found here.


This post will be updated with examples on how to utilize this tool to build various charts. I will add examples upon request. If you have a smashing chart or a special trick, please send it to me and i will post the images here.


Combo chart

The chart:
IMG

The list:
IMG

The configuration:
IMG

The reason for using “chartArea.left:50” is to align the chart left so that the legend is not truncated.

NOTE: The “Column index” used when setting up the series is one notch down from the index shown to the left of the columns. This has to do with the series only applying to the numeric columns.


How to put multiple charts in one page

Here is a few screenshots showing how to put multiple charts in one page.
IMG

IMG

IMG

You must ensure that the CEWP holding the code is placed below all containers.


Motion chart

The chart:
IMG

The list:
IMG

The configuration:
IMG


URL filter

This filter is applied in the “Custom CAML” section like this:
The configuration:
IMG
Use the FieldInternalName of your field – my field is named “Status”.

The URL:
IMG

The chart:
IMG

Manual filter setup filter

This is used to filter on “Single line of text” columns. This filter is applied in the “Custom CAML” section like this:
The configuration:
IMG
To get the CAML, select the checkbox “Create a filter field above the chart using”, select “manual filter setup” and then click “Build CAML from selected filters”. You need to know the FieldInternalName of the field and insert it in the placeholder in the CAML.

The list:
IMG

The chart:
IMG

Grouped by choice column value

I got a request from Michael:

Hello,
Im trying to chart a Calendar, i created a Column called “Category” (its a drop-down box with the categories)
Now i want to create a chart that:
– is a bar chart
– the h axis is “created by”
– the v axis is a the sum of a column that calculate the hours between end and starttime of events (allready solved this with a formular)
– the Bars are grouped by the Creator
– the chart is stacked (allready done)
– the stacked bars show the name of the category
– for each category a specified colour

Here are some screenshots of the list fields

The “Time” field calculates the difference between start and end:
IMG

The Category field is a plain choice field:
IMG

The series in the chart are made up by one calculated column per choice in the “Category” field:
IMG
Make one for each category.

The configuration:
IMG

The chart:
IMG


Please make requests, and i will post examples to the best of my knowledge.

Alexander

Charting for SharePoint using Google Visualization API: Update 05.06.2011


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.9.0. The changes are described here, but the background story and the code is found in original article. This post describes the new features in v2.9.0.

You should read the original post found here, the update for v2.8 posted here and the update for v2.8.5 posted here.


Changes from v2.8.5:

  1. Changed the behavior of “Average” to divide the grand total on the number of columns actually containing a value. This means any columns being blank or “null” will not be considered into the “total column count”. Thanks to Ayodele Ayodeji for pointing this out to me.
  2. Changed the “Chart option parser” to use eval on the Chart Options. YES i know it’s not the smartest thing to do, but as the Google Visualization API changes, my crude “option parser” failed to swallow some of the object literation formatted options.
  3. Added new chart types: Combo Chart and Candlestick Chart
  4. Added support for PatternFormat
  5. Pulled the variables: “showFilterInfo”, “showItemCount”, “itemCountPrefix”, “itemCountSuffix”, “labelIfEmpty”, introduced in v.2.8.5 in from the CEWP code and into the Chart configuration. This update should override the CEWP settings and these obsolete settings should not interfere.
  6. Added a few minor GUI tweaks like auto open the configuration for a newly added chart – no more “Table has no columns” error. Changed some of the error message. And a few other small fixes.
  7. Changed the solution to check for any missing columns in the configuration list and add them as needed. No more need to manually add columns to the configuration list when updating from a previous version!
  8. I have changed the code for the “save configuration function” as jQuery v1.6 introduced a new approach on DOM attributes and properties which affected the value returned by .attr(“checked”). This made saving “checked” checkboxes impossible when using jQuery v1.6.x.

NOTE: I have done one change in the way the formatters are targeted to a column. I originally went for a numbering of the columns starting with 1, but changed this to use 0 for the first column. This has to do with the columns being 0-indexed in the DataTable “behind the scenes” and this making things easier. To those of you using formatters now, you might end up with an error message, but it is just a matter of notching the “index” down by one.

If you use this solution, please consider donating a few dollars.