All posts by Alexander Bautz

SPJS Charts for SharePoint v4

SPJS Charts for SharePoint v4: Generate filter values with a function

43 Comments

This post will describe how you can generate the filter values for a chart dynamically in SPJS Charts for SharePoint v4 using a function in the Master Web Part CEWP code.

This is something you will have to do if you want to filter a chart based on a column value that has dynamic contents and you therefore cannot use a static filter set in the chart options.

Step 1
Go to the SPJS Charts for SharePoint v4: Master Web Part, and set the variable “allowEval” to true:

<div class="spjs_chartPlaceholder_master"></div>
<script type="text/javascript">
// Load the Google Visualization API release candidate
var loadRC = false;
// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = true;

var spjs_chart_license = "XXXX-XXXXXXXXXXXXXXXXXXX";
</script>

Step 2
Add the code for creating the filter to the same CEWP:

<script type="text/javascript">
function spjs_getFilterValue(list,field){
	var q = "<Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where><OrderBy><FieldRef Name='"+field+"' /></OrderBy>";
	var res = spjs.charts.qItems({"listName":list,"query":q,"viewFields":[field]});
	var b = [];
	var fObj = {};
	$.each(res.items,function(i,item){
		if(fObj[item[field]] === undefined){
			fObj[item[field]] = 1;
			b.push({"f":""+item[field]+"","v":""+item[field]});
		}
	});
	return b;
}
var myCustomFilterOptions = spjs_getFilterValue("Poll","Answer");
</script>

This example queries the list “Poll” for the unique values in the column “Answer”. You can use any code you like to obtain these values, as long as the result is formatted like an array with this syntax:

[{"f":"Value 1","v":"Value 1"},{"f":"Value 2","v":"Value 2"}]

The “f” property is the “friendly name” that is shown in the dropdown select. This can be modified to make sense for the user. The “v” property is the value you expect to find in the database. For further instructions look at the “instructions” above the filter textarea in the “Edit chart GUI”.

Step 3
Use the variable “myCustomFilterOptions” in the filter textarea in the “Edit chart GUI”:

[{"label":"Answer","urlKey":"Answer","multiselect":false,"size":3,"options":myCustomFilterOptions}]

And use this CAML:

<Where><Eq><FieldRef Name='Answer' /><Value Type='Text'>{filter:Answer}</Value></Eq></Where>

Ask if anything is unclear.
Alexander

SPJS Charts for SharePoint v4

SPJS Charts for SharePoint v4: chartOptionOverride

5 Comments

This function is used in SPJS Charts for SharePoint v4 to override / intercept the chart options before the chart is drawn. You must inset this function in the Master CEWP code for it to be available when the chart is about to be drawn.

This example shows how you can color the series based on the values.

First without spjs_chartOptionOverride:
IMG

The override code:

function spjs_chartOptionOverride(chartId,data,options){
	// Color code the series based on the value
	if(chartId === "2ad99c9f-affd-4583-8146-54b03af7fa45"){
		var red = '#ff0000', green = '#00ff00', yellow = '#ffff00', value, i, j;
		options.colors = [];	
		for(i = 1; i < data.getNumberOfColumns(); i++){
			value = 0;
			for(j = 0; j < data.getNumberOfRows(); j++){
				if(data.getValue(j, i) !== null){
					value += data.getValue(j, i);
				}
			}
			options.colors.push(value > 10 ? red : (value > 5 ? yellow : green));
		}
	}
	return options;
}

Please note that this override will only affect the chart with ID “2ad99c9f-affd-4583-8146-54b03af7fa45”.

Result:
IMG

Ask if anything is unclear,
Alexander

Charts, SPJS Charts for SharePoint v4

SPJS Charts for SharePoint v4

213 Comments

SPJS Charts for SharePoint v5 is finally here

Change log
February 06. 2014
v4.14:
Another fix for scenarios where you have an initial filter that returns data for these columns:
“apples”, “oranges”, “bananas”You then filter the chart and get data in these columns only:
“apples”, “bananas”

You will then get an error
“Invalid column index 3. Should be an integer in the range [0-2]”

January 26. 2014
v4.11:

  • Removed the “pageId” prefix for charts stored in the configuration list. This means you now can rename web part pages containing charts without “loosing the chart”. There will be no problems with existing charts.
  • Bugfix for the following scenario: You have a chart with a filter, and you load the chart initially with a filter that returns no items. You then change the filter and select “all”. You may end up with data plotted in the wrong column / series.
  • Fixed the “Visible columns” for table charts.
  • Added option to show the data label in the column (you find it under advanced options):
    IMG
  • Minified the code.

December 7. 2013
v4.05:

  • Fixed “Consume filter from list view” where the filter value had & in it.
  • Fixed the L_Menu_BaseUrl override in SP2013 as this came out wrong in the root site.

