SharePoint list migration between sites – a few lessons learned

What a week! Believe it or not, my job isn’t one as a SharePoint administrator, although sometimes it feels like it is (and recent blog posts might suggest otherwise)!

The thing about SharePoint is that even non-developers like me can string together a few webparts and lists to create something reasonably useful. Last week, one of the sites that I’m managing as part of my day job was migrated from its incubation location as a subsite of my team’s portal, to join some related content on another site. It should have been fairly straightforward, but sadly that wasn’t the case…

Updating hyperlinks

I knew that I needed to edit the hyperlinks on some of my pages but I forgot that the webparts showing views of my lists would also need to be changed, meaning that the data was actually being displayed from the old site (still left in place until I knew that the migration had been a success). I couldn’t find a way to just edit a link to the list, so I had to replace each webpart with a new version, picking up the appropriate list (and view).  That was issue number 1 sorted.

Corrupted lists with orphaned columns

Most of the lists in my site came across without issue but the largest one (30-odd columns and around 350 items) was generating an error, with the expected data replaced by “One or more field types are not installed properly. Go to the list settings page to delete these fields”. I found various articles about this, but they all seemed to relate to migrations from SharePoint 2007 to 2010.

Then, as I was digging around in the site, I found a “hidden” column that wasn’t visible in the list settings but could be seen when editing list views. The name of the column was familiar though – it matched a workflow that someone had created on the original (source) site but which wasn’t attached to the list. Apparently, when SharePoint starts a workflow on a list for the first time, it adds a workflow status column to the default view of the list  and it seems that “orphaned” workflow status columns are not unheard of. I tried creating another column with the same name (to then delete it again) but, predictably, that wasn’t possible (although creating a new workflow with the same name did create a duplicate column, which was deleted when the workflow was removed).

A little more research turned up “official” advice from Microsoft that suggested these orphaned columns couldn’t be deleted but, as is often the case, there is a way to do it with third party software and a bit of coding, as described by Nik Patel at his SharePoint World. Unfortunately, the SharePoint Manager tool that Nik used needs to be run on the server – there’s no chance of me being able to do that – but our SharePoint admins were not convinced that the orphaned column was causing the issue anyway and found some columns using lookups that were not correctly linked following the list migration. These couldn’t be edited (at least not through the SharePoint web interface), so they were deleted and recreated, before I copied the data back into them. A page refresh later and the list was displayed as expected (so the orphaned column was not the cause), although there was still one final problem…

Broken New and Edit forms

I still had an issue in that attempting to add a new item to the list resulted in “an unexpected error has occurred” messages, which are far from helpful!

A colleague had spotted that the NewForm.aspx and EditForm.aspx forms were incorrectly linked (the list had been renamed at some point and for some reason the original list name was still being used in the path to the forms) but that was easily corrected in SharePoint Designer. Even so, adding or editing an item in the list was generating errors and I was running out of ideas (as were my colleagues).

I thought that I had spotted all of the differences between the two copies of the list (source and target sites) so, I conceded defeat and started to recreate the list from scratch (before copying in the data in data sheet view – I know I can import/export via Excel, but that sometimes results in incorrect column types that can’t be edited). That’s when I found some more corrupted lookup columns. I couldn’t edit them (at least not through the SharePoint web interface) so, again, I deleted and recreated them, before repopulating the data. All of a sudden, my site was working as it should have been – hooray!


All of the problems I had were avoidable, with the benefit of hindsight, but I’m hoping not to have to migrate too many SharePoint sites in future. I expect I won’t be the last person to go through this process though and hopefully the experiences I’ve written about here will come up on a search when others are looking for help…

Creating a dashboard using a SharePoint list (part 4): bringing it all together

This week has been a bit of a SharePoint-fest as I’ve walked through the stages of creating my SharePoint dashboard. For obvious reasons, I can’t share the version I use at work, so I’ve created a mockup using the SharePoint site that comes with my Office 365 subscription. Feel free to take a look…

The three posts that explain how I built this dashboard can be found below:

As the code examples I gave use quite a lot of SharePoint formulas for calculated values, I thought it might also help to link to Microsoft’s formulas and functions documentation as well as their examples of common SharePoint formulas.

Right, that’s enough SharePoint for now… next week will be back to the usual mix of tech, marketing, photography and whatever else I stumble across…

