Publishing InfoPath Forms to SharePoint 2007 “The following URL is not valid:”

We have been making quite a bit of use of InfoPath forms recently and have published successfully to a number of sites across our farm.  However yesterday I tried to publish to a new site and got this error:

The following URL is not valid: https://…….

After a lot of googling I confirmed that this was not an uncommon error, but none of the scenarios matched our situation; i.e. we already have a root site and have been publishing successfully for some time, and could still publish to other sites.

After trying some test the culprit seems to be the Office SharePoint Server Publishing Infrastructure feature!

  • trying to publish to sites which have Office SharePoint Server Publishing Infrastructure enabled throws this error
  • publishing to a subsite which does not have Office SharePoint Server Publishing Infrastructure enabled works fine

It would have been nice to have seen this documented somewhere when I googled, but I suppose that is the danger of just searching.

URI encode Source attribute in SharePoint 2007 Data View Web Part calling an InfoPath form

In the previous post, Javascript in Data View Web Part XSLT, I showed how to use javascript to do things that XSLT alone cannot do in the SharePoint 2007 Data View Web Part.

The primary motivation for investigating this was wanting to

  • add a DVWP that showed items from a Forms Library
  • include in that DVWP a link that would open the InfoPath web form
  • direct the user back to the DVWP when he/she closed the InfoPath web form (including any querystring that could be used to filter the DVWP)

Creating a link to an InfoPath web form is tricky enough because of the syntax, but the real challenge was URI encoding the current page URL so that it could be used as the Source attribute and direct users back to the DVWP.

This approach calls a javascript function from the link column in the DVWP that constructs the InfoPath friendly URL and redirects the browser to that address.

This function uri encodes both the url and the query string and then inserts them as the Source attribute.

trgt = @FileDirRef

function uses trgt to calculate the subsite address for the /_layouts/FormServer.aspx url using trgt.substring(0,trgt.lastIndexOf(“/”))

(typically the DVWP will be showing the contents of the form library so this will work)

fn = @FileRef

This is the full file name of the form that is to be opened

Javascript in Data View Web Part XSLT

Querying SharePoint 2007 Lists from InfoPath using XML

As a number of bloggers have noted, InfoPath does not read SharePoint lists quite as well as you would expect it to be able to do, at least in its 2007 incarnation.  The most obvious ways to query a SharePoint list have serious limitations

  • Creating a data connection using the SharePoint list wizard only allows lookups on the ID column (great for master->detail lookups but not a lot else)
  • Using the SharePoint web service GetListItems simply fails

The approach presented here builds on a Sharepoint Tips And Tricks article which explains these limitations and suggests an alternative approach.

Unlike in the article, in this situation we need to connect to SharePoint to look up a value in a list based on values on the InfoPath form, i.e using a respondent’s age, gender and body fat percentage the system returns a result ranging from “very lean” to “very fat” from a SharePoint list which has all the permutations for gender, body fat and ages (about 350 records).

By using an XML data source InfoPath is able to filter the SharePoint list data on arbitrary fields rather than just ID, in our case gender, age range and body fat.

Create an XML data connection

The process is the same as described in the article with one small exception.

  • add data connection
  • Choose XML
  • paste in the address in the form http://server/infopath/_vti_bin/owssvr.dll?Cmd=Display&List={listGUID}&XMLDATA=TRUE
    • http://server/infopath is the full path to the web where the list is found
    • {listGUID} is the list GUID (which can be found from the List Setting URL)
  • choose “Access the data from the specified location”
  • Give the data connection a name
  • Check “Automatically retrieve data when form is opened” (important!)
    • this is different from the article

The list data is now available to InfoPath, but with a few provisos.

Using the XML Data Connection

