SharePoint and adding actions to date controls in 2010 and 2013

SharePoint and adding actions to date controls in 2010 and 2013

Overview

There are many instances where I want to add javascript to a SharePoint form. This may read and process field values, sometimes I can run this in the PreSaveAction () function that is called when the user presses the save button. Occasionally I need to add a function to the onchange event of a control. The script for doing this varies on the control type, with dropdowns (lookups or choice) in SharePoint 2007 or 2010 it varies based on the number of items in the dropdown.

Scenarios in SharePoint 2010

Date/Time controls are one of the trickier ones to deal with. If we imagine a SharePoint 2010 (I’ll look at 2013 in a bit) custom list with a date field as shown below:

The Start Time field is actually four separate things that we need to pay attention to:

  • Input box
  • Date Picker control
  • Hours Select
  • Minutes Select

The script to get a pointer to the input box and add an onchange event is straight forward:

MyDateField = getTagFromIdentifierAndTitle('Input', '', 'Start Time');  
MyDateField.onchange=function() {TimeHasChanged()};

The Ids given to the minute and hours dropdowns are the same as the same as the Id of the input field suffixed by the word Minutes or Hours. We can use the following to add actions to those (we want item 0 in the returned collection):

var MyMinuteField = $('#'+MyDateField.id+'Minutes');
var MyHourField = $('#'+MyDateField.id+'Hours');
MyMinuteField[0].onchange=function() {TimeHasChanged()};
MyHourField[0].onchange=function() {TimeHasChanged()};

It would be common sense that the input’s onchange event would fire when the date picker is used to set that date. However this is simply NOT the case, however hard you click on a day in the date picker that does not trigger the onchange for the associated input field, even though its value is clearly changed.

We can follow the article on Howdididothat and use the onvaluesetfrompicker function that also appears on the input field, even if we can’t see it in the debugger.

MyDateField.onvaluesetfrompicker=function() {TimeHasChanged()};

Simple and this case it is. However if you check the Howdididothat link you may notice a comment that it didn’t work for someone. Lets try a slightly different scenario: consider that instead of a custom list with a date control, we have a list created using SharePoint 2010’s Calendar template. The NewForm will look like:

The first important thing to notice is that if you change the Start Time the End Time will automatically change to the same date unless you have already altered the End Time. This is clearly a terrific time saver, normally you set the start date of something and you want the end date to follow you. However the downside is that the onvaluesetfrompicker function suddenly doesn’t get triggered. SharePoint changing date code must stamp out the function that we had been intercepting. On a custom list onvaluesetfrompicker will work, on the Start Time or End Time in a Calendar form onvaluesetfrompicker will not work for you. This post gives an alternative by overriding the ‘OnPickerFinish’ function from DatePicker.js with the following snippet:

//Override the OnPickerFinish function in DatePicker.js to fire the event when the date is updated from the date picker.
function OnPickerFinish(resultfield)
{
	clickDatePicker(null,"","");
	alert("Blah");
}

This seems to work in every circumstance of a date picker that I’ve tested. However it hooks into all dates on the form and seems to fire twice for each change. In my example I declared MyDateField as a global variable (did anyone spot that?) and used it to test whether the control I was interested in had been activated:

function OnPickerFinish(resultfield)
 {
    clickDatePicker(null,"","");
    if (resultfield.id == MyDateField.id)
    {
        alert(resultfield.value);
    }
 }

You can build in some other test so that you don’t alert (or presumably something more useful) twice. My entire code then is:

