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

February 10, 2011

Site column gallery and “The given key was not present in the dictionary”

Filed under: Customization,SharePoint,Troubleshooting — Lars Nielsen @ 8:02 pm
Tags: ,

In creating a custom field type recently, I got my site collection into a state where I could no longer look at the site columns gallery in Site Settings.

The given key was not present in the dictionary

This happened after I had retracted the feature that deployed the custom field type, but there was still a field of that type left in the site column gallery. I eventually fixed this by redeploying the feature to bring back the user controls and fldtypes_OOOO.xml files for the custom field types.  That meant I could get to the site columns gallery, but there was one site column which I could not delete or edit because whenever I did, I got an exception screen.

I had to remove the offending site column through code.  To do this I followed through this thread and simplified the page code in there so I could be sure that I was doing what I intended to do, and deleting only the field that I wanted to delete.  There’s no way to recover a site column once it has been deleted – it doesn’t go in the recycle bin.

I created a page called DeleteMyField.aspx and put into the LAYOUTS folder in the 12 Hive.  This will delete a site column with the static name that you put into the call to DeleteField in the Page_Load method on line 14 of the listing below.  In this case the field is called “MyCustomFieldName” but you can substitute in whatever your site column is called.  Note that I had to set AllowUnsafeUpdates to true on the site.  To use this page you add this file into the LAYOUTS folder in all the web servers.  Then browse to the page in the site where the site column is.  For example if the site column is in this site:

http://mydomain.com/sites/HR/payroll

Then you would browse to:

http://mydomain.com/sites/HR/payroll/_layouts/DeleteMyField.aspx

Then after that, if you go to the site column gallery on the site you should find that the site column has been removed.

Here’s the code for the DeleteMyField.aspx page:

<% @ Page Language="C#" Debug="true" EnableSessionState="true" ValidateRequest="False" MasterPageFile="~/_layouts/application.master"%>
<% @ Import Namespace="Microsoft.SharePoint" %>
<% @ Import Namespace="Microsoft.SharePoint.Utilities" %>
<% @ Import Namespace="System.IO" %>
<% @ Import Namespace="System.Xml" %>
<script language="C#" runat="server">

private void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
     try
     {
        DeleteField("MyCustomFieldName");
     }
     catch (Exception ex)
     {
        this.results.Text += ex.Message + "<br />" + ex.StackTrace + "<br />";
     }
  }
}

private void DeleteField(string fieldStaticName)
{
   SPWeb w = SPContext.Current.Site.RootWeb;
   w.AllowUnsafeUpdates = true;
   w.Update();
   this.results.Text += "Attempting to delete field " + fieldStaticName + "<br/>";
   w.Fields.Delete(fieldStaticName);
   this.results.Text += "Deleted OK<br/>";
   w.AllowUnsafeUpdates = false;
   w.Update();
}

</script>

<asp:content id="Content1" contentplaceholderid="PlaceHolderMain" runat="server">
<asp:literal runat="server" id="results" />
</asp:content>

January 31, 2011

Custom EditForm.aspx page error “The page you have selected does not contain a reference to the correct SharePoint list”

Filed under: Customization,SharePoint,Troubleshooting — Lars Nielsen @ 10:52 pm
Tags: ,

If you are trying to create a custom property editor screen for a document library (to replace the default EditForm.aspx page), you might follow Jan Tielens instructions on how to do it for a normal list.  That works fine for a normal list, but when you try to do the same with a document library, when you click the Apply button in the Supporting Files tab in SharePoint Designer you’ll get an error:

The page you have selected does not contain a reference to the correct SharePoint list. Please select another page

To avoid this you need to make sure that the original ListForm web part is still there in the markup on the page, but you make it invisible and add a custom list form web part above it.  But you also need to remember that how to create your custom edit page is important as well.  You might think the easy way is to do this:

  • Open the site in SharePoint Designer
  • Select the EditForm.aspx in the Forms folder of the document library
  • Right click – Copy
  • Select the Forms folder
  • Right click – Paste

