EndUserSharePoint.com: Email and SharePoint Designer Workflow – Part 1
Author: Paul Galvin, Microsoft MVP – SharePoint
Web site: Paul Galvin’s SharePoint Space
Many business workflow scenarios call for us to send an email at different parts of the process.
Like many things with SharePoint Designer workflow, this is relatively easy to accomplish, but is sometimes harder than it would seem. In this article, I address two tricky scenarios and outline a third which I will address in a future article.
The Scenario
To work through these two simple issues, let’s work with a specific business scenario in mind. We are developing a workflow solution to manage the new hire process. Before a new hire walks through the door on their start date, many different business steps need to take place. For example:
- Assign an office location.
- Set up in payroll.
- Get a security badge.
- Set up network credentials and systems access.
The workflow process is fairly straight forward:
- Create an entry in a custom list where we specify:
- Name
- Start date
- Direct manager
- Comment
- A set of status fields, including:
- Was office location assigned?
- Was payroll set up?
- Was a security badge created and ready for issue?
- Have network credentials been set up?
- When the workflow begins, send an email to several department managers who are responsible for assigning an office location, setting up payroll, etc.
I’m not going to discuss the status fields very much in this article. I include them to plant the idea that we can add these kinds of status columns to a list and then have the workflow update them. This allows us to see the current status of the workflow by simply viewing the list and looking at those columns.”
Based on that, we’ll end up with a “New Hire” form like this:
This example is a bit contrived. We would probably just assign a task to those individuals and let the system send them an email. However, let’s assume that the default task assignment email does not meet our email requirements in this case.
When we send these emails, we want them to follow this convention:
Subject:
New hire [new hire name] begins work on [start date].
Body:
Greetings, [new hire], a new hire, begins work on [start date].
Additional comment: [comment]
Note that if [comment] is blank, then we want to show “No special comment.”
This required email forces us to deal with the two issues I want to clear up in this article:
- How do we create a dynamic email subject?
- How do we test for an empty comment and create a dynamic message body?
Dynamic Subject
Dynamic email subjects are very easy, but not obvious at first blush. Many first-time and inexperienced SharePoint Designer workflow builders wrongly conclude that the “send an email” action only allows a fixed subject as shown:
To create dynamic email subject, use a workflow variable and the “build a dynamic string” action. The following screens show these steps:
First, click on “Variables …” and then “Add…” when the “Workflow Local Variables” dialog box opens:
We name our variable and give it a data type. We pick string, but there are several choices:
- Boolean: May be assigned either “Yes” or “No.
- Date / Time: Specifies and date and time.
- List Item ID: Refers to the unique ID of a list item. If you have ever created or modified a document library or custom list view, you probably noticed that all such creatures have an “ID” field.
- Number: Any numeric value.
- String: Any string value (all of the text in this article is a big “string”).
Now that we have defined our workflow variable, we need to assign it a value. We do this with the “Build Dynamic String” action:
In the following screen shot, I have manually typed in this string: New hire [] begins work on[].
I then put the mouse pointer in between the double brackets next to New hire and clicked the “Add Lookup” button:
This allows us to insert the new hire’s name from the list item. Repeat this process for the new hire start date. This is what it looks like when done:
SharePoint Designer uses the notation: [%List Name:Column Name%] to tell us that when this workflow runs, it will insert that column’s value from the list into variable.
With this new variable in hand, we can now create our email with a dynamic subject, as shown:
This is what the send email subject looks like when we have finished:
Managing Blank Fields
The next problem we run into is how to handle blank fields.
First, let’s illustrate the problem. We use the same technique (build dynamic string action) to create a message body as shown:
We’ll leverage the dynamic string function above but this time, we’ll use it to create the body of the email as shown:
Let’s create a new hire, make sure the comment field is blank, launch the workflow and see the what we get:
SharePoint shows five question marks when we expect it to show a blank value. Clearly, “blank” isn’t really blank when it comes to SharePoint Designer emails. We see those ?’s because the field really isn’t blank, it’s “null.” Null is a computer science term that simply means that the value of that field is undefined. Adding to the confusion, SharePoint normally shows a blank value when a particular field is blank, as we see here:
We don’t see any question mark indicator there. Why do we see it in our history or email body?
This happens because the actual value deep in the bowels of the SharePoint database, is Null, not blank. SharePoint seems to break a computing rule called “orthogonality.” This means that when you do “something” in one context, you can reasonably expect that when you do the same thing in a different context, it will behave the same way. SharePoint is breaking orthogonality rules here because when we show the list item in a view, we don’t see “?????”, we actually see a blank. Yet, when we try to use a the same blank field in an email (or history log as shown above), SharePoint shows the question marks.
Fortunately, it’s easy enough to solve this problem.
Create a new step as shown:
Select “Comment” as the field to compare and “is empty” as shown:
Then:
- Add an action, “Set field in current item"
- Pick “Comment” from the drop-down list.
- Assign the value, “No special instructions.”
This time, when we send the email, it won’t have a null value and render as “?????.” Instead, it will have the value “No special instructions.”
Manage Other Kinds of Blank Values
The blank comment field is straight-forward for us to correct. However, if we try to do the same with a date field, we quickly run into a problem. SharePoint Designer does not offer a condition that checks for “is empty” on a date field, as shown:
To get around this, we take these steps:
1. Create a new workflow variable named “StartDateAsString.”
2. Create a step named “Fix blank date (step 1 of 2)” and assign “StartDateAsString” = the current item’s “New Hire Date.” Note that we can assign a string value equal to column of type “Date and Time” but no vice-versa.
3. Create a step named “Fix blank date (step 2 of 2)” and use the “is empty” trick on the workflow variable “StartDateAsString.”
In the second step, add both the check for “is empty” and an “Else If” block as shown:
In the above screen shot, I’m just logging a message indicating whether the field is blank or not. In practice, you would assign an actual value to the “StartDateAsString” such as “Unspecifiied.”
Conclusion
This article shows that SharePoint Designer enables us to send emails with dynamic subjects and dynamic bodies via the “build dynamic string” action. We need to keep in mind that blank values (including text columns, dates, numbers, etc) are not really “blank” as far as SharePoint is concerned, but rather null. We account for this using some conditional logic in our workflow and being a little clever when it comes to dates and numbers since we cannot directly whether such fields are “empty.”
This is not the final word on email. Oftentimes, we send an email because we need someone to take some action (often by completing a task of fill in an InfoPath form). In the next article in this series, I’ll point out some of the difficulties SharePoint Designer throws our way and offer up some approaches to deal with them.
Paul Galvin, Microsoft MVP – SharePoint
Web site: Paul Galvin’s SharePoint Space
Paul is a Solutions Architect currently working most closely with Microsoft Office SharePoint Server 2007. He was recently awarded Microsoft MVP – SharePoint status for his work with the SharePoint community.
Hi- I am trying to figure out when I need to place in string builder in order to have the site that the URL is pointed to open in a new window. I figured out the I put the http://xxxxx, display, but I can’t seem to find out what I need to add to the builder to have that http://XXXX open in a new window. Any ideas?
thanks,
Linda
Linda,
If I am understanding your question properly you should use standard html tags (the tag specifically) within your email…in conjunction with the _blank parameter. Here’s a cheesie example of an email body one could have:
———————————
Dear [workflow lookup],
A new request has been submitted and needs approval. Click here to view the item and complete the approval action.
Thank You,
name goes here
———————————–
The target paremeter is what will have it open in a new window. I hope this helps.
Thanks Nick for the quick response. I probably asked this question in the wrong blog entry. I am specifically trying to create a workflow that dynamically creates a URL link in a custom list. Through the workflow functions I added a string builder that created the http://Xxxxxlookup variable],View Record (which is what is displayed) — I am trying to figure out how to manipulate that string that would allow me to click on View Record in the custom list and the site that is pulled up is opened in a different window. Does that make sense? There is no <A href in this code. Again…thanks for the prompt reply.
Linda…that is entirely my fault. I thought I was responding to a different question. You didn’t post it in the wrong spot…I just responded to the incorrection question. I believe that your answer lies on this page. I just used this solution to this problem yesterday and it worked like a dream: http://www.sharepointblogs.com/peoplenet/archive/2007/05/10/moss-workflow-isn-t-always-a-walk-in-the-park-encoded-absolute-url.aspx
Wow am I an idiot….Linda I apologize to you and to the owner of this blog. I will put myself on a 3 strikes and you’re out rule if I post another un-related/incorrect response again. After actually taking the time to read what you really wrote I think I understand what you are saying. I tried this when we got SharePoint a few years ago and it worked out well for us.: http://www.mindsharpblogs.com/todd/archive/2005/08/16/654.aspx
Thanks Nick — you’re not out!! Thanks I will try this.
Hi,
I was able to set up a custom message but how do i suppress the The-task-has-been-assigned-to-you-message MOSS sends? Now people get 3 emails (another one when the item has been changed) for every item :/
tick
@Tick :
Regarding your message below, you can go to Tasks -> Settings -> ListSettings -> Advanced Settings
On the advanced settings page, there is an Email Notification field “Send e-mail when ownership is assigned or when an item has been changed.” that you can disable.
Hi,
I was able to set up a custom message but how do i suppress the The-task-has-been-assigned-to-you-message MOSS sends? Now people get 3 emails (another one when the item has been changed) for every item :/
tick
We are getting the names instead of userids by using the LOOKUP USER PROPERTY action found in codeplex Useful SPD activities. Using the property “LinkTitle” for whichever user field you want (created by, modified by, etc). Of course this assumes that the preferred name field is populated in the SSP.
Hi,
I want to assign workflow created from shaerpoint designer to custom document library which is created from c#.net web application. Is it possible? If possible could you plz let me know the article or blogs or any answer.
Thanks in advance
Raghu,
It’s technically possible to do that. If you think about the “fabulous 40″ temnplates — these have SharePoint Designer workflows embedded within them and are then deployed correctly at run-time. So, it’s certainly possible.
I have done a very small bit of research in this area and I can offer this link: http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/543f4d7b-bcb4-4d25-9605-778d0e1c72c1/
I hope that helps.
–Paul Galvin
Hi Paul,
I was looking to create an approval workflow which would dynamically fetch the name of the user from the “Assigned To” column, actually using the “People and Groups” field, from the list.
Thanks in advance for any help.
Smith.
Hi Paul,
The email body sent from SP Designer WF does not have new lines, the original email body with multiple lines became one line. Do you have any idea to fix this? Thanks in advance.
Hi Paul,
In my workflow, I have a Collect form with 2 dropdown lists that are lookup fields. I want to check if these fields are empty (not selected by the user) but somehow they always return not empty even though I left the dropdown list blank. Another thing is unchecked the lists to not allow blank values, but somehow the form doesn’t validate these required fields. Do you have any suggestion?
Thanks Paul! That was an easy win for me :)
Hi,
I was very fascinated to read your article.
I am trying to create a workflow using SPD to update a list item(Master list) when a list item in another form(Slave list) is changed.
I am created a new column in Master to have same value as ID column in Slave.
Then I am comparing these 2 fields and updating the Master list when a field gets changed in Slave List.
But it does not seem to be working.
Any help would be greatly appreciated.
Thanks,
Vaishnavi
Hi guys, can someone help me here. I wantto replace the “?????” with just a blank space.. i dont want to show any data there. how can i do that because if i leave it blank it shows 0.
Thanks
For everyone who was asking about how to get rid of the timestamp that is associated with date fields used in workflow emails. I found this: http://sharepointblogs.com/forums/t/20605.aspx
Hi Paul,
Very nice post!
I have question regarding the workflow i am trying to create.
I want to create a workflow which gets fired when a column value remains the same for more than a week, which will send an email to the person in charge.
Can this be done using only SPD workflows?
Please provide me with any suggestions you might have.
Thanks,
Vaishnavi
The Field I am dealing with is Approver Comments (for a doc lib with Content Approval enabled).
I want to replace the ???? in the workflow lookup for the Approver Comments field with “No comments provided” when that value is empty.
When I use your method, I do not see Approver Comments as an available option in the value list in the “set field in current item” workflow action. Any ideas?
Hi ,
Thanks for the great article.
Is it possible to access the version history while sending the email. What i want specifically is to display the item which has changed with its previous value and current value.
Thanks,
Kevin
Kevin, I don’t think there’s any good way to get at the old values.
You can keep track of old/new values via a trick that is usually done to prevent endless loops, but not through the version history.
There may be some clever way of getting to the version history but I don’t know what that would be.
To do it nicely, you almost certainly need to create a custom sharepoint designer action, which requires some programming.
Paul,
Thanks a lot for the reply.
Can you let me know how you can do this?
“You can keep track of old/new values via a trick that is usually done to prevent endless loops, but not through the version history.”
how assigned task to muliple users?
i select allow multple user or group selection when i created person or group column but when i assign task more then one person it not sending task to users.
only work with one user.
thanks
Is somebody stealing your article?
http://localgovernmentsharepoint.wordpress.com/2010/03/13/email-and-sharepoint-designer-workflows/
Hi Paul,
Thanks for sharing excellent article. I had created dynamic string and its working fine but I have one problem. If I insert Created by in dynamic string it inserts name as “Domain\UserName” where the business requirement would be always First Name Last Name. Can you help me out on this ? I would be really happy to know solution. Any help is appreciated.
Thanks,
Preet
Did you ever find a solution to this? I am in the same boat.
Hi Paul,
Thanks for the great post! I have been able to follow all steps up until the last one (fix blank Comments field). I get the same issue that Nancy posted back in Sept 2009…I don’t get the Comments field as an option when in “set field in current item” when selecting the action. At first I thought it was because I was attempting to make this workflow using an InfoPath form, but I get the same results now that I am doing this from a custom SharePoint list. I am sooo close to getting this working, so any help would be appreciated very much. Thank you!!!