November 7. 2013
v4.03:

  • Fixed small bug where”No items found” is repeated if you filter the chart and you get 0 items returned two or more consecutive queries.
  • Org chart now supports this format in the first column: 
    {"v":"Alexander","f":"Alexander<div style='color:red;font-style:italic;'>SharePoint JavaScripts</div>"}

October 8. 2013
v4.01: Fixes small bug with the “Filtering…” overlay when using “filterAdditionalCharts”.

October 5. 2013
Updated the CEWP code for the Master Web Part as some users had problems loading the solution. One of the closing script tags had disappeared from the code, and this is most likely the cause. I have also moved jQuery above the JSAPI as some users fixed the problems this way.

This is v4 of SPJS Charts for SharePoint. You find the previous articles here, but please note that examples and techniques may have changed in v4.

This version, as the previous ones, lets you create charts from data stored in any lists in the site collection, and add them to any SharePoint page. The charts are created using Google Visualization API.

I have redone the script in v4, and added some improvements like support for animations, automatic refresh of the chart when the datasource is updated, and more. You find a full change log below.

This solution is still entirely client side – nothing is installed on the server.
Changelog
  • Major overhaul of the code.
  • Changed how the chart containers are named. Previously you had to name them in the CEWP, but now this is done automatically.
  • Its now much easier to add multiple charts to a page as you do not have to edit the code, just add another web part to the page.
  • Bypassed rowlimit for list views to overcome the “paging limit” when using a SharePoint view (and not Custom CAML).
  • Added timeline chart. Please note that there are some bugs in this charts tooltip in IE 10. Hopefully this will be fixed by Google in the next release.
  • Fixed preselecting multiple filter values.
  • Added support for automatically setting up filter for all single line of text columns, calculated columns, choice columns and single choice people picker columns.
  • Added support for animations when loading and filtering.
  • Added support for auto refresh of the chart when the datasource is updates.
  • The last good configuration is automatically stored. You can revert to this if there are errors in the config preventing you from loading it.
  • Added support for password protection of individual charts.
  • Added support for copying, importing and exporting chart configuration (requires licensed version).
  • Added support for setting the series color by label. Like “In progress” = green, “Deferred” = yellow and “Not started” = red. Click “Custom options” at bottom of the Options sections in Edit Chart GUI for instructions.
  • And lots of small fixes.
Deprecated features
  • The “chartOptionOverride” object has been replaced with the function “spjs_chartOptionOverride”. See details on the new function in a separate post.
  • The “prefix” for “sum”, “count” and “average” has been removed. Use numberformat: prefix in stead.
Upgrading from previous version

This solution is NOT directly compatible with previous versions (1.x, 2.x and 3.x). You must set up a new configuration list and modify all existing chart containers with the new code. When this is done, you can import your chart one-by-one from the old configuration list.

Please note that the import feature require a licensed version of SPJS Charts for SharePoint. Click here to buy a license code to unlock the import / export and copy features.

Why is it not compatible with previous versions?

The reason for not making this version backwards compatible has to do with the completely different way the configuration is stored in the configuration list, and the fact that the chart containers are no longer named in the CEWP code. The chart containers now get their unique id assigned automatically. This is done to make putting multiple charts in one page as easy as adding another web part.

Follow these steps to set up the solution
  1. Download jQuery 1.10.x from here. You can use v1.6.4 and up, but you should stay with the 1.x version as 2.x is NOT compatible with Internet Explorer 6, 7 and 8.
  2. Download spjs-charts-v4.js from here.
  3. Create a document library to hold the scripts. You must ensure all users have read access to this library. You can also create a folder using SharePoint designer.
  4. Upload the files to this library / folder.
  5. Create a text file and add this code – ensure you modify the script “src” for “jquery-1.10.2.min.js” and “spjs-charts-v4.js” to point to the location you put your files: 
    <div class="spjs_chartPlaceholder_master"></div>
<script type="text/javascript">
// Set this to true to load the Google Visualization API release candidate
var loadRC = false;
// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = false;
</script>
<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="/Scripts/Charts/spjs-charts-v4.js"></script>
  6. Upload the text file to the same location you put the other files.
  7. Add a CEWP to the page where you want the chart to appear, and use the “Content link” option to link to the text file with the CEWP code from step 5. You can skip step 6 and use a HTML Form Web Part and add the code in the Source editor, but I recommend using the CEWP and content link option to be able to update the code for all charts in one centralized location.
    Please note that you can NOT add the “master code” directly to a CEWP source editor. Either use the content link option in the CEWP to link to a file with the code, or use a HTML form web part. This is necessary because SharePoint messes with the code by appending the script generated contents to the CEWP when you edit the page.
  8. Reload the page and click OK on these two dialogs:
    IMG
    IMG
  9. Finished!

If you want to override the loading animation, the filtering label or the “No items found” label, add these variables to the CEWP code:

var spjs_charts_loadingOverlayLabel = "Loading chart...";
var spjs_charts_filteringLabel = "Filtering, please wait...";
var spjs_charts_noItemsFoundLabel = "No items match this filter...";
Configuring your first chart

