Category Archives: List view modification

Approve or reject list items

I have previously posted a JavaScript solution for approving multiple documents or list items. This is an updated version that adds two buttons in the ribbon: one for “Approve” and one for “Reject”. Refer the original solution for details, but please note that the variables for controlling the text values have changed.

Use this code to load the solution in a list view
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script type="text/javascript" src="/Scripts/ApproveOrRejectSelected.js"></script>
Change the default text values

If you are happy with the default, English text, you can skip this next step.

To override the default text, add this to the CEWP below the script reference to “ApproveOrRejectSelected.js”,and change the text as you like:

<script type="text/javascript">
spjs.contentApproval.text = {
	"approveBtn":"Approve selected",
	"rejectBtn":"Reject selected",
	"groupLabel":"Approve or Reject",
	"working":"Processing items...",
	"done":"{0} items processed"
};
</script>

You can wrap it like this to switch based on the logged in users language (in MUI environments):

switch(_spPageContextInfo.currentLanguage){
	case 1044:
		// Add Norwegian values
		spjs.contentApproval.text = {
			"approveBtn":"Approve selected",
			"rejectBtn":"Reject selected",
			"groupLabel":"Approve or Reject",
			"working":"Processing items...",
			"done":"{0} items processed"
		};
	break;
	case 1031:
		// Add German values
		spjs.contentApproval.text = {
			"approveBtn":"Approve selected",
			"rejectBtn":"Reject selected",
			"groupLabel":"Approve or Reject",
			"working":"Processing items...",
			"done":"{0} items processed"
		};
	break;
}
Download ApproveOrRejectSelected.js

Get the file here.

Alexander

List view: Preview item on hover

Change log
June 1, 2014
Updated to v1.38:

  • Added “URL” and “Image” as “type” in the fieldData section of the configuration (for use with field of type Hyperlink or Image).
  • Tidied up a few unused variables.
  • Added id attribute “spjs_previewTable” to the main preview table for easier styling. Add style tag to the CEWP code and target the table like this:
    #spjs_previewTable{
    /* your css here */
    }

April 21. 2014
Updated to v1.37:

  • Fixed error when using in list with folders by adding “scope=RecursiveAll” to the query. Please note that you must also update spjs-utility.js to v1.182 or above.
  • Added option to turn on or off the edit item button in the top right corner of the dialog by adding “showEditBtn”:[true or false] to the configuration. See details in the configuration details below.

April 1. 2014
Updated to v1.36:
Fixed an issue with having multiple lists in a web part page. Let me know if there are still issues as I cannot test every possible combination.

March 30. 2014
Updated to v1.35:
Fixed a type in the script that affected IE8 when using the cross list functionality.

March 12. 2014
Updated to v1.32:

  • Removed String;# prefix when previewing calculated columns.
  • Added option to use other columns than ID to identify which item to preview when using the “advanced option” to pull information from another list. See details at the bottom of this article.

January 29. 2014
Updated to v1.31:
Fixed a bug when using in a calendar view on a web part page.
You must add a new variable to the CEWP code alongside the “spjs_listItemPreviewFIN” like this:

var spjs_listItemPreviewFIN = "HoverMe";
var spjs_listItemPreviewList = "{2b6961fc-2e5d-420e-b6d9-3ce7d1bb88b2}";

January 16. 2014
Updated to v1.3:
Fixed a bug that slowed the loading significantly due to a query being executed for each list item in stead of only once per list view.

December 15. 2013
Updated to v1.2 with these changes:

  • Fixed linefeed in multiline plain text fields.
  • Added option to use the value from any field in the form as dialog title. The standard value is “Title”. You find this as a new option in the config object “dlgTitleField”.
  • Added support for showing attachments in the preview. To enable showing attachments, configure it as any other field you want to include in the fields array – use “icon” to show or hide the document icon:
    {"fin":"Attachments","label":true,"icon":true}
    

December 09. 2013
v1.1: Updated the code for the file “PreviewItem.js” and the code example for the calculated column to support list view web parts in web part pages. The change to the calculated column is that the list GUID must be included. This is necessary only if you plan to use a list view web part, and not if you use the code in a proper list view. See details in the code for the calculated column below.

I have previously made several solutions for previewing information for list items by hovering over them in a list view. This is one supports SP2010 and SP2013 – and possibly SP2007 as well. I no longer have access to a SP2007 site and cannot test it there, but I have made an effort to adapt the code to (hopefully) work in SP2007 as well. If you find any problems with the SP2007 support, please post a comment below, and I’ll try my best to fix it.

IMG

This solution uses the well known trick of inserting an image tag in a calculated column, and selecting “number” as the format for the calculated column. This is the code that triggers the solution in a list view (see separate section for calendar view):

="<img src='/_layouts/images/loadingcirclests16.gif' fin='Preview' list='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' configindescription='1' onload='spjs.listItemPreview.init(this)'>"
New in v1.1:

The attribute “list” must be added if you want to use the solution in a list view web part outside the list itself. This attribute is the GUID of the list. You must change the string xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx to match your list. You can find the GUID in SP 2010 or SP 2013 by right clicking a list view in the list > view source > search for “pageListId”. In SP 2007 you must search for “ctx.Listname”.

Please note the FieldInternalName of the field in the “fin” attribute, and the “configindescription” set to “1” indicating that the configuration should be read from the field description. Set “configindescription=’0′” to configure the setup in the HTML Form Web Part (required for SP 2007, optional for SP 2010 and SP 2013).

Ensure you use the “Number” format to trick SharePoint into rendering it as proper HTML:
IMG

When you set “configindescription=’1′” in the calculated column, the configuration is read from the description of the calculated column itself. This way you do not need to edit the setup in a separate file. Please note that you still need to refer the code in the list views where you want to use this solution – or you could put it in the master page to access it in the entire site or site collection. When this is done, the actual configuration for this particular field is done in the list settings!

I’ll get back to the configuration later, but first the general setup.

Setup the scripts
Step 1:

I have used a “icon font” called Font Awesome for all the icons in this solution. Go get it here: Font Awesome.

Here is how Dave Gandy describes Font Awesome:

Font Awesome gives you scalable vector icons that can instantly be customized — size, color, drop shadow, and anything that can be done with the power of CSS.

In my example code I have linked the font awesome css file from bootstrapcdn, but you can download it locally if you like.

Step 2:

Download the file “spjs-utility.js” from here. Ensure you get the latest version.

Step 3:

Get jQuery from here. Use v1.10.x as the 2.x series in not compatible with IE below 9.

Step 4:

Get the code for the file “PreviewItem.js” from here. Ensure you get the latest version.

Step 5:

Add this code to a HTML form web part in the list view where you want the solution activated. Please note that you must insert it ABOVE the list view for the code to be accessible during page load.

<link href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.css" rel="stylesheet">
<script type="text/javascript" src="/Scripts/jQuery-1.10.2.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript" src="/Scripts/PreviewListItem3/PreviewItem.js"></script>

Now the initial setup is completed and you can configure the calculated column.

Setting up the configuration in the calculated column
Instructions for SP 2010 and 2013

First start by creating a calculated column with this formula:

="<img src='/_layouts/images/loadingcirclests16.gif' fin='Preview' list='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' configindescription='1' onload='spjs.listItemPreview.init(this)'>"
New in v1.1:

The attribute “list” must be added if you want to use the solution in a list view web part outside the list itself. This attribute is the GUID of the list. You must change the string xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx to match your list. You can find the GUID in SP 2010 or SP 2013 by right clicking a list view in the list > view source > search for “pageListId”. In SP 2007 you must search for “ctx.Listname”.

Insert the FieldInternalName of the newly created field in the attribute “fin” in the formula.

This is the settings you have access to when storing the setup in a the description of the calculated column:

{"headerCss":"",	
"clickToView":false,
"hoverDelay":200,
"showEditBtn":true,
"editInDlg":true,
"hoverIcon":{
	"off":"fa-arrow-circle-o-right",
	"on":"fa-arrow-circle-down",
	"css":"color:#FF6103;"
},
"dlgTitleField":"Title",
"maxHeight":400,
"maxWidth":600,
"dateFormat":"dd.MM.yyyy",
"fields":[
	{"fin":"Title","label":true},
	{"fin":"DateAndTime","label":true},
	{"fin":"Multiline","label":true},
	{"fin":"MyChoiceCol","label":true},
	{"fin":"MultiUser","label":true},
	{"fin":"Lookup","label":true},
	{"fin":"Attachments","label":true,"icon":true}
],
"footer":{
	"show":true,
	"css":"",
	"dateFormat":"dd.MM.yyyy hh:mm",
	"strings":{
		"default":["Created at","by","Last modified at","by"],
		"1044":["Opprettet","av","Sist endret","av"]
	}
},
"editFormUrl":null,
"listGuid":null,
"listBaseUrl":null
}
Instructions for SP 2007

First start by creating a calculated column with this formula:

="<img src='[Path to image]' fin='Preview' configindescription='0' iid='"&_ID&"' onload='spjs.listItemPreview.init(this)'>"

Insert the FieldInternalName of the newly created field in the attribute “fin” in the formula. You must also provide the URL to an image you want to show while the solution is loading. The image used in the SP2010 and SP2013 setup is not available in SP2007.

Please note the reference to the _ID field. To use this solution in a list view you must provide the item ID in the calculated column as this is not accessible in the list view table in SP 2007. You cannot use the ID directly in a calculated column as new items will get “0” for ID.

You can use the method described in this article to get the _ID column populated, or you can create a workflow that inserts the ID into the _ID field.

The configuration options for SP2007 can be found below in the section “Setting up the configuration in the HTML form web part”.