Creating a dashboard using a SharePoint list (part 3): indicating several states of progress

Yesterday’s post looked at using ticks/crosses on my SharePoint dashboard to track progress on activities. I was quite happy with this, but my manager wanted to show progress, rather than a simple binary done/not done – and I wanted it to be visual, not text-based.

We settled on a system using a series of circles with various stages of “filling-in” (none, quarter, half, three-quarters, full) to show how far an activity had progressed, and I amended the code that I used for the ticks and crosses.

Using the same principle as with the ticks and crosses, this time the font changed from Wingdings to Arial Unicode MS, which includes the appropriate shapes under the block elements and geometric shapes subrange:

First of all, I changed my column to indicate progress from a Yes/No to a Choice with radio buttons for Not Started; Started; Additional Information Required; Almost Complete; Complete and Not Applicable.

Next I needed to adapt the formula used to calculate the correct HTML code for the display column by nesting repeated iterations of

IF([Task Progress]="Started","<DIV style='font-family:Arial Unicode MS; font-size:24px;'>?</DIV>","<DIV style='font-family:Arial Unicode MS; font-size:24px;'>?</DIV>")

Eventually this ended up as:

=IF([Task Progress]="Complete","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Started","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Additional Information Required","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Almost Complete","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Not Applicable","<DIV style='font-family:Arial; font-size:36px; color:gray;'> </DIV>","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>")))))

The code is not pretty, but it seems to work, and the result is something like this:

Creating a dashboard using a SharePoint list (part 2): tick/cross status indicators

In my last post, I wrote about how I used a script I found on the Internet and a couple of calculated columns to show a Red/Amber/Green (RAG) status on a list in SharePoint. I also wanted to clearly indicate which items in my list were complete, and which were still ongoing.

My inspiration for this came from Chris Bell’s posts on displaying tick marks in HTML, combined with the same code that converts my text RAG Status to HTML.

This time, I converted a Yes/No Checkbox (actually, it’s TRUE/FALSE) to HTML code for a tick or a cross in another column, using the following formula:

=IF([Task Completed]=TRUE,"<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:green'>ü</DIV>","<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:gray;'>û</DIV>")

The end result is something like this:

It needs the Wingdings font to be installed, but everyone who needs to access this list is using a Windows PC – feels like a bit of a fudge but it works for now… and can always be replaced with a graphic (as I did for the KPI indicators on the RAG status).

Creating a dashboard using a SharePoint list (part 1): colour coded status indicators

I’ve been having some fun over the last couple of weeks, developing a dashboard in SharePoint to track a number of activities that I’m co-ordinating.

Within our company there are some people who criticise SharePoint as a platform, largely because of some of the confusing messaging around some of the collaboration features and the way in which it gets mis-used as a document store (complete with folders!) but, whilst it can certainly be infuriating at times, I have worked on quite a few sites over the years where it’s struck me just how powerful the concept of a list is (I should know that really – I remember spending quite a bit of time discussing linked lists in my Computer Studies degree, but that was 20 years ago…).

I wanted to create a list with a red/amber/green status for each item – but I didn’t just want the words – I wanted colour to jump out of the page and say – this is the stuff that’s on track… and this isn’t…

Some calculated values and a script to generate HTML from text

A bit of digging around on the ‘net turned up a method that works for me – and the details are in Anjali Bharadwa’s post on colour coding custom lists and tasks:

Firstly, I  interrogate the contents of my text column (in my case this is called RAG Status, with pre-defined choices or Red, Amber and Green) and convert that to an HTML colour code in another column (called Color). The formula is  as follows:

=IF([RAG Status]="Red","red",IF([RAG Status]="Amber","orange","green"))

I created a page that includes a Web Part that displays a view on my list and used a hidden Content Editor Web Part below this, containing Anjali’s script (there’s an alternative script available from Christophe Humbert at PathToSharePoint but the download link wasn’t working on the day I needed it…), to read this and to generate HTML in another column with a calculated value (called RAG).  For SharePoint 2007, the script can be included within the HTML source for the webpart but, for SharePoint 2010, it’s necessary to link to a separate file (Adam Preston explains how to do this in his post on inserting JavaScript into a Content editor Web Part) in the This time, the formula is:

="<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:"&Color&";'>n</DIV>"

