Guest Author: Alexander Bautz
SharePoint JavaScripts

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) (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

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

Example images:

List view with tasks pulled from tasklist

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

hoverToViewListView:true – before hover:

hoverToViewListView:true – after hover:

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

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.

The target list view is set in the parameter “linkBackView”

DispForm with “newItemLink:true”:

The same item in EditForm:

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):

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 found here. The pictures and the sourcecode refers to jquery-1.3.2.min. If you download another version, be sure to update the script reference in the sourcecode.

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

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="/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" 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="/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" 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="/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" 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” looks like this:

/* vLookup for SharePoint
* ----------------------------------------------------
* Author: Alexander Bautz
* [email protected]
* Version: 1.6.8
* LastMod: 10.06.2010
* LastChanges:
	- 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
	- Some small bugfixes
	- 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.
	- 09.06.2010: Fixed bug when item has no "children" (newItemLink and inQueryString)
	- 10.06.2010: Fixed bug when matching on a calculated column
* ----------------------------------------------------
	Create a calculated column with a selection of these parameters formatted as a string:
		- 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: ""
			Use "ID" to include this items ID
			Use "DispFormRelURL" to include the relative url to this item
			The URL-parameters will be prefixed with "vLookup"
			You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm.aspx

	The required parameters are: "listName","find","findInCol","action","viewFields"
*/

switch(L_Menu_LCID){
	case '1044':
		createNewItemLinkText = "Opprett nytt element";
		openInNewVindowText = 'Åpne i nytt vindu';
		viewItemText = 'Vis element';
		openFileText = 'Åpne fil';
		labelSumOf = "Sum av";
		labelAverageOf = "Gjennomsnitt av";
		labelItems = "elementer";
		labelCount = "Antall";
		labelClickToOpen = "klikk for å åpne";
		hoverToViewText =  "Hold musepekeren her for å vise elementene...";
	break;
	case '1053': // This is from Google translate...
		createNewItemLinkText = "Skapa nytt objekt";
		openInNewVindowText = 'Öppna i nytt fönster';
		viewItemText = 'Visa objekt';
		openFileText = 'Öppna fil';
		labelSumOf = "Summan av";
		labelAverageOf = "Genomsnitt av";
		labelItems = "poster";
		labelCount = "Antall";
		labelClickToOpen = "Klicka för att öppna";
		hoverToViewText =  "Håll muspekaren här för att visa objekten...";
	break
	default:
		createNewItemLinkText = "Create new item";
		openInNewVindowText = 'Open in new window';
		viewItemText = 'View item';
		openFileText = 'Open file';
		labelSumOf = "Sum of";
		labelAverageOf = "Average of";
		labelItems = "items";
		labelCount = "Count";
		labelClickToOpen = "click to open";
		hoverToViewText =  "Hover to view entries...";
}

queryStrParam = getQueryParameters();
/*********************************************
*************** DispForm code ****************
*********************************************/
function init_vLookupForSharePointDispForm(){
mode = 'DispForm';
if(typeof(fields)=='undefined')fields = init_fields_v2();
	$.each(fields,function(fin){
		if(fin.indexOf('vLookup')==0){
			res = false;
			var thisTd = $(fields[fin]).find('.ms-formbody');
			var thisTdText = thisTd.text().replace(/^[\s]+|[\s\xA0]+$/g,'');
			var thisItemID = queryStrParam.ID;
			var split = thisTdText.split('|');
			var dataObj = {};
			$.each(split,function(i,objRaw){
				var splitAgain = objRaw.split(':');
				dataObj[splitAgain[0]]=splitAgain[1];
			});
			// If find is set to "ID", replace with current ID
			if(dataObj.find=='ID')dataObj.find=thisItemID;
			// If find is set to "DispFormRelURL", replace with current items relative DispForm URL
			if(dataObj.find=='DispFormRelURL'){
				var baseUrl = location.pathname;
				var thisVal = baseUrl+"?ID="+thisItemID;
				dataObj.find=thisVal;
			}
			// Check parameters
			var parametersOK = checkParameters(dataObj,fin);
			// Query list and build returnvalue
			var strVal = queryItemsAndBuildReturnVal(dataObj,thisItemID,fin);
			if(strVal=='')strVal="&nbsp;";
			thisTd.html(strVal);
		}
	});
}