Explanation of the various keys
  • headerCss = css to override the dialog header
  • clickToView = true or false to determine whether you must click to open the preview.
  • hoverDelay = number of milliseconds to delay the preview. Used to prevent flickering when moving the mouse over the icons.
  • showEditBtn = true or false to determine whether or not to show the edit item button in the top right corner of the dialog.
  • editInDlg = true or false to determine whether or not to open the EditForm in a dialog. This setting does not apply to SP 2007.
  • hoverIcon = the icon to use for “on” and “off”. These icons must be selected among the font awesome css classes (icons). You can set the icon style using the css setting.
  • maxHeight and maxWidth = number to set the max dialog height and width. You will get a vertical scrollbar if the contents exceed the height setting.
  • dateFormat = a string representing the date format to use. These are the codes representing the various date and time parts:
    yyyy = for digit year.
    yy = two digit year.
    MM = month.
    dd = day.
    hh = hours.
    mm = minutes.
    ss = seconds
  • fields = array of object with the fieldinternal name of the field, and whether or not to render the field label.
  • footer = determine whether or not to show the footer with created and modified.
  • editFormUrl = null by default. Insert the Relative URL of another list to preview items form another location [advanced]
  • listGuid = null by default. Insert the guid of another list to preview items form another location [advanced]
  • listBaseUrl = null by default. Insert the listBaseUrl of another list to preview items form another location [advanced]
Setting up the configuration in the HTML form web part

This section describes how you can set up the configuration in the HTML Form Web Part and not in the calculated columns description field. This is a requirement for SP 2007 and optional for SP 2010 and SP 2013.

var spjs_listItemPreviewConfig = {
	"Preview":{
		"headerCss":"",	
		"clickToView":false,
		"hoverDelay":200,
		"showEditBtn":true,
		"editInDlg":true,
		"hoverIcon":{
			"off":"fa-arrow-circle-o-right",
			"on":"fa-arrow-circle-down",
			"css":"color:#FF6103;"
		},
		"dlgTitleField":"Title",
		"maxHeight":400,
		"maxWidth":600,
		"dateFormat":"dd.MM.yyyy",
		"fields":[
			{"fin":"Title","label":true},
			{"fin":"DateAndTime","label":true},
			{"fin":"Multiline","label":true},
			{"fin":"MyChoiceCol","label":true},
			{"fin":"MultiUser","label":true},
			{"fin":"Lookup","label":true},
			{"fin":"Attachments","label":true,"icon":true}
		],
		"fieldData":{
			"Title":{"disp":{"default":"Title","1044":"Tittel"},"type":"Text"},
			"DateAndTime":{"disp":{"default":"Date / time","1044":"Dato / klokkeslett"},"type":"DateTime"},
			"Multiline":{"disp":{"default":"Multiline","1044":"Flerlinje tekst"},"type":"Text"},
			"MyChoiceCol":{"disp":{"default":"Choice column","1044":"Valgkolonne"},"type":"MultiChoice"},
			"MultiUser":{"disp":{"default":"Multi select people picker","1044":"Flervalgs personvelgerfelt"},"type":"UserMulti"},
			"Lookup":{"disp":{"default":"Lookup","1044":"Oppslag"},"type":"Lookup"},
			"Attachments":{"disp":{"default":"Attachments","1044":"Vedlegg"},"type":"Attachments"}
		},
		"footer":{
			"show":true,
			"css":"",
			"dateFormat":"dd.MM.yyyy hh:mm",
			"strings":{
				"default":["Created at","by","Last modified at","by"],
				"1044":["Opprettet","av","Sist endret","av"]
			}
		},
		"editFormUrl":"/Lists/ListItemPreview3/EditForm.aspx",
		"listGuid":null,
		"listBaseUrl":null
	}
};

As you see, the configuration is mostly the same, but it is defined as an object named “spjs_listItemPreviewConfig” with the configuration for each of the separate calculated columns in it. In this example the FieldInternalName is “Preview”.

When setting up the configuration in the HTML From Web Part you must add the label and the field type for the fields you want to include in the preview. See the “fieldData” key in the code above. As you can see, the text values can be configured for MUI setup, but this will not affect SP 2007 – use the “default” key for the values.

You must provide the relative URL for the EditForm in the “editFormUrl” key. For SP2007 you have to provide the listGuid and the listBaseUrl as well.

Calendar view

In a calendar view you cannot rely on the calculated column to initiate the code for us. For SP 2010 and SP 2013 I have added support for automatically initiating the code, but you must insert two variables in the HTML Form Web Part like this:

var spjs_listItemPreviewFIN = "Preview";
var spjs_listItemPreviewList = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";

“Preview” is the FieldInternalName of the calculated column where the configuration is stored. Change the GUID to the one from your list.

Regarding SP 2007 support for calendar views

I do not have access to a SP 2007 site and cannot ensure it works. If anyone wants to test it and possibly fix it, I’m sure many will be happy. Look at the function “calendar” in the file “PreviewItem.js” to see how I have tried to support month view of a calendar in SP 2007.

In a SP 2007 calendar view, you will have to call the code “manually” from the HTML Form Web Part like this:

<link href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.css" rel="stylesheet">
<script type="text/javascript" src="/Scripts/jQuery-1.10.2.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript" src="/Scripts/PreviewListItem3/PreviewItem.js"></script>
<script type="text/javascript">
$(document).ready(function(){
   spjs.listItemPreview.calendar();
});
</script>
Cross list preview

To be able to do a cross list “preview” you must add the configuration to the HTML Form Web part / CEWP in the “spjs_listItemPreviewConfig” object, and set the “editFormUrl”, “listGuid” and “listBaseUrl” to address the list you want to pull the information from.

You must also supply the ID or another identifier of the item you want to pull the info from in the calculated column in the “iid” attribute in the calculated column.
The default is to lookup an item using the ID for that item. In this case you must have the parent item ID in a column you can use in a calculated column:

="<img iid='"&[TheColumnNameWhereTheIDIsFound]&"' src='/_layouts/images/blank.gif' fin='CrossList' configindescription='0' onload='spjs.listItemPreview.init(this)'>"

* “CrossList” is the FieldInternalName of my calculated column.
* “[TheColumnNameWhereTheIDIsFound]” is the name of the column in the current list item where the ID of the linked item is found.

You can also match the item you want to preview in another column – like the “Standard” column in this example:

="<img iid='Standard:"&Standard&"' src='/_layouts/images/blank.gif' fin='CrossList' configindescription='0' onload='spjs.listItemPreview.init(this)'>"

In the iid attribute in this example I pull the value from the column “Standard” from the current list item, and will match this with the column with the same name in the “lookup list”.

When using this cross list preview you cannot know if the target list will contain an item to preview. If there are no items, the icon will turn gray when you hover over it.

Let me know if you like the solution, have trouble, or find any bugs! – use the comment section below.

Alexander

Multilingual text in a calculated column

Have you ever wanted to have the text in a calculated column translated according to the selected language in a SharePoint sites multilingual user interface?

Lets say you want to have a task list view grouped by the status of the tasks, but the statuses are in Norwegian: Ny, Under behandling and Fullført. For an English, or German speaking person these groups will not make much sense:
IMG

How about having it like this:
IMG

Or like this:
IMG

This is achieved by using a bug(?) in SharePoints handling of calculated fields that return number. We use a HTML image tag with an onload attibute to call a function.

Add a this code to the TOP of the page – ABOVE the list view:

<div id="translateCalcColumn_findAndHide" style="display:none;">This is a dummy used to remove white space above list view</div>
<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
// Remove whitespace from top of list view
$("#translateCalcColumn_findAndHide").parents("td.s4-wpcell-plain").parent().hide();
	
var spjs_translateFilterDisabled = {};	
function spjs_translate(elm){
	var $elm, oVal, a, id, o, l, td, s, v1, v2, v3;
	$elm = $(elm);
	oVal = $elm.attr("val");		
	a = $elm.attr("arr").split("#");
	id = $elm.attr("fin");
	o = {}
	$.each(a,function(i,v1){
		s = v1.split("|");
		$.each(s[1].split(","),function(j,v2){
			if(o[s[0]] === undefined){
				o[s[0]] = {};
			}
			v3 = v2.split(":");
			o[s[0]][v3[0]] = v3[1];	
		});			
	});
	if(o[oVal] !== undefined && o[oVal][g_wsaLCID] !== undefined){
		l = o[oVal][g_wsaLCID];
	}else{
		l = "<span title='Missing translation for LCID "+g_wsaLCID+"' style='color:red;cursor:no-drop;'>"+oVal+"</span>";
	}
	td = $elm.parents("td:first");
	if(spjs_translateFilterDisabled[id] === undefined && $("table.ms-listviewtable").length > 0){
		cIndex = td[0].cellIndex;
		if(!$(td).hasClass("ms-gb") && !$(td).hasClass("ms-gb2")){				
			spjs_translateFilterDisabled[id] = true;
			th = $elm.parents("table.ms-listviewtable").find("th:eq("+cIndex+") div.ms-vh-div");
			th.attr("FilterDisable","TRUE").html(th.text());
		}
	}
	if($(td).attr("class").match("ms-gb") === null){
		$(td).html(l);
	}else{
		$elm.replaceWith(l);
	}
}
</script>

And then add a calculated column to the list using this format:

="<img src='/_layouts/images/loadingcirclests16.gif' onload='spjs_translate(this)' val='"&Status&"' fin='TranslatedStatus' arr='Ny|1033:New,1031:Neue#Under behandling|1033:In Progress,1031:In Bearbeitung#Fullført|1033:Completed,1031:Fertiggestellt'>"

The red text indicates that the original value from the field “Status” is pulled in to the attribute “val” of the image. The green text is the FieldInternalName of the field, but it can be any unique value (must be unique when using multiple translatable calculated columns in one view). The blue text is the translated values in this format:
[The original value option 1]|LCID1:translated text,LCID2:translated text#[The original value option 2]|LCID1:translated text,LCID2:translated text

Please note that for this trick to work, you must set “The data type returned from this formula is: Number (1, 1.0, 100)” in the calculated column.

