Lars Nielsen's Discoveries

February 21, 2014

Validate UK NHS Number in SharePoint

Filed under: Customization,Development,SharePoint — Lars Nielsen @ 7:50 pm
Tags: , , ,

Just to try to get back to blogging again I thought I would post a function that will validate a UK NHS (National Health Service) number in SharePoint. You can use this on any list or library where you need people to enter a valid NHS Number.

NHS Numbers use a check digit to self-validate. This page describes the format and has a tool to check for a valid number

In SharePoint you can specify a validation formula for a column.  So in my example I created a column on a list called NHSNumber and in screen that defines the column, I set the maximum number of characters to be 10, and in the Column Validation section I used the following formula. You can basically just copy and paste this, and substitute your column name for “NHSNumber” in the formula

=IF(LEN(NHSNumber)=10,
	IF(NOT(ISERROR(NHSNumber+1)),
		OR((11-(MOD((LEFT(NHSNumber,1)*10+
				RIGHT(LEFT(NHSNumber,2),1)*9+
				RIGHT(LEFT(NHSNumber,3),1)*8+
				RIGHT(LEFT(NHSNumber,4),1)*7+
				RIGHT(LEFT(NHSNumber,5),1)*6+
				RIGHT(LEFT(NHSNumber,6),1)*5+
				RIGHT(LEFT(NHSNumber,7),1)*4+
				RIGHT(LEFT(NHSNumber,8),1)*3+
				RIGHT(LEFT(NHSNumber,9),1)*2),11)))=RIGHT(NHSNumber,1)*1,
			AND(
				((MOD((LEFT(NHSNumber,1)*10+
				RIGHT(LEFT(NHSNumber,2),1)*9+
				RIGHT(LEFT(NHSNumber,3),1)*8+
				RIGHT(LEFT(NHSNumber,4),1)*7+
				RIGHT(LEFT(NHSNumber,5),1)*6+
				RIGHT(LEFT(NHSNumber,6),1)*5+
				RIGHT(LEFT(NHSNumber,7),1)*4+
				RIGHT(LEFT(NHSNumber,8),1)*3+
				RIGHT(LEFT(NHSNumber,9),1)*2),11)))=0,
				RIGHT(NHSNumber,1)*1=0)
		)
	)
)

The formula above also checks that the string has 10 characters and that all the characters are digits, before calculating the check digit.

It’s not easy to test validation code, but as a check I used this great post to set up a spreadsheet column to  validate a string of text as an NHS Numbers.  I checked that my SharePoint formula is giving the same results as the spreadsheet formula, using several hundred randomly-generated 10-digit strings.  It all seems to work OK, but if you spot any errors please let me know.

Advertisements

January 19, 2013

JavaScript Patterns book

Filed under: Architecture,Development,SharePoint — Lars Nielsen @ 2:00 pm
Tags:

With SharePoint 2013 and HTML 5 it looks like the future is Javascript.  I’m personally not convinced that the demise of Flash will lead to improved security – many exploits are actually injected through Javascript and things like federated authentication provide new scope for exploits (just Google “Facebook fake logon” to find out all about it!).   But anyway clearly Javascript is becoming more and more important and I found this great book, JavaScript Patterns, which demonstrates more than a few features of the Javascript language.  Reading this book you really begin to see how, despite being syntactically similar to C# or Java, Javascript is truly different.

Here’s an interesting example :


var city = "Washington";

function alertCity()
{
   alert(city);
   var city;
   city = "New York";
   alert(city);
}
alertCity();

If you try this code in a page you find that the first alert call outputs not (as you might expect) “Washington” but “Undefined”.   The second one outputs “New York”.  This is because the var city statement inside the alertCity function is hoisted up to the beginning of the function, as if it were declared at the beginning of the function.  It hides the global variable city and it becomes an uninitialised variable.  Later it’s initialised, and so the second alert behaves as you might expect.  It’s a good example of why you should always declare variables with a var statement at the beginning of a function.

