Tuesday 12 August 2014

Display a user's name in workflow email

I needed to create a List in SharePoint 2007 that would allow my colleagues in IT to track installations on staff PCs and laptops. I then wanted to be able to trigger an email to the staff member when the installation was completed, giving instructions on how and when to reboot their computers.

Obviously, a Workflow was the way to go, but I kept bumping my head against the problem of extracting the staff member's name from the "Name (with presence)" field in the SharePoint list. The Workflow was writing "domain\userID" instead of "Firstname Lastname". So I had a bit of a Google and came up with ... not very much. Others were obviously having the same problem - it seems to be a known issue with SP2007.

How to resolve full names from a Person or Group column

Workflow generated email - Username lookup - TechNet

[Annoyingly, the messageboard moderators had in both cases marked these questions as answered, even though the OPs had reported that the answer didn't work for them.]

So how do you get the text of a person's name out of a "Name" list column? Well, you can't really ... at least, not out of the box.

I certainly didn't want to start installing Codeplex solutions onto our server (I work in a Corporate environment and making changes to the server involves much tsk-ing and shaking of heads). So I had to come up with another way of doing it.

I reasoned that the IT project manager running the project would have to type in the staff member's name anyway, so why not just type the name into a plain text field? No difference in effort, right?

So I set up the list with these columns (among others):

Employee - plain text field
UserName - Person or Group, Show field value = Name (with presence)
UserID - Person or Group, Show field value = User Name
Department - Person or Group, Show field value = Department

Then I set up a workflow to copy the values for UserName, User ID and Department from the plain text Employee field, when a new item is created ... that's Step 1.


This copies the user's name from the plain text column into the Person or Group columns ... like this:



Step 2 is where I create an email template to alert the employee that their installation is complete. (For test purposes, I'm sending this email to myself using the "Created by" value, but you can use the "User Name" value to send the email to the employee named in the List Item.) 

Be careful to insert a pause before the email is triggered. If you don't, the email will contain the plain text value as entered in the Employee field for each of the other columns. That is because the system takes a few seconds to resolve the plain text into a "Name (with presence)".


Setting up the email is quite easy. I've reproduced a simplified version here, so you can see which columns I called in the email, then the finished result below:


You can see from the email reproduced here how the columns output into a Workflow email. The one to use if you want the include the User's name, say, in a salutation, is the first, plain text one.


Hope this helps someone.


Thursday 9 January 2014

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.