Opening http://server/infopath/_vti_bin/owssvr.dll?Cmd=Display&List={listGUID}&XMLDATA=TRUE in your browser will show you how SharePoint has “re-interpreted” the list data

  • all the column names are prefixed with “ows_” and use the “internal name” (i.e. no spaces)
  • number values are returned to thirteen decimal places
  • calculated columns are included (unlike in a SharePoint-type data connection), but the formatting may be different
    • in my case concatenating gender, age range identifier and body fat percentage returned a value  ows_uniq=”string;#Female130″

Bearing this in mind values from the XML data source can be used in the same way as other Secondary Data sources, in the XPath formula editor choose the column to return from within rs:data, z:row and click on the filter button to construct a filter.

Considerations

This approach seems to be well suited to my situation where

  • the form is lightly used by a small group of people
  • the SharePoint list data is effectively static
  • the SharePoint list contents are quite small

If this was not the case the option of writing a small webservice proxy might have been preferable.

Calculating the difference in dates on InfoPath forms

Calculating the difference between two dates, as in a person’s age at a particular point in time, seems to be a pretty common request among people developing InfoPath forms for use in SharePoint.  In my case I followed the advice in Alec Pojidaev’s Blog but it did not seem to work for me, giving an age that was too high.

So I have now taken inspiration from Villeroy’s post on the OpenOffice.Org forums, Re: [Solved] Replacing DATEDIF in an Excel equation?, where he presents a generic method of calculating ages in spreadsheets using IF statements.  The logic behind the approach, for years at least, is pretty obvious and can be adapted to InfoPath, even though there is no IF statement as such.

How it works

The solution also takes advantage of several InfoPath features

  • the fixed date format in InfoPath allows you to extract the day, month and year portions using the substring function.
  • rules are applied in order so you can simulate the IF statement logic from the spreadsheet formula
  • conditions on rules can include expressions (at the bottom of the list of fields) again helping to implement the IF logic

The Fields

There are 3 fields in InfoPath: DoB (date of birth), DateOfTest, and Age

Age is a read only field

DoB is a required field

DateOfTest is also required and is the field with the rules attached in this example (not really the best idea see below)

The Rules

This solution uses three rules to simulate the IF statements in Villeroy’s solution.  They are applied in order, but only one is ever run because of the conditions

Rule 1,  Month is less and Age is blank

Condition:

  • Age is blank AND
  • DoB is not blank AND
  • The expression substring(., 6, 2) < substring(../my:DoB, 6, 2)
    • substring(., 6, 2) extracts the Month as 2 digits from DateOfTest
    • substring(../my:DoB, 6, 2) extracts the Month as 2 digits from DoB   (your XPath may vary)

Action:

  • Set field Age to
  • substring(., 1, 4) – substring(DoB, 1, 4) – 1
    • substring(., 1, 4) extracts the Year as 4 digits from DateOfTest
    • substring(DoB, 1, 4) extracts the Year as 4 digits from DoB
    • -1 because we have not reached our client’s birthday yet

Rule 2,  Month is the same, the Day of Month is less and Age is blank

Condition:

  • Age is blank AND
  • DoB is not blank AND
  • The expression substring(., 6, 2) = substring(../my:DoB, 6, 2) AND
    • i.e. both dates have the same month
  • The expression substring(., 9, 2) < substring(../my:DoB, 9, 2)
    • i.e. the day of the month for DateOfTest is less than the day of the month for DoB

Action:

  • Set field Age to
  • substring(., 1, 4) – substring(DoB, 1, 4) – 1
    • i.e. the same as in Rule 1

Rule 3,  Month is greater and Age is blank (i.e. everything else)

Condition:

  • Age is blank AND
  • DoB is not blank

Action:

  • Set field Age to
  • substring(., 1, 4) – substring(DoB, 1, 4)

Why we should not just have rules attached to the DateOfTest field

In this example it is just the DateOfTest field that has the rules.  This is because in this example the DateOfTest field comes later in the form and we can expect users to fill it out sequentially.

However, if there was a danger that users might complete the DateOfTest field before completing the DoB this particular approach would not work.  To overcome this limitation a similar set of rules should also be attached to the DoB field that would be triggered if the DoB field was filled in second.