<script type="text/javascript" language="javascript" src="/Code%20Library/jquery-1.8.2.min.js"></script>
<script type="text/javascript" language="javascript" src="/Code%20Library/jquery.SPServices-0.7.2.min.js"></script>
<script type="text/javascript" language="javascript">// <![CDATA[
function getTagFromIdentifierAndTitle(tagName, identifier, title) {
	var len = identifier.length;
	var tags = document.getElementsByTagName(tagName);
	for (var i=0; i < tags.length; i++) {
		var tempString = tags[i].id;
		if (tags[i].title == title && (identifier == "" || tempString.indexOf(identifier) == tempString.length - len)) {
			return tags[i];
		}
	}
	return null;
}
function TimeHasChanged() {
	alert('The time has changed');
}
//Override the OnPickerFinish function in DatePicker.js to fire the event when the date is updated from the date picker.
function OnPickerFinish(resultfield)
{
	clickDatePicker(null,"","");
	if (resultfield.id == MyDateField.id)
	{
		alert(resultfield.value);
	}
}
$(document).ready(function() {
	//alert("jQuery");
	//alert($().SPServices.SPGetCurrentUser());
	MyDateField = getTagFromIdentifierAndTitle('Input', '', 'Start Time');    
	MyDateField.onchange=function() {TimeHasChanged()};
	var MyMinuteField = $('#'+MyDateField.id+'Minutes');
	var MyHourField = $('#'+MyDateField.id+'Hours');
	MyMinuteField[0].onchange=function() {TimeHasChanged()};
	MyHourField[0].onchange=function() {TimeHasChanged()};
	MyDateField.onvaluesetfrompicker=function() {TimeHasChanged()};
});
function PreSaveAction()
{
	return true;
}
// ]]>
</script>
 

Scenarios in SharePoint 2013

The calendar new form in SharePoint 2013 looks slightly different:

What’s more significant though is that the End Time is no longer changed when the Start Time is updated. The feature seems to have suddenly disappeared; I can’t see it listed on: Changes from SharePoint 2010 to SharePoint 2013. What this means is that our old friend: onvaluesetfrompicker has started working for us again and we no longer need to use OnPickerFinish. This makes life better for picking up a change in the date control and means that Calendar dates are the same as custom dates. However if your users are used to the calendar date behaviour on 2010, they may not appreciate the change in 2013.

Posted in Uncategorized | Leave a comment

Running Total with InfoPath

On a recent project I had a requirement to create a running total in a repeating table in an InfoPath form.  I did a lot of searching and found several people that seem to be confused about what a running total is.  I wanted an extra column in my table that showed the total up to that line.  This turns out to be tricky to find but quite easy to implement.

I found an answer on Stack Overflow by Stas Slabko who describes how to calculate a product with a repeating table.  I used this as a basis for my running sum.

So we need a really simple InfoPath form with a repeating section inRunningTotalDesign

In the table’s footer I have a the TotalValue field that has a simple formula of sum(Value).  The running Total field on the repeating line has a slightly more complex formula as follows:

RunningTotalFormula

../my:Value + concat((count(../preceding-sibling::my:Item[1]) = 0) * 0, 
substring(../preceding-sibling::my:Item[1]/my:RunningTotal, 1, 
(count(../preceding-sibling::my:Item[1]) = 1) * 
string-length(../preceding-sibling::my:Item[1]/my:RunningTotal)))

The RunningTotal field on a line is calculated by adding the Value field to the RunningTotal of the preceding-sibling (i.e., the line above).  If we were to do this on the first row of the repeating table then preceding-sibling would return NaN (Not a Number).  In order to overcome this we check if the count of the preceding sibling is 0 and get either a true or false, we multiply that by a string length to trim out any values that would say NaN.

So how does this look in Preview:

RunningTotalExample

Stas in his article lists the following:

  • Works in a browser form in any sharepoint environment
  • Is not impacted by “16 calculations” threshold
  • The formula is robust to row removal and shifts

Be aware that this solution is impacted by the 16 calculations threshold.  When you update a row then this cascades to all the subsequent rows as cascaded calculations.  Therefore if you change a row that is too far back this will cascade and could hit the limit.  When I implemented I used a Formatting Rule to disable values that were too many rows from the end of the table.  However the way I did that will work only in client InfoPath.

Hope this article helps someone and that my version of a running total matches your version.

Posted in InfoPath | Tagged | 1 Comment

Yet Another round of Microsoft Updates stops SharePoint Services 3 with SQL Embedded from working

Deja Vu with the WSS 3 Updates.  Our support teams tell me of at least three SharePoint failures today. This must now be at least fourth time, that I can think of, a Windows Update has applied a patch to SharePoint Services v3 (there are still a lot of them out there) that it doesn’t recover from.  You seem to get a mixed bag of results dependent upon what Operating System and what SQL version was used and various other factors.

As a general guide we see many SharePoints that just carry on working, several that need Product and Technologies Config Wizard (PSConfigUI) run and a few hardcore ones that need more help.  As a general rule, if SharePoint was installed with SSEE/SQL Embedded/Standalone option it will fall over after some updates.  Running Product and Technologies wizard on its own won’t help as that will fail as well.  If you are in this situation, try following Article ID: 944154 it should get you back and running.

