SharePoint 2007: WorkFlow email removes space from Lookup URL

Here's a thing ... A colleague wanted SharePoint to handle requests for printed stationery (letterheads and so forth), based on Templates stored as PDF files in a SharePoint Document Directory. So, the best way of doing this was to create a new separate Custom List to store the requests and when a new request was logged, use a SharePoint Designer WorkFlow to fire off an email to our facilities people so they could action the printed stationery request.

Sounds simple, right? Well, it was ... up to a point.

So I created the list, and added these columns:

SelectTemplate - A Lookup. This points at the Doc Directory where the PDF tempates are stored. As I couldn't retrieve the file name (SharePoint doesn't offer that option), I had to target the Title field. I made this work this by adding a separate WorkFlow that copies the file name into the Title field whenever you add a new document to the Directory. This field shows as a drop-down picklist in the NewForm.aspx page.

Quantity - obviously, we need to know how many copies of the letterhead they want.

UserText - this is to convert the User ID into a proper name - see the very first post in this blog for how to do that.

This meant that users could create a new item in the Order Stationery list, fill in the NewForm and let the WorkFlow fire off the email. For the email I stole the code SharePoint generates for a List Alert and customised it to hold the fields I wanted. The content of the email would include the Title (I gave the Title field a default value of "Stationery Order"), The PDF template name rendered as a link to the stored PDF, the Quantity required, the name of the Requestor and the date the request was made.

All pretty straightforward.

But when I ran a test, I found something really odd was happening. The link to the PDF didn't work. Here's how I sent up the link in the WorkFlow email:

<a href="http://myserverpath/[%LH Orders:SelectTemplate%].pdf">[%LH Orders:SelectTemplate%]</a>

No reason why that shouldn't work, right? But when I did View Source on the email in my Inbox I could see, weirdly, SharePoint had stripped out the first word space it encountered in the URL but translated the remainder into "%20". Like this:

<a href="http://myserverpath/Birmingham-%20Letterhead%20Jan%2014.pdf">Birmingham - Letterhead Jan 14</a>

Why it was doing that I had no idea. A search on Google showed this problem was frustrating other people as well. So, not just me then.

Not one instance of this problem, posted in the various forums (fora?), had an adequate answer beyond, "Don't put word spaces in file names". My problem with that advice is that these systems are used by human beings and people find file names full of underscores and hyphens hard to read on-screen. Even more confusing when they're trying to find a complex file name in a picklist of many complex file names.

However, because I'd been able, on an earlier occasion, to use JavaScript in a list's NewForm.aspx page to transform the UserID value into a fully-rendered user name I figured there might be a way to replace the word spaces throughout the filename with the "%20" character. So the first thing to do was to create a new column to hold the cleaned up text of the file name. I called it "templateURL".

templateURL - this is the field where we'll store the "escaped" file name.

Then I needed to retrieve the ID of the field as rendered in the NewForm.aspx page. So I opened the NewForm page and did View Source, then grabbed the IDs of both the SelectTemplate field and the templateURL field.

Now my JavaScript skills are pretty poor, so I managed to get a colleague to help me with this. The script he came up with was this:

<script>
function getSelectedText( obj ) {
   return obj.options[obj.selectedIndex].text;
}
    function encodeTemplate() {
var templateURL = document.getElementById("ctl00_m_g_23fc3b40_9a6b_4126_bcf4_a3bfc2fff76d_ctl00_ctl04_ctl05_ctl00_ctl00_ctl04_ctl00_ctl00_TextField");
var templateLookup = document.getElementById("ctl00_m_g_23fc3b40_9a6b_4126_bcf4_a3bfc2fff76d_ctl00_ctl04_ctl01_ctl00_ctl00_ctl04_ctl00_Lookup");
templateURL.value = encodeURI("http://oneintranet.qbe.eo/departments/cres_procedures/QBE Claims/" + getSelectedText(templateLookup) + ".pdf");
}

var templateLookup = document.getElementById("ctl00_m_g_23fc3b40_9a6b_4126_bcf4_a3bfc2fff76d_ctl00_ctl04_ctl01_ctl00_ctl00_ctl04_ctl00_Lookup");
templateLookup.attachEvent("onchange", function(){encodeTemplate()});
</script>

I pasted this script into the NewForm.aspx page after this line:

<asp:Content ContentPlaceHolderId="PlaceHolderBodyAreaClass" runat="server">

The JavaScript grabs the value rendered by the SelectTemplate field. It then replaces the word spaces with "%20" (that's the encodeURI function), then copies the result into the plain text field I set up, templateURL. Finally, it builds a URL for the PDF template file by putting in the server path at the front and tagging ".pdf" on the end. Note that the actual name of the document directory that holds the templates also has a word space in it. The encodeURI takes care of that, too.

All that remained was to change the link in the WorkFlow email to:

<a href="[%LH Orders:templateURL%]">[%LH Orders:SelectTemplate%]</a>

And there you go. All done and dusted. You have a link to a stored document that works and gets round SharePoint's strange habit of removing just the first space it encounters in a URL inserted from a Lookup in a WorkFlow email.

One enhancement you might make would be to hide the text fields in the NewForm.aspx page, so that your users can't mess with the text automatically copied there during the placing of an order. Another of my earlier blogs describes Hiding Fields in NewForm.aspx.

Hope this helps someone.