The placeholder for the chart is indicated with a little gray downarrow where you put the CEWP. Click this image to load the configuration GUI. Here is an example chart with the configuration:
IMG
IMG

Multiple charts in the same page

In this version you basically have two different “Web parts”: The MASTER with the code referred in step 5 above, which calls the script resources, and the SLAVE which is simply any HTML DOM element with the class “spjs_chartPlaceholder”. This element can be for example a div, span or td. You can add it inline in the text, or set it up in a table structure.

The CEWP code for a SLAVE chart 

<div class="spjs_chartPlaceholder"></div>
Please note that you can NOT add the “slave code” directly to a CEWP source editor. Either use the content link option in the CEWP to link to a file with the code, or use a HTML form web part. This is necessary because SharePoint messes with the code by appending the script generated contents to the CEWP when you edit the page.

You can add one Master Web Part to a page, and unlimited number of Slave Web Parts (in theory, buy your page will load slowly if you have to many charts in one page).

See separate article for instructions on how to create Web Part templates in the Web Part Gallery.

Placeholders in edit page mode

When entering edit page mode, the placeholders are highlighted like this:
IMG

Copy an existing chart

If you have the licensed version, you can copy existing charts (from v4) in two ways.
Method 1
IMG
IMG

Method 2:
Go to the chart you want to copy, select “Export configuration as text”:
IMG
IMG

Copy the text and then use “Import configuration from text” and paste it back in the new location.

Import charts from v3

If you have the licensed version, you can import existing charts (from v3) like this:
IMG
IMG

You find other articles in this series here: https://spjsblog.com/category/SPJS-Charts-for-SharePoint-v4

Do you want to buy a license code?

Click here to to learn how to obtain a license code https://spjsblog.com/license-code.

Ask if anything is unclear,
Alexander

DFFS Plugin, Form modification, Javascript, SharePoint 2007, SharePoint 2010, SharePoint 2013, Utilities

Convert text fields to cascading dropdowns – unlimited number of levels

114 Comments

You find updated documentation here

Change log
August 21, 2015
Changes in v3.524

  • Fixed a bug when using single quote in the items used in the dropdown menu.

July 13, 2015
Changes in v3.523

  • Fixed issue with :multi and hide empty dropdowns not hiding the correct elements.
  • Fixed issue with :multi and autofill single option not properly selecting the items.

May 06, 2015
Changes in v3.520

  • Fixed a bug that prevented the solution from working if you did not have spjs-utility.js loaded in the page where you used the cascading dropdowns.
  • Made some changes to the debug output. The debug is now show directly in the page so there is no need to open the developer console.

April 15, 2015
Changes in v3.510

January 19, 2015
Changes in v3.30

  • Added support for autofilling values when using “Autofill subsequent dropdowns when they contain only one valid option.” in DFFS.
  • Changed the jQuery variable from $ to spjs.$ to fix a bug with SharePoints internal file “assetpicker.js” as this one kills jQuery.

September 4. 2014
Changes in v3.27
Fixed an issue where DFFS triggers on subsequent dropdowns would not trigger when the “Autofill subsequent dropdowns when they contain only one valid option” is checked (and the options are autofilled).

September 3. 2014
Changes in v3.26
Added “lookupListBaseUrl” as option in the argument to the function. This is used to lookup from a list that is not located in the same site (but in the same site collection). If you are using DFFS v4 you must update to this version.

June 10. 2014
Changes in v3.25
Skips the loading of the script if it is loaded in DispForm. This will prevent some errors when using this solution with DFFS and you are cloning settings from NewForm or EditForm.

June 7. 2014
Changes in v3.24
Added autofill of subsequent dropdowns if the field has a default value.

March 1. 2014
Changes in v3.22
Added new switches: “autoselectSingleOption” and “clearInvalidSelection”. See description below.

February 20. 2014
Changes in v3.21
Added autoselect if only one option is found.

August 29. 2013
I had forgotten a console.log in v3.1, Please download it again.
August 28. 2013
Changes in v3.1
Fixed a bug that prevents getting more then 30 items for each dropdown.

This solution lets you convert multiple single line textfields to cascading dropdowns populated by a query into another list that holds the data.

I’m reusing these two images from a previous article in this series to demonstrate the source list configuration and the target list appearance after the solution is initiated:
IMG

From v3.510 you can use multiselect in all the dropdowns.

IMG

I have previously posted two solutions in this series:
Cascading dropdowns
Cascading dropdowns in SharePoint text fields – populated by lookup in another list (version 2)

This is the third revision done mainly for compatibility with the Dynamic Forms for SharePoint solution, but it can also be used as a standalone solution. The earlier solutions had a 5-level limit, but this one in unlimited.

Used with the Dynamic Forms for SharePoint solution

Refer this solution in the CEWP where you set up the DFFS solution 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/CascadingDropdowns/SPJS-Casc_min.js"></script>
<script type="text/javascript" src="/Scripts/DFFS/dffs.js"></script>

