Category Archives: Javascript

Headings in SharePoint Forms – jQuery

01.03.2010 Added another method of building headings here: Headings for SharePoint forms – another method

Modified 10.10.2009: Small update for compatibility with the script Narrowing list form to one column.

This script adds heading-support to a custom SharePoint List by using a “prefix” in the field name of a standard “Single line of text” field, and a script to search all column names and reformat it as a heading in NewForm, DispForm and EditForm.

Create the “headings” by adding a column of type “Single line of text” to your list – and prefix your heading with #H# in the column name like this #H#ThisIsMyHeading. You specify the font size in pixes like this #H#17#ThisIsMyHeading, and you can add a custom color to the heading by adding a hex-color code like this #H#17#FF0000#ThisIsMyHeading.

You can also add a background color to your heading as a parameter in your script call like this:

// Specify color like this
showHeadings(190,true,false,true,'#ebf3ff');
// Or inherit like this:
showHeadings(190,true,false,true,$('.ms-formbody').css('background-color'));

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

The script “HeadingsInSharePointLists.js” has this sourcecode:

/* Headings from "Single line of text"
 * ---------------------------------------------
 * Created by Alexander Bautz
 * alexander.bautz@gmail.com
 * https://spjsblog.com
 * LastMod: 10.10.2009
 * ---------------------------------------------
   Example: Create a field of type "Single line of text" like this:
   #H#17#FF0000#This is a RED heading

  	 #H# - Defines heading
 	 17 - Font size
 	 #FF0000# - [optional] color

   If used without specifying the color it looks like this:
 	 #H#17#This is a heading 

Call like this in NewForm.aspx, DispForm.aspx or EditForm.aspx:
  <script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
  <script type="text/javascript" src="/test/English/Javascript/HeadingsInSharePointLists.js"></script>
  <script type="text/javascript">
    showHeadings(190,true,false,true,'#ebf3ff');
  </script>

  Parameters explained:
  divWidth: The width of the "label" colomn - where the fieldname is found. This script removes the nobr-tag from the label
            to prevent long field names to distort the column width.
  paddingTop: Adds a br-tag above the heading to make some air
  paddingBottom: Adds a br-tag below the heading to make some air
  stretch: Adds "colspan:2" to make the heading stretch to the full width of the list form
  bgColor: Background - [optional] background color of the td-tag. You can "copy" the color from
  		   another style to make the background adapt to changing site themes by using this as the
  		   parameter bgColor (no quotes - it's not a string):
  		   $('.ms-formbody').css('background-color')
  		   
  Note: To use with the script "Narrowing list form to one column", you must set the parameter "stretch" to false. 
  You must also call this script before the "Narrowing list form to one column-script".  		   
*/