PS: this trick can be used in DispForm as well, but you must use this “init_fields” function to convert the HTML formatted text into proper HTML as the bug(?) only applies to list views.

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";
				}else if($(this).find("div[id$='TextField_inplacerte']").length>0){
					type=type+"_EHTML";
				}				
			}
			if(type==="SPFieldLookup"){
				if($(this).find("input").length>0){
					type=type+"_Input";
				}
			}
			// HTML Calc
			if(type==='SPFieldCalculated' && $(td).text().match(/(<([^>]+)>)/ig)!==null){
				$(td).html($(td).text());
			}		
			// Build object
			res[fin] = this.parentNode;
			$(res[fin]).attr('FieldDispName',disp);
			$(res[fin]).attr('FieldType',type);
		}		
	});
	return res;
}

Let me know in the comments below if you have trouble, or you get it working.

Alexander

vLookup for SharePoint 2013 v1.5

Change log
September 3, 2014
Changes in v1.730:
Minor update for compatibility with DFFS v4 “direct to backend script”.

June 27, 2014
Changes in v1.723:
Added check for a function named “vlookupChildViewOrEditCallback” to detect open or edit of a child (in dialog only). Add a function with this name, and it will be called when the dialog closes.

April 29, 2014
Changes in v1.721:

  • Fixed a bug from the previous version when using [URL:Key] in the CAML: NOT supplying the URL parameter generated an error.
  • Changed how the debug also alerts the values set in NewForm of the child item when using ?vLookupDebug=1 in the url

April 27, 2014
Changes in v1.720:

  • Fixed first column width if header row is hidden.
  • png icons in SP2013 and fallback to white icon for file types not recognized.
  • Added icons to show the view state of the groups (when using grouping).
  • Added option to use a URL querystring variable in the CAML. Hover over the help icon for the “Build Query” or the “Write your own CAML-query” to learn how to use it.

April 21, 2014
Changes in v1.710:
If you leave all display names in the viewFields section empty, the header row will be hidden.

April 13, 2014
Changes in v1.700:

Please note that there are a number of changes in this version and there will most likely be a few new bugs. You should test it before adding it to a production environment. Let me know if you find a bug, and I’ll fix it as soon as I can manage.
  • Fixed a compatibility issue with “fixQuirkyLookup” in the function “init_vLookupFields”.
  • Added option for selecting whether or not to open “new child” in a dialog.
  • Added “Show all option” when using “Rowlimit”.
  • Added support for grouping the items.
  • When using “Attachments” as viewField you will now get the paperclip in stead of “Yes” or “No” to indicate that an item has attachments.
Please note that you must index the columns you are querying to prevent errors when you reach a total of 5000 items in the list.

April 13. 2014
Changes in v1.651:
Fixed an issue with IE 11 for SP 2013: L_Menu_BaseUrl is undefined.

February 2. 2014
Changes in v1.65:

  • Changed <View Scope=’Recursive’> to <View Scope=’RecursiveAll’> to fix a bug with discussions (that are in fact folders).
  • Added option to refresh all vLookup columns when one has changed.
  • Added option to open links in the same window, and not in a new dialog.
  • Minified the code.

January 04. 2014
Changes in v1.644:

  • Added L_Menu_BaseUrl and L_Menu_LCID to the script for IE 11 compatibility in SP 2013.
  • Wrapped the table cell values in a div for better handling of “ViewField Special configurations CSS”.
  • The “ViewField Special configurations CSS” will also be applied to empty cells.
  • Fixed missing refresh in list view when a vLookup item is added or edited.

December 06. 2013
Changes in v1.640:

  • A new Action: “Comma separated list” is added. See instructions on the help icon for the “Action” field.
  • Custom style now works for all column types and not only text and note columns.
  • Date format can now be specified in the “In URL” section. See the help icon for details.
  • A few small bugfixes.

November 12. 2013
Changes in v1.631:
Child table max-height: changed overflow:scroll > overflow:auto to hide the scrollbar if it is not needed.

November 3. 2013
Changes in v1.63:

  • Added option to pass an URL parameter to the child list NewForm when adding children. Hover over the help icon for instructions.
  • Added option to set the max-height on the child table to have vertical scrollbars.
  • Changed how the page id is calculated when storing and retrieving the configuration. In some cases it came out wrong.

October 11. 2013
Changes in v1.62:
Added the number formatting support for “Action” sum, average and count.

October 11. 2013
Changes in v1.6:
Updated the number formatting support and added separate setting for “thousandsSeparator”. You can now use one or more of the following settings: “decimals”:2,”thousandsSeparator”:”.”,”decimalSeparator”:”,”}

October 06. 2013
Changes in v1.59:

  • Fixed set multichoice column from URL.
  • Added automatic refresh of the vLookup table when editing a child.

September 16. 2013
Changes in v1.56: Fixed a bug regarding showing documents located in sub folders in a document library.

September 10. 2013
Changes in v1.55:

  • Fixed a bug regarding multi choice people pickers.
  • Changed the available “events” you can use.

Interaction / events (put code in the CEWP)

// If defined, this function is called when clicking on "add new child". This function must return true to allow for children, or false to disallow.
function vlookupAddChildPreCheck(fin){
	if(fin==="vLookup_Your_FieldInternalName"){
		// check something and return false if conditions are not met
	}
	return true;
}

