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.

February 5, 2013

Excel Services Troubleshooting

Filed under: Administration,SharePoint,Troubleshooting — Lars Nielsen @ 7:11 pm
Tags: ,

Here are some issues, traps and resolutions you might hit when setting up Excel Services in SharePoint 2010

Firstly here’s a quick review of the steps that you need to go through to provision Excel Services. The best rundown I have found on this is here

In my case I wanted to create a specific service for Excel Services, which means also I needed to set up an application pool for Excel Services. I think it’s better to have a dedicated service account in case I ever need to grant access to file shares to that account, or configure Kerberos settings on the account. You can also use the SharePoint Web Services Default application pool and its associated app pool account.

Here are the steps in order:

1) Create a service account for Excel Services. It doesn’t need any special group membership or permissions. I called mine sp_ExcelServices

2) Register the account sp_ExcelServices as a managed account in SharePoint

3) Start the Excel Services service on one or more servers (use Manage Services on Server link in Central Admin)

3) Create a new Excel Services service application. Choose to create a new application pool using the sp_ExcelServices managed account

5) Run the Powershell commands to grant access to the sp_ExcelServices account to the appropriate web application(s) where you want to use Excel Services.


$wa = Get-SPW1ebApplication -identity http://myweb.mydomain.com
$wa.GrantAccessToProcessIdentity("MyDomain\sp_ExcelServices")

6) Activate the SharePoint Server Enterprise features for both the Site Collection and the Site

After that you should be able to load an Excel spreadsheet into a document library and view it in the browser via Excel Services.

Here are some problems you might hit when you try to open a workbook.

The workbook cannot be opened

Two possible causes of this one:

Check if the Excel Services server is not started on any of the servers in the farm. Check this in Central Admin – Application Management – Manage Services on Server and start the service if necessary.

Also you can check that the Excel Services app pool account has access to the web application. You can check this with the User Policy screen for the Web Application. You should see an entry for the managed account for Excel Services. For good measure you can run the Powershell commands in step 5 again.

Web Application Policy

Wait a few minutes and try performing this operation again

If you get this when you try to open an Excel file then check the error logs and the Windows event log on the web server. You might see this error:

Error 5244

Unexpected error when trying to access service settings in the configuration database. Make sure the proxy for this service application is a member of the default proxy group for the active web application. Error = ExcelServerWebServiceApplication.Local: Could not get the web application associated with this context. This indicates that the Excel Server service is not properly registered or provisioned..

This might be caused by incorrect database permissions on the Configuration database.  Note that in this link they say that the Excel Service Application Pool account requires db_owner role. But I’ve found that its default WSS_Content_Application_Pools role seems to be enough.

However if you can run the Get-SPExcelServiceApplication command in Powershell and it runs OK, then it probably is not a problem with permissions on the config database. It might be that you have not associated the Excel Services service application with any application proxy groups. To resolve this go to Central Administration and in the Application Management screen click on Configure Service Application Associations. Then the simplest thing is to check if Excel Services is in the default association group (assuming that your web application is using that default group).

Service Application Proxies

Workbook cannot be opened because it is not stored in an Excel Services Application trusted location

Go to the management screen for Excel Services and click on the Trusted File Locations link.  Here you can see a list of locations that are trusted to provide Excel workbooks to Excel Services.  By default there’s usually one “HTTP” which is set to trust all children in SharePoint.  That effectively means everything in every SharePoint web application in the farm.  So you shouldn’t see any trust errors if your Excel files are in SharePoint.

But notice that it says HTTP and not HTTPS.   What if you are using Alternate Access Mappings to provide an SSL version of your SharePoint site?  Or you’re using Forefront UAG to force SSL?  In that case you might see this trust error when using the SSL address for the site, and you need to add a trusted location that starts with HTTPS, such as the hostname of the web application.  So for example if your Intranet is mapped to an externally available address, you might want to trust all the children of
https://extranet.myorganisation.com

You can’t see the Excel Web Access web part in the web part gallery or the Excel file doesn’t open in the browser

It might be obvious, but just check that the SharePoint Server Enterprise feature is activated on the Site Collection and the Site

Excel Web Access web part doesn’t update

If you have an Excel file that is being rendered onto a page via an Excel Web Access web part, then you update the Excel file, then refresh the page, you’d expect to see the changes on the Excel file to be reflected on the page. But there is caching in Excel Services and you might need to wait a few minutes before the changes show up in the web part on the page.

I’ve also found that sometimes, if you edit the Excel Web Access web part properties and hit the Apply button, it loses its named range property and you have to fill it in again. And you can’t use a server-relative URL for the location of the workbook in the web part – it always fills in the fully qualified URL.

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 27, 2012

SharePoint doesn’t overwrite a document with the same name, but creates duplicates

Filed under: Content Management,SharePoint,Troubleshooting — Lars Nielsen @ 8:27 am
Tags: ,

Here’s a strange SharePoint oddity a colleague of mine saw recently.

We found that in a certain case, SharePoint looked as if it would not overwrite a Word document when we uploaded a new version of the document, even though the uploaded document has exactly same name as the existing one.  Instead SharePoint appeared to create a fresh new copy of the document, but with exactly the same name as the existing one.  So we ended with (apparently) 2 different Word documents, both with exactly the same name, in the same document library!  Here’s what we did and what was really going on:

The document library was set up to keep only major versions of documents, no enforced check-out and no content approval.  We uploaded a document into the document library with a filename of (let’s say) Departmental Resources Statement (2011-2012) .docx

The document library had a content type that required two metadata items for Title (text) and Category (choice drop-down).  So we filled in the metadata and the document appeared in the document library as expected.

Next we edited the document outside of SharePoint and uploaded it again, making sure that the tickbox was ticked to overwrite existing documents to create a new version.  On the next screen we expected to see the metadata (Title and Category) of the existing document.  But instead we saw a blank Title textbox and the default choice for Category.  So we re-entered the title again and selected the category again, the same as before, and clicked the OK button.

Next SharePoint threw an exception screen with an error message:

The specified name is already in use. A document cannot have the same name as another document or folder in this document library or folder.  Use your browser’s Back button, and type a new name.

On looking at the documents in the document library, we could see that there were now apparently 2 copies of the document both called Departmental Resources Statement (2011-2012).docx but with different Last Modified dates.

The cause of all this was the actual filename of the original document on my colleague’s PC. The document was called (look carefully!):

Departmental Resources Statement (2011-2012) .docx

See that space character after the closing parentheses and before the dot?  When you upload the document into SharePoint, on the metadata screen it trims out whitespace either side of the filename (not the extension) so that in the SharePoint document library, this file became named:

Departmental Resources Statement (2011-2012).docx

Then when we edited the original source document and uploaded it, it was still called

Departmental Resources Statement (2011-2012) .docx

SharePoint first uploaded the file into the document library, but because of that extra space it saw the filename as different to the existing document (from which the space character has been removed) so it added it as a new document.  Then it presented the metadata screen and again SharePoint trimmed off the extra space in the name. So when we hit the OK button, SharePoint saw we were trying to give this new document the name:

Departmental Resources Statement (2011-2012).docx

And because there was already a document with that filename in the document library, it threw the error. But the 2nd copy of the document had already been uploaded at this point, so both documents were visible in the document library. On the regular document library listing in SharePoint, in proportional font, that extra space character in SharePoint is practically invisible so it looked as if both documents had the same name.

Removing the extra space character in the filename solved all this. So that was it.  Simply don’t put space characters at the beginning or end of the filename and if you do, you might see the behaviour above.  Hope this helps if someone hits the same thing one day.

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!

Next Page »

Blog at WordPress.com.