/*********************************************
*************** EditForm code ****************
*********************************************/
function init_vLookupForSharePointEditForm(arrOfFieldObjects){
var listName = $(".ms-pagetitle a").text();
var thisItemID = queryStrParam.ID;
var arrOfFin = [];
var arrOfDisp = [];
var arrOfInsertHere = [];

$.each(arrOfFieldObjects,function(i,fieldObj){
	arrOfFin.push(fieldObj.FieldInternalName);
	arrOfDisp.push(fieldObj.FieldDisplayName);
	arrOfInsertHere.push(fieldObj.insertHere);
});

// Query
wsBaseUrl = L_Menu_BaseUrl + '/_vti_bin/';
vLookupListItem = getItemById(listName,thisItemID,arrOfFin);
mode = 'EditForm';
if(typeof(fields)=='undefined')fields = init_fields_v2();
	$.each(arrOfFin,function(i,fin){
		if(fin.indexOf('vLookup')==0){
			res = false;
			var formula = vLookupListItem[fin].replace(/^[\w+]+;#/,'');
			var split = formula.split('|');
			var dataObj = {};
			$.each(split,function(i,objRaw){
				var splitAgain = objRaw.split(':');
				dataObj[splitAgain[0]]=splitAgain[1];
			});
			// If find is set to "ID", replace with current ID
			if(dataObj.find=='ID')dataObj.find=thisItemID;
			// If find is set to "DispFormRelURL", replace with current items relative DispForm URL
			if(dataObj.find=='DispFormRelURL'){
				var baseUrl = location.pathname.replace(/EditForm/,'DispForm');
				var thisVal = baseUrl+"?ID="+thisItemID;
				dataObj.find=thisVal;
			}
			// Check parameters
			var parametersOK = checkParameters(dataObj,fin);
			// Query list and build returnvalue
			var strVal = queryItemsAndBuildReturnVal(dataObj,thisItemID,fin);
			if(strVal=='')strVal="&nbsp;";
			var fieldDispName = arrOfDisp[i];
			var insertHere = arrOfInsertHere[i];
			var newHtml = "<tr><td width='190' class='ms-formlabel' noWrap='nowrap' vAlign='top'><h3 class='ms-standardheader'>"+fieldDispName+"</h3></td><td width='400' class='ms-formbody' vAlign='top'>"+strVal+"</td></tr>";
			if(fields[insertHere]==undefined){
				if(insertHere=='top'){
					$("table.ms-formtable tbody:first").prepend(newHtml);
				}else if(insertHere=='bottom'){
					$("table.ms-formtable tbody:first").append(newHtml);
				}
			}else{
				$(fields[insertHere]).after(newHtml);
			}
		}
	});
}

/*********************************************
*************** List view code ***************
*********************************************/

function init_vLookupForSharePointListView(hideIdColumn){
mode = 'ListView';
	$("div[id^='WebPartWPQ']").each(function(){
		thisWPContainer = $(this);
		// ctx - find displayFormUrl from current webpart's ctx - requires the "Title with menu" visible
		var thisListCTX = thisWPContainer.find("table[ctxname]:first").attr('ctxname');
		if(thisListCTX!=undefined){
			thisListCTX = eval(thisListCTX);
			var displayFormUrl = thisListCTX.displayFormUrl;
			thisWPContainer.data("displayFormUrl",displayFormUrl);
		}
		if(thisWPContainer.find(".ms-listviewtable").length==0){
			return;
		}else{
			if(typeof(hideIdColumn)!='undefined'){
				thisWPContainer.data("hideIdColumn",hideIdColumn);
			}else{
				hideIdColumn = thisWPContainer.data("hideIdColumn");
			}
		}
		var vLookupColInView = false;
		if(thisWPContainer.data("IDcolIndex")==undefined){
			thisWPContainer.find(".ms-viewheadertr th").each(function(){
				var intName = $(this).find('table:first').attr('name');
				if(intName=='ID'){
					thisWPContainer.data("IDcolIndex",$(this).attr('cellIndex'));
					// Hide ID column
					if(hideIdColumn){
						$(this).addClass('dummyHideClass');
					}
				}else if(intName!=undefined&&intName.match('vLookup')!=null){
					vLookupColInView = true;
				}
			});
		}
		if(thisWPContainer.data("IDcolIndex")==undefined && location.href.match(/ShowInGrid=True/)==null && vLookupColInView){
			alert("The ID column must be in the view.\nYou may hide it in the script call by setting the argument \"hideIdColumn\" to true.");
		}

		// Find the cellIndex of all the fields to address
		if(thisWPContainer.data("arrTargetCols")==undefined){
			arrTargetCols = [];
			thisWPContainer.find(".ms-viewheadertr th").each(function(){
			colIndex = '';
			var intName = $(this).find('table:first').attr('name');

				if(intName!=undefined&&intName.match('vLookup')!=null){
					var dispName = $(this).find('table:first').attr('displayname');
					colIndex = $(this).attr('cellIndex');
					arrTargetCols.push(intName+"|"+colIndex);
					// Disable filter
					$(this).removeClass('ms-vh2').addClass('ms-vh2-nograd').html(dispName);
				}
			});
			thisWPContainer.data("arrTargetCols",arrTargetCols);
		}else{
			arrTargetCols = thisWPContainer.data("arrTargetCols");
		}
		$.each(arrTargetCols,function(i,raw){
			var split = raw.split('|');
			var intName = split[0];
			var colIndex = split[1];
			// Call function
			vLookupForSharePoint(thisWPContainer,colIndex,intName);
		});
	});
	// Hide ID column if specified
	if(hideIdColumn){
		$(".dummyHideClass").hide();
	}
}

function vLookupForSharePoint(wpContainer,colIndex,fin){
	res = false;
	$(wpContainer).find("table.ms-listviewtable tbody").each(function(){
		if($(this).attr('id').match('aggr')==null){
			$(this).find("tr:has(td.ms-vb2)["+fin+"!='1']").each(function(){
				var IDcolIndex = wpContainer.data("IDcolIndex");
				$(this).attr(fin,1);
				var thisTr = $(this);
				var thisTd = $(this).find(">td[cellIndex="+colIndex+"]");
				var thisTdText = thisTd.text();
				var thisItemID = $(this).find(">td[cellIndex="+IDcolIndex+"]").text();
				// Hide ID column
				if(wpContainer.data("hideIdColumn")){
					$(this).find(">td[cellIndex="+IDcolIndex+"]").addClass('dummyHideClass');
				}
				var split = thisTdText.split('|');
				var dataObj = {};
				$.each(split,function(i,objRaw){
					var splitAgain = objRaw.split(':');
					dataObj[splitAgain[0]]=splitAgain[1];
				});
				// If find is set to "ID", replace with current ID
				if(dataObj.find=='ID')dataObj.find=thisItemID;
				// If find is set to "DispFormRelURL", replace with current items relative DispForm URL
				if(dataObj.find=='DispFormRelURL' && wpContainer.data("displayFormUrl")!=undefined){
					var baseUrl = wpContainer.data("displayFormUrl");
					var thisVal = baseUrl+"?ID="+thisItemID;
					dataObj.find=thisVal;
				}

				// Check parameters
				var parametersOK = checkParameters(dataObj,fin);
				// Query list and build returnvalue
				var strVal = queryItemsAndBuildReturnVal(dataObj,thisItemID,fin);
				thisTd.html(strVal);
			});
		}else if($(this).attr('id').match('aggr')!=null && hideId){
			$(this).find("td[cellIndex="+IDcolIndex+"]").addClass('dummyHideClass');
		}
	});
}

/*********************************************
******************* Query ********************
*********************************************/
function queryItemsAndBuildReturnVal(dataObj,thisItemID,fin){
// Set variables from dataObj
	var listName = dataObj.listName;
	var listBaseUrl = (dataObj.listBaseUrl!=undefined)?dataObj.listBaseUrl:L_Menu_BaseUrl;
	var find = dataObj.find;
	var findInCol = dataObj.findInCol;
	var returnFieldArrRaw = dataObj.viewFields.split(';');
	// Split viewFields in two arrays
	var arrFin = [];
	var arrDispName = [];
		$.each(returnFieldArrRaw,function(i,item){
			var split = item.split('#');
			arrFin.push(split[0]);
			arrDispName.push(split[1]);
		});
	var action = dataObj.action.toLowerCase();
	var sumOrAvgPrefix = (dataObj.sumOrAvgPrefix!=undefined)?dataObj.sumOrAvgPrefix:'';
	var sumOrAvgPostfix = (dataObj.sumOrAvgPostfix!=undefined)?dataObj.sumOrAvgPostfix:'';
	var linkBack = (dataObj.linkBack!=undefined&&dataObj.linkBack.toLowerCase()=='true')?true:false;
	var linkBackView = (dataObj.linkBackView!=undefined)?dataObj.linkBackView:'AllItems.aspx';
	var hoverToViewDispForm = (dataObj.hoverToViewDispForm!=undefined&&dataObj.hoverToViewDispForm.toLowerCase()=='true')?true:false;
	var hoverToViewEditForm = (dataObj.hoverToViewEditForm!=undefined&&dataObj.hoverToViewEditForm.toLowerCase()=='true')?true:false;
	var hoverToViewListView = (dataObj.hoverToViewListView!=undefined&&dataObj.hoverToViewListView.toLowerCase()=='true')?true:false;
	var newItemLink = (dataObj.newItemLink!=undefined&&dataObj.newItemLink.toLowerCase()=='true')?true:false;
	var inQueryString = (dataObj.inQueryString!=undefined)?dataObj.inQueryString:false;
	// Run query if it is not already done
	if(res==false){
		wsBaseUrl = listBaseUrl + "/_vti_bin/";
		var query = "<Where><IsNotNull><FieldRef Name='"+findInCol+"' /></IsNotNull></Where>";
		var viewFields = ['ID','ServerUrl','EncodedAbsUrl',findInCol];
		viewFields = viewFields.concat(arrFin);
		res = queryItems(listName,query,viewFields);
		if(res.count==-1)alert("An error occured in the query. Please check \"listName\", \"listBaseUrl\" and \"findInCol\" for column \""+fin+"\".");
	}

	// ReturnVal is either string or int
	returnVal = (action=='concat')?'':0;
	itemCount = 0;
	var retObj = {};
	var newItemUrl = '';
	// Loop trough all items
	$.each(res.items,function(i,item){
		qfId = item[findInCol];
		filterVal = qfId;

		// Match an item in a multiLookup column by ID
		var multiLookupMatch = false;
		var qfIdMatch = qfId.match(/\d+;#/g);
		if(qfIdMatch!=null && qfIdMatch.length>0){
			$.each(qfIdMatch,function(i,raw){
				var id = raw.match(/\d+/);
				if(id==find){
					multiLookupMatch = true;
				}
			});
		}
		// Separate id and value for lookup columns
		if(qfId.match(/\d+;#/)!=null){
			qfId = qfId.substring(0,qfId.indexOf(';#'));
			filterVal = filterVal.substring(filterVal.indexOf(';#')+2);
		}
		// Calculated columns - remove "string;#" or other prefix
		if(qfId.match(/\w+;#/)!=null){
			qfId = qfId.substring(qfId.indexOf(';#')+2);
			filterVal = filterVal.substring(filterVal.indexOf(';#')+2);
		}
		// "Build" the returnVal if the item matches the "query-ID"
		if(qfId==find || multiLookupMatch){
			itemCount += 1;
			var retVal = '';
			var highligthStyle = 'background-color:#F1F1F1';
				if(itemCount%2!=0){
					highligthStyle = '';
				}
			if(linkBack){ // LinkBack
				var src = location.pathname;
				if(src.match(/DispForm.aspx/)!=null){
					src += "?ID="+thisItemID
				}
				var urlRaw = item['ServerUrl'];
				url = urlRaw.substring(0,urlRaw.lastIndexOf('/'));
				if(url.indexOf('/Lists/')<0){
					url += "/Forms";
				}
				newItemUrl = url + "/NewForm.aspx";
				// Build the menu bar for linking back to items
				var linkBackDivStyle = 'padding:2;border:1px silver solid;color:gray;background-color:white;white-space:nowrap;display:none';
				if(mode=='DispForm' && hoverToViewDispForm || mode=='EditForm' && hoverToViewEditForm){
					linkBackDivStyle = 'padding:2;color:gray;white-space:nowrap;display:block';
				}else if(mode=='ListView'){
					if(hoverToViewListView){
						linkBackDivStyle = 'padding:2;color:gray;white-space:nowrap;display:block';
					}
				}
				var postStr = "<div style='"+linkBackDivStyle+"'>";
				if(mode!='EditForm'){
						postStr += "<a style='color:gray' title='"+viewItemText+"' href='"+url+"/DispForm.aspx?ID="+item['ID']+"&source="+src+"'>"+viewItemText+"</a>";
						postStr += "&nbsp;|&nbsp;";
					}
					postStr += "<a style='color:gray' title='"+openInNewVindowText+"' href='"+url+"/DispForm.aspx?ID="+item['ID']+"' target='_blank'>"+openInNewVindowText+"</a>";
					postStr += "</div>";
			}else{ // No linkback
				var urlRaw = item['ServerUrl'];
				url = urlRaw.substring(0,urlRaw.lastIndexOf('/'));
				if(url.indexOf('/Lists/')<0){
					url += "/Forms";
				}
				newItemUrl = url + "/NewForm.aspx";

				var postStr = "";
			}
			// Build concatenated value with hover effect if action is "concat"
			if(action=='concat'){
				var borderStyle = '';
				$.each(arrFin,function(i,fin){
					thisVal = (item[fin]==null)?'':item[fin];
					if(thisVal.match(/^[A-Za-z0-9]+;#/)){
						thisVal = thisVal.substring(thisVal.indexOf(';#')+2);
					}
					// Is it a document from a document lilbrary?
					if(fin=='FileLeafRef'){
						thisVal = "<a title='"+openFileText+": "+thisVal+"' href='"+item['EncodedAbsUrl']+"' onclick='javascript:customOpenOfficeDocuments(\""+item['EncodedAbsUrl']+"\");return false'>"+thisVal+"</a>";

					}
					// Multichoice - strip off ;# and replace with <br />
					if(thisVal.indexOf(';#')==0){
						thisVal = thisVal.substring(2).split(';#').join('<br />');
					}
					if(thisVal.replace(/\s|\xA0/g,'')!=''){
						if(arrFin.length>1){ // Add display name above each field
							retVal += "<tr><td colspan='2' style='border-bottom:1px gray solid;padding:0 0 0 2;font-size:11px;font-weight:bold'>"+arrDispName[i]+"</td></tr>";
							retVal += "<tr><td>&nbsp;</td><td style='border-left:1px gray solid;padding-left:3px;font-size:11px'>"+thisVal+"</td></tr>";
							borderStyle = 'border-top:3px gray double;padding:3 0 3 3';
						}else{ // Only one field - no display name
							retVal += "<tr><td style='font-size:11px;padding:2 0 0 0'>"+thisVal+"</td></tr>";
							borderStyle = 'padding:0 0 0 0';
						}
					}
				});
				retVal="<table cellpadding='0' cellspacing='0' border='0'>"+retVal+"</table>";
			}else{ // Action is not "concat" - takes only the first FieldInternalName's value, even if multiple fields are specified
				retVal = (item[arrFin[0]]!=null)?item[arrFin[0]]:0;
			}
			// Build object
			if(action=='concat'){
				if(mode=='DispForm' || mode=='EditForm'){
					if((mode=='DispForm' && linkBack && hoverToViewDispForm) || (mode=='EditForm' && linkBack && hoverToViewEditForm)){
						var returnWrap = (retVal!='')?"<div style='"+borderStyle+"'>"+retVal + postStr+"</div>":'';
					}else if(linkBack){
						var returnWrap = (retVal!='')?"<div style='"+borderStyle+"' onmouseover='javascript:hoverLinkBack($(this))'>"+retVal + postStr+"</div>":'';
					}else{
						var returnWrap = (retVal!='')?"<div style='"+borderStyle+"'>"+retVal + postStr+"</div>":'';
					}
				}else if(mode=='ListView'){
					if(linkBack && hoverToViewListView){
						var returnWrap = (retVal!='')?"<div style='"+borderStyle+"'>"+retVal + postStr+"</div>":'';
					}else if(linkBack){
						var returnWrap = (retVal!='')?"<div style='"+borderStyle+"' onmouseover='javascript:hoverLinkBack($(this))'>"+retVal + postStr+"</div>":'';
					}else{
						var returnWrap = (retVal!='')?"<div style='"+borderStyle+"'>"+retVal + postStr+"</div>":'';
					}
				}

				if(returnWrap!=''){
					// Obj
					if(retObj[fin]==undefined){
						retObj[fin] = {'action':action,'retStr':"<div style='"+highligthStyle+"'>"+returnWrap+"</div>"};
					}else{
						retObj[fin]['retStr'] += "<div style='"+highligthStyle+"'>"+returnWrap+"</div>";
					}
				}
			}else if((action=='sum' || action=='avg') && retVal!=null){
				retVal = parseFloat(retVal);
				// Obj
				if(retObj[fin]==undefined){
					retObj[fin] = {'action':action,'retVal':retVal,'url':url,'ff':findInCol,'fv':filterVal};
				}else{
					retObj[fin]['retVal'] += retVal;
				}
			}else if(action=='count'){
				if(retObj[fin]==undefined){
					retObj[fin] = {'action':action,'url':url,'ff':findInCol,'fv':filterVal};
				}
			}
		}
	});	

	// If object is defined for this item, build the content and write back to the placeholder
	if(typeof(retObj[fin])=='object'){
		obj = retObj[fin];
		action = obj.action;
		str = obj.retStr;
		val = obj.retVal;
		url = obj.url;
		filterField = obj.ff;
		filterVal = obj.fv;

		if(action=='concat'){
			returnVal = str;
			if(mode=='DispForm' || mode=='EditForm'){
				if(newItemLink){
					var qStr = '';
					if(inQueryString!=false){
						qStr = vLookupBuildQueryString(inQueryString,thisItemID,mode);
					}
					var target = '_self';
					var title = '';
					var sourcePath = location.pathname;
					if(mode=='EditForm'){
						target = '_blank';
						title = openInNewVindowText;
						sourcePath = location.pathname.replace(/EditForm/,'DispForm');
					}
					var returnURL = newItemUrl+"?source="+sourcePath+"?ID="+thisItemID+qStr;
					returnVal = "<div id='newItemLinkContainer'><a title='"+title+"' href='"+returnURL+"' target='"+target+"'>"+createNewItemLinkText+"</a></div>" + returnVal;
				}
				if(mode=='DispForm' && hoverToViewDispForm || mode=='EditForm' && hoverToViewEditForm){
					return "<div onmouseover='javascript:hoverDisplay($(this))'><span style='cursor:pointer'>"+hoverToViewText+"</span>"+
							"<div style='background-color:white;border:1px gray solid;width:500px;padding:5px;display:none'>"+returnVal+"</div></div>";
				}else{
					return "<div>"+returnVal+"</div>";
				}
			}else if(mode=='ListView'){
				if(hoverToViewListView){
					return "<div onmouseover='javascript:hoverDisplay($(this))'><span style='cursor:pointer'>"+hoverToViewText+"</span>"+
							"<div style='background-color:white;border:1px gray solid;width:500px;padding:5px;display:none'>"+returnVal+"</div></div>";
				}else{
					return "<div>"+returnVal+"</div>";
				}
			}

		}else if(action=='sum' || action=='avg'){
			if(action=='sum'){
				var labelPrefix = labelSumOf;
			}else if(action=='avg'){
				var labelPrefix = labelAverageOf;
			}
			var target = '_self';
			if(mode=='EditForm'){
				target = '_blank';
			}
			if(linkBack && itemCount>0){
				var label = labelPrefix+" "+itemCount+" "+labelItems+" - "+labelClickToOpen;
				returnVal = "<a title='"+label+"' href='"+url+"/"+linkBackView+"?FilterField1="+filterField+"&FilterValue1="+filterVal+"' style='cursor:pointer' target='"+target+"'>"+sumOrAvgPrefix+roundNumber(action,val,itemCount)+sumOrAvgPostfix+"</a>";
			}else if(!linkBack && itemCount>0){
				var label = labelPrefix+" "+itemCount+" "+labelItems;
				returnVal = "<span title='"+label+"' style='cursor:default'>"+sumOrAvgPrefix+roundNumber(action,val,itemCount)+sumOrAvgPostfix+"</span>";
			}
			// Write back to the TD
			return returnVal;
		}else if(action=='count'){
			if(linkBack && itemCount>0){
				var label = labelCount+": "+itemCount+" - "+labelClickToOpen+" - "+labelClickToOpen;
				returnVal = "<a title='"+label+"' href='"+url+"/"+linkBackView+"?FilterField1="+filterField+"&FilterValue1="+filterVal+"' style='cursor:pointer'>" + itemCount + "</a>";
			}else if(!linkBack && itemCount>0){
				var label = labelCount+": "+itemCount;
				returnVal = "<span title='"+label+"' style='cursor:default'>" + itemCount + "</span>";
			}

			// return value
			return returnVal;
		}
	}else{
		// No "children" are connected to this item
		if(action=='concat'){
			if(newItemLink && mode!='ListView'){
				var qStr = '';
				var listPath = getListPath(listName,listBaseUrl);
				if(inQueryString!=false){
					var qStr = vLookupBuildQueryString(inQueryString,thisItemID,mode);
				}
				returnVal = "<div><a href='"+listPath+"/NewForm.aspx?source="+location.pathname+"?ID="+thisItemID+qStr+"'>"+createNewItemLinkText+"</a></div>" + returnVal;
			}
		}
		return returnVal;
	}
}

function vLookupBuildQueryString(fieldsToInclude,thisID,mode){
	var queryStringParameters = [];
	inQueryStringArr = fieldsToInclude.split(';')
	$.each(inQueryStringArr,function(i,fin){
		if(fields[fin]==undefined && fin!='ID' && fin!='DispFormRelURL')return;
		if(fin=='ID'){
			var thisVal = thisID;
		}else if(fin=='DispFormRelURL'){
			var baseUrl = location.pathname.replace(/EditForm/,'DispForm');;
			var thisVal = encodeURIComponent(baseUrl+"?ID="+thisID);
		}else{
			if(mode=='DispForm'){
				var thisVal = vLookupGetFieldValue(fin,'disp');
			}else if(mode=='EditForm'){
				var thisVal = vLookupGetFieldValue(fin,'edit');
			}
		}
		queryStringParameters.push("&vLookup"+fin+"="+thisVal);
	});
	var qStr = queryStringParameters.join('');
	return qStr;
}

/*
  LastMod: 29.04.2010
*/
function vLookupGetFieldValue(fin,edit_OR_disp){
// Return if FieldInternalName is not found
if(fields[fin]==undefined)return;
var thisField = $(fields[fin]);
// If "edit_OR_disp" is undefined, default to "edit"
if(edit_OR_disp==undefined)edit_OR_disp='edit';
	if(edit_OR_disp=='disp'){ // If "disp"
		var valRaw = $(fields[fin]).find('.ms-formbody').text();
		return (valRaw.replace(/[ \xA0]+$/,'')=='')?'':valRaw.replace(/[ \xA0]+$/,'');
	}else{ // If "edit"
		var fieldType = $(fields[fin]).attr('FieldType');
		if(fieldType==undefined){
			alert("The attribute \"FieldType\" is missing.\nEnsure the function init_fields_v2() is used.");
			return false;
		}
		fieldVal = '';
		switch(fieldType){
			case 'SPFieldText':
			case 'SPFieldNumber':
			case 'SPFieldCurrency':
				fieldVal = thisField.find('input').val();
			break;
			case 'SPFieldChoice':
				if(thisField.find('input:radio').length>0){
					fieldVal = thisField.find('input:radio:checked').next().text();
				}else{
					fieldVal = thisField.find('select').val();
				}
			break;
			case 'SPFieldMultiChoice':
				var multiChoice = [];
				thisField.find('input:checkbox:checked').each(function(i,opt){
					opt = $(opt);
					multiChoice.push(opt.next().text());
				});
				fieldVal = multiChoice.join('<br />');
			break;
			case 'SPFieldUser':
				var myPeoplePicker = thisField.find("div[id='divEntityData']");
				fieldVal = myPeoplePicker.attr('displaytext');
				fieldVal = (fieldVal!=undefined)?returnVal:'';
			break;
			case 'SPFieldUserMulti':
				var userMulti = [];
				thisField.find("div[id='divEntityData']").each(function(i,div){
					thisVal = $(div).attr('displaytext');
					if(thisVal!=undefined){
						userMulti.push(thisVal);
					}
				});
				fieldVal = userMulti.join(';');
			break;
			case 'SPFieldLookup':
				if(thisField.find('select').length>0){
					fieldVal = thisField.find('select option:selected').text();
				}else{
					fieldVal = thisField.find('input').val();
				}
			break;
			case 'SPFieldLookupMulti':
				var lookupMulti = [];
				thisField.find("select:last option").each(function(i,opt){
					opt = $(opt);
					lookupMulti.push(opt.text());
				});
				fieldVal = lookupMulti.join(';');
			break;
			case 'SPFieldBoolean':
				fieldVal = (thisField.find('input').attr('checked')==true)?true:false;
			break;
			case 'SPFieldURL':
				var link = thisField.find('input:first').val();
				var descr = thisField.find('input:last').val();
				fieldVal = "<a href='"+link+"'>"+descr+"</a>";
			break;
			case 'SPFieldDateTime':
				var date = thisField.find('input:first').val();
				var hour = thisField.find('select:first option:selected').val()
					hour = (hour==null)?'':hour.match(/^[\d]+/)+":";
				var AMPM = thisField.find('select:first option:selected').val()
					AMPM = (AMPM==null)?'':AMPM.match(/AM|PM/);
				var minutes = thisField.find('select:last option:selected').val();
					minutes = (minutes==null)?'':minutes;
				fieldVal = date+" "+hour+minutes+" "+AMPM;
			break;
			case 'SPFieldNote':
				fieldVal = thisField.find('textarea:first').val();
			break;
			case 'customHeading':
				fieldVal = '';
			break;
			default:
			fieldVal = "Unknown fieldType: <strong>"+fieldType+"</strong>, please check the script.";
		}
		if(fieldVal==='')fieldVal="&nbsp;";
		return fieldVal;
	}
}

/*****************************************************
					Get list path
*****************************************************/
function getListPath(nameOrGuid,baseUrl){
var returnVal = '';
	xmlStr = "<GetList xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>"+nameOrGuid+"</listName></GetList>";
	wrapSoapRequest(baseUrl + '/_vti_bin/lists.asmx', 'http://schemas.microsoft.com/sharepoint/soap/GetList', xmlStr, function(data){
		if($('ErrorText', data).length > 0) {
			returnVal = "vLookup - function getListPath error.";
		}else{
			returnVal = $(data).find('List').attr('RootFolder');
		}
	});
	return returnVal;
}
/*****************************************************
				Wrap webservice call
*****************************************************/
function 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.statusText);
		}
	});
}

