All posts by Alexander Bautz

Filter a calendar view based on user profile property

I got this request from Carel Schotborgh:

Using WSS 3.0

Not have been able to find it anywhere so, since it is possible to filter a list items (in all event/task/etc view) based on membership in SharePoint group (By Alexander). I would like to request the possibility to filter an Agenda View on a single line text, based on a field of the current user information field (for instance department). I import data into SharePoint list what contains names of people. So users do not create the list items and there are multiple columns with names like Judge1 Judge2 Lawyer etc. As you all know you can’t use [ME] value in a single line text. This can only be used with columns: Created by, Modified by, Assigned to and People picking data. Problem with agenda view is also that it only displays 1 field (in general description of appointment for instance).

What do I want?
Filter (a) specific column(s) on the basis of the current user that is logged on (department field for example) in an agenda view.

The agenda should only show items that match the same text as entered in the department field of the user information field of the user that is currently logged on.

In my case IF Judge1 = Mr. J. Jenkins display it OR IF Judge2 = Mr. J. Jenkins display it. The OR statement is very important since SharePoint also filters with AND statement. So I would like to filter multiple columns with department field (any other also ok, as long I can fill in a value that is connected to the current user).

I already managed to create something that filters all event/tasks/etc list view, searching all the columns based upon the current user department field with or statement idea, but not specific columns. Also in agenda view my code and Alexanders code will not work.

If it is not possible to do this on each specific column it is also ok to search all of the columns since the names in my list are unique for each column. Other columns do not contain their names in such a way anyway. I’m already using my script for the not agenda view and works perfectly except user with slow internet will see the entire list my filter starts working.

Excuse me for long text, but it is complex. Much appreciated any help, suggestion or solution.


Here is one suggestion on how this can be done. It pulls the “Department” from the user profile and searches for a match in one of the fields specified in the script (in the variable “arrOfFieldInternalNames”).

This solution does:

  • Filters a calendar view based on a text retrieved from the current users profile (from People and Groups)
  • Filters with an “OR” statement looking for a match in multiple text fields
  • Handles “single day items” and items spanning over multiple days.
  • Hide the calendar view until it has been filtered

This solution doesn’t:

  • If multiple elements are located in one day, the standard calendar view will display a “x more items” link to expand all items that day. My filter solution will filter the items, but may display a link saying there are “3 more items”, but when clicking the link there may no items to display.

The solution
To keep a nice even background when removing items spanning over multiple days, i had to change the background for the “not in this month items” to white.

Add this code to a CEWP below the calendar view:

<style type="text/css">
.ms-cal-nodataMid,.ms-cal-nodataBtm2{
	background-color:#FFFFFF;
}
</style>
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript">

// Set variables
listGuid = 'FilterCalendarView'; // Use list Name or List Guid
listBaseUrl = L_Menu_BaseUrl;
userListGuid = "{570D772F-0EAB-45A8-8C54-9CCD4EC6A0AF}"; // The "People and Groups" list
userListBaseUrl = ''; // Modify if on a managed path
// The FieldInternalNames to search for information matching the selected user property
arrOfFieldInternalNames = ['Judge1','Judge2'];

// Hide calendar view until it is filtered
$(".ms-cal-gempty").hide();

// Get the UserInfo
var userInfoObj = getUserInfo();
var dep = userInfoObj.Department; // User profile property "Department"

// Find all "relevant" items
var myItems = getMyItemsID(dep,arrOfFieldInternalNames);
// Filter view
filterCalendarView(myItems);

function getMyItemsID(find,findInArr){
	wsBaseUrl = listBaseUrl + '/_vti_bin/';
	var query = "";
	$.each(findInArr,function(i,fin){
		query += "<Eq><FieldRef Name='"+fin+"' /><Value Type='Text'>"+find+"</Value></Eq>";
		if(i>0)query = "<Or>" + query + "</Or>";
	});
	query = "<Where>" + query + "</Where>";
	var arrToReturn = [];
	res = queryItems(listGuid,query,['ID']);
	$.each(res.items,function(i,item){
		arrToReturn.push(item.ID);
	});
	return arrToReturn;
}

function filterCalendarView(arrOfIDs){
	$(".ms-cal-gempty a[href*='DispForm.aspx?ID=']").each(function(){
		var currID = $(this).attr('href').match(/ID=(d+)/);
		if($.inArray(currID[1],arrOfIDs)==-1){
			// remove
			var thisParentTd = $(this).parents('table:first').parents('td:first');
			var colspan = thisParentTd.attr('colspan');
			if(colspan>1){		
				for(i=1;i<colspan;i++){				
					thisParentTd.after("<td class='ms-cal-workitem'> </td>");
				}
				thisParentTd.replaceWith("<td class='ms-cal-workitem'> </td>");
			}else{
				thisParentTd.html(' ');			
			}		
		}
	});
// Show calendar view after it has been filtered
$(".ms-cal-gempty").show();
}

function getUserInfo(UserId){
wsBaseUrl = userListBaseUrl + '/_vti_bin/';
var uiObj = {};

if(typeof(UserId)=="undefined" || UserId=='')UserId = _spUserId;

var arrOfFields = ['ID', 'Name', 'Title', 'EMail', 'Department', 'JobTitle', 'Notes', 'Picture',
'IsSiteAdmin', 'Created', 'Author', 'Modified', 'Editor', 'SipAddress', 'Deleted'];

var item = getItemById(userListGuid,UserId,arrOfFields);
    if(item != null){
	    for(i=0;i<arrOfFields.length;i++){
	    	if(item[arrOfFields[i]]!=null){
	    		uiObj[arrOfFields[i]] = item[arrOfFields[i]];
	    	}else{
	    		uiObj[arrOfFields[i]] = '';
	    	}
	    }
       	return uiObj;
    }else{
        for(i=0;i<arrOfFields.length;i++){
    		uiObj[arrOfFields[i]] = "User with id " + UserId + " not found.";
    	}
		return uiObj;
	}
}	
</script>

Modify the parameters:

  • listGuid: The list name or list Guid of the list to filter.
  • listBaseUrl: The base URL of the list to filter.
  • userListGuid: The list Guid of the user list (People and Groups).
  • userListBaseUrl: The user list base URL – Most likely an empty string “”. If the site resides on a manage path this must be reflected.
  • arrOfFieldInternalNames: Array of the FieldInternalNames to search for a match on the search string-

The jQuery-library is found here. The sourcecode refers to jquery-1.3.2.min.js. If you use another version, please update the reference in the code.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

Read here how to add a CEWP to NewForm or EditForm, and how to get the guid for a list.

I do not think this is a “production environment solution” at this stage, but please test it and post back any comments or suggestions and i will try to adapt the script.

Regards
Alexander

Revised function init_fields()

19.08.2011 Updated the function to support new versions of Firefox.


I got a tip from Alexey Krasheninnikov regarding using regex rather then “indexOf” and “substring” in the function “init_fields()”. It is both quicker and cleaner.

This function is used in all my scripts that works with NewForm, DispForm or EditForm. It only works in non-modified forms.

Here is a revised version of the function. The regex is supplied by Alexey Krasheninnikov, my regex knowledge is not quite there, but I’m learning.

<script type="text/javascript" src="../../Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript">
fields = init_fields();

function init_fields(){
	var res = {};
	$("td.ms-formbody").each(function(){
	var myMatch = $(this).html().match(/FieldName="(.+)"s+FieldInternalName="(.+)"s+FieldType="(.+)"s+/);	
		if(myMatch!=null){
			// Display name
			var disp = myMatch[1];
			// FieldInternalName
			var fin = myMatch[2];
			// FieldType
			var type = myMatch[3];
			if(type=='SPFieldNote'){
				if($(this).find('script').length>0){
					type=type+"_HTML";
				}
			}
			if(type=='SPFieldLookup'){
				if($(this).find('input').length>0){
					type=type+"_Input";
				}
			}
			// Build object
			res[fin] = this.parentNode;
			$(res[fin]).attr('FieldDispName',disp);
			$(res[fin]).attr('FieldType',type);
		}		
	});
	return res;
}
</script>

To address a field you use the same method as before:

// Get the table row containing the "Title field"
var titleFieldTR = $(fields['Title']);

I have added the fields “DisplayName” and the “FieldType” as attributes as it might come in handy:

// Alert the DisplayName for the "Title field"
alert($(fields['Title']).attr('FieldDispName'));
// Alert the FieldType for the "Title field"
alert($(fields['Title']).attr('FieldType'));

Regards
Alexander

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

18.09.2011 I have posted a new solution to this “problem”. I recommend you use the new one. You find it here


I got this request from Indra:

Hi Alex,

This is really great blog!! awesome work and i am big fan of this site, i check everyday and look at the things and learn , but i need something that i cannot find any where. Let me explain what i need..

I have a list with a people picker and a field called email address, i want to get the email address populated automatically depending the person on people picker ( single user). I know that i need to get user information from profile, but i am not a programmer and there i need your help.

I know you are really busy but please spotlight when you have time.

Thanks in Advance.

This code fills the e-mail address when the item is saved. It pulls the email address from a “resolved” user in a people picker, and writes it to a separate text field.

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.4.min. If you use another version, please update the reference in the code.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

Read here how to add a CEWP to NewForm or EditForm, and how to get the guid for a list.

Add this code to a CEWP below the NewForm or EditForm:

<script type="text/javascript" src="/test/English/Javascript/jquery-1.4.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript">
fields = init_fields();

userListGuid = '570D772F-0EAB-45A8-8C54-9CCD4EC6A0AF';
userListBaseUrl = '';
myPeoplePickerFin = 'MyPeoplePicker';
myTextFieldFin = 'MyTextField';

function PreSaveAction(){
	var myPeoplePicker = $(fields[myPeoplePickerFin]).find('.ms-formbody').find("div[id='divEntityData']");
	if(myPeoplePicker.length>0){
		var isResolved = (myPeoplePicker.attr('isresolved').toLowerCase()=='true')?true:false;
		if(isResolved){	
			var login = myPeoplePicker.attr('description');	
			var uiObj = getUserInfoFromLogin(login);
			$(fields[myTextFieldFin]).find('input').val(uiObj.EMail);	
		}
	}
	return true;
}

function getUserInfoFromLogin(UserLogin){ //
wsBaseUrl = userListBaseUrl + '/_vti_bin/'; 
var retObj = {};
var arrOfFields = ['ID','Name','Title','EMail','Department','JobTitle','Notes','Picture','IsSiteAdmin',
				   'Created','Author','Modified','Editor','SipAddress','Deleted'];
				   
var query = "<Where><Eq><FieldRef Name='Name' /><Value Type='Text'>" + UserLogin + "</Value></Eq></Where>";
var res = queryItems(userListGuid,query,arrOfFields,1); 
    if(res.count>0){ 
	    for(i=0;i<arrOfFields.length;i++){
	    	retObj[arrOfFields[i]] = res.items[0][arrOfFields[i]];    
	    }
       	return retObj; 	
    }else{
        for(i=0;i<arrOfFields.length;i++){
    		retObj[arrOfFields[i]] = "User with id " + UserLogin + " not found.";    
    	}
		return retObj; 
	}
}

function init_fields(){
var res = {};
$("td.ms-formbody").each(function(){
if($(this).html().indexOf('FieldInternalName="')<0) return;
var start = $(this).html().indexOf('FieldInternalName="')+19;
var stopp = $(this).html().indexOf('FieldType="')-7;
var nm = $(this).html().substring(start,stopp);
res[nm] = this.parentNode;
});
return res;
}
</script>

You must change these four parameters in the script:

  • userListGuid: The listGuid of the user list
  • userListBaseUrl: The base URL for the userList. Most likely an empty string “”, but If the site resides on a managed path, this must be reflected.
  • myPeoplePickerFin: FieldInternalName for the people picker
  • myTextFieldFin: FieldInternalName for the single line text field to hold the email address

Ask if anything is unclear.

Regards
Alexander

How to troubleshoot when the scripts does not work

In most cases where users have trouble getting my solutions to work, the problem is the script references. I will give a few examples here on how to troubleshoot these cases.

To copy the script from sourcecode provided in the article, hover over the script and click “view source”. Highlight, copy and paste in notepad. Save with the correct file extension like this:

  • Select “Save as” from the file menu and be sure to use the “.js” extention in the file name. Below the file name, you find a drop down where you can set the file type.
  • Select “All files”. If you don not do this the file will be saved as “fielname.js.txt”.

To check that the scripts are referred correctly, insert an alert in the CEWP like this:

<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript">
  // Check that jQuery is loaded:
  alert(typeof($));

  // Check that a specific function is loaded:
  alert(typeof(InsertTheFunctionNameHere));
</script>

Both these alerts should give a dialog box with the text “function”. If you get “undefined”, the script reference are wrong.