You always need to consider which database engine you are installing with.  SSEE will mean that you don’t have a database size restriction but it comes with a bunch of problems and issues that you also need to be aware of.  Remember with SQL 2008R2 Express Microsoft increased the size limit to 10GB.  Express edition has some of the drawbacks of embedded but it makes several of them go away.  If you are anywhere near that size for your content databases then you really should consider the features of Workgroup or above.

The GroupBoard Workspace template that Microsoft produced for WSS3, really seems to add to these woes

September 2011 last time we had a spate of these

Posted in SharePoint Services v3 | Tagged , , | Leave a comment

Content Database Growth on DB Attach Migration from WSS3 to SharePoint Foundation 2010

Introduction

I’ve done a lot of SharePoint Services 3 to Foundation 2010 migrations now and one thing I’ve learnt is that they all have to be treated individually on their merits.  A recent one was a move from WSS 3 on SBS 2003 using SQL 2005 Embedded Edition to a SharePoint Foundation as part of SBS 2011 Standard Edition using SQL 2008R2 Express Edition.

The WSS 3 had a single site collection in a single content database.  I was a little concerned that this database was 7.2GB and I’d have a 10GB limit on SQL Express that is on the Foundation Server.  However I thought I had plenty of spare space for this to work.

I did the usual steps to complete the Farm configuration on the new SBS box and applied WSS Service Pack 2 to the old SBS 2003 box.  The Pre Upgrade Check in STSADM gives you a special warning to let you know that you have a content database that is over 4GB and you might have to do something when you migrate.  Your options are along the lines of:

 

  1. Deploy SQL Workgroup or above and use that for the content database
  2. Remove content from the database before upgrading
  3. Use a time machine to return to point of installation and change the design to use multiple site collections in separate databases
  4.  Hope that it will fit following the upgrade

 Option 4 may be an attractive one but what if we use a SQL Standard in a test machine to trial the upgrade before we attempt to attach to the live SBS 2011 Server.

 Trial Upgrade

I restored the database to a test machine running SharePoint Foundation 2010 using a SQL Standard and used STSADM –o addcontentdatabase.  I didn’t pre-allocate any space to the database or the log so that I could see the files grow.  The database was set to Simple Logging

The STSADM starts counting its percentages and then stopped at 19.69% going no further.  A check of the upgrade.log file shows that it ends with:

[STSADM] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [5/14/2012 10:13:33 PM]: Populating AllDocStreams Content Column
[STSADM] [SPContentDatabaseSequence] [DEBUG] [5/14/2012 10:13:33 PM]: Executing SQL DDL Script.

SQL is still busy, so I use SQL Activity Monitor to see what it’s doing.   I can see one process of interest on my Content Database that is executing the following:

            INSERT INTO AllDocStreams (
                Id,
                SiteId,
                InternalVersion,
                Content,
                Size,
                RbsId)
            SELECT
                ADV.Id,
                ADV.SiteId,
                ADV.InternalVersion,
        ADV.Content,
              ADV.Size,
                ADV.RbsId
            FROM
                AllDocVersions as ADV
            WITH
                (TABLOCK)

Meanwhile the Log file and the database file both start growing and keep growing until the log file is 5.3GB and the database is 11.7GB.  This happens as the result of a single SQL transaction so, even on simple logging, the transaction log will grow excessively.  You can of course shrink the log afterwards.  If you try this on the live site with a 10GB limit the upgrade will fail on allocating space when the database file gets to 10GB.

Conclusion

In this case the customer archived old data out of the content database and we disabled the Recycle Bin to reclaim space.  The database could then be shrunk to 5.5 GB and upgraded to 9GB.  We now just need to deal with the limited capacity for growth in the content database, could really use that time machine.

Posted in SharePoint 2010, SQL Server | Tagged , , | Leave a comment

CRM 2011 Launch Dialogue from a Button

Introduction

Most of my work is with SharePoint and SQL Server.  However on a recent project I’ve been working with my ConcentrixTSG colleagues on a Dynamics CRM 2011 implementation.  CRM 4.0 was a good product but they’ve certainly added and improved when they released 2011.  On of the new features that mapped well to a customer process was Dialogs.  Currently a prospect may phone up and our customer would talk them through the questions on a New Business Enquiry Form.  These paper forms are then passed around several areas of the business and recorded on some Excel based registers.  These registers are used for tracking and conversion analysis.

This seemed to map well to launching a dialogue that would provide the questions, record the answers and feed them into a new Lead record within CRM.  There are two main issues with CRM to overcome:

  • CRM 2011 Processes, whether workflows or dialogues, are still bound to an instance of an entity.  So you must already have a Lead to run a dialogue to create a lead
  • I wanted to be able to deliver a button on the ribbon that would launch the dialogue in a user friendly way.

It turns out that these two issues take a fair degree of effort to get around. Fortunately I found some help on the Internet.  The articles that I mention here both helped me but I wanted to record my own experience as well.  For my screenshots you’ll see that Lead has been renamed to New Business Enquiry.

Lead Creation Dialogue without a lead to run it against

The answer here to to run the dialogue off of an entity instance that you know will already exist, collect your answers and then use the dialogue to create a new lead.  CRM Trick Bag gave me the answer here.  When you create the Dialogue Process in CRM 2011 and you have to pick an entity, choose the User entity.  The currently logged in user can always select their user record and press the Start Dialog button.  They can then choose to launch the Lead Data Capture Dialogue, fill in the fields and on completion the dialog will create a new Lead based on the data captured.

For our Ribbon button we are going to put the URL required to call the dialogue directly onto the ribbon.  We thus need to capture that URL somehow.  Once your dialogue has been created and activated, select a user, press the Start Dialog button and choose your dialogue.  On the Dialogue’s first screen that comes up press CTRL+N.  This will launch a new screen.  From this screen you can look at the URL and capture the GUID for the process.  You’ll see this on the Trick Bag, you are looking for the first bit after %7b and up to %7d, these are the escaped versions of { and } used to surround a GUID.  Copy that into a notepad window and keep it for later.

Add a button to the Ribbon

In CRM 4.0 this was a little bit tricky, in 2011 in much harder.  Most of my guidance for this comes from CRM Mongrel so you’ll maybe want to look there as well.  However the first thing you want to do is create some Web Resources so that the button will have something to do.

Create some Web Resources

Go into Settings/Customisations and open the default Solution or one of your own solutions, click into Web Resources and create a new Script file web resource.  You can see that my function constructs a URL using Xrm.Page.context.getServerUrl() to find where you are and Xrm.Page.context.getUserId() to find who you are.  The function then does a window.open to launch the dialog in a new window.  This will pretty much work as-is in most situations as long as it has the correct URL that you got in the previous section.

Web Resource
Web Resource LaunchNBEF

image
The Web Resource editor.  This one shows the function that launches the Dialog, GUID has been obscured but your GUID will be different to mine.

function callDialog() {
    var url = Xrm.Page.context.getServerUrl();
    url += "/cs/dialog/rundialog.aspx?DialogId=%7bED03DEEA-6AD6-4BE9-92C7-2C831071419D%7d&EntityName=systemuser&ObjectId=" + Xrm.Page.context.getUserId();
    window.open(url, "", "status=no,scrollbars=no,toolbars=no,menubar=no,location=no");
}

For the time being, although the resource needs to have a function defined, it can just be a simple alert(“you pressed a button”).  You can then get the button in and working before pasting in the correct function.

Our button is also going need an icon.  for my example I reused the one from CRM Mongrel you need to add this as another web resource

Add the Ribbon Button

We are then going to edit the ribbon and

  • Add a command that calls the function in our first web resource
  • Add a button that has the picture from our second resources and when pressed calls the command

Buttons on the ribbon can only be added in valid places on the ribbon.  In order to find a valid place you need to look at the SDK, find the ribbon for your entity and look what position numbers the existing buttons have been given.  In the SDK you need to look in: \resources\exportedribbonxml\.  You’ll find that there is a separate file with the ribbon xml for each of the built in ribbons.  Open the one for the entity that you are interested in and look through it.

To put a button onto the main application you are going to be in the sections about: Mscrm.HomepageGrid.  A Lead form would be: Mscrm.Form.lead.MainTab.  The thing that you need to pay attention to is the Sequence numbers so that you can position your own button.

There are several ways to edit the actual ribbon, my approach was to use the Ribbon Editor for Microsoft Dynamics CRM from Codeplex.  This doesn’t (or at least didn’t) work with Claims Auth enabled; my scenario was pre-live so I turned claims off for the work and back on after.