function customOpenOfficeDocuments(docUrl){
var arr = ['doc','xls','dot','dotx','dotm','bmp','gif','jpeg','jpg','csv','docm','docx','docmhtml',
	'docxml','dothtml','gcsx','ico','mdb','mde','mdn','png','pot','pothtml','potm','potx','pps', 'ppsx',
	'ppt','pptm','pptx','pub','potm','ppam','ppsm','xlb','xlc','xls','xlsx','xlsm','xlt','xltm','xlsb','xlam','xltx','xlw','xlxml'];

	var thisDocExt = docUrl.substring(docUrl.lastIndexOf('.')+1);
	if($.inArray(thisDocExt,arr)>-1){
		ViewDoc(makeAbsUrl(docUrl),'SharePoint.OpenDocuments');
	}else{
		window.open(docUrl,'_blank')
	}
return false;
}

/*********************************************
************** Check parameters **************
*********************************************/
function checkParameters(obj,fin){
	// Check parameters
	var arrBasis = ['listName','find','findInCol','action','viewFields'];
	var basisOk = true;
	$.each(arrBasis,function(i,param){
		if(obj[param]==undefined){
			alert("Check parameter \""+param+"\" in calculated column \""+fin+"\".");
			basisOk = false;
		}
	});
	// Exit on error
	if(!basisOk){
		return false;
	}else{
		return true;
	}
}