You will now find the configuration for the solution as a separate tab in the DFFS GUI:
IMG

Please note that you will not find this tab when configuring DispForm, and that this will require version 3.1 or later of the DFFS solution.
Used as a standalone solution

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/CascadingDropdowns/CascadingDropdowns.js"></script>
<script type="text/javascript">
spjs.casc.init({
	lookupList:"Cars",
	lookupListBaseUrl:L_Menu_BaseUrl,
	lookupListFields:["Make","Model"],
	thisListFields:["CarMake","CarModel"],
	dropDownDefaultvalue:"select",
	hideEmptyDropdowns:false,
	autoselectSingleOption:true,
	clearInvalidSelection:true,
	debug:false
});
</script>
Please note that the CEWP must be placed below the list view web part.
Parameters explained

lookupList: List name or GUID of the list that holds the values to populate the dropdowns.
lookupListBaseUrl: Base URL of the site where the list is located. This is NOT the URL of the list itself.
lookupListFields: The FieldInternalNames of the fields in the above list.
thisListFields: The FieldInternalNames of the fields to convert to dropdowns.
dropDownDefaultvalue: The value to display in the dropdown select when no items has been selected.
hideEmptyDropdowns: Determines whether or not to hide dropdowns with no available options.
autoselectSingleOption: If the next dropdown contains only one option it will be automatically selected.
clearInvalidSelection: If you open a list item in EditForm and the original selection no longer is a valid selection, you can decide whether to clear it or keep it.
debug: Used to troubleshoot the setup. You must have the developer console activated to read the debug information. While this is “true”, the single line of text field will show below the dropdown select.

The length of the arrays “lookupListFields” and “thisListFields” must be the same.

Using multi select

Read this article to learn how to use the multi select option for the fields.

Download code

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

Please support the development of this solution by hitting the “beer button” in the top right corner of this page to buy me a few beers.

Have fun,
Alexander

DFFS Plugin, Form modification, Javascript, List view modification, SharePoint 2010, SharePoint 2013, Utilities, vLookup

vLookup for SharePoint 2013 v1.5

200 Comments
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

Javascript, SharePoint 2007, SharePoint 2010, SharePoint 2013, Utilities

spjs-utility.js v1.17

13 Comments
Change log
January 29. 2014
v1.179:
Added option to clear a people picker in SP 2013 using setFieldValue – specifying an empty string as new value.

November 24. 2013
v1.178:
Added option to pass scope to the function “spjs_QueryItems”. Use “scope”:”Recursive” to search all items without folders.

October 30. 2013
v1.177:
Fixed bug in init_fields_v2() regarding converting HTML formatted text in a calculated column to proper HTML.
getUserInfo_v2: customProp

October 23. 2013
v1.175
Radio: setFieldValue will clear choices first. This mean you can set a radio choice field to “null” by passing an invalid value – like a blank string.
Calculated html converted in init_fields_v2()

September 13. 2013
In v1.173:

  • Added option to set a single choice Lookup column rendered as a <select> by ID.
  • This affects lookup columns with <20 items in IE, and all lookup columns in other browsers.
  • Changed the method of setting lookup colums to ensure they first try to match by TEXT and them by ID.

August 21. 2013
I had a mixup in the files and uploaded v1.17 in stead of v1.171. You will have to download v1.171 again. Sorry for the inconvenience.

August 20. 2013
In v1.171 i have fixed a typo preventing you from getting the loginName when using it to get the value from a people picker field in SP2013. I have also changed how getFieldValue handles people pickers in SP2013 by using the built in methods to get the field value.

When using getFieldValue to get a people picker value on page load, you must wrap the call like this:

ExecuteOrDelayUntilScriptLoaded(function(){
	var myPeoplePickerValue = getFieldValue("MyPeoplePickersFieldInternalName","","; ","loginName");
	alert(myPeoplePickerValue);
},'sp.ribbon.js');

I have updated spjs-utility.js to v1.17 with these changes:

  • Fixed compatibility issue in SP2013 with RTE fields and people picker fields.
  • Added support for getting and setting “Managed metadata fields” or “SPFieldTaxonomyFieldType” and “SPFieldTaxonomyFieldTypeMulti”.

This file is used in multiple solutions – like this one.

Alexander

Form modification, Javascript, SharePoint 2007, SharePoint 2010, SharePoint 2013, Tabs, Utilities

Dynamic Forms for SharePoint v3

767 Comments
Change log
Always test a new version to see that it works as expected before adding it to a production environment.

June 19. 2014
v3.352 has these changes:
Fixed the following that went missing in v3.350:

  • Added back the content type choice as trigger and selectable field.
  • Added back the file name field as trigger and selectable field.
  • Added back the additional lookup column fields to the DispForm configuration.

June 10. 2014
v3.351 has these changes:

  • fixQuirkyLookup: Added title attribute to the select for compatibility with SPServices
  • Added back the Attachment field as this had disappeared from the field select in the tabs in v3.350.

June 7. 2014
v3.350 has these changes:

  • The method for reading the fields for the list in the configuration GUI has changed. I now query the list schema for the fields rather than finding them from the current form. This means that you can now configure DFFS for a form with content types without having to add all fields to the default content type. Please note that only the fields in the active content type will be available to the script when NewForm, DispForm or EditForm is rendered.
  • Lookup columns containing more than 20 items in SP 2007 / 2010 using Internet Explorer (quirky lookup columns) can now be used as triggeres. If you are NOT using SPServices cascading dropdown you must tick “Fix quirky lookup columns” in the Misc tab.
  • Triggers are now organized in more “optgroups” to clarify how they trigger (on load, on save etc.)
  • The “doSetFieldValue” function have been changed to support setting Cascading dropdown value using the “Set field value” action in the rules. Please update the cascading dropdown plugin to v3.24 or above.
  • You can now manually specify the location of the DFFS configuration list. This may come in handy when using DFFS in a list template. See separate heading in the bottom of this article.

May 8. 2014
v3.343 has these changes:

  • Changed how the plugin tabs are shown if the plugin is not loaded: the tab will contain a link to download it.
  • Changed how the configuration is done for the resource management plugin.
    Setup is now done in a separate tab and you must remove any configuration from the Custom JS section in the Misc tab.

May 1. 2014
v3.342 has these changes:

  • Changed how the SPFieldMultiChoice events are triggered. It was initially on “click” and now it is on “change”.
  • Added “is changed” trigger on SPFieldChoice, SPFieldMultiChoice and SPFieldBoolean.
  • Changed a little bit on the “is changed” comparison to catch change BACK TO what was the initial value when the form was loaded.

April 25. 2014
v3.341 has these changes:

  • Navtabs now operates in a circular motion: When passing the last tab, it will start from the first tab again.
  • Added check to see if the attachment field in not part of any tab. If so, the attachments will show in all tab.
  • Added option to disable individual rules by a checkbox in the top right corner of each rule.

Only “dffs_min.js” is updated.

April 20. 2014
v3.340 has these changes:

  • Fixed setQuirkyLookup so that getFieldValue will pick up the correct selection if you use it in PreSaveAction.
  • Added support for the new version of SPJS Resource Management: http://wp.me/p3eibN-1sv
  • Moved password to Misc tab.
  • Displayed the plugin version used for the various plugins like vLookup, Cascading Dropdowns etc.

April 7. 2014
v3.336 has these changes:
Changed the offset of the custom edit item image in the ribbon for SP2013 as Microsoft uses different image maps in 365 and on premises install of SharePoint – resulting in the wrong image being displayed.

April 3. 2014
v3.335 has these changes:
Fixed bug related to “orphans tab” not working after update in v3.333. Please note that the id of the tab <lt> has changed from “tab_orphans” to “dffs_tab_999”. This affects any custom code you may have used to style or attach events to this tab.

March 22. 2014
v3.334 has these changes:
Changed an alert for when you are trying to hide the active tag by a rule.