// If defined, this function is called for each of the fields set in the child from the "In URL" setting.
function vLookupPresetFromUrlDone(fin){
	if(fin==="FieldInternalName_you_are_setting"){
		// do something
	}
}

// If defined, this function is called when a vLookup column is rendered complete.
function vLookupIsLoadedCallback(fin){
	if(fin==="vLookup_Your_FieldInternalName"){
		// do something
	}
}

// If defined, this function is called when a child is added and you close the dialog with "OK".
function vlookupChildAddedCallback(){
	// Do something
}

// If defined, this function is called when you click "add new child", but close the dialog without saving.
function vlookupChildNotAddedCallback(){
	// Do something
}

August 28. 2013
Changes in v1.53:

  • Fixed bug in multiselect viewfields (checkboxes, multilookup and multiuser)
  • Added rowlimit setting for the query.
  • Changed some ids and variable names in the code.

This solution lets you create relationships between two or more lists based on for example a lookup column connection. You can list all children in the parent element (the children has a lookup column targeting the parent). This is not possible out of the box in SharePoint.

You can also have any kind of dynamic query based on values in the current form. You can for example pull in information from a list based on a selection in a choice column. Please note that this last example will not pull in this information on the fly when changing the selection, but when the form is loaded in DispForm, EditForm or in a list view.

This solution also lets you create new children from the parent (both DispForm, EditForm and in list views), and lets you auto-populate the “connection” to the parent element to ensure a connection.

Image from the GUI
IMG
I have previously posted two articles in this series:
vLookup for SharePoint 2010
vLookup now supports SharePoint 2013

This one is done mainly for compatibility with the Dynamic Forms for SharePoint solution, but I have redone the code and also fixes a few bugs and adds some new features:

  • The creation of the configuration list is now triggered when you first enter the setup. This is done to prevent some rare bugs where the list is not detected onload, thus asking to create it despite it already exists.
  • The heading for number columns are now aligned right (as the values)
  • Fixed bug where exiting the setup did not load the form with its item-ID.
  • Added custom css for the viewFields. Hover over the help icon to the right of the “ViewFields” section for details.
  • Added option to enter a license code to remove the PayPal-logo in the top of the GUI
Used with the Dynamic Forms for SharePoint solution

Refer this solution in the CEWP where you set up the DFFS solution (DispForm and EditForm) like this:

<link type="text/css" href="/Scripts/DFFS/default.css" rel="stylesheet">
<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript" src="/Scripts/vLookup/vLookup.js"></script>
<script type="text/javascript" src="/Scripts/DFFS/dffs.js"></script>
Used as a standalone solution in forms or list views

To use this as a standalone solution, you must do the configuration in the CEWP code like this:

<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript" src="/Scripts/vLookup/vLookup.js"></script>
</script>
First setup

Please note that the setup must be MANUALLY triggered the first time. Set up the scripts, create the vLookup column and then – in DispForm – add this to the URL to create the list:
/Lists/mylist/DispForm.aspx?ID=[an existing ID in your list]&vLookupSetup=1

Refer the previous articles in this series for further details on the setup process.

Download code

Get the code for the file “vLookup.js” here.

How to remove the PayPal-logo / Unlicensed version from the GUI?

In this version I have added support for providing a license code for removing the PayPal-logo and displaying a “Registered to” text in stead. To obtain a license code, go to this page, scroll down to “Dynamic Forms for SharePoint / vLookup for SP201X” and follow the instructions.

As this is JavaScript, there is of course no problem to bypass this license code and manually remove the logo, but please remember that I put in a lot of hours developing these solutions, and I appreciate a few beers now and then.

Please post any bugs or comments below.
Alexander

Remove click events and disable anchor tags in a SP2010 calendar view

I got a request from Larry Pfaff:

…Can all the links be disabled on a calendar view and allow navigation between months for sp 2010?

A bit back and forth between Larry and me produced this code:

<!-- refer jQuery -->
<script type="text/javascript">
 
$(".ms-acal-rootdiv").bind("click dblclick",function(){
            return false;
});
 
setInterval(function(){           
            $(".ms-acal-item [bricked!='1']").each(function(){    
                        $(this).attr("bricked","1").bind("click dblclick",function(){              
                                    return false;
                        });
            });
            // Single day events
            $(".ms-acal-sdiv a").each(function(){
                        var text = $(this).text();
                        $(this).before(text);
                        $(this).remove();
                       
            });
            // Events spanning multiple days
            $(".ms-acal-mdiv a").each(function(){
                        var text = $(this).text();
                        $(this).before(text);
                        $(this).remove();
                       
            });
 
},500);
</script>

Put it in a HTML Form web part – or link it from a CEWP placed below the calendar view.

This code is tested in SP 2010 only.

Alexander

vLookup now supports SharePoint 2013

Change log
June 19. 2013
Changes in v1.47

  • Added {currentSite} as option in “List base URL” to dynamically use the current sites base url. Use this if you plan to save the site as a template.

