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

Create a free website or blog at WordPress.com.