The first thing is to add a Command Definition to the Lead entity (renamed in this scenario).

Command Definition

I gave it one rule to always enable the button.  This means that anyone who can get to Leads can press the button.  This could be made more complex so that only people with rights to create Leads can have the button.

image

Then define the Action for the Command Definition that calls the function in the web resource that we defined earlier.

image

Having got the Command defined we can now add a Custom Action that will be represented by a new button:

image

The Location box has a text string that contains the information we read in the ribbon xml.  In my example:

Mscrm.HomepageGrid.lead.MainTab.Workflow.Controls._children

We then need to define all the properties for the buttonimage

Sequence:60 – identifies the place we chose for our button in ribbonxml
Template:o1 – identifies a big square button

The rest is labels, image, tooltips and a pointer at the command we defined earlier.

CONCLUSION

There’s no getting away from it this is a long and complex process but I think the results of following it through create a good user experience for custom functionality.

Posted in Uncategorized | Leave a comment

External Data List Open in Dialog Missing

On a recent project I have an external data list showing some data from a SQL database that is part of an LOB system.  When I click any of the items in the list I’m getting the list’s display form but it launches in a dialogue.  I really want it to be a full-page so List Tools/List/List Settings but Advanced settings isn’t there because it’s an external data list.

One of the things that would often work in a previous version if a setting got trimmed out of the UI is just construct the URL to the setting.  My list is on the Admin subsite of my web application, so I can use:

http://sharepoint/Admin/_layouts/advsetng.aspx?List=

and then put the GUID of my list on the end.  I get the advanced settings with one option Launch Forms in a Dialog.  If I put this to No and press OK, I can turn dialogues off on the list, just like any other.  There really was no need to trim this setting out of the interface

Posted in SharePoint 2010 | Tagged , , | Leave a comment

Logging in the SSIS Script Task and Script Component

Recently I had some fun with logging in the SSIS script component and I thought I’d share it. I have an SSIS package that uses scripts in a data flow to write to Dynamics CRM Web Services. I wanted to add some logging to record the records that got updated. I found the MSDN article Logging in the Script Task but was unable to follow it. The article says that I should be able to use:

Dts.Log(“Rows processed: ” + rowsProcessed.ToString(), 0, emptyBytes);

However when I tried it there was no Dts.Log to be found. Well the answer is this.

Control Flows have Script Tasks and Data Flows have Script Components. Logging is separate and the way it is handled is different.

To Enable Logging for the Script Task:

  • On the SSIS Menu select Logging.
  • Add a Provider (I’m using the Log Provider for SQL Server and under its configuration have selected an OLEDB connection manager that points at the database that I want to record the logs in
  • Enable Logging for the Package in the Containers section
  • Click the grey tick for the script task that you want to log (sequence containers that house it/them)
  • Add log code as per the link above to your scripts
Log Provider for SQL Server Image

Log Provider for SQL Server

Script Task Log Settings Image

Script Task Log Settings

To enable a Script Component of a Data Flow task to be logged, select the data flow task and enable the appropriate logging:

Script Component Log Image

Script Component Log

Once the logging is enabled then you just need to add a few lines of code to the script component and/or the script task.

To test and demonstrate I created a new, simple package on a test system.  My simple package looks like:

Control Flow

Control Flow for simple package

In my single data flow I have an XML source that takes its XML from a string variable with a manually typed two lines and then a script component:

Script Component in Data Flow Task

Script Component in Data Flow Task

In my Script Task I can use code from the article linked at the beginning:

//script task
 public void Main()
 {
   byte[] emptyBytes = new byte[0];
   Dts.Log("Log from a Script Task", 0, emptyBytes);
   Dts.TaskResult = (int)ScriptResults.Success;
 }

There is an example of the code to use in a script component on a data flow task. Logging in the Script Component. The example uses VB.Net. I had a go at converting that to C# and came up with

//script component
public override void PreExecute()
{
  base.PreExecute();
  byte[] emptyBytes = new byte[0];
  this.Log("Script Task in Dataflow Component startup", 0, emptyBytes);
}

What are the results of this then, my SYSSSISLog table shows the following

SYSSSISLog

SYSSSISLog

Posted in Uncategorized | 2 Comments