Another thing:
In most cases the CEWP must be placed below the list for or list view for the code to work.

Regards
Alexander

Edit SharePoint Field properties, including lookup column list association, with client side code only

20.10.2010 Updated code to add some more features: Edit list properties: “Hidden”, “ShowUser”, “Ordered”, “Direction”. Field properties: ShowInDIP (document information panel) and cross site lookup functionality for a standard SharePoint lookup column. The article is updated and describes the changes.


This code enables you to edit the following using javascript/jQuery only

List properties:

  • Hidden: Hide the list so that it does not appear on the Documents and Lists page, Quick Launch bar, Modify Site Content page, or Add Column page as an option for lookup fields.
  • ShowUser: Specify that names of users are shown in “Created by” and “Modified by” fields.
  • Ordered: Specify that the option to allow users to reorder items in the list is available on the Edit View page for the list.
  • Direction: LTR if the reading order is left-to-right, RTL if it is right-to-left.

Field properties:

  • Hidden: Specifies whether the field is displayed in the list.
  • ReadOnly: Specifies whether values in the field can be modified.
  • Required: Determines whether the field requires values.
  • Show In NewForm: Specifies whether the field is displayed in the form that is used to create list items.
  • Show In DispForm: Specifies whether the field is displayed in the form for displaying list items.
  • Show In EditForm: Specifies whether the field is displayed in the form that is used to edit list items.
  • Show In DIP: Specifies whether the field is displayed in the “Document Information Panel” in compatible Microsoft Office applications. Only available for document libraries.
  • Show In List Settings: Specifies whether the field is displayed in the page for customizing list settings.
  • Show In Version History: Specifies whether the field is displayed in the page for viewing list item versions.
  • Show In View Forms: Specifies whether the field is displayed in pages that are used to view list data.
  • Lookup column web and list association (cross site lookup): Change the web and list association for a lookup column – Note: If you do this, all existing elements in this column will be lost. The cross site option is only available for site collection administrators!

Images:

IMG


IMG


IMG


Disclaimer:
I have removed some list types and some column types, but be careful using this tool. Test this in a “test list” before you use it in your “production environment”. If you edit the wrong field, you could break the list. Use this tool at your own risk!

Create a WebPartPage, insert a CEWP, and paste the code.
You find the code here

Regards
Alexander

Autocomplete for SharePoint people picker

26.03.2010 Updated code to be able to clear the selection It’s only the code for the file “AutocompleteForPeoplePicker.js” that has changed. Thanks to Steve for pointing out the bug.

This solution adds auto complete functionality to a standard SharePoint people picker.

This solution is built with the autocomplete widget from jQuery UI. The values are pulled from the user list based on a query. All users with an email address are available.

Limitations:

  • Single choice only
  • No filter against user group, all users are displayed

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG

In addition to the above scripts, i have the jQuery UI 1.8 in a separate folder. See the CEWP code and point the links to your jQuery UI location.

The jQuery UI-library is found here. The pictures and the sourcecode refers to jquery-ui-1.8. The autocomplete widget is not found in previous releases.

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.4.min. The autocomplete widget is not supported in previous releases.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

The sourcecode for the file “AutocompleteForPeoplePicker.js” is found below.

Read here how to add a CEWP to the NewForm or EditForm, how to find the list Guid of your list, and how to find the FieldInternalName of your columns.

Add a CEWP below the list form in NewForm or EditForm, and insert this code:

<style type="text/css">
.ui-menu .ui-menu-item {
	font-size:xx-small;
}
</style>
<link type="text/css" href="/test/English/jQueryUI18/smoothness/jquery-ui-1.8.custom.css" rel="stylesheet" />
<script type="text/javascript" src="/test/English/Javascript/jquery-1.4.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript" src="/test/English/jQueryUI18/jquery-ui-1.8.custom.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/AutocompleteForPeoplePicker.js"></script>
<script type="text/javascript">
fields = init_fields();

// Find all users from userlist. Parameters "userListBaseUrl" and "userListGuid"
var allUsers = getUsers('','570D772F-0EAB-45A8-8C54-9CCD4EC6A0AF');

// Call with FieldInternalName of your people picker field(s)
$(document).ready(function(){
	peoplePickerAutoclomplete('MyPeoplePicker');
	peoplePickerAutoclomplete('AnotherPeoplePicker');	
});
</script>