function showHeadings(divWidth,paddingTop,paddingBottom,stretch,bgColor){
if(divWidth==undefined || divWidth=='')divWidth=190;
	$("td.ms-formlabel").each(function(){
		$(this).children().children('nobr').replaceWith('<div>' + $(this).children().children('nobr').html() + '</div>'); // Removes nobr-tag from label
		$(this).attr('width',divWidth); // Width of all "td.ms-formlabel"
		if($(this).text().match('#H#')){ // It's a heading
			var customDiv = $("<div></div>");
				if($(this).find('div').text()!=''){
					rawHeading = $(this).find('div').text()
				}else{
					rawHeading = $(this).text();
				}
			hSize = rawHeading.substring(3,5); // Find size
			customDiv.css({'fontSize':hSize}); // Set size
			if(rawHeading.substring(5,12).match(new RegExp(/^#?([a-f]|[A-F]|[0-9]){3}(([a-f]|[A-F]|[0-9]){3})?$/))){ // Find color
				customDiv.css({'color':rawHeading.substring(5,12)}); // Set color
			}
			if(stretch){ // Removes the "ms-formbody-td" and sets colspan=2 to stretch the heading
				$(this).next().hide();
				$(this).attr('width','');
				$(this).attr({colSpan:'2'});
			}
			if(typeof(bgColor)!="undefined" && bgColor != ''){
				$(this).css({'background-color':bgColor});
					if(!stretch){
						$(this).next().css({'background-color':bgColor});
					}
			}

			$(this).html(customDiv.text(rawHeading.substring(rawHeading.lastIndexOf('#')+1))); // Set new heading
			if(paddingTop)$(this).prepend('<br>');if(paddingBottom)$(this).append('<br>');	// Padding
			// Hide input
			if(!window.location.href.substring(0,window.location.href.indexOf('?')).match('DispForm.aspx')){
				$(this).next('td').children('span:eq(0)').hide();
				// Preserve borders if stretch = false
				newSpan = $("<span>&nbsp;</span>");
				$(this).next('td').append(newSpan);
			}
		}
	});
}

Save this as a text file and rename to “HeadingsInSharePointLists.js”, then upload to the library as shown above.

Then you add a CEWP below the list form in NewForm, DispForm and EditForm with this sourceCode:

<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/HeadingsInSharePointLists.js"></script>
<script type="text/javascript">
// To inherit the bgColor of the ms-formBody use this
showHeadings(190,true,false,true,$('.ms-formbody').css('background-color'));
// To specify the bgColor do it like this in stead
// showHeadings(190,true,false,true,'#ebf3ff'); 
</script>

Parameters explained:

  • divWidth: The width of the “label” colomn – where the fieldname is found. This script removes the nobr-tag from the label to prevent long field names to distort the column width.
  • paddingTop: Adds a br-tag above the heading
  • paddingBottom: Adds a br-tag below the heading
  • stretch: Adds “colspan:2” to make the heading stretch to the full width of the list form
  • bgColor:  [optional] background color of the td-tag

You can “copy” the color from another style to make the background adapt to changing site themes by specifying the parameter bgColor like this:

showHeadings(190,true,false,true,$('.ms-formbody').css('background-color'));

The end result should look like this:
IMG

Regards
Alexander

A follow-up on this article describing how to add heading-support to a list view is found here.

Dynamic expand/collapse fields or array of fields

28.11.2009 A follow up article on using multi select checkboxes is found here.

13.11.2009 Updated code for better function in EditForm and DispForm

This is a short intro on how to dynamically expand or collapse a field or array of fields based upon selection made in another field.

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

This is the only script you need to refer for this example.

Create a custom list with these fields:
IMG

Pay attention to the FieldNames. Always create names without spaces or special character to get a nice “FieldInternalName” – then you can rename the field to whatever you want – the “FieldInternalName” newer changes!

The field “MySelect” looks like this:
IMG

Then you add a CEWP below the list form in NewForm like this:
IMG

Sourcecode for NewForm.aspx and EditForm.aspx

&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/jquery-1.3.2.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
fields = init_fields();
// Arrays of fields to show or hide
var arrRed = ['ShowIfRed1','ShowIfRed2'];
var arrBlue = ['ShowIfBlue1','ShowIfBlue2'];

// Hide all onload
var arrToHide = [];
arrToHide = arrToHide.concat(arrRed,arrBlue);
toggleArr(arrToHide,true);

// Onchange
$(fields['MySelect']).find('select').change(function(){
var c = $(this).find('option:selected').text();
	dynamicDisplay(c);
});

// Onload
var c = $(fields['MySelect']).find('option:selected').text();
dynamicDisplay(c);

function dynamicDisplay(color){
// Hide all initially
toggleArr(arrToHide,true);
	if(color=='Red'){
		toggleArr(arrRed,false);
	}
	else if(color=='Blue'){
		toggleArr(arrBlue,false);
	}
	else if(color=='Yellow'){
		alert(&quot;No array defined for &quot;Yellow&quot;&quot;);
	}
}

function toggleArr(arr,hide){
  if(hide){
    for(i=0;i&lt;arr.length;i++){
      $(fields[arr[i]]).hide();
    }
  }else if(!hide){
    for(i=0;i&lt;arr.length;i++){
      $(fields[arr[i]]).show();
    }
  }
}

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

Sourcecode for DispForm.aspx

&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/jquery-1.3.2.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
fields = init_fields();
// Arrays of fields to show or hide
var arrRed = ['ShowIfRed1','ShowIfRed2'];
var arrBlue = ['ShowIfBlue1','ShowIfBlue2'];

// Hide all onload
var arrToHide = [];
arrToHide = arrToHide.concat(arrRed,arrBlue);
toggleArr(arrToHide,true);

// Show the array for the selected color
var c = $(fields['MySelect']).find('.ms-formbody').text().replace(/s|xA0/g,'');
dynamicDisplay(c);

function dynamicDisplay(color){
// Hide all initially
toggleArr(arrToHide,true);
	if(color=='Red'){
		toggleArr(arrRed,false);
	}
	else if(color=='Blue'){
		toggleArr(arrBlue,false);
	}
	else if(color=='Yellow'){
		alert(&quot;No array defined for &quot;Yellow&quot;&quot;);
	}
}

function toggleArr(arr,hide){
  if(hide){
    for(i=0;i&lt;arr.length;i++){
      $(fields[arr[i]]).hide();
    }
  }else if(!hide){
    for(i=0;i&lt;arr.length;i++){
      $(fields[arr[i]]).show();
    }
  }
}

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

The function “init_fields” is a modified version of the one created by Erucy and posted here. I have modified it to use “FieldInternalName” instead of “DisplayName” for locating the fields.

The end result should look like this:
IMG
IMG
IMG
IMG

Other fields can be used to trigger the event – look here to learn how to refer the various types of fields:
http://docs.jquery.com/Selectors

Have fun!
Alexander

Redirect from NewForm to EditForm or custom page

23.04.2011 Yet another article about the same topic can be found here

21.11.2009 A new article describing another method for redirecting a user to a custom page on “OK” and another page on “Cancel” is found here. This method is a good replacement for the “simple redirect” described in this article.

I have struggled a bit trying to redirect the user from NewForm to EditForm of the same list item – midway in filling the form. The reason i wanted to do this was to have access to the item ID when proceeding with the rest of the form fields (to make a relation between this list item and an item in another list).

I ended up using a brilliant solution called sessvars, from a guy named Thomas Frank. Read about it and get the sessvars.js here.

This technique can also be used to “redirect” the user to a custom “Thank you for participating…” – page after filling the form and clicking the “OK” button. I will provide examples of both methods.

In both examples you begin like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a subsite named “test” with a subsite named “English” with a document library named “Javascript”):
Img

A simple redirect from NewForm to a “Thank you for submitting” – page can be done like this.
Add a CEWP below the listform in NewForm and add reference to the scripts like this:

Img

The variable “sessvars.targetUrl” is set in a function called PreSaveAction which is always called when you click the “OK-button”. It must return true for the item to be saved. When this variable is set in this function, it is only set if the user actually saves the list item and not if the user clicks “Cancel”. this overcomes the problem with adding a ?Source=/test/English/ThankYou.aspx to the URL – which would redirect the user even if he clicks “Cancel”.

&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/sessvars.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
function PreSaveAction(){
  sessvars.targetUrl = '/test/English/ThankYou.aspx';
return true;
}
&lt;/script&gt;

Then you have to add a CEWP to the standard view of your list (the view you normally would be redirected to after saving a list item if you have no ?Source= in the query string of your URL):
Img

This code checks if the variable “sessvars.targetUrl” is defined, and redirects the user to the specified address. Note that right before the redirect, the variable is cleared with “sessvars.$.clearMem()”, to prevent the user from getting redirected multiple times.

&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/sessvars.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
var targetUrl = sessvars.targetUrl;
if(targetUrl!=undefined){
  sessvars.$.clearMem();
  window.location.href = targetUrl; 
}
&lt;/script&gt;

When the user adds a new item to the list, he is redirected to the URL specified in the variable “sessvars.targetUrl” like this:
Img

A redirect from NewForm to EditForm can be done like this:
Like in the example above, add the necessary scripts:
Img

Here i have used some new scripts published on CodePlex by a user named DuWei. Get them here. I have used interaction.js and stringBuffer.js in this example.

Add a CEWP to the NewForm with this code:
Img

You have to provide the URL to EditForm.aspx, and the list GUID of the list.

&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/sessvars.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot;&gt;
function PreSaveAction(){
  // SharePoint variable of current user's id
  sessvars.userId = _spUserId; 
  // Path of the list to redirect to - current list
  sessvars.targetUrl = '/test/English/Lists/NewFormToEditFormRedirect/EditForm.aspx'; 
  // The BaseUrl of the site or subsite you are located  	
  sessvars.listBaseUrl = L_Menu_BaseUrl; // SharePoint provides this
  // List Guid of the list to redirect to
  sessvars.listGuid = '{1791b3f2-9725-49fb-a8a4-561a57e38b34}'; 
  return true;
}
&lt;/script&gt;

Then you add a CEWP to the standard view of your list:
Img

&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/jquery-1.3.2.min.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/interaction.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/stringBuffer.js&quot;&gt;&lt;/script&gt;
&lt;script type=&quot;text/javascript&quot; src=&quot;/test/English/Javascript/sessvars.js&quot;&gt;&lt;/script&gt;

&lt;script type=&quot;text/javascript&quot;&gt;
var userId = sessvars.userId;
var targetUrl = sessvars.targetUrl;
var listBaseUrl = sessvars.listBaseUrl;
var listGuid = sessvars.listGuid;

if(userId!=undefined &amp;&amp; targetUrl!=undefined &amp;&amp; listGuid!=undefined){
// Query to find the newest item added by current user
wsBaseUrl = listBaseUrl + '/_vti_bin/'; 
var res = queryItems(listGuid,&quot;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name='Author' LookupId='TRUE'/&gt;&lt;Value Type='Integer'&gt;&quot; + userId  + &quot;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name='ID' Ascending='FALSE'/&gt;&lt;/OrderBy&gt;&quot;,['ID']);    
// Gets the ID of the item
var itemId = res.items[0]['ID']; // This is the newest item added by current user
// Clears the session variables to prevent redirecting multiple times
sessvars.$.clearMem();
// Redirects the user to the correct item and opens it in EditForm
window.location.href = targetUrl + &quot;?ID=&quot; + itemId; 
}
&lt;/script&gt;

When a user saves the list item – he is redirected to the EditForm (or DispForm if you specify it in the URL) of the list item he just added.

Please ask if something is unclear.
Alexander

Wrap choice-field in multiple columns

Change log
October 19. 2014
Updated the code to get rid of a few wrong single quotes introduced when migrating this code from another server.

I received a comment on another post requesting a function to wrap the choice-field into any given number of columns. Say you have a choice-field of “Checkbox” or “Radio” type with 20 choices and want to arrange them in multiple columns like this:
IMG

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

You find the sourcecode for “WrapChoiceField.js” below.

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

Add a CEWP below the listform in NewForm and EditForm and add reference to the scripts like this:
IMG

Create the list-fields as normal:
IMG

Sourcecode for the file WrapChoiceField.js:

/* Wrap choice-field in multiple columns
* ----------------------------------------------------
* Author: Alexander Bautz
* alexander.bautz@gmail.com
* Version: 1.3
* LastMod: 19.10.2014
* ----------------------------------------------------
* Must include reference to jQuery
* ----------------------------------------------------
  Example:
  wrapChoiceField("FieldInternalName of your field",number of columns to wrap it into);
* ----------------------------------------------------
*/

var fields = init_fields_v2();
 
function wrapChoiceField(fin,breakAfter){
	var toFind, index, counter, table, fillIn, len;
	toFind = "td.ms-formbody";
	if($("td.ms-formbodysurvey").length>0){
		toFind = "td.ms-formbodysurvey";
	}
	if(fields[fin]!==undefined){
		index = 0;
		counter = 0;
		table = $(fields[fin]).find(toFind+' table:first');
		fillIn = $(table).find("tr:last").find("input:text").length === 1;
		len = $(table).find("tr").length;
		$(table).prepend("<tr id='vertical_"+fin+"_"+index+"'></tr>");
		$(table).find('tr:first').nextAll().each(function(i,tr){
			if(counter > 0 && counter%breakAfter === 0){				
				$("#vertical_"+fin+"_"+index).after("<tr id='vertical_"+fin+"_"+(index+1)+"'></tr>");
				index += 1;
			}else if((fillIn && i === (len-1)) || (fillIn && i === (len-2))){
				$("#vertical_"+fin+"_"+index).after("<tr><td valign='top' colspan='"+breakAfter+"'><table><tr id='vertical_"+fin+"_"+(index+1)+"'></tr></table></td></tr>");
				index += 1;
			}						
			$(tr).find('td:first').css("white-space","nowrap").appendTo($("#vertical_"+fin+"_"+index));
			$(tr).remove();
			counter += 1;
		});
	}
}
 
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' && $(this).text().match(/(<([^>]+)>)/ig)!==null){
				$(this).html($(this).text());
			}		
			// Build object
			res[fin] = this.parentNode;
			$(res[fin]).attr('FieldDispName',disp);
			$(res[fin]).attr('FieldType',type);
		}		
	});
	return res;
}