Reading through this book I’ve come to realise that JavaScript’s treatment of functions as objects is in many ways closer to functional programming than many other of the “C-like” languages like Java or C#.  Most of the time I must admit I tend to write Javascript as if it was C# without types, and I guess many .NET or Java developers do the same. But it’s worth knowing that when working in Javascript, you can do clever things like curry functions which, in the C# world for example, have only relatively recently become possible with the introduction of lambda expressions.

October 1, 2012

Session State problems with SharePoint custom pages

Filed under: Development,SharePoint — Lars Nielsen @ 7:37 pm
Tags: , ,

It’s a common requirement to host or embed web applications within an Intranet in SharePoint. The quick-but-dirty solution to this is to use a Page Viewer Web Part or a custom HTML page that displays an external ASPX page within a SharePoint page inside an IFRAME. The problem with this is what happens when the content within the IFRAME changes – you’ll see either a big patch of white space in the frame (if it shrinks) or an embedded scrollbar (if it grows) – neither of which is very nice for end users.

But because SharePoint is itself written in .NET, you can do something more clever and you can create ASPX pages in a SharePoint site that reference exactly the same master page and branding as the site itself. I use this technique to create web applications outside of SharePoint and then copy the ASPX pages over into a SharePoint project so that they can be provisioned into a SharePoint site via feature activation. This has the neat advantage that a single application can be deployed several times into different SharePoint sites, simply by activating the feature on each site. And because SharePoint master pages are dynamic (if you have the Publishing features activated), the same ASP.NET web application can even be branded differently on different sites.

The original great articles by Andrew Connell describe this technique very well here  and here. Although these are rather old now, the same technique can be adapted to SharePoint 2010 and Visual Studio 2010.

Recently when I was working on this I came across a problem when trying to use Session State in the code behind to persist a value between 2 different pages. Everything worked fine outside SharePoint, in a native ASP.NET web application. But when I migrated the pages into a SharePoint site, and tried them, I got this error:

Session state can only be used when enableSessionState is set to true, either in a configuration file or in the Page directive. Please also make sure that System.Web.SessionStateModule or a custom session state module is included in the \<system.web>\ section in the application configuration.

I checked to see that session state was enabled in SharePoint and had a quick look at the web.config file for the web application. In the web.config there is a line in – <system.web> like this:

<pages … enableSessionState=”false” … />

So I thought that turns off session state for all ASPX pages by default. To use session state on my pages I added to the Page directive an attribute to turn on session state:

<%@ Page EnableSessionState=”true” … %>

I refreshed the main page and saw this error instead:

The enablesessionstate attribute on the page directive is not allowed in this page