Parameters explained:

  • userListBaseUrl: The base URL of the site the user list resides. If your site is in a managed path, it must reflect this managed path. If not, this parameter is most likely an empty string (“”)
  • userListGuid: The list Guid of the user list.

The sourcecode for the file “AutocompleteForPeoplePicker.js” looks like this:

/* Add autocomplete functionality to a SharePoint people picker
 * -----------------------------
 * Created by Alexander Bautz
 * alexander.bautz@gmail.com
 * https://spjsblog.com
 * v1.0
 * LastMod: 24.03.2010
 * -----------------------------
 * Must include reference to jQuery 1.4, jQuery UI 1.8 and to the folloving scripts:
 * ----------------------------------------------------
 * interaction.js - http://spjslib.codeplex.com/
 * stringBuffer.js - http://spjslib.codeplex.com/
 * ----------------------------------------------------
*/

function peoplePickerAutoclomplete(FieldInternalName){
if(typeof(fields)=='undefined')fields = init_fields();

var myPicker = $(fields[FieldInternalName]);
if(myPicker.find('div.ms-inputuserfield:visible').length>0){
	var ie=true; // Internet Explorer
	var toFind = 'div.ms-inputuserfield';
	var inputStyle = "margin:-1 0 -1 0;height:18px;font-family:Verdana,sans-serif;font-size:8pt;width:100%;display:none";

}else{
	var ie=false; // Firefox
	var toFind = 'textarea:first';
	myPicker.find('textarea:first').css({'height':'18px'});
	var inputStyle = "margin:1 0 1 0;height:18px;font-family:Verdana,sans-serif;font-size:8pt;width:100%;display:none";
}
	myPicker.find(toFind)
		.before("<input hiddenval='' style='"+inputStyle+"' id='"+FieldInternalName+"'/>")
		.focus(function(){
			$(this).hide().prev().show().focus();
		});

	// Add autocomplete
	$("#"+FieldInternalName).autocomplete({
			source: allUsers,
			select: function(event, ui) {
				fillPicker(ie,myPicker,FieldInternalName,ui.item.writeBackValue,ui.item.value);				
				return false;
			}		
		}).blur(function(){
			var currVal = $(this).val();
			var prevVal = $(this).attr('hiddenval')
			if(currVal!=''&&currVal!=prevVal){
				$(this).val($(this).attr('hiddenval'))
			}else if(currVal==''){
				fillPicker(ie,myPicker,FieldInternalName,'','');	
			}
			$(this).hide().next().show();
	});	
}

function fillPicker(ie,field,fin,loginName,dispName){
	if(ie){
		// IE
		field.find('.ms-inputuserfield').html(loginName);
		$("#"+fin).val(dispName).attr('hiddenval',dispName);
		field.find('img:first').click();
	}else{
		// FF
		field.find("textarea:first").val(loginName);
		$("#"+fin).val(dispName).attr('hiddenval',dispName);
	}
}

function getUsers(userListBaseUrl,userListGuid){
	var query = "<Where><And><IsNotNull><FieldRef Name='EMail' /></IsNotNull>" +
				"<Eq><FieldRef Name='ContentType' /><Value Type='Text'>Person</Value></Eq></And></Where>" +
				"<OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy>";
	wsBaseUrl = userListBaseUrl + '/_vti_bin/';	
	var res = queryItems(userListGuid,query,['ID','Title','Name','EMail','ContentType']); 
    var ret = [];
    $.each(res.items,function(idx,item){ 	
    	ret.push({label:item['Title']+"<br>"+item['EMail']+"<br>"+item['Name'],value:item['Title'],writeBackValue:item['Name'],desc:'Test'});	
    });
    return ret;    
}

function init_fields(){
var res = {};
$("td.ms-formbody").each(function(){
if($(this).html().indexOf('FieldInternalName="')<0) return;
var start = $(this).html().indexOf('FieldInternalName="')+19;
var stopp = $(this).html().indexOf('FieldType="')-7;
var nm = $(this).html().substring(start,stopp);
res[nm] = this.parentNode;
});
return res;
}

Save as “AutocompleteForPeoplePicker.js”, mind the file extension, and upload to the script library as shown above.

I will update the article on Edit date, single line text, number or boolean columns directly in list view based on this script to allow setting a people picker field directly in a list view.

Ask is anything is unclear.

Regards
Alexander

vLookup type rollup for SharePoint