Save the sourcecode to a file named “WrapChoiceField.js”, and upload to your document library as shown above.
Be sure to get the file type correct – the file extension is .js

That’s it!

Customized form?
How to use these scripts in a customized form

Regards
Alexander

Cascading dropdowns

14.06.2010 A new version of this script is released Cascading dropdowns in SharePoint text fields – populated by lookup in another list (version 2)

I recommend using the new version.


Updated 14.08.2009
This script “converts” singleline textfields to lookup-dropdowns. One dropdown filteres the next. You can have 2-5 connected dropdowns in each “collection”. You can have as many “collections” as you want in each listform.

The source of the cascading dropdowns is a standard custom list containing the information to populate each of the dropdowns. This list can reside in the current site, the rootsite or in any subsite as long as users has access to it.

The script uses the original textfield to hold the value selected, and “spawns” a dropdown to take it’s place. In editform (or on refresh of the form on validation) the script updates the dropdown to hold the correct value – read from the original textfield.

If you open in editform and the original value selected in no longer a valid choice (the value is deleted from the source list), the dropdown and the corresponding hidden textfield is cleared.

When changing a “parent” dropdown, the children is cleared for the previously selected values and repopulated with the new choices.

To use this script you have to reference jQuery (tested with jquery-1.3.2.min.js) and these scripts:
* SPAPI_Core.js
* SPAPI_Lists.js
* SPAPI_dspsts.js
* Made by Darren Johnstone http://darrenjohnstone.net
download the scripts here. This download contains more scripts than you need to refer – only the scripts named over is used.