March 21. 2014
v3.333 has these changes:

  • Fixed compatibility with SP 2007 and IE8 running on Windows XP.
  • Changed / fixed tab selection by url (?sTab=X), by cookie or when one or more tabs are hidden by rules.
  • Fixed a bug in showing field description with readonly fields when the field description has a trailing <br>.
  • Fixed a bug with using “selected tab index” as trigger in rules (the selected tab index was not always updated on click.

March 17. 2014
v3.330 has these changes:
Fixed a bug from v3.329 where the form was hidden if you had not configured tabs.

March 16. 2014
v3.329 has these changes:
Fixed initial tab selection if the primary tab is hidden by a rule. In this scenario you would have no selected tab, but the form would still be visible. The code will now try to select the next visible tab. If no tabs can be selected, the form i hidden.

March 15. 2014
v3.328 has these changes:

  • Fixed “Selected tab index” in Misc tab. This was disabled by the cookie introduced in v3.320. The selection will still be remembered by the cookie, but on the first visit to this tab in this browser session it will preselect the tab from the Misc tab.
  • Added option in the Misc tab to control the use of cookie for remembering the selected tab.
  • Disabled the “selected tab” cookie for NewForm.

v3.326 has these changes:
Changed dffs_PreSaveAction() to be able to return false to abort the save process. Returning true or nothing will proceed with the save.

v3.325 has these changes:

  • If there is a function “dffs_Ready()” in the CEWP or in the Custom JS, this will be called when the DFFS solution has finished loading.
  • Added “is changed from initial value” trigger to choice columns as this was missing.
  • Added MUI support to the tab navigation buttons.

March 5. 2014
v3.323 has these changes:
Fixed a random error message: [DFFS: spjs.dffs.loadAndApplyRules]: Configuration error.

March 4. 2014
v3.322 has these changes:
Added back the missing “extra” edit item button to the left of the “Cancel” button. This had disappeared in v3.20.

v3.321 has these changes:
Removed a trailing comma in an object (introduced in v 3.32) that broke the script in IE8.

March 1. 2014
v3.32 has these changes:
Fixed

  • Error in description for the trigger “If logged in user is in group with ID”.
  • Duplicated edit button in some situations.

Enhancements

  • Added alert to catch use of “And these rule indexes are true” looking at a rule that has a higher index than the current rule.
  • Added option to turn on/off the “Fix quirky lookups” feature introduced in v3.3. You find the option in the Misc tab.

New features

  • Added a cookie to remember selected tab trough page refresh.
  • Added new trigger “is changed” for text / number fields. This triggers on any change in the field. The existing trigger with the same name has been renamed to “is changed from initial value” (existing configurations will be automatically updated).
  • New config options for cascading dropdowns: Autofill and Clear invalid. Please note that you must update the cascading dropdown solution separately for the new features to be supported.
  • Added options to show navigation buttons to switch tabs. You find the setting in the Misc tab.

February 17. 2014
v3.315 has these changes:

  • Added “Run these functions” feature to DispForm.

February 13. 2014
v3.314 has these changes:
Attempted to fix an issue with having multiple triggers on “Selected tab index”, where only the last rule applied.

January 29. 2014
v3.313 has these changes:
Fixed a bug in “setQuirkyLookup” for SP2007 as the id of the hidden input field contained characters that needed to be escaped.

January 25. 2014
v3.312 has these changes:

  • Small fix for dialog size when adding attachments.
  • Triggering on date fields now properly supports “Equal to” = [empty string].
  • Fixed bug that halted the script when using ID as trigger in EditForm.
  • Fix for timing issue in SP 2013 that resulted in triggers not picking up the correct value.
  • Added a handful of missing semicolons and minified the code using packer by Dean Edwards.

You can now use the minified version of dffs.js. Pick it up in the download section.

January 21. 2014
v3.311 has these changes:

  • Removed check for fields that were set as required in the list settings as this produced an error in “spjs.dffs.clear_Mandatory”.
  • Fix for SP 2007 compatibility for the “Fixes the quirky dropdown select” added in v3.30.
  • Attempted to fix the randomly missing scrollbar issue in dialog boxes.

January 4. 2014
v3.31 has these changes:

  • Added option to fix the dialog to the top of the screen to prevent it from jumping around when you change tabs. This applies to SP 2010 / 2013 and can be found in the Misc tab.
  • Addressed an issue with spjs.dffs.clear_Mandatory and fields set as requires in the list settings.

December 21. 2013
v3.3 has these changes:

  • Added operator “is changed”. This is used to trigger a rule if the field value has changed after the form was loaded.
  • Added trigger “Compare logged in user with people picker field”. This can be used to trigger a rule if the logged in user is or is not selected in a given people picker field.
  • Fixed tooltip left adjustment to prevent the text form rendering outside the screen when using side-by-side.
  • Added z-index:1001 to the tooltip container to fix a rendering issue in SP2013 where some elements shined trough the container.
  • Tried to fix an issue with setting a people picker to readonly in SP2013 and getting a “field is not ready” error. I could not reproduce this error and therefore cannot promise this will fix it.
  • Fixes the quirky dropdown select in Internet Exploree used for a single lookup column when there are more than 20 items in the lookup source list (SP2007 and SP2010).

You must update both the “DynamicFormsForSharePoint_v3.js” and the “default.css”.

Please note
I no longer have access to a SP 2007 environment and therefore I cannot guarantee compatibility with SP 2007. If you use this solution with SP 2007 you must add this variable to the page for compatibility with v3.3+:

var _spPageContextInfo = {"pageListId":"{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"};

You must change the string “xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx” with the list GUID of the current list. You should be able to find it by viewing the page source and searching for “ctx.Listname” (SP 2007).

November 28. 2013
v3.262 has these changes:
Fixed bug with attachments due to a function changing the id “idAttachmentsRow”.

v3.261 has these changes:
Fixed a bug in v3.26 regarding L_Menu_BaseUrl.

November 24. 2013
v3.26 has these changes:
Added a check for a function named “dffs_PreSaveAction” when saving the form. Use this function to add “pre save code” like this (the function must NOT return anything):
function dffs_PreSaveAction(){
// do stuff before saving
}
November 21. 2013
v3.25 has these changes:
Yet another fix for detecting folder content types and summary tasks in a tasks list.

November 17. 2013
v3.23 has these changes:

  • Better detection of “New folder” to prevent the DFFS code from triggering.
  • Tooltip now supports vLookup columns that use “Hide form label”.
  • Set field value now updates the value in readonly fields.

November 3. 2013
v3.222 has these changes:

  • Removed the paperclip in front of the “Attachments” formlabel as adding attachments is not possible in DispForm.
  • Fixed undefined variable “isNewForm” > “spjs.dffs.data.isNewForm”.
  • Added description to the tooltip section: If you use HTML code, do not add line feeds using Enter, add HTML code as a continuous string.
  • Hide save item btn now works both ways: No = show button.
  • Added option to hide edit item btn in DispForm. This works like the “Hide save item button”.

October 31. 2013
v3.221 has these changes:

  • Fixed unnecessary escaping og double quotes in object formatted strings (bug introduced in 3.22).
  • Fixed missing tabs in SP2013 Document library.
  • Fixed bug where sometimes the config is not recognized due to wrong case in the URL.

October 28. 2013
v3.22 has these changes:
Replaced a custom stringify method with the standard JSON.stringify. This because the custom method failed in some rare cases. This change will affect SP2007 when using IE. Because the master page forces Document Mode: Quirks, you must use another browser, or force the Document mode to IE8 using the developer console.

October 27. 2013
v3.21 has these changes:

  • Edit item button will not show in DispForm if the user does not have edit access.
  • Set Field Value now supports all available properties from the user list in SharePoint (not the user profile service). Use it like this: {currentUser:Prop}.

October 21. 2013
v3.20 has these changes:

  • Side-by-side: Label position can now be set to “Hidden”, “Top” or “Left”.
  • You can add attachments by clicking the paperclip in front of the Attachments label.
  • Added id attribute to the table row in this format: dffs_[FieldInternalName]. You can use this to address individual fields using custom code.
  • Comparing field values against other fields in the form is now supported for number columns (and text columns containing numbers)
  • Fixed an issue with “Set field value” using a value pulled from another field when the first character is not “{“.
  • “Set field value”: {timestamp} – see details below.
  • “Set field value”: {currentUser} and {currentUserEmail} – see details below.
  • “Set field value”: Prepend or append to existing text – see details below.
  • Creating a new folder now bypasses the DFFS solution.
New features in the “Set field value” section

Timestamp:
Use {timestamp} to add a timestamp to a field.

Current user
Use {currentUser} or {currentUserEmail} to add the user name or the email (malito link) in a field.

Prepend or append to existing text in a multi line text field:
Prefix the string with “prepend:” or “append:”. Here is an example:

prepend:{timestamp} - {currentUser}: {Log}\n************\n

September 18. 2013
I have updated to v3.15 with these changes:

  • Initially hidden field can not be made visible by including them in a tab – you must use a rule.
  • Fixed set field value for multiline textfield

September 13. 2013
I have updated to v3.14 with these changes:

  • Fixed bug in clearing / reversing rules for unselected choice columns. This bug would incorrectly trigger the rule when clicking another option in the choice columns.

September 12. 2013
I have updated to v3.13 with these changes:

  • Fixed typo “dropdodwns”
  • Changed the CSS file to make the form look the same in DispForm and EditForm when using side-by-side.
  • Added trigger for “Selected tab index.
CSS when using side-by-side

Insert this CSS in the Custom CSS section in the Misc tab if you want to:

stretch the outer table for all rows of side-by-side fields

.sbs_OuterTable{
   width:100%;
}

stretch the outer table for a specific row of side-by-side fields

#sbs_OuterTR_1 .sbs_OuterTable{
   width:100%;
}

sbs_OuterTR_1 the highlighted number represents the side-by-side index used in the setup.

control each field when using side-by-side

.sbs_OuterTable{
   width:auto;
}
#sbs_Field_YourFieldInternalName{
width:300px;
}
Trigger “Selected tab index”

