Richard Willis – SharePoint Learning Kit Coordinator, SalamanderSoft Owner

What are Cascading Drop Downs and why do we want them?

Cascading drop downs are where you have linked drop down boxes where the contents of the second drop down depends on the selection of the first one. They are used to target selections and make choosing them much easier. The canonical example is choosing a city where in the first drop down you choose the county/state and the second drop down is then filtered to the list of cities actually in that county/state.

SharePoint Support

SharePoint 2007 didn’t natively support cascading drop downs in list forms. There were a couple of workarounds which either involve adding a solution onto the server or building a JavaScript version using the SharePoint web services.

However, in SharePoint 2010 Enterprise version, you can implement Cascading Drop Downs out of the box with no code. The 2 features which make this possible are:

1. InfoPath list forms.
2. Browser based support for the cascading drop downs in InfoPath 2010.

Background

In this example I’m going to implement a system for listing after school activities. The school is split over 3 sites and as part of creating the activity the teacher needs to choose the site the activity is on, which will then allow them to choose the room the activity is in. In addition to this, we’ll add some finishing touches so that the room value is cleared when the site changes and the room drop down shouldn’t be enabled until a site has been selected.

How to Implement Cascading Drop Downs

Before you begin make sure that the site collection feature "SharePoint Server Enterprise Site Collection features" is activated or you won’t have support for InfoPath forms.

1. Create the Lists

Firstly you need to create 2 lists to store the sites and the room. So create a custom list called Site and add some values to it, in this case I chosen Centre, East and West. No additional columns are required.

Next create a custom list called Room and add a lookup column to the Site list. This column should be required and link to the Title column in the site list.

Next add some rooms to the Room list.

Now we need to create the actual after school activities list. This can contain whatever columns you require for its use, but also a site a room column which are lookups to the Site and Room lists. Here’s the list with a couple of entries in.

Now when we Add a new item you can see that the room drop down contains all the possible values, no matter what the site selection is.

2. Link the Room Drop Down to the Site Drop Down

Now we need to modify the form so the the room drop down depends on the site drop down. To enable this select the list tab in the ribbon and click Customize Form. For this you need InfoPath 2010 installed on your machine to edit the form. Once the form is published your users do not need InfoPath installed locally.

Once you click on Customize Form it will open up in InfoPath.

There’s now several steps to go through to enable the cascading drop downs. In a nutshell you create a new data source pulling from the room list, then add a filter on the Room drop down to only show items from the room list with the same site as that in the Site drop down.

Create the Data Source

First of all right click on the Room drop down and select Drop-Down List Properties to bring up the properties for the drop down.

First we need to add a new data source so click on Add next to Data Source,

and step through the wizard. You want to create a new connection to receive data i.e. the defaults, and select the source of the data to be SharePoint library of list.

Next you select the location of the site which as we opened from within SharePoint should be already correctly prefilled, and in the next step select the Room list.

In the next step you need to select the fields you want from the data source. You need to select the ID field as that is the actual value written to the activity room column, the Title column to display and the Site column for the data to filter on. You can sort on any column you want, but in this case only Title makes any sense. You can then choose to store the data in the form template for offline mode, but this generally isn’t required.

Finally you need to choose a name for the data connection. As long as it’s descriptive and unique it doesn’t matter what you choose. The default name is the name of the list with a 1 on the end. When you finish you’ll be taken back to the properties form where the name of the data source will have been changed to the new one you have just created.

Filter the Values

Next we need to filter the values in the Room drop down based on the selected Site. To start this we click on the button next to the Entries value, then on the Select a Field or Group window click on Filter Data at the bottom.

To add a filter click the Add button and then select the Filter Conditions.

For the filter we are looking to create one which limits the data to only those rows which have the Site value equal to the selected Site i.e. the site from the main data in the form. To generate this the filter needs to be "Site" "is equal to" "Select a field or Group".

This will pop up another window to Select a Field or Group. You need to change the Fields drop down to Main and then select the data field Site. Finally click OK and the result will display Site in the third drop down in the Specify Filter Conditions window.

Click OK to go back to the Filter Data window, OK to go back to the Select a Field or Group window and then OK again to go back to the Drop Down Properties Window.

Finally in the drop down List Box Properties you need to change the Value to d:ID by selecting it via the button to the right. This is because internally the lookup value stored is actually the ID in the lookup table. If you leave it as d:Title you will get an error when you try to save the list item. Once this is done click OK.