02.03.2011 Updated the code for the file “vLookupForSharePoint.js” to v1.7.0

  • Add an “orderBy” option to sort the returned items by a FieldInternalName – See parameter descriptions below.
  • The dependencies upon interaction.js and stringBuffer.js has been eliminated.

10.06.2010 Updated the code for the file “vLookupForSharePoint.js” to fix some bugs.
This update includes:

  • Support for multiple webparts in one page
  • Bugfix: get listName in EditForm mode
  • Added option “DispFormRelURL” under “inQueryString” to provide current items relative DispForm URL
  • Handling multichoice values by replacing “;#” with “<br />”
  • Fixed bug with “newItemLink” if “linkBack” is not true
  • Added id tag for the “newItemLinkContainer” – to make hiding it possible.
  • Fixed bug when item has no “children” (newItemLink and inQueryString)
  • Fixed bug when matching on a calculated column

06.05.2010 Updated the code for the file “vLookupForSharePoint.js”.

This update includes:

  • Some small bugfixes
  • Added support for getting items connected by a multiLookup
  • Added createNewItem option by setting the parameter “newItemLink:true”
  • Added inQueryString option to include values from DispForm when creating new items using the “createNewItem” option
  • Added option to display vLookup connected items in EditForm
  • Localized for Norwegian, Swedish and English

01.04.2010 Small update to the file “vLookupForSharePoint.js”. I have modified the argument “hoverToView” to have separate settings for DispForm and for ListView. You must change the argument “hoverToView” to “hoverToViewDispForm” and “hoverToViewListView” in your calculated columns.


I have long thought of making a generic solution for pulling information from another list, kind of like you would do in Excel with the function vLookup. Here is my attempt to create just this. I’m fairly confident this solution will fill an empty space in many SharePointers hearts…

This solution features

  • Easy, generic interface to pull information from any list in current site or cross site
  • Concatenation of values from multiple items
  • Sum, average or count values from multiple items
  • Link back to filtered view of all matched items in “sum”, “avg” or “count” -mode, or to individual items in “concat” -mode
  • Direct link to documents
  • “Reverse lookup” on lookup columns – the “mother” item now has link back to the “children”.
  • And more…

The way this solution works is by using a calculated column to build a string with all the parameters like this:

This code matches the ID of the current item against a lookup column (the “child-list” has a lookup column named “ParentBug” targeting the “Mother-list” – what field the lookup is connected to is irrelevant as it is the ID we are looking for). This approach is a bit different than it will be for other columns as the ID is not accessible to a normal calculated column.

="listName:vLookupBugTrackerTasks|find:ID|findInCol:ParentBug|action:concat|linkBack:true|viewFields:Title#Title;TaskDescription#Task description;Deadline#Deadline"

This example matches the text in the “Title” column in one list against the “Title” column in another list.

="listName:vLookupBugTrackerTasks|find:"&Title&"|findInCol:Title|action:concat|linkBack:true|viewFields:Title#Title"

These are the available arguments:

  • listName: Name or GUID of the target list.
  • listBaseUrl: The base URL of the site the target list is located in. Defaults to current site is the argument is omitted.
  • find: The string to search for in the column specified as “findInCol” below. To use the current items ID (in case of a lookup connection), specify like this: “find:ID”.
  • findInCol: The FieldInternalName of the column to query against.
  • viewFields: “Array” of the fields of which to return a value for. Format: FieldInternalName1#DisplayName1. Separate multiple fields with semicolon.
  • action: “sum”, “avg”, “count” or “concat”.
  • sumOrAvgPrefix: A prefix in “sum” or “avg” -mode, like “$” for dollar.
  • sumOrAvgPostfix: As above, but postfix.
  • linkBack: true=Link back to item(s), false=no linkback. Default value: false
  • linkBackView: If in “sum”, “avg” or “count” -mode, set the target view name for the filtered result on “linkBack”. Defaults to “AllItems.aspx” if omitted.
  • hoverToViewListView: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in list view. Default value: false
  • hoverToViewDispForm: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in DispForm. Default value: false
  • hoverToViewEditForm: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in DispForm. Default value: false
  • newItemLink: (In “concat” -mode) true=Displays a “Create new item” link. Default value: false
  • inQueryString: (In “concat” -mode) semicolon separated array of FieldInternalNames to include the value from in the queryString that is passed to the NewForm. This only applies if the above parameter is set to true. Default value: “”. You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm
  • orderBy: New! A FieldInternalName to sort the returned items by. Set the parameter “orderBy” in the calculated column like this: orderBy:Title. To have the result ordered descending, append a hash behind the FieldInternalName like this: orderBy:Title#