Using a particular font is risky (how do you know that everyone has it?) but in this case I could be sure that all of my audience would be running our corporate build with Windows and Internet Explorer. I later changed the formula to use a different symbol:

="<DIV style='font-family:Arial Unicode MS; font-size:36px; color:"&Color&";'>?</DIV>"

Sorting the RAG status

One annoyance with this approach was that the list was sorted based on my RAG Status column, but I wanted Red-Amber-Green and the alphabetical order was Amber-Green-Red. My fix for this was to change the RAG Status choices to include a number so they become: (1) Red; (2) Amber; and (3) Green.

I also tweaked the calculated value for the Color column:

=IF([RAG Status]="(1) Red","red",IF([RAG Status]="(2) Amber","orange","green"))

With this change in place, my column ordering works – and it gave me the opportunity to change the way I represent the status too…

Alternative visualisations

Path to SharePoint has some ideas for alternative visualisations for colour-coding (and a follow-up post). I particularly like the KPI indicator version, which is based on some of SharePoint’s standard images:


To use these, I changed the formula for my RAG column to:

="<DIV><IMG src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT([RAG Status],2),1))&".gif' /></DIV>"

This change also means that the Color column is redundant, although I’ve left it in place in case I want to go back to it later.

What next

So that’s my RAG status sorted. In the next post, I’ll explain how I used this method to show ticks and crosses to indicate activity completeness.

Calculated value based on today’s date in a SharePoint column

One of my current projects involves developing an internal system to keep track of the technologies that are approved for use in our solutions.  As part of that, I’d like to set a flag on each entry so that it is regularly reviewed – for example every 90, or 180 days.

I had hoped to use a calculated column for this (i.e. today’s date plus x days) but it’s generally recognised that calculated fields only work on data entered by a user, and not from the system. Indeed, Microsoft’s own documentation for the TODAY function says:

“[…] You can use the TODAY function only as a default value; you cannot use it in a calculated column.”

Even so, I’ve had some success (with Microsoft Office SharePoint Server 2007) in setting a column type of Date and Time, in Date Only format, with a Calculated Value of =Today+90.  I’m not sure how/why this is working, given Microsoft’s statement above, but it may be worth considering.  Just be aware that your mileage may vary…

(Christophe Humbert has more information and another approach over at Path to SharePoint)

More SharePoint shenanigans

This week, I’ave mostly been working in SharePoint (those of a certain age may spot the reference to Jesse from the Fast Show?)

Earlier this month, I wrote a post with a few hints and tips I’d picked up whilst developing a site based on SharePoint.  Since then, I’ve come up against a few more barriers…

Item level permissions on document libraries

SharePoint allows administrators to set permissions on lists so that users can read and/or edit only their own items. Unfortunately, whilst that functionality is exposed in the user interface for lists, it’s not for document libraries (although it is in the object model). Basically, if you can write some code, you might be able to set the requisite permissions, but that’s beyond my abilities.  Thankfully, others have done the legwork, either in the form of a rough-and-ready utility like Matt Morse’s or Tim Larson’s Read/Write Security solution.  Either way I need to get the the changes applied to our SharePoint farm… which may well be more trouble than it’s worth…

Changing the contact for a page

Even though there is a page contact in the properties for each page (at least there is in my company – that might just be metadata that we use…), changing that property doesn’t seem to affect the page contact (which our templates show at the bottom of the page – and is also shown in a view of all pages).  The answer is to edit the page settings (in the same way as to change the page layout). Thanks to Ian Mitchell (@ianmitchell2) for setting me on the right path there…

A calculated column, with a formula based on a Yes/No field

I wanted to display a tick or a cross instead of a yes or a no in a view on one of my lists and, in order to do this I needed to create a calculated column that produced the necessary HTML (and a script to display it…). I’ll write another post about the fancy formatting but I really struggled to work out how the Yes/No is recorded (Yes/No; 1/0; TRUE/FALSE?). Exporting my list to Excel proved that SharePoint stores boolean values as TRUE/FALSE (confirmed by Peter Allen) but the trick is to leave out any quotes – if you look for =IF(Column="TRUE","This","That") it will always be negative and the outcome will be “That”.  The correct formula is =IF(Column=TRUE,"This","That").

Editing in data sheet view but the view is read-only?