May 15. 2013
Changes in v1.45

  • Numbers are now aligned right.

May 14. 2013
Changes in v1.44

  • Added formatter for percentage in the viewfields: {“percentage”:true,”suffix”:”%”}
  • Applied “toLocaleString” to numbers that are not formatted as “percentage”.

May 13. 2013
Changes in v1.43

  • Fixed bug related to Dynamic Forms for SharePoint and reloading form when creating a child element.
  • Fixed a bug regarding prefix and suffix for currency and number columns. Suppresses the prefix / suffix if the field is empty.

Added event hooks:

To have something done before a child element is creates, add a function with this name: “vlookupAddChildPreCheck”
This function must return true to procede with the add child action.

To have something happen after a child had successfully been added, add a funcion with this name: “vlookupChildAddedCallback”
This function must return true to procede with the updating of the “vLookup child table” in the form.

To have somethng happen after the vLoolup solution has finished loading the “Add new item button”, add a function with this name: “vLookupIsLoadedCallback”

I have upgraded the vLookup solution to support SharePoint 2013. I have not dissected SP2013 fully, and therefore you might find some bugs regarding the SP2013 support. Please post any bugs or suggestions below, and I’ll address them as soon as I can manage.

Read this article for setup instructions and download instructions

Besides adding SP2013 support, his version also features some new features:

  • Added option to write your own CAML-query freehand
  • Added dateFormat option to specify the returnvalue for a date and tinme column using a string like this: yyyy-MM-dd HH:mm:ss

IMG

Post any comments below,
Alexander

vLookup for SharePoint 2010

16.02.2013 Updated to v1.3 with these changes:

  • Added more options to process the values pulled back from the query. In addition to the previous substring, you can now have prefix and suffix and additional date formatting options. Hover over the help icon to learn more. Please note that you must reconfigure any existing “substring setup” after upgrading to v1.3.
  • Added support for hyperlink columns.
  • Added count to the “Action menu”.
  • Fixed bug with “hide field” when using a text string enclosed in curly braces in the “In URL” configuration.

25.01.2013 Updated to v1.26 Added option to split the string by a specified separator, returning a selected array index.


30.11.2012 I have updated to v1.23 to fix a compatibility issue with Office 365 for SP2013.


18.11.2012 Updated to v1.22 and added some video tutorials:

Please note that this requires v1.22 of vLookup_sp2010.js

08.11.2012 v1.2 fixes some bugs and adds some new features.
Bugs fixed:

  • Removed an extra “/NewForm.aspx” from the url generated in the “addChild” function.
  • When creating a child in a subsite or a parent site, the setting of values from the “parent item” failed due to a wrong baseUrl parameter in the URL.

Features added:

  • As suggested by “advacomp”, added an option to use a text string to set a field in the child list. See instructions on the help icon in the “In URL” section of the setup.
  • Added an option in the GUI to hide the form label in DispForm and EditForm.

16.10.2012 v1.1 fixes a bug with wrong list ID appearing when you edit the configuration from DispForm or EditForm.


I have previously posted a solution for SharePoint 2007, and by request I have rewritten the solution for SharePoint 2010. Please note that this solution will NOT work for any SharePoint version prior to SharePoint 2010.

This solution, as the previous one, supports these “methods”:

  • Show all records: Returns a list view of all the records.
  • Sum: Sums the values for all the records.
  • Average: Sums the values for all the records, and devides the sum on the number of non-empty records.

In the 2007 version, the configuration was setup in a calculated column trough the list settings > edit field.

This new solution is much easier to set up as it has a GUI:
IMG

In this solution, the configuration is stored in a dedicated list – shared by all vLookup columns in the site. This eliminates the fiddly work of hand editing the configuration.

When you first setup this solution in a site, the configuration list is created automatically. This list is not to be hand edited, and you might optionally hide it from browsers trough SharePoint Designer. You must however ensure all users have read access to this list.

You can use this solution in plain list views, grouped views and in DispForm / EditForm (see note bout field type below).

When this solution is setup in a list view, it looks like this:
IMG

DispForm and EditForm:
IMG

IMG
Adding new “Tasks” in EditForm does not refresh the form, only the “vLookup items” – thus you does not lose unsaved data in your EditForm.

How to set up the solution
  1. Download the code for the file “vLookup_sp2010.js” from here.
  2. Download the file “spjs-utility.js” from here. Ensure you get the latest version (by date of the folder).
  3. If you prefer a local copy of jQuery, get it here.
  4. Upload this files to a library in your SharePoint site collection, or to a folder in the root site – created using SharePoint Designer.
  5. Add a column to your list / library where the FieldInternalName starts with “vLookup”. This is what triggers the solution. Note that it’s the FieldInternalName and not the Display name that must match.

    You can use both a calculated column or a single line of text column as “placeholder” for the “vLookup items”. If you use a calculated column, use a formula like this:

    =""

    The contents of the field will be overwritten anyway.

    To be able to have the solution work in EditForm, you must use a single line of text column as “vLookup field”. Note that you cannot use this field to store anything as the contents will be overwritten by the vLookup solution.
  6. Add a HTML Form Web Part to the bottom of your list view, DispForm or EditForm, and add this code trough the “Source editor”:

    <script type="text/javascript" src="/test/vLookupv2/Script/jquery-1.7.2.min.js"></script>
    <script type="text/javascript" src="/test/vLookupv2/Script/spjs-utility.js"></script>
    <script type="text/javascript" src="/test/vLookupv2/Script/vLookup/vLookup_sp2010.js"></script>

    IMG

    You use the exact same code for list views and for list forms.