That will create a copy of the EditForm.aspx file that you can rename and work with but you keep getting the error above when you try to apply it to the document library.  So instead create the custom editor page  like this:

  • Open in SharePoint Designer
  • Open the EditForm.aspx in the Forms folder of the document library
  • Save it with a different filename

Now start editing your new page.  Remember not to delete the existing ListForm web part, just make it invisible.

January 20, 2011

Only e-mail when task is assigned, not when it changes

Filed under: Customization,Development,SharePoint — Lars Nielsen @ 3:16 pm
Tags: ,

One complaint that end users sometimes mention about the workflows in SharePoint, even the built-in workflows, is that they are flooded with auto-generated e-mails about their task.

If you’re developing your own workflow, or if you use for example the Approval built-in workflow, then you need to tell the workflow what tasks to use when you associate the workflow with a list or content type. The list is set up to send an alert to the “Assigned To” person (or group – did you know you can assign a task to a SharePoint group? Very useful. Everyone in the group gets an e-mail to say they have been assigned the task). The alerts come for every change to the task, when it’s first assigned, when it’s completed, when it’s deleted.  At minimum everyone who is assigned the task gets 2 emails.  But you can change this so that they get only one: the “You have been assigned a task” e-mail.  Often, that’s all they need.

To do this you need to change the alert on the task list.  This is created automatically by SharePoint.  You can use SharePoint Manager to change it manually.  But you can also do it programmatically, for example in a feature OnActivated method so that it runs every time you activate the feature.

Here’s a method you can use to reset the task alerts so that it only alerts use when they are assigned a new task:


/// <summary>
/// Set the alerts for a task list so that it only sends alerts for new tasks assigned, not when
/// tasks are updated or completed. This reduces the e-mail traffic related to tasks
/// </summary>
/// <param name="web" />Site that contains the task list
/// <param name="taskListName">Display name (title) of tasks list
/// <returns>True if changed, false if no relevant alerts were found</returns>
public static bool SetTasksAlertsToNewOnly(SPWeb web, string taskListName)
{
   bool result = false;
   SPAlertCollection alerts = web.Alerts;
   foreach (SPAlert alert in alerts)
   {
      if (taskListName == alert.List.Title)
      {
         alert.EventType = SPEventType.Add;
         alert.Update();
         web.Update();
         result = true;
      }
   }
   return result;
}

August 15, 2010

Calculated field for age or time passed

Filed under: Customization,SharePoint — Lars Nielsen @ 10:09 pm
Tags:

A short post on how to create a calculated field that shows someone’s age, or how much time has passed since a date (for example, how long since a document was last modified).  I’m just pulling together various blog posts that cover this into a simple set of instructions. So let’s say you have a list of people with a column called DateOfBirth, and you want a calculated field that shows their age in years and months.   To work out the age:

Step 1 – Create a new column in the document library called Today (the name is important), a single text column

Step 2 – Create a calculated column called Age – it doesn’t matter what you name it, again single text. The formula for the Age column is:


=IF( [DateOfBirth]="" , "" , DATEDIF( [DateOfBirth], [Today], "Y") & "y " & DATEDIF( [DateOfBirth], [Today], "YM") & "m" ) )

Step 3 – Save the calculated column, and then delete the Today column. The Age column will show the age in the form “35y 4m”.  You can use the DATEDIF function to pull back the age in other ways, e.g. days, completed months.  If the Age column is showing #NUM! it’s probably because the DateOfBirth value is after today’s date, so DATEDIF is returning a negative number.

To edit the Age formula, you first need to create the dummy Today field again, and then delete it again after making changes to the formula. Obviously you can use the same technique to show how much time has elapsed since a document was last changed – just use [Modifed] instead of [DateOfBirth] in the formula.

Blog at WordPress.com.