Things I’ve Learnt About InfoPath and Forms Services

Recently I’ve been involved in the development of a SharePoint 2007 solution using InfoPath and Forms Services.  The purpose of the solution was collection of employee questionnaire data using one of several forms designed as a multi-page survey.  InfoPath was chosen because the process needed to make use of SharePoint workflows, which are not supported with survey responses.  Also, the survey form needed to behave differently depending upon whether an employee or their manager was completing it.

I wanted to use this post to document some of the things I learnt during the development process.

Only Use Custom Code if Rules and Views Can’t Do the Job: You can do an awful lot with InfoPath Rules and Views to give the user a good experience when completing a form.  You also have the added advantage that when publishing your browser-compatible form to SharePoint Forms Services, you don’t have to deploy it as an Administrator-approved form.  Over at EnduserSharePoint, Paul Galvin has posted a useful starter template for InfoPath which includes views for handling successful submission and discarding the form.  And there are other useful InfoPath tips on Paul’s blog.

“Wizard” Style Forms Work Well Using Views: Using [Next>] and [<Back] buttons combined with views, you can create a nice “wizard” style user interface for your form.  Here’s a nice post from the MS InfoPath team on this topic.

But You can Only Validate a Maximum of 5 Fields Using a Rule: This is one of the issues which required use of custom code.  On our “wizard” style form, there are 7 questions per page, and the user should not be able to move to the next page until all 7 have been answered.  So we ended up putting validation code on the “click” event of each “[Next>]” button.  This tutorial from DevExpertise explains how it’s done.  It ended up as one of the reasons for a custom code requirement – I guess it might also be possible to to this using XPath, but as we also ended up needing code for another feature (see below), we used custom code here.

If You Want to Pass URL Parameters to your Browser Based Form, You Need to Write Some Code: For this solution, a manager could be assigned multiple questionnaires to complete (one for each person they are managing).  So we wanted to pass the ID of the employee into the new form, then this would be used to look up employee details on a secondary data connection.  Turns out that you can only read URL (querystring) parameters using code.  Here’s a post from the InfoPath Team showing how to pass/retrieve these parameters.

Use Site Columns for Promoted InfoPath Fields: Our solution required two different forms to be available as content types in the same forms library.  If both of these forms use the same set of promoted columns, your life will be a lot simpler if you first create the columns you want to promote as a set of site columns specific to the solution.  If you don’t, and you deploy the forms to different servers, you can end up with multiple “Microsoft InfoPath” site columns with same name but different IDs.  Also, we wanted to promote some InfoPath text to SharePoint multiline text columns.   This doesn’t seem to be possible using the default “Create New Column in this library” option but can be done using site columns.

Ensuring your Submitted Forms Have Unique Names: You need to ensure that the submitted form has a unique name.  Here are couple of items you might find useful – Hannah Scott: Submit InfoPath forms to SharePoint with Unique File Name and  SharePoint User Group UK Submit InfoPath with unique filename using SharePoint Library ID.

Centrally Managed Data Connections Make it Easy to Switch from Dev to Test to Production: Converting your SharePoint data connections to centrally managed ones makes it much simpler to move your solution between servers or web applications.  To make this work you will either first need to use a data connection library for data connections, then upload these using Central Admin, or use this UDC File Authoring Tool (which is also an InfoPath template).  More information here and here.

If You Are New to InfoPath, Read Paul Culmsee’s “Humble Leave Form” Series”:  Paul Culmsee of CleverWorkarounds fame has written an amusing and useful series on InfoPath called A Tribute To The Humble Leave Form.  There are 7 parts to the series so far, all based around a leave form for the Springfield Nuclear Plant.  In part 5 Paul discusses using the UserProfile web service to get more useful info about the user completing the form. 

And More Information on Getting User Information Without Code: Here are a couple more posts on getting user information from Itay Shakury and Clayton Cobb.

I hope others embarking on InfoPath/Forms Services projects find some of this useful!

Technorati tags: InfoPath, Forms Services, SharePoint

Planning for SharePoint 2010

So, lots of discussion in the SharePoint community following this post from the Microsoft SharePoint Team on preliminary system requirements for SharePoint Server 2010.

We’ve known for quite a while now that the next release of SharePoint will be 64-bit only.  The new and interesting information is:

It will require 64-bit Windows Server 2008 or 2008 R2.  In other words, you won’t be able to use Windows Server 2003, even if it is a 64-bit version.

It will also require 64-bit SQL Server, either 2008 or 2005.  In fact Microsoft best practice suggests that in a SharePoint farm, the SQL Server boxes should be the first candidate for 64-bit technology anyway, but this certainly gives you more reason to go 64-bit.

Now it’s worth bearing in mind we are talking about a product version which isn’t even in a full beta yet, so it’s going to be a while before real deployments (probably second half of 2010 for those pioneers who aren’t afraid of arrows).  But the points covered in the post should certainly be taken into consideration, particularly if you are in the planning stage and looking at hardware and software selection.  Also Microsoft are promising a much easier upgrade path this time, which is why they’ve put the Upgrade Checker into Service Pack 2.