These are the base arguments that are required:
‘listName’,’find’,’findInCol’,’action’,’viewFields’

Note: If your formula errors out, it most likely has to do with the string being to long. To overcome this problem, just concatenate the string like this:
…first part of the string her”&”and the rest here…


Example images:

List view with tasks pulled from tasklist
IMG

Hover over each item for “link back menu”. Click on a “link back” will take you to the item
IMG

hoverToViewListView:true – before hover:
IMG

hoverToViewListView:true – after hover:
IMG

“sum”, “avg” or “count” looks like this:
IMG

A click on a “link back” on items in “sum”,”avg” or “count” -mode will take you to a filtered list of all matched items.
IMG
The target list view is set in the parameter “linkBackView”.

DispForm with “newItemLink:true”:
IMG

The same item in EditForm:
IMG

When setting the parameter inQueryString like this “inQueryString:Title;ID”, the value from these fields are included in the querystring of the URL (click to enlarge the image):
IMG
You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm. Look here for an example.

When the code is added to the listView and to DispForm/EditForm, you create a new “enhanced lookup column” by creating a new field of type “Calculated (calculation based on other columns)” with a FieldInternalName starting with “vLookup” (you can rename the column afterwards, it’s only the FieldInternalName that is important). All fields with a FieldInternalName starting with this text will be included. No need to specify the columns to include! (this is not true for EditForm though, see separate instructions). Note: the ID column must be in the view (but can be hidden in the script).

Here is the code

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”).

The jQuery-library is referred from Google, but if you prefer a local copy, it is found here. The pictures and the sourcecode refers to jquery-1.5.1.min. If you download another version, be sure to update the script reference in the sourcecode.

The sourcecode for the file “vLookupForSharePoint.js” is found below.

Read here how to add a CEWP to the DispForm or EditForm.

Add this code in a CEWP below the list form in DispForm:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
  init_vLookupForSharePointDispForm();
</script>

Add this code in a CEWP below the list form in EditForm:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
  // This is an array of objects. Add more fields by adding a new object to the array
  // The "insertHere" value can be "top", "bottom" or a FieldInternalName to append the returned data to
  init_vLookupForSharePointEditForm([{'FieldInternalName':'vLookupTasks','FieldDisplayName':'Tasks','insertHere':'BugDescription'}]);
</script>

The reason the EditForm code is different is that the calculated column is not available unless we query for it using a CAML query. The “insertHere” parameter is used to place the “new field”.

Add this code in a CEWP below the list view:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
// The argument "true" sets the ID column to hidden
  init_vLookupForSharePointListView(true);
</script>

The code for the file “vLookupForSharePoint.js”:
download code

Upload the file to the scriptlibrary as described above.

If you find any bugs, please let me know!

Regards
Alexander

Filter Radio Buttons or Checkboxes based on selection in another field

Change log:

06.10.2010 Fixed a bug with unchecking hidden options when using a multi choice checkbox type column as “consumer” column (line 84 in the code). Thanks to Mike for finding the bug.

31.08.2010 Modified the code to hide the “consumer field” entirely if no items is visible.

16.07.2010 Updated the code to support checkboxes (allow multiple selections) in both “trigger” and “consumer” columns. I have also changed the logic from using the index of the option to use the actual text value.

15.03.2010 small update to the code to handle blank “triggervalue”. Thanks to Larry.


I got this request from Elstone:

Hello

I have two choice fields, 1 drop down and another is multiple radio buttons (6 values/radio button). This I want to implement on newform/editform

In dropdown I have 3 values i.e. Yes/No/Not Sure (selected by default).

I want to show/hide some of the radio buttons out of 6 radio buttons depending on condiion.

Let say
if user select “Yes”, then show radiobutton1 and radiobutton 3
If user select “No” , then show radiobutton2 and radiobutton 5
If user select “Not Sure” then show all the radio buttons

How can I do this?


IMG

IMG

This script will work for single value <select>, Radio Buttons and Checkboxes. The script will uncheck all hidden options, so if you flip the selection from “Yes” to “No” any selections made under “Yes” will be cleared.

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG
Note: picture refers to jquery-1.3.2.min.js, but code uses jquery-1.4.2.min.js.