How to use:
Make a sourceList to hold the selectable values to fill your dropdowns.
Img
Create the fields like this:
Img

The calculated column (_Lookup) that concatenates all these columns is the field the script uses to find matching values to populate the next dropdown.

Find the ListGuid of your SourceList like this:
Img
You need it in the function call later.

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

In the consumer list (the list where your dropdowns is supposed to be), add corresponding columns (standard SharePoint single line textfields) for all but the calculated field. The final “product” looks like this:
Img

Insert a CEWP below the listform in NewForm and EditForm and add reference to the scripts like this:
Img
The source looks like this (copy the source below):
Img
The listGuid of your sourceList is used here

Sourcecode:
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/SPAPI_Core.js"></script>
<script type="text/javascript" src="/test/English/Javascript/SPAPI_Lists.js"></script>
<script type="text/javascript" src="/test/English/Javascript/SPAPI_dspsts.js"></script>
<script type="text/javascript" src="/test/English/Javascript/CascadingDropDowns.js"></script>

<script type="text/javascript">
/*
* providerArr = Array of FieldInternalNames of the fields in the source-list.
* consumerArr = Array of FieldInternalNames of the fields in the list where the dropdowns will be created.
* dropDownIdPrefix = Unique prefix if there are more then one instance of cascading dropdowns in one list.
* lookupSourceCalculatedField = FieldInternalName of the calculated column in the source-list that concatenates all lookup values in one string – separated by "delimiter".
* lookupSourceListGuid = GUID of the source-list.
* dropDownDefaultvalue = The default value of dropdowns that are not empty – ex. <select>.
* numberOfDropdowns = Number of levels of cascading dropdowns – (2-5).
* delimiter = Separator character between the values of the calculated column in "lookupSourceCalculatedField". Must be the same as the delimiter used in the "_Lookup" in the sourceList.
* sourceListURL = relative URL to site/subsite. If root site use only two apostrophes representing a blank string – like this: ”.
* debug = true or false – if true: the textfield that holds the dropdownvalue is visible.
*/
// You don’t have to use the same names in providerArr and consumerArr, but you have to use the FieldInternalName and not the Displayname.
providerArr = [‘Title’,’Model’,’Color’,’Year’,’Milage’];
consumerArr = [‘Title’,’Model’,’Color’,’Year’,’Milage’];
cascadingDropDowns(providerArr,consumerArr,’CascNr1′,’_Lookup’,'{83eb224b-03fa-4a8b-b493-80253373a962}’,'<Select>’,5,’ – ‘,true,’/test/English’,false);
// For description of all the parameters see the sourcecode.
</script>
[/javascript]
Be sure to use the correct path to your script in the reference – mine is located at “/test/English/Javascript”.

How to find the FieldInternalName? – Click here

Get the sourcecode for “CascadingDropDowns.js” here and save it as CascadingDropDowns.js. Make sure you get only the script and not other text – copy manually – do not use Ctrl+A.

Upload in the document library with the other scripts needed – as shown above.

If you need more info to setup this script, feel free to ask.

PS. I have other scripts for single filtered lookup’s both for for obtaining value only, and for link to list item or to document. Ask for it and i will post it.

Regards
Alexander

Return value of all SharePoint fieldtypes with Javascript

This post is old… you will find other ways to do all this in newer posts – Alexander