If you are investigating 64-bit now, I would recommend taking a look at the Combined Knowledge whitepaper on how to move MOSS2007 from 32-bit to 64-bit which you can download from here (lots of other good downloads there as well). 

You might also be interested in best practices for using virtualisation technologies in a SharePoint farm.  If so, let me point you to the set of Best Practice for Virtualizing SharePoint articles from Brian Wilson of the Microsoft UK SharePoint team.

Technorati tags: sharepoint, moss, 2010, upgrade, 64-bit

SharePoint Knowledgebase Part 2 – Creating a Knowledge Base Site

In part 1 I talked about my favourite SharePoint Knowledge Base sites, places I look to frequently for SharePoint information.  In this post I’m going to talk about resources available if you want to create your own SharePoint Knowledge Base site or site collection.

What is a Knowledge Base?

A quick check on Wikipedia gives us this:

“Human-readable knowledge bases are designed to allow people to retrieve and use the knowledge they contain. They are commonly used to complement a help desk or for sharing information among employees within an organization. They might store troubleshooting information, articles, white papers, user manuals, or answers to frequently asked questions. Typically, a search engine is used to locate information in the system, or users may browse through a classification scheme.”

And that immediately raises the question you should ask first – what information problem am I trying to address here?  For example, do I want a site where users can post questions and other users can answer them?  Or maybe I already have a set of papers, manuals or other documentation which I need to upload onto a site, possibly adding more value with SharePoint metadata such as classification, topic area, or review date?

Out of the Box Functionality

The good news is that we can get a lot of that functionality “out of the box” with SharePoint.  Judicious use of document libraries, Wiki Pages and simple custom lists will give you a nice, easy-to navigate structure for for your body of knowledge.  All of these can have an approval process added to them and I would also recommend adding a review date column where appropriate so that key personnel can get a “due for review” view of the list or library. 

Also, think about how you want to categorise your documents and articles and use one or more custom lists as lookup columns for use in views or as filters.   A good example of this approach can be seen in the End User SharePoint case study Pages and Sites in SharePoint 2007.

Microsoft’s Free Knowledge Base Template for WSS 3.0

This is a free download from Microsoft, one of the “fantastic 40 templates” and could be worth a look if you want to get some ideas.  It’s an administrator template, which means it needs to be deployed onto the server rather than uploaded as a site template (stp) file.  It includes pre-defined content types which let site users upload documents or articles and cross-reference.  Ian Morrish is hosting all 40 templates here, so you can try it out first to see if it suits your requirements.

Take Advantage of Search Keywords and Best Bets

If you are using SharePoint Server 2007 or indeed Search Server Express, make sure you are regularly reviewing search usage data and use this information to create and update search keywords and Best Bets.  Your users will get much more value out of your solution if they can quickly find what they’re looking for.

Zevenseas – Anatomy of a SharePoint Solution

Daniel McPherson has a series of posts covering the Anatomy of a SharePoint Solution.  The solution is in fact an employee blogging platform but many features of the approach used could apply to a knowledge base solution.  And some of the custom features they are using, such as Tagged Links, are available as free downloads.  They have also developed custom “Most Discussed” and “Most Viewed” features, something I am frequently asked about by clients (but not available for free unfortunately).

Other Free Stuff

Here are a couple of other free add-ons which might be useful for a knowledge base implementation (note, these are community contributions, so don’t normally come with any warranty – use at your own discretion):

SharePoint Document Rating System – lets users rate documents by applying a number of stars.

Ton Stegeman’s List Item Filter and Content by Type web parts – both provide nice ways to make it easier for users to filter and locate content.

SharePoint Knowledge Base Solution Accelerator from Bamboo Solutions

Last but by no means least, take a look at the Bamboo Knowledge Base Solution Accelerator.  The licence price for this solution is currently $2,000, but that’s pretty good if you consider how much a bespoke development might cost.  It incorporates the key features you’ll need to get your Knowledge Base up and running, including categorisation, search, questions and answers, ratings and most popular articles.  There’s a trial download available here, or for a two-minute overview of how it works, take a look at this:

Technorati tags: Knowledge base, sharepoint, bamboo

SharePoint Project Management with IntelliGantt and Bamboo Calendar Plus

I’m very impressed with the potential uses of SharePoint tasks for project management, and recently I’ve been working with the IntelliGantt Add-in for Microsoft Project 2007 to extend these capabilities.  The reason for this is that people want to create dependencies between tasks in their project (out of the box SharePoint can’t do this, but MS Project of course can) but still want to use the SharePoint tasks assignment and progress tracking capabilities.  I’ve found Bamboo Solutions Calendar Plus web part works well here too, particularly the Gantt view which hooks up nicely to the project task list.  More on this from Bamboo here.

One of the nice features of the Calendar Plus Gantt view is the option to show milestones, as coloured flags, diamonds or triangles.  The web part lets you select any Yes/No column in your task or calendar list as the milestone column.

Now, the IntelliGantt Add-in for Project 2007 lets you select a variety of Project 2007 columns which can be synchronised with SharePoint, but early on I discovered that the Milestone column wasn’t one of them.  I got in touch with John Milan from TeamDirection Inc (IntelliGantt’s vendor and developer) to suggest this as an enhancement and I’m pleased to say that within a matter of days a new build of the IntelliGantt add-in was available and included the option of exporting the MS Project milestone column to SharePoint.

In Microsoft Office Project 2007, a milestone is a reference point that marks a major event in a project and is used to monitor the project’s progress. Any task with zero duration is automatically displayed as a milestone. You can also mark any other task of any duration as a milestone using Task Information like so:

I created a small test project and identified milestones accordingly.  Here’s a list of the tasks in Project, with the milestone column showing:

Using the IntelliGantt Add-in for Microsoft Project, you can customize Workspace Settings when Sharing a project to a SharePoint list, and “push” the milestone column out to the task list:

Once the task is shared to a SharePoint site (I used a standard Project Tasks list as the starting point), IntelliGantt creates the new Milestone column as Yes/No data:

And we can create a SharePoint view of the list to show milestones only:

 

Now we can use the Bamboo Calendar Plus web part to display a Project Gantt.   We can create a web part page, add the Bamboo Calendar Plus web part to it, and select our Milestone column for display in the Gantt:

Which gives us a nice Gantt view in SharePoint with milestones clearly visible (here as flags):

Calendar Plus also has list filtering options, so we can choose only to show milestones as you can see here:

Which produces this type of display:

So far I’m very pleased with the way these products work together, and impressed with the rapid response from TeamDirection in adding the extra column.  The IntelliGantt add-in for Project 2007 uses .Net ClickOnce deployment technology, so licensed users will get the update automatically.  Also Bamboo’s Calendar Plus can use a list roll-up web part as its data source, so it should be possible to see specific tasks or milestones from multiple projects in one view.

Technorati tags: SharePoint, Microsoft Project, IntelliGantt, Gantt, Bamboo

Using Access 2007 To Update SharePoint Lists

Someone asked a question on the UK SharePoint User Group Forums about moving Access data to SharePoint lists, so I thought I would illustrate some of the Access 2007 features you can use with SharePoint.  Before starting, please note – SharePoint is not a relational database system, so don’t try to use it for full-blown relational database applications!!!  Having said that, it does work well for many smaller list-management applications for which you might have used Access or Excel, and I guess it’s possible (pure speculation here) that by the time we start hearing more about SharePoint 14 and Office 14, SharePoint and Access may be getting even closer together.

So, lets look at a SharePoint scenario:

I have a SharePoint list, in this case for contacts, and it includes a “Department” column.  This list may have been imported from Excel or Access, and the department column is currently a single line of text:

So I create a separate custom Department list with a Title column like so:

But at this stage, it’s not possible to edit the Contacts Department column from text to lookup, as you will see if you try to edit the Column properties:

So instead, I am going to create a new Department column of type Lookup, and use Access to do the updating work.  First, we RENAME the existing department as OldDepartment, then create a new column of type Lookup to store the lookup values from the Department list:

Now at the moment the Department list doesn’t contain any values, so we’ll also populate that list from a set of unique departments in the contact list.  So fire up Access 2007 and create a new blank database.   Next we link the contacts and department lists from SharePoint.  Do this by selecting the External Data tab and clicking on SharePoint list:

The Get External Data – SharePoint site wizard starts up:

Enter the url of the site containing the Contacts and Department lists, and be sure to select “Link to the data source by creating a linked table” option.  Then check the lists you want to link to and click OK:

Now we’ll populate the Departments list.  from the Create tab in Access select “Query Design”:

Add the Contacts list to the Query pane and drag OldDepartment into the grid:

We want a unique list of departments, so open the query property sheet, and set Unique Values to “Yes”:

This will give us our unique list of departments, and we want to append these to our empty Department list, so change the query type to Append:

Select Department as the table you want to append to:

Then select Title as the field you want to append to:

Now run the query.  Access will tell you how many records will be appended, and warn that the action cannot be undone.  Once it runs, you will have a unique set of departments:

Now we can update our department lookup column in Contacts.  Under the covers, the lookup will be stored as a number in the Contact list, and match to the ID column of a row in the Department list.  So, in effect, we want to create a query which says “look up the Department ID by matching Contacts oldDepartment with Title in Department, and add the Looked up Department ID to the Department column in Contacts”.  So we create a new Update query to do this.  If we create a query from Contacts and department, it will default to joining Contacts.Department to Department.ID:

But we don’t want this, so delete the join, and instead drag Department.Title to join to Contacts.OldCompany like so:

We need to change the query type to an Update Query:

And we will update the Contacts.Department to the value of matching Department ID:

Now we can run the query, and when it completes, our lookup values will be updated:

We can now delete the OldDepartment column from our SharePoint list, and we will have available Department values in the correct lookup column:

And that’s it!

Technorati tags: Access 2007, SharePoint