This trigger is used for triggering rules when clicking on a tab. Please note that the rule will be reversed when it does NOT match.
IMG

Tip

Lets say you want a field to trigger a rule for one tab only. To achieve this, first create a rule using “Selected tab index”, but leave all “actions” empty (no required fields etc.).

Now create the rule for the trigger field and insert the rule index of the “Selected tab index rule” in the “And these rule indexes are true”.

You now have a rule that triggers on a field, but only applies for this specific tab.

August 19. 2013
I have updated this solution to v3.1 withe these changes:
New features:

  • Added support for Cascading dropdowns – see separate article for detail.
  • Added option to enter a license code to remove the PayPal-logo in the top of the GUI.

Bugfixes:

  • Removed “Set field value” from the DispForm configuration.
  • Fixed a bug preventing “Set field value” using [Today].
  • Fixed a bug when triggering a function in a rule on load.

You must update both the *.js, the *.css file and spjs-utility.js.

August 13. 2013
Second attempt with v3.02: fixes a bug preventing the user from saving the first time the configuration is added.

How to remove the text “Unlicensed version” from the GUI?

To obtain a license code, please go to this page: Obtain License Code, select the license type and pay using PayPal. I will send you a license code as soon as i receive the receipt from PayPal.

Overview

This is a solution for creating dynamic forms from out of the box SharePoint forms (preferably without content types). You can create rules that control whether fields are required or optional, visible or hidden, editable or read-only. You can also create tabs that you can target specific SharePoint users or groups, and you can add headings and rich tooltips for fields in the form.