I needed to perform some bulk updates on lists in SharePoint but, frustratingly, the view was marked as Read Only so I couldn’t make any edits.  I couldn’t see why this was, but googling turned up an explanation – the list was set to require content approval.  PointBeyond has more information on configuring approval in SharePoint but temporarily removing this setting allowed me to make the necessary updates, before re-enabling it.

Connecting data in web parts

Back in around 2003/2004, I remember attending a SharePoint training course where I connected a couple of webparts to work together.  For someone like myself with little or no coding skills, this was magical… and then I forgot how to do it.  Yesterday, I ran up against an issue where, partly as a result of some database design decisions by the previous  designer, I found myself unable to display the view on a list that I wanted to, as there was an implied hierarchy in the data, but the lower levels in the hierarchy link to their parent, rather than parents linking to children (it would be better still if things could work both ways…).

The workaround, albeit clunky, was to configure two webparts, each showing a view on a different list, before configuring a data connection so that the first list provided a row upon which to filter the second list.  I needed to be careful in selecting columns (i.e. the second list needs to have a column that is a lookup on the first) but, with that in place, I was able to at least show the relationships between the items in each list.

A collection of SharePoint shortcuts

I spent most of last Friday developing a business system with Microsoft Office SharePoint Server (2007).  I’ve worked on a few SharePoint sites over the years and I’m impressed at how much can be done using just standard functionality (lists, etc.) but, whilst the platform is powerful and flexible in many ways, it’s also intensely infuriating at times.

In developing this latest site, there were a few things that I had to Google for – and I’m hoping that posting them here might help others…

Changing the page layout

I created a new page using one of the templates provided for me by my IT department.  Unfortunately I found that the webpart layout was a little too restrictive and I needed to change the page layout.  I hunted around for a while (even after a colleague had told me to look for the page settings) and then I found a post by Shane Young that helped me out. As Shane descibes, the steps are:

  1. “Browse to the page
  2. Click Site Actions, Edit Page
  3. From the tool bar click Page
  4. In the drop down list click Page Settings
  5. Now pick your Page Layout
  6. Click OK”

With the new page layout in place I was able to get the page looking (almost) how I wanted.

Hiding the Title column from forms

My site is built around a document library with a number of columns. One of the default columns is called Title and it’s not really that useful to me as it really just duplicates the Name field (doubling up the details that users need to enter for a document in the library). I can always hide column from list views but I can’t delete it completely and the field still appears in forms. Sometimes, I repurpose Title by changing the column name but I can’t change the column type – it’s always a single line of text. Then I found John Owings’ post which describes the steps to hide the Title column from forms:

  1. “From the list view click Settings [then] List Settings
  2. On the Settings Screen, under the ‘General Settings’ heading, click ‘Advanced Settings’
  3. On the Advanced Settings screen click ‘Yes’ for the value: ‘Allow Management of Content Types?’
  4. Click ‘OK’
  5. Now, back on the Settings Screen, under the ‘Content Types’ heading, click ‘Item’
  6. On the Content Type Management Screen, under the ‘Columns’ section, click on the ‘Title’ column
  7. On the next screen click the radio button for ‘Hidden (Will not appear in forms)’
  8. Click ‘OK’”

Internal anchors

Whilst I’m sure it’s possible to use inline CSS, my SharePoint pages resort to some awful HTML hacks at times, like using tables for layout (and then having to mess around with valign directives and other such code that I haven’t used in about ten years…). I probably shouldn’t admit to such awful practices but I also had to relearn something I’d forgotten many years ago – the use of internal anchors within a page.

It’s worth noting though that, using SharePoint’s Rich Text Editor to create a link to #anchor actually created a link to http://server.domain.tld/layouts/RTE2PUEditor.aspx#anchor. I had to explicitly include the full pathname (e.g. http://server.domain.tld/Pages/Page.aspx#anchor) in the link in order to avoid this behaviour.

SharePoint, Dropbox, and shadow IT

This morning I had a problem with SharePoint. Well, when I say the problem was with SharePoint, it could be considered a “layer 8 problem” (i.e. user error) but it still illustrates a major issue  with corporate IT provision – not just in my organisation but in many, many businesses, all over the world.

You see, last night, I uploaded a presentation to our intranet. It was a 20MB file over an ADSL/VPN connection and the browser upload session timed out so I used SharePoint’s Windows Explorer view (which I think is WebDAV).  The file was copied, I edited the properties in the browser and all was good, I thought.

