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.

I’ve just released SLK 1.6 to Beta on the CodePlex site. This has actually been ready to go for a while, several months in fact, but I haven’t managed to find the time for documentation of the new functionality. I’ve now taken the decision to release it without the documentation so that people can at least start using it and feeding back any problems.

The main changes are:

• Better handling of not quite fully compliant SCORM content
• Textual grading as well as numeric (optional) e.g. ABC type grades
• Quick assignment type. That is assignments not based on a document. This should make it much easier to use for a homework diary, especially if the assignment is along the lines of "Read chapters 1 & 2 of book y."
• Email alerts. The most requested extra functionality.

The Calendar Overlays in SharePoint 2010 is a fantastic feature which allows you to display multiple calendars in the same view. This allows you to logically partition your calendar events into separate calendars and view them all together without having to lump them all in one calendar. This makes maintaining the calendars far simpler, especially as each calendar can have it’s own permissions limiting read and write access to the events.

An example from a school might be:

Here’s a sceenshot of the calendar’s overlayed in month view. The Whole School events are in green, the Exams in yellow and the staff in blue.

  This all works great out of the box. The problem comes when you don’t have permission to one of the calendars. So when a student views this they can see all the events from the Whole School and Exams calendar, but also get the error message "Unexpected response from server".

This doesn’t look very good.

Luckily there’s an easy solution to this. You just need to add the following css to the page. The easiest way is through a content editor web part, either directly in it, or my preferred method is to link to a text file with it in so it can be reused.

<style type="text/css">.ms-acal-error{    display:none !important;}</style>

This will hide the text and error image from showing so they won’t know that anything is amiss, or that there is a calendar they don’t have access to unless they look at the page source.

I have just added an option to export thumbnails from Sims.net using our free PhotoExport tool.

The driver behind this was to load the photos into Active Directory for use in Outlook/Exchange 2010. For use in Outlook the photos need to go into the thumbnailPhoto attribute. The attribute’s maximum size is 100Kb, however the Exchange commandlet Import-RecipientDataProperty puts a 10Kb limit on it. In addition the recommended size for images in Outlook/Exchange is 96 x 96 pixels.

So when the thumbnail option is used for PhotoExport, it first checks to see if the image size if over 10Kb and if it is, it then resizes the image so the longest side is 96 pixels. How large the image file size actually is will depend on it’s format of course. With the images from the Sims test database a 96 x 96 bmp is about 22 Kb while the same size Jpeg is only about 3Kb. As the file format required by Outlook/Exchange is Jpeg, then that works out as a good size.

The switch to export thumbnails is /thumbnail, so to export thumbnail jpegs for staff the command would be

PhotoExport username password outputFolder /format:jpg /thumbnail /staff

Here’s a screen shot showing the difference in sizes

This post is for SCORM tool and package developers rather than the users of SLK.

If you need to see the interactions between a SCORM package and the SharePoint Learning Kit frameset there is a debug mode which will show you all the interactions between the package and SLK. Once enabled, the frameset will pop up another window into which all the interactions are logged. Here is an example from saving the answers from the first Quick Check in the Solitaire package (click for a bigger image).

Looking at the output more closely at the highlighted lines below, you can see that the first interaction has an ID of Q1, is of type fill-in and the value is patience.

Hopefully, this should give you enough information if your package isn’t working as expected with SLK.

To enable the debugging:

1. Do not do this on a production server, only on a development machine.
2. Find FramesetMgr.js in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\SharePointLearningKit\Frameset\Include or the equivalent path on your server. If you are running SharePoint 2007, the 14 will be a 12 and if you are using a localised version of Windows it will be in the localised path.
3. Open the javascript file in your favourite text editor and find line numbers 153 and 154.
4. Comment out the FM_NoOp line and uncomment the FM_DebugLog line to give
5. Next time you open the Frameset you should also get the debug window showing. Note, that you may need to allow pop-up windows for it to show.

Hopefully this will enable you to debug your packages interactions with SLK.

I’ve just released SharePoint Learning Kit 1.5.2. This is a bug fix release and contains fixes for:

• Configuration Link in Central Administration for SharePoint 2007
• Enumerating Domain Groups

This is now the recommended stable release. You can download from the 1.5 release page.

All new fixes will now go into SharePoint Learning Kit 1.6.

I am pleased to announce that SalamanderSoft has developed an integration solution from iSAMs to PASS Finance.

The solution will pull data from iSAMS and ensure that your PASS Finance system reflects the data in iSAMS. This avoids manual re-keying and the resulting mistakes creeping in.

We are partners with both iSAMS and WCBS so you can be assured that the solution is fully supported.

Currently, we support updating pupil and contact information in PASS. So whether it’s applicants, current pupils or leavers we can update PASS. The system is completely configurable, so you can integrate as little or as much information as you want.

Staff, extra curricular and exam billing integration will follow once WCBS complete their API.

It’s also not just iSAMS we can integrate with PASS, we can pull data from any of our supported MIS systems (e.g. Sims, Facility CMIS, Integris, Phoenix) and update PASS with it.

I’ve finally got round to putting some information up about our Online Reporting Web Parts on our company web site. These have been available for some time, but I hadn’t found the time to write anything about them. The information on there is still very basic, but at least it’s something up, even if it’s mainly screenshots.

They are completely customisable, with many different configuration options. Especially the look and feel, which is governed by CSS which you can easily modify/replace.

They currently support Sims and Facility CMIS in both in-school and hosted situations, and we have designed them so we can easily add support for other MIS’s if there’s demand.

To whet your appetite here’s a screen shot of it in action (click on it for a larger image) or go to their page on our company web site for a bit more information.

I shall be blogging more details about them under the Online Reporting category of this blog, so check back for more details.

Inspired by a discussion on Edugeek I’ve quickly knocked up a quick utility to check the format of email addresses in a csv file. You can download it from here.

This utility will iterate through an input file and check the validity of email addresses. Each line of the input file must be of the format identifier,email address. The identifier is used in the output message, and the email is of course checked.

Example input file

002345,info@salamandersoft.co.uk
002346,me@domain.com
002347,do not email
    

The utility will pick up the 3rd line as an invalid email address and output

POSSIBLY INVALID:002347:do not email

Internally it uses a regular expression to check the email. This is not perfect and will give false positives when compared to RFC 2822, but it is good enough for most purposes.

The regular expression used is: ^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$

To use:

1. Run CheckEmails.exe inputFile
2. inputFile is the relative of absolute path to the csv file

So to use to check your email addresses in Sims, first create a report which contains their Admission Number and email which saves to csv. The run the report and the use the resulting file as input to CheckEmails.

You can run CheckEmails.exe /? for a brief help summary.