As promised, here are the post on returning the value of any SharePoint-field with the use of Javascript.
As for now this script is IE only! – as stated earlier, i personally do not use Firefox, and i have not had the time to try to adapt it to Firefox. The main thing that has to be done is to handle the node-issue where Firefox counts line breaks in the code as a node.

I first learned to manipulate list items with javascript here: Microsoft SharePoint Designer Team Blog, and the piece on returning radio button values is a product of this guy here: Markuso.

About the script
This script returns the value of the following SharePoint-fieldTypes

  • Single line text
  • Multiple line text (Plain text)
  • Number
  • Currency
  • Multiple choice check box
  • Date
  • Radio Button
  • Drop down
  • Yes/No
  • Hyperlink
  • Picture
  • Lookup – Single
  • Lookup – Multiple
  • Person (DisplayName or LoginName)

I always use the fields “FieldInternalName” when addressing it in code, it never changes no matter how many times you change the “DisplayName”.

The script:

<script type="text/javascript">
/*************************************************************
Type:
Singelline tekst and multiline tekst (Plain text), Number, Currency = text
Multichoice checkbox = checkbox (returns commaseparated values)
Date (returns date only, but works with both DateOnly and DateAndTime) = date
RadioButton = radio
Dropdown = dropdown
Yes/No = bool
Hyperlink or picture (returns commaseparated: description,url) = url
Lookup – single (below and over 20 items), MultiLookup (returns array) = lookup
Lookup Multiple – you can use plain ‘lookup’, but if you want only "multi" = lookupMulti
Person – DisplayName = personDisplay
Person – LoginName = personLogin
Use: var value = returnValue(‘checkbox’,’FieldInternalNameOfYourField’);
*************************************************************/
function returnValue(type, fieldInternalName) {
var arr = document.getElementsByTagName(‘!’);//get all comments
for (var i=0;i < arr.length; i++ ) {
// Date
if (type==’date’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
var fieldTitle = arr[i].innerHTML.substring(arr[i].innerHTML.indexOf(‘FieldName=’)+11,arr[i].innerHTML.indexOf(‘n’)-2);
var tags = document.getElementsByTagName(‘input’);
for (var i=0; i < tags.length; i++) {
if (tags[i].title == fieldTitle && tags[i].parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.innerHTML.indexOf(‘FieldName="’+fieldTitle+’"’) > -1)
{
return tags[i].value;
}
}
// RadioButton – Credit to http://blog.markuso.com/ – slightly modified by me to use FieldInternalName
}else if(type==’radio’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
var tags = document.getElementsByTagName("input");
for (var i=0; i < tags.length; i++) {
var nameString = tags[i].name;
// get selected radio button value only
if (tags[i].type == "radio") {
var tagParentHTML = tags[i].parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.innerHTML;
if (tagParentHTML.indexOf(‘FieldInternalName="’+fieldInternalName+’"’) > -1) {
var radioButtons = document.getElementsByName(nameString);
var radioValue = "";
for (var x=0; x < radioButtons.length; x++) {
if (radioButtons[x].checked) {
radioValue = radioButtons[x].parentElement.title;
break;
}
}
var o = document.createElement("INPUT");
o.type = "hidden";
o.value = radioValue;
return o.value;
}
}
}
return null;
// Checkbox
}else if(type==’checkbox’) {
var arr = [];
var arrValue = [];
var tags = document.getElementsByTagName("input");
for (var i=0; i < tags.length; i++) {
if (tags[i].type == "checkbox") {
var tagParentHTML = tags[i].parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.parentElement.innerHTML;
if (tagParentHTML.indexOf(‘FieldInternalName="’+fieldInternalName+’"’) > -1) {
arr.push(tags[i].id);
}
}
}
for (var x=0; x < arr.length; x++) {
var chkBox = document.getElementById(arr[x]);
if (chkBox.checked) {
arrValue.push(chkBox.parentElement.title);
}
}
return arrValue.toString();
// Text or Dropdown
}else if((type==’text’ || type==’dropdown’) && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
return arr[i].parentNode.childNodes[1].childNodes[0].value;
// Hyperlink or Picture
}else if(type==’url’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
// returns commaseparated (description,url) so it can be split later
var hyperlink = [];
hyperlink.push(arr[i].parentNode.childNodes[1].childNodes[4].value);
hyperlink.push(arr[i].parentNode.childNodes[1].childNodes[1].value);
return hyperlink;
// Bool
}else if(type==’bool’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
return arr[i].parentNode.childNodes[1].childNodes[0].checked;
// Lookup
}else if(type==’lookup’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
// Under 20 items
var opt = arr[i].parentNode.childNodes[1].childNodes[0].options;
if(opt!=null) {
for (var x=0;x<opt.length;x++) {
if(opt[x].selected == true) {
return (opt[x].innerHTML);
}
}
}else if(opt==null){
if(arr[i].parentNode.childNodes[1].childNodes[0].childNodes[0]!= null) {
// Over 20 items
return arr[i].parentNode.childNodes[1].childNodes[0].childNodes[0].value;
}else{
// MultiLookup – optionally you can call the script with type=’lookupMulti’
var arrSelected = [];
var fieldId = arr[i].parentNode.childNodes[1].childNodes[0].id;
var selectResultId = fieldId.substr(0,fieldId.indexOf(‘MultiLookupPicker’)) + ‘SelectResult’;
var selectResultField = document.getElementById(selectResultId);
for(var x=0;x < selectResultField.length; x++){
arrSelected.push (selectResultField[x].innerHTML);
}
return arrSelected.toString();
}
}
}
// Lookup Multi (can also use plain ‘lookup’ as spacified above
else if(type==’lookupMulti’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
var arrSelected = [];
var fieldId = arr[i].parentNode.childNodes[1].childNodes[0].id;
var selectResultId = fieldId.substr(0,fieldId.indexOf(‘MultiLookupPicker’)) + ‘SelectResult’;
var selectResultField = document.getElementById(selectResultId);
for(var x=0;x < selectResultField.length; x++){
arrSelected.push (selectResultField[x].innerHTML);
}
return arrSelected.toString();
// Person
}else if(type==’personDisplay’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
var rawString = arr[i].parentNode.childNodes[1].innerHTML;
if(rawString.indexOf(‘isresolved="True"’)>0){
// user resolved OK
// Use these for DisplayName
var displayName = rawString.indexOf(‘displaytext="’);
var start = displayName + 13;
var stopp = displayName + rawString.substring(rawString.indexOf(‘displaytext="’)).indexOf(‘key="’)-2;
var user = rawString.substring(start, stopp);
return user;
}else{
// user not resolved
return "";
}
}else if(type==’personLogin’ && arr[i].innerHTML.match(‘FieldInternalName="’ + fieldInternalName + ‘"’)) {
var rawString = arr[i].parentNode.childNodes[1].innerHTML;
if(rawString.indexOf(‘isresolved="True"’)>0){
// Use these for Loginname
var loginName = rawString.indexOf(‘isresolved="True"’);
var start = loginName + rawString.substring(rawString.indexOf(‘isresolved="True"’)).indexOf(‘key="’)+5;
var stopp = loginName + rawString.substring(rawString.indexOf(‘isresolved="True"’)).indexOf(‘">’);
var user = rawString.substring(start, stopp);
return user;
}else{
// user not resolved
return "";
}
}

}
}
</script>
[/javascript]

You should now be able to return all fieldvalues from SharePoint forms.

Here’s an example of how to use it in the PreSaveAction (add this script and it will always execute between you pressing th “OK-button” and the actual save of the item:

<script type="text/javascript">
function PreSaveAction() {
var checkBoxValue = returnValue(‘checkbox’,’CheckboxesChoice’);
if(checkBoxValue.match(‘Yellow’)==null) {
alert("You did not select Yellow!nnIf you do not select Yellow, you will not be able to save this list item!");
// aborts the save process
return false;
}
// Nothing above has returned false – go through with the save process
return true;
}
</script>
[/javascript]

I will return with more on validating input with both the SharePoint function PreSaveAction and on “events” in the fields themselves.

That’s all for now.

Alexander

Find and hide fields in NewForm, DispForm and EditForm in both IE and Firefox

This post is old… you will find other ways to do all this in newer posts – Alexander

This post will talk about how to use javascript to find a form-field in av SharePoint list form (NewForm, DispForm and EditForm) from its unique FieldInternalName.

When you have found it you can do almost anything you want with it – hide it, make it readOnly, return the value of the field or validate the input. I will come back to this in future posts in this “Series”.

The method i will talk about here is not new – i believe the guys at CleverWorkarounds posted it first, or was it here – I’m not really sure (if you do know who to credit – please let me know!).

Almost everyone uses a method where they search for a field by the fields DisplayName, but this is not a very robust way of doing it as the displayName can easily be altered – and thereby breaking the script.

It has also been talked about using the Id of the field as reference – the id one would believe to be unique – but it is NOT! If you change the column order in the list form – the id changes – it has a reference to the column order in the id.

This leaves only one suitable way to reference fields – by it’s FieldInternalName.
To find the FieldInternalName you can view the source of the page and search for your DisplayName.

One thing to remember when using FieldInternalName is that it newer changes – and therefore the FieldInternalName could end up totally different from the DisplayName.

When creating fields all spaces in the fieldName will be replased with _x0020_, and special characters with its equivalent hex-code – therefore create a proper fieldname first – then edit it to add spaces and special characters. By doing this your FieldInternalName will be readable and makes sense in your code.

The script i have created uses the FieldInternalName to uniquely identify the field, but it works ONLY in Internet Explorer because it searches for a “Comment-tag” which is not recognized in Firefox as a valid HTML tag:

&lt;script type=&quot;text/javascript&quot;&gt;
function findacontrol(FieldInternalName) {
var arr = document.getElementsByTagName('!');
	for (var i=0;i &lt; arr.length; i++ ) {
		if (arr[i].innerHTML.match('FieldInternalName=&quot;' + FieldInternalName + '&quot;')) {
			if(window.location.href.indexOf('DispForm.aspx')&gt;0) {
				return arr[i].parentNode.childNodes[1];
			}else{
				return arr[i].parentNode.childNodes[1].childNodes[0];
			}
		}
	}
}
&lt;/script&gt;

I’m not using Firefox myself, but realized that it would be nice to have this work in Firefox to – almost all my scripts depends on this method of finding fields.

I therefore made a similar script that works on both browsers, but it’s not as sleek as the one for IE only.

The main reason it has to be different is that Firefox does not recognize <!– the comment- tag –>[/javascript] as a valid HTML tag and therefore one have to use the td-tag to search the code. The disadvantage of this approach is that there are far more td-tags than comments in the code and it must loop trough more code in the hunt for “your field”.

One other thing Firefox does different than IE, is childNodes and nextSibling. Firefox treats line breaks in the code as a node and therefore the depth of childNodes in the script must be different for the two.

Script for both IE and Firefox:

<script type="text/javascript">
function findacontrolCrossBrowser(FieldInternalName) {
var arr = document.getElementsByTagName(‘td’);
for (var i=0;i < arr.length; i++ ) {
if (arr[i].innerHTML.match(‘FieldInternalName="’ + FieldInternalName + ‘"’) && arr[i].className == ‘ms-formbody’) {
if(window.location.href.indexOf(‘DispForm.aspx’)>0) {
return arr[i].parentNode.childNodes[1];
}else{
if(arr[i].childNodes[1].nodeType==1) {
// Assumes it is IE
return arr[i].childNodes[1].childNodes[0];
}else if(arr[i].childNodes[1].nodeType==8) {
// Assumes it is Firefox
return arr[i].childNodes[3].childNodes[0];
}
}
}
}
}
</script>
[/javascript]

How to add webPart to NewForm, DispForm or EditForm? Click here.

One possible use of this script is to hide a single field, or a field array:

function hideFieldsArray() {
var array = [‘Field1’, ‘Field2’];
for (x in array)
{
xField = findacontrolCrossBrowser(array[x]);
if(xField!=null)
{
if(window.location.href.indexOf(‘DispForm.aspx’)>0)
{
xField.parentNode.parentNode.style.display="none";
}else{
xField.parentNode.parentNode.parentNode.style.display="none";
}
}
}
}
</script>
[/javascript]

A fully functional setup of this script would be to add a CEWP below the form in for instance NewForm.aspx, and add the following script to it – of course change ‘Field1’ and ‘Field2’ with your FieldInternalName:

&lt;script type=&quot;text/javascript&quot;&gt;
hideFieldsArray();

function hideFieldsArray() {
var array = ['Field1', 'Field2'];
for (x in array)
{
xField = findacontrolCrossBrowser(array[x]);
	if(xField!=null)
		{
			if(window.location.href.indexOf('DispForm.aspx')&gt;0)
			{
				xField.parentNode.parentNode.style.display=&quot;none&quot;;
			}else{
				xField.parentNode.parentNode.parentNode.style.display=&quot;none&quot;;
			}
		}
	}
}

function findacontrolCrossBrowser(FieldInternalName) {
var arr = document.getElementsByTagName('td');
	for (var i=0;i &lt; arr.length; i++ )  {
		if (arr[i].innerHTML.match('FieldInternalName=&quot;' + FieldInternalName + '&quot;') &amp;&amp; arr[i].className == 'ms-formbody') {
			if(window.location.href.indexOf('DispForm.aspx')&gt;0) {
				return arr[i].parentNode.childNodes[1];
			}else{
				if(arr[i].childNodes[1].nodeType==1) {
					// Assumes it is IE
					return arr[i].childNodes[1].childNodes[0];
				}else if(arr[i].childNodes[1].nodeType==8) {
					// Assumes it is Firefox
					return arr[i].childNodes[3].childNodes[0];
				}
			}
		}
	}
}
&lt;/script&gt;

You can also call scripts on pageLoad with:

<script type="text/javascript">
_spBodyOnLoadFunctionNames.push("functionName");
</script>
[/javascript]
but then the hiding of the fields will happen after the page has been presented to the use, and will result in a seconds flash of all your “hidden fields” – not good.

I will return with more on how to return the value from almost all field types (Single line of text, Multiple lines of text (Plain text), Choice (dropdown, radio and multi choice), Date and Time, Lookup, Yes/No, Person or Group), how to use and validate the input, and how to dynamically collapse or expand fields based on selections in other fields.

Stay tuned!

Alexander

Compare DueDate with Today in list view

15.09.2009: Complete rewrite of the script to tidy up the code, improve functionality, and to add jQuery-logic to make it run smoother.

This script is used to Compare a “DueDate” in a list view with “Today” and generate “traffic lights” to flag status. It also adds a mouseover tooltip that shows the list element’s “day, hour and minute – status”.

The end result looks like this:
IMG

You can filter on the “Status-column”:
IMG

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

Add a CEWP to your list view – below the list form, and add this code:

<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/trafficLightsOnDueDate.js"></script>
<script type="text/javascript">
// Offset
yOffsetMinutes = 2880; // When to show the yellow "flag"
rOffsetMinutes = 1440; // when to show the red "flag"

// Order of apperance of the dateFields in your Formula (1 = first position, 2 = second position and 3 = third position)
OrderDay = 2;
OrderMonth = 1;
OrderYear = 3;

// Settings
defaultTimeIfDateOnly = '12:00'; // If used with "Date Only" - what time should be used as "DueTime" - Military Time
splitChar = '/'; // The separator between the day month year of your calculated dateField: 01[splitChar]01[splitChar]2009
separatorDateTime = ' '; // The separator in the calculated column between date and time: 01/01/2009[separatorDateTime]08:00
LeadInTextBeforeDate = 'Due:'; // The leadin text of your calculated dateField - used to locate the correct table cells

// Tooltip on "traffic lights" before days/hours/minutes
TooltipDueIn = 'Due in: '; 
TooltipOverdue = 'Overdue: ';

// Tooltip on "traffic lights" after days/hours/minutes
TooltipDays = ['day','s']; // Array - first value for "singular", second for "plural"
TooltipHours = ['hour','s']; // Array - first value for "singular", second for "plural"
TooltipMinutes = ['minute','s']; // Array - first value for "singular", second for "plural"

// Call script 
trafficLightsOnDueDate();
</script>

Like this:
IMG

The script “trafficLightsOnDueDate.js” has this soursecode:

/* Compare DueDate with Today in list view and generate "traffic lights" from DueDate
* ----------------------------------------------------
* Author: Alexander Bautz
* alexander.bautz@gmail.com
* Version: 2.0
* LastMod: 15.09.2009
* ----------------------------------------------------
* Must include reference to jQuery
* ----------------------------------------------------
*
* All variables are defined in a CEWP below the list view of your list. See separate codeblock for code.
*
* ----------------------------------------------------
*/

function trafficLightsOnDueDate() {
// Find today's date
var now = new Date();
$("td.ms-vb2:contains('" + LeadInTextBeforeDate + "')").each(function(){
	var sepDate = '';
	var sepHourMinutes = '';
	if($(this).text().substring(LeadInTextBeforeDate.length + 1).length>10){
		sepDate = $(this).text().substring(LeadInTextBeforeDate.length + 1,$(this).text().lastIndexOf(separatorDateTime)).split(splitChar);
		sepHourMinutes = $(this).text().substring($(this).text().lastIndexOf(separatorDateTime)+separatorDateTime.length).split(':');
		IncludeHoursAndMinutes = true;
	}else{
		sepDate = $(this).text().substring(LeadInTextBeforeDate.length + 1).split(splitChar);
		IncludeHoursAndMinutes = false;
	}		

	var m = sepDate[OrderMonth - 1];
	var d = sepDate[OrderDay - 1];
	var y = sepDate[OrderYear - 1];
	
	if(IncludeHoursAndMinutes){
		h = sepHourMinutes[0];
		min = sepHourMinutes[1];
	}else{	
		h = defaultTimeIfDateOnly.split(':')[0]
		min = defaultTimeIfDateOnly.split(':')[1]
	}
	// Build the datestring from fieldvalue
	var fieldDate = new Date(y,m-1,d,h,min,00);

	// Build friendly string for due/overdue mouseover tooltip
		var DueByDays = msToDHM(Math.round((fieldDate.getTime() - now.getTime())));
		var overDueByDays = msToDHM(Math.round((now.getTime() - fieldDate.getTime())));
	
	// OffsetDate - Create the traffic lights
	var yellowOffsetDate = now.getTime() + (yOffsetMinutes * 60000);
	var redOffsetDate = now.getTime() + (rOffsetMinutes * 60000);
	
		if(yellowOffsetDate < fieldDate.getTime() && redOffsetDate < fieldDate.getTime()){
		// green
			$(this).html("<div style='font-weight:bold; font-size:16px; color:green'>•</div>");
			$(this).parent().attr('title',TooltipDueIn + DueByDays);
		}
		else if(yellowOffsetDate >= fieldDate.getTime() && redOffsetDate < fieldDate.getTime()){
		// yellow
			$(this).html("<div style='font-weight:bold; font-size:16px; color:yellow'>•</div>");
			$(this).parent().attr('title',TooltipDueIn + DueByDays);
		}		
		else if(redOffsetDate > fieldDate.getTime() && now.getTime() < fieldDate.getTime()){
		// red due in...
			$(this).html("<div style='font-weight:bold; font-size:16px; color:red'>•</div>");
			$(this).parent().attr('title',TooltipDueIn + DueByDays);
		}
		else if(redOffsetDate > fieldDate.getTime() && now.getTime() > fieldDate.getTime()){
			// red overdue by...
			$(this).html("<div style='font-weight:bold; font-size:16px; color:red'>•</div>");
			// Red text in all row (but the title with link)
			$(this).parent().attr('title',TooltipOverdue + overDueByDays).find('td').addClass('ms-errorcolor ms-bold');
		}

	});
}

function msToDHM(ms){
	var countDays = Math.floor(ms / 86400000); // 86400000 in one day
	var countHours = Math.floor((ms % 86400000) / 3600000); // Find "leftovers" after all the full days have been taken away / 3600000 ms in one hour
	var countMinutes = Math.floor(((ms % 86400000) % 3600000) / 60000); // Find "leftovers" after all hours have been taken away / 60000 ms in one minute
	var str = '';
	if(countDays!=0){
		if(countDays>1){
			str +=  countDays + " " + TooltipDays[0] + TooltipDays[1] + " ";
		}else{
			str +=  countDays + " " + TooltipDays[0] + " ";
		}	
	}
	if(countHours!=0){
		if(countHours>1){
			str += countHours + " " + TooltipHours[0] + TooltipHours[1] + " ";
		}else{
			str += countHours + " " + TooltipHours[0] + " ";
		}
	}
	if(countMinutes!=0){
		if(countMinutes>1){
			str +=  countMinutes + " " + TooltipMinutes[0] + TooltipMinutes[1];
		}else{
			str +=  countMinutes + " " + TooltipMinutes[0];
		}
	}
	if(countDays + countHours + countMinutes == 0)str += " 0 " + TooltipMinutes[0] + TooltipMinutes[1]
	return str;
}

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

Add these columns to your list:

  • a column of type “Date and Time”, format “Date Only” or “Date & Time” named DueDate
  • a column of type “Yes/No (check box)” named Completed
  • a column of type “Calculated (calculation based on other columns)” named Status

The DueDate-column does not have to be in the list view, but the calculated column must be.

The column named Status has this formula for “DateOnly”:

=IF(DueDate="","N/A",IF(Completed<>TRUE,"Due: "&TEXT(DueDate,"mm/dd/yyyy"),"Completed"))

And this formula for “Date & Time”:

=IF(DueDate="","N/A",IF(Completed<>TRUE,"Due: "&TEXT(DueDate,"mm/dd/yyyy HH:mm"),"Completed"))

This has formula for “Date & Time”:
IMG

Thats it!

Alexander