You can link directly to a specific tab using this URL format:
…/NewForm.aspx?sTab=2
…/DispForm.aspx?ID=[an existing id from your list]&sTab=2
…/EditForm.aspx?ID=[an existing id from your list]&sTab=2

The GUI:
IMG

Previously articles in this series
New in v3

I have done a major overhaul of the code, fixed some bugs and added new features:

  • Radio buttons can now be arranged vertical.
  • When arranging multichoice or radio buttons vertical, the “fill in” will line up correctly.
  • Fixed width of select when using “Arrange multi lookup columns vertical” in SP 2007.
  • Custom CSS and JS: Small fix to try to avoid occasional errors.
  • Export and import of settings are now in place.
  • A copy of the last good configuration is automatically kept, and can be restored by a click of a button if you “break” it.
  • Backwards compatibility is now better. Please note that downgrading to an older version will require you to restore a backup of the original setup.
  • Changed the rule builder and added more fine grain controls of:
    • Optional fields and required fields
    • Visible fields and hidden fields
    • Editable fields and read-only fields
    • Visible tabs and hidden tabs
    • Visible headings and hidden headings
  • “Display this message” and “Alert this message” is now present in DispForm.
  • You can now control who sees the enter setup button.
  • You can now control who sees the orphan fields tab.
  • You can now distribute tabs over multiple lines and not only 2 as before.
Manually specify the location of the DFFS configuration list

Why do it:
If you want to use DFFS in a site template and share the configuration between all the sites created from that template.

The configuation is stored using the list name from the URL in lower case letters as “unique identifier” like this:
/lists/tasks/newform.aspx
This means you cannot configure DFFS for a list with the same URL name in a different site without them sharing this configuration.

How is it done:
Add this variable to the CEWP or HTML Form Web Part code (above the script tag that loads the DFFS script):

var dffs_configListBaseUrl = "/RelativeUrlToTheSite";

Tip:
You don’t have to put the shared configuration in the root site, you can add a new, empty subsite to hold the configuration list, but you must ensure all users have read access to this site.

Repetition of how to set up this solution

Step one
Download the latest version of the DFFS solution files from here. This includes the *.js file, *.css file and the gradient.zip. This last one should be unzipped before uploading to the file location (see below).

Step two
Download the latest version of spjs-utility.js from here.

Step three
Download v1.10.x or BELOW of jQuery here. Please note that version 2.x of jQuery does NOT support Internet Explorer 8 or below.

Step four
Put all these files in a standard document library or a folder created using SharePoint designer. Ensure all users have read access to the file location. Do NOT refer these files from spjsfiles.com as this server is not rigged to serve these files “live”.

Step five
Download the files “CEWP_example.txt” and “CEWP_Overlay_Example.txt” from here. Edit the script links (src) in “CEWP_example.txt” to point to the files stored locally. I repeat that you should NOT refer these files from spjsfiles.com.

Add the code to NewForm, DispForm and EditForm of the lists where you want this solution activated. The “CEWP_Overlay_Example.txt” must be placed ABOVE the form web part, and the “CEWP_example.txt” code must be placed BELOW the form web part.

How to add a webpart to the form page
SharePoint 2007
Go to NewForm, DispForm and EditForm and insert toolpaneview=2 behind the URL in this format:
…/NewForm.aspx?toolpaneview=2
…/DispForm.aspx?ID=[an existing id from your list]&toolpaneview=2
…/EditForm.aspx?ID=[an existing id from your list]&toolpaneview=2

Press Enter and you should be able to add a CEWP to the page. Repeat for all three forms.

SharePoint 2010 / 2013
In the list view, click “List” on the List Tools menu. Then select “Form Web Parts” and pick NewForm, DispForm or Editform from there and add a CEWP to the page. Repeat for all three forms.

I recommend using the Content link option in the CEWP to refer the code. The CEWP code should be put in a text file (txt) or an aspx file and put in the same location as the other files.

If you plan to put the code directly into the CEWP, you should choose the HTML Form Web Part in stead.

Step six
Go to the list and click “Add new item”. Click the little dot in the bottom left corner of the form to enter setup. When entering setup for the first time, the script will create a list to store the configuration. For help in the GUI, hover over the question marks.

Upgrading from a previous version?

You will have to upgrade both the *.js, the *.css file and spjs-utility.js.

As this is a major overhaul of the code, you might have to do some reconfiguring of the rules. In general, all settings are kept, but you now have more fine-grain control of the various settings so some rules might have to be changed.

Make a backup
When entering the setup after loading the new version, go to the “Misc” tab and export the settings by copying the text to a file. This is your backup in case something goes wrong and you have to downgrade to an earlier version. If you must do this, use the backup file and restore it by pasting the text in the text area under “Import settings”. When you have done this, you can load the older version of the script.

Please support the development of this solution by hitting the “beer button” in the top right corner of this page to buy me a few beers.

Use the comment section below let me know if you find find a bug, or you have a question.

Alexander

Javascript, List view modification, Requests, SharePoint, SharePoint 2010, Utilities

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

9 Comments

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