/*********************************************
**************** Hover effect ****************
*********************************************/
function hoverLinkBack(obj){
	var offset = obj.offset();
	var objHeight = obj.outerHeight();
	var oTop = offset.top+objHeight;
	obj.hover(function(){
		obj.attr('hover',1);
		setTimeout(function(){
			if(obj.attr('hover')==1){
				obj.find('div:last')
					.css({'position':'absolute',
						 'top':oTop,
						 'left':offset.left})
					.fadeIn();
			}
		},250);
	},function(){
		obj.attr('hover',0);
		obj.find('div:last').stop(true,true).hide();
	});
}

function hoverDisplay(obj){
var offset = obj.offset();
var winHeight = $(window).height();
var winWidth = $(window).width();
var scrollLeft = $(window).scrollLeft();
var scrollTop = $(window).scrollTop();
var objHeight = obj.find('div:first').outerHeight();
var objWidth = obj.find('div:first').width()+15;
if(((winWidth+scrollLeft)-offset.left)<objWidth){
	offset.left=((winWidth+scrollLeft)-objWidth);
}
var maxHeight = (winHeight+scrollTop)-offset.top;
var height = (objHeight>maxHeight)?maxHeight:objHeight;
if(maxHeight<400&objHeight>maxHeight){
	if(offset.top-scrollTop<objHeight){
		offset.top=scrollTop;
	}else{
		offset.top=offset.top-objHeight;
	}
	height = (objHeight<winHeight)?objHeight:winHeight;
}
	obj.hover(function(){
		obj.attr('hover',1);
		setTimeout(function(){
			if(obj.attr('hover')==1){
				obj.find('div:first')
					.css({'position':'absolute',
						 'top':offset.top,
						 'left':offset.left,
						 'height':height,
						 'overflow':'auto'}).fadeIn();
			}
		},300);
	},function(){
		obj.attr('hover',0);
		obj.find('div:first').fadeOut(150);
	});
}