At this point I looked around the web and found this discussion.  But I didn’t want to mess around with the PageParserPaths element in the web.config. I thought there must be a better way, and found this discussion ( where it seems that resetting the pages to the site definition will fix it.  I tried resetting to the site definition, but it didn’t help.  Then I found this gave me a clue.

I tried changing the master page in my SharePoint site, which was a custom master page, to the out-of-the-box default.master.  Suddenly the error disappeared!  I switched back to my custom master page and the error came back.  I even created my own custom master page which was a copy of the standard default.master page, and applied this to the site, and that copy still caused the same exception.   It seems session state will not work unless the master page is uncustomized.

For the moment I don’t how to get around this and I just design my code assuming that session state is not available, but if anyone has any good ideas how to use session state please let the world know!

July 13, 2012

SharePoint Designer, Reporting Services and UAG

Filed under: Development,SharePoint,Troubleshooting — Lars Nielsen @ 6:51 pm
Tags: , ,

I set up a SharePoint 2010 environment with Forefront UAG and Kerberos authentication throughout, all via SSL.  Now our SharePoint sites can be published out to the Internet with a protection barrier.  It all works fine for general browsing and opening documents.  And now finally I  have solved the problem of people having to authenticate again when opening documents from SharePoint…  With UAG they authenticate once when they browse to the site and then that’s it.  They can click to open documents from SharePoint, edit documents,  and even open the document library in Explorer view without having to authenticate again.

So having got all that working, I thought: let’s push the envelope a bit and see what happens when we try to use Reporting Services reports in SharePoint Integrated mode over UAG.   Not only that, but the SSRS reports we were trying to deploy into SharePoint included hard links on the report which reference the Report Server URL directly using the ReportServerURL built-in field in the link.  So for example with regular Reporting Services your report server URL might be http://MyServer/ReportServer and then dynamicallythe report creates a link like this:

http://MyServer/ReportServer?https://myportal.domain.com/Reports/Myreport.rdl&rs:Command=Render&MyParameter=Something

That’s fine when the browser can get to http://MyServer but of course once you’re out on the Internet that URL won’t work because there’s no public DNS entry for it.  So the link will break.  You can work round this because SharePoint creates a proxy address for the Report Server which you can use instead.  The URL is like this:

https://myportal.domain.com/_vti_bin/ReportServer

OK so then this should work as the link to the report:

https://myportal.domain.com/_vti_bin/ReportServer?https://myportal.domain.com/Reports/Myreport.rdl&rs:Command=Render&MyParameter=Something

There was also a requirement for a link that directly exports the report as a PDF, using an additional query string parameter:

&rs:Format=PDF

So I thought just tag that on to the end of the main URL like this:

https://myportal.domain.com/_vti_bin/ReportServer?https://myportal.domain.com/Reports/Myreport.rdl&rs:Command=Render&MyParameter=Something&rs:Format=PDF

But when I tried this URL, instead of downloading a PDF file of the report I just got a 500 internal server error from the web server.  I tried adding an entry in my HOSTS file for myportal.domain.com to bypass the UAG server and connect directly to the SharePoint web server.  Then the link above worked OK and the download PDF popup box appeared.  So UAG is doing something to break that link.

The SharePoint ULS logs pointed to the problem.  Every time I got a 500 errors from Reporting Services I say errors like this in the logs:

Unexpected error in ProcessRequest: System.Net.CookieException: The ‘Value’=’2012,6,13,13,14,27,56,0’ part of the cookie is invalid.     at System.Net.Cookie.VerifySetDefaults(CookieVariant variant, Uri uri, Boolean isLocalDomain, String localDomain, Boolean set_default, Boolean isThrow)     at System.Net.CookieContainer.Add(Cookie cookie)     at Microsoft.ReportingServices.SharePoint.Soap.Utils.SetRelayRequestCookies(HttpWebRequest relayRequest, HttpRequest clientRequest)     at Microsoft.ReportingServices.SharePoint.Soap.RSBaseHttpHandler.ProcessRequest(HttpContext context) 2b0eb16a-e8df-4d6c-8cb8-3741011353f4

It looked as if there was some problem with a cookie with a value of 2012,6,13,13,14,27,56,0 – some kind of time stamp apparently.  I had a look at the site in Chrome and used the developer toolbar Resources tab to look at the cookies in the browser.  Sure enough there was a cookie called dwLastDetectionTimestamp with a value that looked like a timestamp with commas between the values.  I deleted this cookie manually from the browser and tried the URL again and it worked.  So it was that rogue cookie that was causing Reporting Services to fail.

Chrome Cookies

It turns out UAG injects a Javascript file called logoff.js into every web request that passes through it.  This Javascript file is what does the work of checking whether the activity timeout period has run out and whether to warn the end user of an impending auto-timeout.  It enters into an infinite recursive loop, checking that value of the cookie and if it’s null, setting it to be the current date and time, then checking to see if a certain period of time has elapsed.  Reporting Services for some reason inspects that value of this cookie but cannot understand it (because it belongs to UAG) and thus throws an exception.

The way round this is to override the UAG script and redefine that loop function to set the value of that cookie to be some non-null value, which deletes it from the cookie set.  I created a standard page in SharePoint to act as the target for Reporting Services reports.  The SharePoint page itself had a single Content Editor web part in it with a reference to my Javascript file which looks like this:

/* Strip the dwLastDetectionTimestamp cookie off the request and redirect it to the Report Server proxy */
function compareTimeStamps()
{
   $.cookie("dwLastDetectionTimestamp", null, { path: '/' }); // delete cookie
}

$(document).ready(function() {
  var FullUrl = window.location.href;
  var QueryString = FullUrl.split("?")[1];
  var NewServerUrl;
  if ((QueryString.match(/^https:\/\/myportal.mydomain.com.+$/ig))) {
    NewServerUrl = "https://myportal.mydomain.com/_vti_bin/ReportServer?" + QueryString;
    $.cookie("dwLastDetectionTimestamp", null, { path: '/' });
    window.location.href = NewServerUrl;
  }
});

To use this script you’ll need to have <script> tag references to JQuery and to the JQuery cookie plugin.  And of course you need to replace myportal.domain.com with the address of the root of your SharePoint site. The code in the script strips out the query string from the incoming request URL and checks to see if it’s a request to Report Server, and if so then it removes the cookie and redirects the browser to the Report Server proxy with the same query string.  Note the regular expression test (QueryString.match) which prevents the redirect happening when you are editing the page.  It will only redirect if the first thing after the question mark is a URL string.  The script also redefines the compareTimeStamps function within the script that UAG injects.  This is the function that is called continuously to check for auto-timeout.

In a report or a SharePoint page when you need to create a link that would use the URL of Report Server, you use instead the URL of the SharePoint page that holds this script. Add any query string parameters as required just as if you were going to the Report Server directly.

April 2, 2012

PowerShell ForEach behaviour with null and empty lists

Filed under: Administration,Development,SharePoint — Lars Nielsen @ 6:23 pm
Tags: ,

Watch out when using ForEach in Powershell scripts.

Here’s a simple script that enumerates the site collections that have “portal” in the URL, and lists their URL’s:

Get-SPSite | Where-Object {$_.Url -like "*portal*"}

If you wanted to something more complex with each SPSite you might want to use a ForEach loop like this:

$sites = Get-SPSite | Where-Object {$_.Url -like "*portal*"}
foreach ($site in $sites) {
  Write-Host "Site is SPSite Url=$site"
}

This will output something like this:

Site is SPSite Url=http://portal.mydomain.com
Site is SPSite Url=http://portal.mydomain.com/sites/site1
Site is SPSite Url=http://portal.mydomain.com/sites/site2

But what if you change the condition in the Where-Object to something which doesn’t match any of your site collections, like this:

$sites = Get-SPSite | Where-Object {$_.Url -like "*8h78fw7rhfr78*"}
foreach ($site in $sites) {
  Write-Host "Site is SPSite Url=$site"
}

How many times will the ForEach loop execute in this case? If you’re a C# developer you’ll say zero – it won’t execute at all. Or you might expect an error – depending on whether $sites is a zero-length array, or null. But actually in Powershell this script above outputs this:

Site is SPSite Url=

The ForEach loop does execute, once, with a null value.

However, if you don’t use the $sites variable then it behaves differently, so if you do this:

foreach ($site in (Get-SPSite | Where-Object {$_.Url -like "*J9898fsh*"})) {
     Write-Host "Site is $site"
  }

It will output nothing and the ForEach loop doesn’t execute.

I think this is because of the way ForEach works will $null values. If you want to use variables and ForEach you need to include an explicit check for null values, which in fact you would normally do in C# anyway to be on the safe side:

$sites = Get-SPSite | Where-Object {$_.Url -like "*J9898fsh*"}
if ($sites -ne $null) {
   foreach ($site in $sites) {
     Write-Host "Site is $site"
   }
}

In this case the ForEach loop will not execute when $sites is null, neither when $sites is a zero-length array.

Next Page »

Create a free website or blog at WordPress.com.