The jQuery-library is found here. The sourcecode refers to jquery-1.4.2.min. If you download another version, be sure to update the script reference in the sourcecode.

Add this code to a CEWP below the list form in NewForm and EditForm:

&lt;script type=&quot;text/javascript&quot; src=&quot;../../Javascript/jquery-1.4.2.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
// Make object of all TR
fields = init_fields_v2();

// Call function with FieldInternalName of triggerfield and targetfield
init_hideRadiobuttons('MyTriggerColumn','MyRadioButton');

function init_hideRadiobuttons(triggerColumnFIN,radioButtonFIN){
var type = 'single';
if($(fields[triggerColumnFIN]).find('input').length&gt;0)type = 'multi';
	var thisVal = [];
	switch(type){
	case 'single':
		// Onload
		thisVal.push($(fields[triggerColumnFIN]).find('option:selected').text());
		hideRadiobuttons(radioButtonFIN,thisVal);
		// Onchange
		$(fields[triggerColumnFIN]).find('select').change(function(){
		thisVal = [];
			thisVal.push($(this).find('option:selected').text());
			hideRadiobuttons(radioButtonFIN,thisVal);
		});
	break;
	case 'multi':	
		// Onload
		$(fields[triggerColumnFIN]).find('input').each(function(){
			if($(this).attr('checked')){
				thisVal.push($(this).next().text());
			}
		});
		hideRadiobuttons(radioButtonFIN,thisVal);			

		// Onclick
		$(fields[triggerColumnFIN]).find('input').click(function(){
		thisVal = [];
			$(fields[triggerColumnFIN]).find('input').each(function(){
				if($(this).attr('checked')){
					thisVal.push($(this).next().text());
				}
			});
			hideRadiobuttons(radioButtonFIN,thisVal);			
		});
		break;
	}
}

function hideRadiobuttons(FieldInternalName,arrOfValues){
var showArr = [];
	$.each(arrOfValues,function(i,val){
		switch(val){
			case 'Yes':
				showArr.push('Selected Yes - option 1',
							 'Selected Yes - option 2',
							 'Selected Yes - option 3');
			break;
			case 'No':
				showArr.push('Selected No - option 1',
							 'Selected No - option 2',
							 'Selected No - option 3');
			break;
			case 'Not Sure':
				showArr.push('Selected Yes - option 1',
							 'Selected Yes - option 2',
							 'Selected Yes - option 3',
							 'Selected No - option 1',
							 'Selected No - option 2',
							 'Selected No - option 3');
			break
		}
	});
	if(showArr.length==0){
		// Hide row
		$(fields[FieldInternalName]).hide();
	}else{
		// Unhide row
		$(fields[FieldInternalName]).show();
		// Show options
		$(fields[FieldInternalName]).find('label').each(function(){
			var thisVal = $(this).text();
			if($.inArray(thisVal,showArr)&gt;-1){
				$(this).parents('tr:first').show();
			}else{
				$(this).prev().attr('checked',false)
				$(this).parents('tr:first').hide();			
			}
		});
	}
}

/*
  LastMod: 07.05.2010
*/
function init_fields_v2(){
	var res = {};
	$(&quot;td.ms-formbody&quot;).each(function(){
	var myMatch = $(this).html().match(/FieldName=&quot;(.+)&quot;s+FieldInternalName=&quot;(.+)&quot;s+FieldType=&quot;(.+)&quot;s+/);	
		if(myMatch!=null){
			// Display name
			var disp = myMatch[1];
			// FieldInternalName
			var fin = myMatch[2];
			// FieldType
			var type = myMatch[3];
			if(type=='SPFieldNote'){
				if($(this).find('script').length&gt;0){
					type=type+&quot;_HTML&quot;;
				}
			}
			if(type=='SPFieldLookup'){
				if($(this).find('input').length&gt;0){
					type=type+&quot;_Input&quot;;
				}
			}
			// Build object
			res[fin] = this.parentNode;
			res[fin].FieldDispName = disp;
			res[fin].FieldType = type;
		}		
	});
	return res;
}
&lt;/script&gt;

You must update the “cases” in the function “hideRadiobuttons” to match your setup.

Read here how to add a CEWP to the NewForm, DispForm or EditForm, and how to find the FieldInternalName of your columns.

Regards
Alexander