Creating the configuration list

The first time you set up the solution, you will get this prompt:
IMG

Hit “OK” to create the list:
IMG

You will now get this prompt (provided your field is in the view):
IMG

You will now be presented with the configuration GUI:
IMG

This GUI is automatically presented while the configuration has not been created, or it contains errors that are detected by this solution. To reenter the configuration at a later time, add “vLookupSetup=1” to the URL like this:

// List view
.../Lists/MyList/AllItems.aspx?vLookupSetup=1
// DispForm
.../Lists/MyList/DispForm.aspx?ID=12&vLookupSetup=1

All the configuration option are explained by hovering over the question mark.

Creating new items in the “child list”

When you add items to the child list, and want to prefill values in your NewForm, you use the In URL section of the configuration to set the “from” and “to” fields.

You must add the same code to a HTML form web part below the form web part in NewForm as you have used in DispForm and list views. This code will pull in the values from the “parent list” and fill in the “child list”.

If you are “connecting” the two lists by a lookup column, ensure you prefill the lookup column like in the example in the top of this article.

MUI support

This solution supports MUI and you can set labels and column headers in multiple languages. Use “default” as “LCID” to have a default value and/or set up the correct LCID (matching your installed language packs) as alternate values.

Do you use this solution?
I spend a lot of time developing these free solutions. If you use it, please hit the PayPal button to send me a few bucks as motivation for releasing new solutions.

Show field description in list view column header – updated version

I have previously posted this solution that lets you add the field description to the list view column header.

By request I have updated the solution to work in a web part page with multiple lists / libraries.

Put this code in a Content Editor Web Part at the bottom of your list view or web part page:

&lt;script type=&quot;text/javascript&quot; src=&quot;http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
var is2010 = typeof(_fV4UI)!=='undefined';

$(&quot;div[id^='WebPartWPQ']&quot;).each(function(){	
	var wpID, tCtxId, tCtx, myTooltipObj, toFind, fieldDisplayName
	wpID = $(this).attr('WebPartID').toUpperCase();	
	tCtxId = g_ViewIdToViewCounterMap[&quot;{&quot;+wpID+&quot;}&quot;];
	if(tCtxId!==undefined){
		tCtx = eval(&quot;(ctx&quot;+tCtxId+&quot;)&quot;);	
		myTooltipObj = customGetList(tCtx.listName);	
		toFind = &quot;table.ms-listviewtable th&quot;;
		if(is2010){
			toFind = &quot;div.ms-vh-div&quot;;
		}		
		$(this).find(toFind).each(function(){
			if(is2010){
				fieldDisplayName = $(this).attr('DisplayName');	
			}else{
				fieldDisplayName = $(this).find(&quot;table:first&quot;).attr('DisplayName');	
			}
			if(fieldDisplayName===undefined){
				fieldDisplayName = $(this).text();
			}
			if(myTooltipObj[fieldDisplayName]!==undefined){
				$(this).attr('title',myTooltipObj[fieldDisplayName]).find('a').attr('title',myTooltipObj[fieldDisplayName]);;
			}
		});
	}
});

function customGetList(listName){
	var xmlWrap, result;
	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('&lt;GetList xmlns=&quot;http://schemas.microsoft.com/sharepoint/soap/&quot;&gt;');
	xmlWrap.push('&lt;listName&gt;' + listName + '&lt;/listName&gt;');
	xmlWrap.push('&lt;/GetList&gt;');
	xmlWrap.push(&quot;&lt;/soap:Body&gt;&quot;);
	xmlWrap.push(&quot;&lt;/soap:Envelope&gt;&quot;);
	result = {};
	$.ajax({
		async:false,
		type:&quot;POST&quot;,
		url:L_Menu_BaseUrl + '/_vti_bin/lists.asmx',
		contentType:&quot;text/xml; charset=utf-8&quot;,
		processData:false,
		data:xmlWrap.join(''),
		dataType:&quot;xml&quot;,
		beforeSend:function(xhr){
			xhr.setRequestHeader('SOAPAction','http://schemas.microsoft.com/sharepoint/soap/GetList');
		},
		success:function(data){
			$('Field', data).each(function(i){
				if(result[$(this).attr('DisplayName')]===undefined || result[$(this).attr('DisplayName')]===''){
					result[$(this).attr('DisplayName')] = ($(this).attr('Description')===undefined)?'':$(this).attr('Description');
				}
			});
		}
	});
	return result;
}
&lt;/script&gt;

If you like this solution, buy me a beer!

Alexander