Fast forward to this morning and people were telling me the links to the presentation in my team’s newsletter didn’t work. But they did for me… embarrassingly (because the newsletter goes right up the company – to CEO level), I sent an email with the correct link in naked form (horrible long URL, rather than as a hyperlink on some nice text) but people were still getting HTTP 404 responses (file not found).

To cut a long story short, the WebDAV upload had not checked in the file (by design, I now think) and even editing the properties afterwards didn’t. I could see the file, but no-one else could. Once the file was checked in all was well – except from  my red face (and my insistence that HTTP 404 isn’t a permissions error – that would be 403).

I lost a good chunk of this morning on this and the related clean-up activities when, essentially, all I wanted to do was share a file with some colleagues – a common business requirement that shouldn’t really be a problem in 2011. So I tweeted:

I need Corporate Dropbox; SharePoint is just one usability nightmare after another... (cue flow of tweets telling me SharePoint is great...)
Mark Wilson

I expected a deluge of people supporting SharePoint and telling me that I’m just a dumb user, what I actually got was RTs (showing this is not just an issue for me) and then a succession of people suggesting various Dropbox-like  products for that could be used by corporates.

Lots of people are suggesting and there’s Dropbox for TeamsOxygenCloud and ShareFile too. I suppose, taken at face value this sort of product is exactly what my tweet asked for but it’s not really a corporate version of Dropbox that I need – it’s the simplicity of Dropbox (dump “stuff” in a folder and it’s wherever I need it – in the cloud, on other machines, available to share with others, etc.) – I’m sure there are many solutions that do this, with varying degrees of success (just that Microsoft SharePoint is not one of them…). But technology is only one part of the issue.

My scenario (and the reason I’m writing this) is actually a perfect example of why we have shadow IT in organisations today. End users (consumers) want to do “something”. That “something” is hard to do with their enterprise tools, so they find another way around the problem. Over time that solution becomes embedded – that’s when the problems start for the CIO (or, maybe, for the individual who didn’t follow the stated IT policy…). Those problems generally boil down to one of two things: security and manageability. In this case, the file is already available on SlideShare, but it could have been something confidential – like the business model I was creating yesterday afternoon – and that wouldn’t have been something I wanted floating around on servers that my company doesn’t control.

I’m sure that the multitude of “solutions” to my problem are all great in their own way but if I start to use them, well, all I’ll really be doing is perpetuating the issue of shadow IT.

(Incidentally, I did come across some interesting projects from the responses I received: remember Novell iFolder? it’s still around in open source form from Kablink; and VMware’s Project Octopus could have potential too.)

Exporting SharePoint Calendar details in iCalendar format

One of my current activities involves sharing the contents of a SharePoint calendar, which is hosted on an Intranet site, with external contacts.  An extranet portal would be one possible approach but it’s probably over-engineering the solution and a simple calendar export, updated on a regular basis would also suit the requirement.

SharePoint allows RSS export from a Calendar but the events are exported in the order in which they were added to the calendar, rather than in chronolical order.  I thought it would be far more useful to export them in iCalendar format and it turns out that’s possible too – with the addition of  an open source webpart called iCal Exporter (which my colleague Andrew Richardson tracked down).  You can also interrogate the SharePoint object model directly but that’s beyond my limited coding abilities.

Installing the webpart is pretty straightforward:

  1. Unzip the compiled version of the iCal Exporter webpart and copy the iCalExporter.wsp file to the hard drive on a SharePoint server (I used Windows SharePoint Services 3.0).
  2. From the command prompt, issue the following commands to navigate to the folder containing stsadm.exe, install the solution and deploy the solution:
    cd “%commonprogramfiles%\Microsoft Shared\Web Server Extensions\12\bin”
    stsadm –o addsolution –filename “c:\iCalExporter.wsp”
    stsadm –o deploysolution –name iCalExporter.wsp –local
    (it may be necessary to specify other options if deploying in a multi-server environments.)
  3. Using a web browser, navigate to the server’s site collection features page and click the Activate button on the iCalendar export button feature.

Once installed, there is an additional option on the Actions menu export the calendar in iCalendar format.  Give the resulting file an .ics extension and distribute it at will – most calendar clients (I tested in Outlook but it should work for others too) will be able to view the appointment details.