Final Touches

At this point you can publish the form and you will have a working cascading drop down. When you select a site the room drop down will be populated with the appropriate rooms. However, there are a couple of problems with it. Firstly, the Room value is not cleared when changing the Site. As the room value will not be in the filter for the new site value it will get changed to the id value which will be confusing for your users. Secondly if the Site is not a required field the Room Drop Down is still enabled if there is no Site chosen.

Both of these can be resolved with rules.

Clear Room Value When Site Changes

To clear the room value when the site changes you need to add a rule to the site field. In InfoPath select the site field then, from the ribbon use Add Rule | This Field Changes | Set a field’s Value.

Then it’s simply a case of selecting Room for the Field and leaving the Value blank.

Publish the form and test – It’s as simple as that.

Disable Room Drop Down When No Site Selected

To disable the Room Drop Down again add a rule, but this time you need to add a conditional formatting rule. To add this select the Room field and then from the ribbon select Manage Rules. This will open the Rules side bar. From this side bar select New | Formatting. This will then add the formatting options to the side bar.

Choose a suitable name for the rule then click on the "None" under condition. This will open the Condition window which is very similar to the Filter Condition one before. This time we choose "Site" "is equal to" and "Use a Formula". The Insert Formula windows pops up and just enter "" for an empty string.

Once you click OK to save the condition, just select "Disable this control" in the side bar and we are done. Publish the form and test, this time the room drop down will be disabled when no site has been selected.

Conclusion

SharePoint 2010 Enterprise Edition allows us to easily create powerful InfoPath forms incorporating cascading drop downs without any code or installing any additional solutions onto the server.

There are currently still places available for SharePoint Saturday in November.

SharePoint Saturday is a free community event devoted to SharePoint, and this year is being held in Nottingham. There’s more information at Home – SharePoint Saturday UK. It’s got a fantastic line up of speakers and well worth attending if you have any interest in SharePoint, whether an IT Pro, Dev or End User.

I’m attending so if anyone wants to talk SLK or SharePoint in education, I’d be happy to meet up.

When running Update-SPSolution you can sometimes get the error:

Admin SVC must be running in order to create deployment timer job

There’s a very simple solution to this. Ensure that the SharePoint 2010 Administration service is started.

Once you have started that the command will run correctly.

Having removed the "Use a Meeting Workspace" option from individual calendars in my previous post, I wondered if there was a way to remove them from all existing and new calendars within a site collection.

I found a way to do it by modifying the calendar schema in the hive folder. However, although this works, it technically leaves SharePoint in an unsupported state, so I didn’t want to go down that route.

Then I had a thought that all the items in a calendar list are of the Event content type, so if you can modify that, to make the Workspace option hidden, then it would be removed from all calendars.

Unfortunately when you look at the Event Content Type in the Site Content Types gallery, the Workspace column is greyed out so you can’t edit it.

However, it proved to be remarkably easy to script using PowerShell:

# Script to remove Use a Meeting Workspace from all calendars
$siteUrl = "http://myserver/sites/mysite"
[system.reflection.assembly]::LoadWithPartialName("Microsoft.Sharepoint") > $null
$site = New-Object Microsoft.SharePoint.SPSite($siteUrl)

$contentTypeId = [Microsoft.SharePoint.SPBuiltInContentTypeId]::Event
$eventContentType = $site.RootWeb.ContentTypes[$contentTypeId]

$fieldId = [Microsoft.SharePoint.SPBuiltInFieldId]::WorkspaceLink
$field = $eventContentType.FieldLinks[$fieldId]

$field.Hidden = $true
$eventContentType.Update($true);

# Dispose the site object
if ($site) {$site.Dispose()}

The only slightly tricky bit was needing to use the FieldLinks property instead of Fields.

The $eventContentType.Update($true); line ensures that it updates all existing calendars in the site collection, otherwise it will just be for new ones.

I’ve actually increased the script by 2 lines to fit it in my blog by adding in the $fieldId and $contentTypeId. Originally it was even more compact.

Again, this takes effect immediately throughout the entire site collection with no need for an iisreset or an application pool recycle.

I’ve just deployed a custom meeting site to a customer to handle multiple meetings within one site. Hence the option to “Use a Meeting Workspace” when creating a new or updating a calendar item is unnecessary, so they asked me to remove it to prevent anyone using it by mistake.

When creating a calendar item (or updating) this is the dialog you get, with the Use a Meeting Workspace highlighted.

From SharePoint Blues I discovered that if you set the calendar list field to hidden then this option would no longer display. Taking this concept, I created a PowerShell script to set this to hidden on a one off basis.

The script is:

# Script to remove Use a Meeting Workspace from a specific calendar

function RemoveWorkspaceLink ([String]$siteUrl, [String]$calendarName)
{
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.Sharepoint”) > $null
$site = New-Object Microsoft.SharePoint.SPSite($siteUrl)
if ($site -eq $null)
{
return
}

$web = $site.OpenWeb()
$list = $web.Lists[$calendarName]
if ($list -eq $null)
{
“Invalid list name ” + $calendarName
}
else
{
$field = $list.Fields[[Microsoft.SharePoint.SPBuiltInFieldId]::WorkspaceLink]
$field.Hidden = $true
$field.Update()
“Updated ” + $calendarName + ” on ” + $siteUrl
}

# Dispose of the SharePoint objects
if ($web) {$web.Dispose()}
if ($site) {$site.Dispose()}
}

RemoveWorkspaceLink “http://myserver/sites/site1″ “Calendar1″
RemoveWorkspaceLink “http://myserver/sites/site2″ “Calendar2″

I’ve written this as a function so that you can update multiple calendars at the time. If you saved this as a .ps1 file and then run it, the 2 calendars referenced at the bottom would be updated. Alternatively you could . source the file and use the function interactively, or just hard code the $siteUrl and $calendarName variables each time you run it.

The option is removed immediately you have run the script with no iisreset or application pool recycle required.

So with the script is run this is what you see with the Meeting Workspace option removed:

This works well for hiding it on individual calendar lists, but doesn’t affect any other existing calendars or ones created afterwards.

I’ve since found a way to update all calendars in a site collection.

Recently I was using testing the Managed Metadata Service to share Content Types via Content Type Hub Syndication. I created my content types in the hub and had the Managed Metadata Service all set up and my web application was subscribed to it.

So I created a new blank site collection to test the synchronisation of my content types, but no matter what I tried, they just didn’t show up. I checked all the setting on the Managed Metadata Service, ensured that the content types were published and manually ran the Content Type timer jobs, but all to no avail.

Finally, after some searching on the internet I tracked down the problem. To subscribe to the content type hub the feature TaxonomyFieldAdded needs to be enabled. This is a hidden feature so can only be enabled programmatically or via PowerShell for which the command is

Enable-SPFeature–Url "http://mysiteurl" -Identity "73EF14B1-13A9-416b-A9B5-ECECA2B0604C"

where the guid 73EF14B1-13A9-416b-A9B5-ECECA2B0604C is the id of the feature.

Once this feature is enabled you will get the Content type publishing link in Site Settings

and the published content types immediately appeared in the site collection’s Site content types.

This feature also enables the use of Managed Metadata columns in lists. Without the feature enabled you get this error when trying to use a Managed Metadata column:

which renders the column useless. As soon as the feature is enabled then you can use it as normal.

Note, that this problem only occurs in site collections which have been created as a blank site. It does not affect blank sites created within other site collections created from another template. The reason for this is that there is another feature called TaxonomyFeatureStapler which staples the feature to most other sites, with the notable exception of the blank site. I can only assume that it’s this way because the blank site template is supposed to be a base site and have minimal functionality. If you regularly create blank site collections and need to use Managed Metadata on them you should be able to create your own feature stapler to add the TaxonomyFieldAdded feature to the blank site template.

While building a school’s Learning Gateway I needed to bulk upload all their student images into a picture library so that the My Children web part could display them. Since there were several hundred of them I wrote a little PowerShell script to perform this.

$siteUrl = "http://sharepoint/schools/test"
$listName = "Students Picture Library"

[system.reflection.assembly]::LoadWithPartialName("Microsoft.Sharepoint")
$site = New-Object Microsoft.SharePoint.SPSite($siteUrl)
$web = $site.OpenWeb()$list = $web.Lists[$listName]
$fileCollection = $list.RootFolder.Files

$files = get-childItem -Exclude *.ps1

foreach ($file in $files)
{
    $stream = $file.OpenRead()
    $uploaded = $fileCollection.Add($file.Name, $stream, $TRUE)
    "Uploaded " + $file.Name

    if ($stream) {$stream.Dispose()}
}

if ($web) {$web.Dispose()}
if ($site) {$site.Dispose()}

In order to use this:

1. Save as as .ps1 file in the same folder as the files to upload.
2. Change the site url and name of the list at the beginning of the script to your values
3. Make sure that only the files to upload and the ps1 file is in the folder
4. Open PowerShell using Run As Administrator
5. Navigate to the folder
6. Run the .ps1 file

The script will then iterate through all files in the current folder and upload them to the given list, overwriting them if they already exist.

I’ve blogged before on how to Easily add a dll to the GAC, but that involves two instances of Windows Explorer and the mouse.

It would be quicker and easier to use the command line to update a dll in the GAC. You can do this with the Global Assembly Cache Tool gacutil, but this is only present if you have the .net SDK or Windows SDK installed. This is going to be the case with your development machine, but you don’t really want to be installing it on all your test servers.

To get round this I knocked up a quick utility which will remove an existing version of a dll from the GAC and then install the latest version. The code is:

using System;using System.Globalization;using System.EnterpriseServices.Internal;

namespace Test{    public class GacInstall    {        public static void Main(string[] arguments)        {            try            {                string assemblyPath = arguments[0];                Publish publish = new Publish();                publish.GacRemove(assemblyPath);                publish.GacInstall(assemblyPath);            }            catch (Exception e)            {                Console.WriteLine(e);            }        }    }}

You can then use it by passing it the dll you want as a command line parameter e.g.

GacInstall MyUpdatedCode.dll

Assuming that MyUpdatedCode.dll is in the same directory.

Combined with my last tip Recycle An Individual Application Pool From The Command Line you can now write a bat or cmd file to replace the dll in the GAC and recycle the application pool with 2 key presses: The up arrow key and then return.

When developing SharePoint artifacts which live in the GAC you have the following options to get your new release in as part of the development process:

1. Build your installation package and upgrade it
2. Put the new dll in the GAC and run iisreset
3. Put the new dll in the GAC and recycle the SharePoint application pool

This is because SharePoint will only pick up updated dlls in the GAC after a recycle.

As it is an iterative process when you are developing this happens a lot, so ideally you need a process which is as quick as possible. 1 & 2 take the longest time so you should aim to use 3.

Luckily there is a command line command which will recycle individual application pools, which will be quicker than using the IIS administration tool. The command is

C:\windows\System32\inetsrv\appcmd.exe recycle apppool "SharePoint – 80"

Where you replace "SharePoint – 80" with the name of your application pool.

Of course when releasing to a live server you should always go down the proper installation package upgrade, this tip is purely for development purposes on your development server.

There’s a lot more you can do with appcmd – it’s a utility for making configuration changes to IIS so pretty much anything you can do through the IIS MMC snap in can be done via it. There are more details about appcmd on Technet or you can run

C:\windows\System32\inetsrv\appcmd.exe /?

to get help details.

Note: appcmd is for IIS7 not IIS6.

If you are in education and using SharePoint then the Learning Gateway Conference is the must attend event of the year. Run by Alex Pearce (MVP) and myself it follows on from the highly successful first Learning Gateway Conference last year. This year will be even bigger and better! We have added a whole new track which is dedicated to leading SharePoint schools telling us their stories of how they have implemented their Learning Gateways, what has worked for them and what pitfalls to avoid.

With the recent release of SharePoint 2010, it is an exciting time in the SharePoint world and the educational pricing makes it a great product for use in schools, colleges and local authorities. However, in addition to covering some of the fantastic new features in SharePoint 2010, we will still have lots of content for SharePoint 2007. So whether you have already moved to SharePoint 2010, are planning to upgrade over the summer or don’t currently have any plans to change, there will be lots of great information you can take away and use in your Gateway.

With both technical and user oriented sessions there is something for everybody – network managers, technicians, users, Learning Gateway managers or members of senior leadership teams. In addition the conference will be relevant and useful whether you are an experienced SharePoint user, just starting out or just considering SharePoint. With 3 concurrent tracks running throughout the day it would be worth considering having more than one attendee!

In addition to the content it will of course be a great networking event with the opportunity to meet others in the same situation as you and discuss what you are doing. At only £150 for the day it’s an unrivalled professional development opportunity providing information you will be able to put in practice straight away.

The venue is Church House, Westminster in Central London so is easy to get to from almost anywhere.

For more information visit the conference web site and register today. To whet your appetite here is a list of the sessions:

We hope to see you there on the 14th!