Some SharePoint tips/tricks for editing list forms and hiding standard page elements

Over the last couple of years, I’ve written a few SharePoint-based blog posts as I’ve hacked my way around the company’s infrastructure, trying to get it working the way I’d like it to (creating dashboards, adding the odd bit of workflow and custom columns to pre-populate list data, embedding video, etc.).  There’s a rumour that we might get SharePoint 2013 soon but, for now, all of my hacks/tweaks have been based on 2007.

In the last few weeks, one of the projects I’ve been involved in has taken a major step forward, engaging a real designer and a real web developer, instead of li’l ol’ me.  From my perspective, this has been great news, although there have been some times when I’ve questioned the maintainability of custom code used in situations that have caused problems in the past (e.g. to edit the default forms used for displaying list items).

As you might expect, I’ve also picked up a couple of tips, working alongside some experts – and I thought I’d make a quick note of them here, for future reference.

Editing list forms without using SharePoint Designer

First up, a handy little trick to edit list forms (like DispForm.aspx) without having to resort to using SharePoint Destroyer Designer, which is little more than Microsoft FrontPage (just about OK in it’s day but long past its sell-by date now). Just add ToolPaneView=2 into the query string, for example

Unfortunately, this doesn’t seem to work on my Office 365 SharePoint site, so it might be 2007-only but useful nevertheless. In our case, this was used to add a (hidden) Content Editor Web Part with some jQuery and CSS to style up the form.

Hiding page elements

Sometimes, there are standard elements of a page that you don’t want to display and, without access to the templates used on a corporate site, that might be difficult. As it happens, all that’s required is a little in-line CSS, included in a (hidden) Content Editor Web Part:

Just use the Developer Tools in Internet Exploder (or similar in your choice of browser – although, if you’re using SharePoint 2007, it probably will be IE) to find the class of the element to be hidden. In this case, I removed some standard text that was inserted below the breadcrumb trail at the top of the page but I’ve also seen it used to hide the page title and it could be applied to other elements too.

Credits: Thanks to Steve Haxell for sharing these methods with me.

Embedding Windows Media in a SharePoint website

A few weeks ago, I found myself standing in front of a green screen in a meeting room that had been “converted” into a temporary film studio, recording a video for internal communication on the technology standardisation initiatives I’ve been running for the last few months.  After all the edits and final approvals, the videos are now coming online and, as “Chief SharePoint Officer” for our team (I jest), it was up to me to hack our portal and get them online.

I figured that the guys in our internal studio must have done this before and, sure enough, the advice I received was to use JWPlayer for Flash content or to embed a media player for Windows Media files. We went with Windows Media (I can play the WMVs offline too), so I used a method described on Stack Overflow to embed an object inside a SharePoint Content Editor Web Part.

I’m sure that there are alternatives that provide better cross-browser support but as this is a SharePoint 2007 website, the only browser that will be used is some variant of Internet Exploder (and our corporate browser is Internet Explorer 8) so not too much to worry about.

I needed just one slight variation.  The videos I used were 480×270 pixels so, with the controls, I needed the player to be slightly taller. Playing around until I had no black bars around the video got me to the following code:

A full-quality download of this video is also available.

Internet Explorer crashes when editing lists in SharePoint datasheet view

Recently, I found I was experiencing issues when editing lists in SharePoint.  After switching to datasheet view, Internet Explorer displayed a message which said:

Internet Explorer has stopped working

Windows is checking for a solution to the problem…


A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available.

IE then attempts to reload before presenting a page that gives more information:

It seems that Windows Data Execution Prevention (DEP) detected an add-on trying to use system memory incorrectly, so I took a look at the DEP settings but everything seemed in order.

Eventually I tracked the problem down to cells that contained HTML code (for a dashboard that I created in SharePoint), being mis-interpreted as a malicious exploit.  I created a new view, minus the columns containing HTML and was able to edit without any browser crashes.  It’s a bit of a nuisance but it seems to work…

Given the choice, I wouldn’t be using Internet Exploder anyway but, as this is SharePoint 2007, I don’t have a lot of choice (I understand that cross-browser support is a lot better in later SharePoint releases).

SharePoint datasheet mode crashes Internet Explorer

Back in the summer I wrote about creating dashboards in SharePoint using some borrowed JavaScript in a webpart to display calculate columns of HTML.  I needed to create another dashboard recently, so I reused my old technique but then, today, I found that I could no longer edit my list in SharePoint’s Datasheet mode.  Each time I tried, Internet Exploder crashed, blaming the problem on the Data Execution Prevention (DEP) functionality that is meant to prevent malicious code from being executed in memory.

Of course, being SharePoint (well, on 2007 at least), I couldn’t use an alternative browser but I was pretty sure the issue was related to the HTML generated an placed in a calculated column in my list. By creating a new view that excluded the problematic column (i.e. the one containing  the HTML), I was able to edit as normal, without a browser crash.

Export to spreadsheet from SharePoint 2007 with Excel 2010

I frequently have to export data from a SharePoint list to Excel but earlier today I found it no longer works since I’ve upgraded to Office 2010.  Our SharePoint infrastructure is based on SharePoint 2007 and each time I attempted to Export to Spreadsheet from the Actions menu on the Toolbar, Excel would hang.

Luckily, I found Gustaf Lindqvist’s post on synchronising data between Excel 2010 and a SharePoint 2007 list. He suggests installing the 2007 Office System Driver Data Connectivity Components (I wasn’t getting an error message to help me find the problem – just a “Not Responding” application and the “doughnut of death”.

It’s still a bit flaky – and I’m not sure I have the complete answer (in fact, I suspect there may even be issues with some of the views in SharePoint) – but at least I can export data now…

Setting a blank value in a SharePoint workflow

I spent far too much of yesterday fighting with SharePoint workflows… including trying to cancel some that have run away on a list with almost 400 items in it…

Some idiot (initials MW) created a workflow to auto-populate a column based when a record was created or updated. Then, thinking that was successful, he created another one to update a different column. He was so pleased with himself that he didn’t stop to think of the consequences of one workflow updating an item, which would then trigger another workflow because the item had been updated… the resulting event from which would then trigger another workflow… and so on…

The long and short of it, is that I found SharePoint 2007 doesn’t have the ability to bulk remove errant (or otherwise) workflow tasks. Not from the user interface, at least – and I didn’t want to do it, twice, for each one of 377 items (there weren’t quite that many runaway workflows but it was well into three figures). I found some code on a blog post from SharePoint MVP Brian Farnhill (@BrianFarnhill) to cancel a workflow programmatically but I’ll need to put the code  into an app and I’m not really sure how to do that (my limit is probably opening Visual Studio). Besides which, I figure that the administrators of our SharePoint platform, who have probably spent the last 3 weeks wondering why it was running out of memory (or running more processes, or something similar) won’t appreciate me running unsupported code against their servers – even if it is client side…

Although it would be gratifying to create the app, in the end I settled for removing the workflow from the list, which has the side effect of ending all running workflows (unless you select the “no new instances” option). Unfortunately I still have a bunch of items that generate an error message when I try to open them – goodness knows why.

I decided to fight fire with fire and given that I’m clearly some sort of SharePoint Destroyer Designer workflow deity (not), I created another workflow to nuke all the column settings I’d created with the first two workflows (since neutered).

The logic goes like this:

Condition: If columnname is not empty
Action: Set columnname to null

Unfortunately it’s not possible to leave a null/blank value when setting field values but, like so many things in SharePoint, there is a workaround. That workaround is to set a workflow initiation parameter (imaginatively, I called mine Blank) of type single line of text, with an empty default value.

Now, the workflow action can use the value Initiation: Blank (i.e. null) to clear the contents of a column.

Unfortunately, it still hasn’t fixed the problem with the items that will not open. And, in case my manager is reading this post (which I doubt), hopefully this now puts to bed the running joke whether it’s he or I that is our team’s “SharePoint guru”. Clearly my infrastructure background does not lend itself to being a “guru” in an application platform (as I have always maintained!)…

Creating a SharePoint calculated column that uses information from a lookup column

A few months ago, Klout suggested I knew something about SharePoint. I laughed at the time but the last few weeks have seen me spending far more time working on a couple of SharePoint-based systems than I would like, so maybe Klout is less of a measure of social influence than one of future gazing…

…anyway, back to the point.

SharePoint lists have the ability to include columns that are lookups from other lists. That’s really useful when building a system with several lists of related items, for example Technologies and Vendors. I use a lookup on Name from the Vendor list to populate the available entries for a Technology item’s Product Vendor column.  That’s all fine but I also have Product Family, Product Name and Product Version columns. And then there is a Similar Products column – for which Product Name is not a clear enough lookup – I need a combination of Product Vendor, Product Family (where present), Product Name and Product Version – For example a concatenation of Microsoft + Office + SharePoint Server + 2007.

Creating a calculated column (Full Product Name) for the “in-list” (Product Family, Product Name, Product Version) is straightforward but certain column types (Lookup, Person, Group) are not available for calculated columns. I believe this is because they are based on internal SharePoint IDs, rather than the information displayed.

The workaround seems to be to create an additional text column and a workflow in SharePoint Designer that sets a value in this field.

The workflow (which I named Set Product Vendor) is a basic workflow with one step. That step has no conditions (i.e. it applies to all items) and an Update List Item action which updates the Current Item to set my new column (Product Vendor Plain Text) to the Technologies:Product Vendor value (using the fx button to select).

Now, when creating a new item the lookup is used to select an existing Product Vendor but the plain text version of the field can then be used for calculations, like the Full Product Name. The formula for my Full Product Name column is:

=IF([Product Family]=””,[Product Vendor Plain Text]&” “,[Product Vendor Plain Text]&” “&[Product Family]&” “)&[Product Name]&” (version: “&[Product Version]&”)”

This is used to create a single line of text.

The logic here is that a Product Family value might be empty. If it is, then I take the Product Vendor Plain Text value and add a space, if it’s not I take the Product Vendor Plain Text Value and the Product Family, adding spaces to separate, then (regardless of the previous condition), I add the Product Name and the Product Version (with some additional text around it).

Unfortunately, whilst the workflow can be triggered manually for an item (which is what I did to test it), or on item creation/update, there is no way to run the workflow on all of the existing items in a list.  But, like so many things in SharePoint, this also has a workaround. By viewing all items in SharePoint datasheet view and making a minor change to each one (for example adding an entry to a temporary column that can then be removed), this will update the item, triggering the workflow.

The bonus of all of this is that I have now updated my Similar Products column to be a lookup on Full Product Name instead of Product Name and, because it works on the item IDs, the entries are now correct, with no data cleansing required on my part.

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”,”


Eventually this ended up as:

=IF([Task Progress]=”Complete”,”

“,IF([Task Progress]=”Started”,”
“,IF([Task Progress]=”Additional Information Required”,”
“,IF([Task Progress]=”Almost Complete”,”
“,IF([Task Progress]=”Not Applicable”,”

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