// Round numbers for use in sum and average
function roundNumber(act,val,iCount){
	if(act=='sum'){
		var returnVal = Math.round(val*100)/100;
		return returnVal;
	}else if(act=='avg'){
		var returnVal = Math.round((val/iCount)*100)/100;
		return returnVal;
	}
}

// Function to separate each url search string parameters
function getQueryParameters(){
qObj = {};
var urlSearch = window.location.search;
	if(urlSearch.length>0){
		var qpart = urlSearch.substring(1).split('&');
		$.each(qpart,function(i,item){
			var splitAgain = item.split('=');
			qObj[splitAgain[0]] = splitAgain[1];
		});
	}
return qObj;
}

/*
  LastMod: 07.05.2010
*/
function init_fields_v2(){
	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].FieldDispName = disp;
			res[fin].FieldType = type;
		}
	});
	return res;
}

// Attaches a call to the function to the "expand grouped elements function" for it to function in grouped listview's
function ExpGroupRenderData(htmlToRender, groupName, isLoaded){
	var tbody=document.getElementById("tbod"+groupName+"_");
	var wrapDiv=document.createElement("DIV");
	wrapDiv.innerHTML="<TABLE><TBODY id=\"tbod"+groupName+"_\" isLoaded=\""+isLoaded+"\">"+htmlToRender+"</TBODY></TABLE>";
	tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
init_vLookupForSharePointListView();
}

Save as “vLookupForSharePoint.js”, mind the file extension, and upload to the scriptlibrary as shown above.

If you find any bugs, please let me know!

Regards
Alexander

Guest Author: Alexander Bautz
SharePoint JavaScripts

Alexander Bautz is a SharePoint consultant/developer (mainly JavaScript/jQuery solution) living in Norway. Alexander spends a lot of his spare time blogging on the same topics. His focus area is "end user customizations" with no (or as little as possible) server side code.