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

Dashboards, Data, Sparklines and Excel 2010

I’ve been really busy over the past few weeks, so haven’t had much time for new blog posts.  So I thought I would roll up some related news into one post, as all of the information is about Business Intelligence, data and dashboards.

First, if you are interested in how browser-based dashboards can be used to show a company’s financial data, take a look at Microsoft Investor Central.  Here’s a snippet showing what it looks like right now:

If you visit the page you will see it makes heavy use of Silverlight to let you zoom in on and manipulate the charts.  Thanks to Patrick Husting for the original heads up on this.

And while on the subject of dashboards, can I also recommend this demonstration PerformancePoint site from Richard Lees, which has loads of examples using Analysis Services, data mining techniques and PerformancePoint scorecards.

Last week Microsoft made all sorts of announcements about Office 2010.  I’ve been taking a look at Excel 2010, and in particular the new data presentation options.  It looks like this release will have some worthwhile new Business Intelligence features.  As well as Gemini, which will I believe be available as a separate add-in, this will be the first release which will let you create Sparklines, those little word-sized graphics which I first blogged about in 2007.

Here’s how they look:

You can read more about how they work in Excel 2010 over on the Microsoft Excel Team blog here and even more here.

Finally, I’d like to mention the Guardian Newspaper’s Data Store.  This is a fairly unique exercise by a national newspaper to make datasets they have created, frequently to support news stories, publicly available.  Most of these can be accessed as Google spreadsheets, also downloaded.  As well as being interesting datasets in their own right, they are incredibly useful if you want to populate demonstration dashboards, load sample lists, or create training exercises.  You can keep up to date on what they are doing on their blog or on Twitter.

Technorati tags: excel 2010, sparklines, dashboard, performancepoint

Add PDF Result Type to MOSS 2007 Advanced Search

Originally this post was going to be a lot longer – the plan was to go into lots of detail about how to customise the SharePoint 2007 or Search Server Advanced Search page and extend the Result Type drop-down to allow selection of Acrobat PDF Files.  Then I saw this post over at Get the Point which contains links to several very good articles from Ben Curry and Bill English describing how to customise the SharePoint 2007 Search Centre. 

One of their articles, which you can download as a PDF file, is called Customize the Advanced Search Page in an Office SharePoint Server 2007 Search Center Site and it covers the initial customisation options for Advanced Search web parts very well.  So all I need to do is explain how to use their techniques to add PDF documents as a Result Type.

If we create a document in Microsoft Word 2007, we use standard document properties to create searchable metadata like this:

And once the document has been uploaded to SharePoint and successfully crawled, those properties become searchable through the Advanced Search page.

Equally, an Acrobat PDF document may contain properties.  Here is the same document converted to PDF, this time showing the properties in Adobe Reader:

So how do we make it easier for our SharePoint users to search only within PDF files, and for specific document properties?  Well, as you will see if you download and read the article mentioned above, the trick is to add a new ResultType for PDF files in the properties XML.  NOTE – of course all of this assumes that your SharePoint server is already successfully crawling and indexing Acrobat PDF files…

The “properties” section which determines what can be selected for Result Type “Word Documents” looks like this:

<ResultType DisplayName="Word Documents" Name="worddocuments">
    <Query>FileExtension=’doc’ Or FileExtension=’docx’ Or FileExtension=’dot’</Query>
    <PropertyRef Name="Author" />
    <PropertyRef Name="DocComments"/>
    <PropertyRef Name="Description" />
    <PropertyRef Name="DocKeywords"/>
    <PropertyRef Name="FileName" />
    <PropertyRef Name="Size" />
    <PropertyRef Name="DocSubject"/>
    <PropertyRef Name="Path" />
    <PropertyRef Name="Created" />
    <PropertyRef Name="Write" />
    <PropertyRef Name="CreatedBy" />
    <PropertyRef Name="ModifiedBy" />
    <PropertyRef Name="Title"/>
    <PropertyRef Name="Manager" />
    <PropertyRef Name="Company"/>
</ResultType>

Now, not all of the same properties can be found in a PDF document.  In the test PDF document I created using the Office 2007 PDF add-in, the properties I was able to successfully search in SharePoint were Keywords, Name, Size, Subject, Created Date, Last Modified Date and Title.  And this is the ResultType I added to my Properties XML to make this work:

<ResultType DisplayName="PDF Documents" Name="acrobatpdf">
    <Query>FileExtension=’pdf’</Query>
    <PropertyRef Name="DocKeywords"/>
    <PropertyRef Name="FileName" />
    <PropertyRef Name="Size" />
    <PropertyRef Name="DocSubject"/>
    <PropertyRef Name="Created" />
    <PropertyRef Name="Write" />
    <PropertyRef Name="Title"/>
</ResultType>

All of which needs to be added above the </ResultTypes> closing tag at the end of the properties.  Once this was in place, I could select PDF result Types and search by property, like so:

One word of caution if you plan to edit the Properties XML in the web part – the text editor you get in the Web part is pretty rudimentary, as you can see here

So if you aren’t familiar with XML and don’t have another XML editor to paste the text into, you might want to copy and paste the original text into a notepad file before you start.

By the way, if your documents are currently stored in Office format, and you’re looking for a solution which manages conversion to PDF and transfers metadata across, take a look at the Office to PDF Conversion Solution Accelerator from Bamboo Solutions – more info here.

Update 12 June 09 – Another SharePoint add-on worth a look if you are converting Office documents to PDFs is Muhimbi PDF Coverter for SharePoint.  Right now it looks like you can convert to PDF from various Word document versions as well as RTF and plain text, although I understand more file types will follow.  They also have a feature which lets you do the PDF conversion from a SharePoint Designer Workflow which looks like it could be useful.  Muhimibi also tell me you get the option to copy metadata across when you create the PDF. I plan to try this out and hopefully post about it soon.

Technorati tags: Advanced Search, SharePoint 2007, PDF

Word 2007 – Filling Up Demo Documents

Often when I’m demonstrating things like SharePoint document libraries, I find I need to create a new Word document and quickly fill it with some placeholder text, ideally real text rather than the usual “asdadsadasdasda” that you end up typing otherwise.

For a long time I’ve used a little feature in Microsoft Word, whereby you can type =rand() and hit enter to generate some placeholder paragraphs, or =rand(paragraphs,sentences) to specify a number paragraphs and sentences.  Even though this feature has been around in Word for several versions now, most people still don’t know it exists.  One thing that has changed in Word 2007 is that the generated paragraphs are a little bit of Word 2007 help, like this sample -

On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document …

In previous versions of Word, using =rand() generated multiple paragraphs of The quick brown fox jumps over the lazy dog which is a pangram containing all letters of the alphabet.  Well, thanks to this post on the Microsoft Word Team blog I’ve discovered you can still use the “quick brown fox” version – you just have to use =rand.old() instead. 

The Word Team’s post also mentions another function (new to me) which can be used in Word to generate Lorem Ipsum text, using = lorem() – again, quite useful for generating placeholder text in documents, web pages and SharePoint web parts.

Technorati tags: Word 2007, lorem ipsum, placeholder

Kilimanjaro Becomes SQL Server 2008 R2

News in the past week of plans for the next version of SQL Server.  This previously had the code name Kilimanjaro, but now becomes SQL Server 2008 R2.  I’m not really sure I understand the Microsoft’s logic behind product naming.  As far as I can see SQL Server 2008 R2 will be released in H1 2010 – at around the same time that we will start to see other new products such as SharePoint Server 2010 and Office 2010…

Anyhoo, you can take a look at some of the new features and services on this SQL Server R2 page, and you can also sign up for notification when it gets to Community Technology Preview stage.  I’ve also heard that this release of SQL Server will, like other future server products, be 64-bit only.  As well as some interesting screenshots the page has a clever “silent movie” style video:

It does look like R2 will get some nice new Business Intelligence and reporting features.  One of these is currently known as “Project Gemini”,  which uses in-memory database technology and column-oriented processing techniques.  This can be combined with a forthcoming Excel 2010 add-in to provide a much improved ad-hoc data analysis tool.  Excel 2007 is pretty good at this at the moment, but not as good as it should be, and certainly not as good as some of the desktop analysis tools from ProClarity.

We don’t have all the details on Project Gemini at the moment, but here’s a video of a demo by Donald Farmer from Microsoft showing how it will work with Excel (with 20 million rows of data!):

And here’s another one showing how you might publish your analysis out to a SharePoint site:

Technorati tags: SQL Server 2008, Kilimanjaro, Business Intelligence, Project Gemini