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

1 Comment »

  1. Yeah, cool, the algorithm has been on the NHS Data Dictionary website for years…so few people bother to look at it. And too many people are still doing stuff in the presentation layer when it should be in the business layer. Ho hum

    Comment by Lars Haggqvist — February 25, 2014 @ 8:44 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: