Monday, April 9, 2007

Multi-Value Columns in SharePoint Designer - Solution #1

Recently I’ve been working with Microsoft Office SharePoint Server (MOSS) 2007. Since this is my first post on the topic I’d love to start at a high level about what it is and how it works, but let’s get to the interesting stuff: what doesn’t work well and how to get around it.

Specifically, this will be the first in a series regarding a deficiency in the workflow component of SharePoint that doesn’t allow you to send e-mail to multiple recipients from a multi-value column of a list using SharePoint Designer 2007 (SPD).

This first post will describe the problem, provide lots of screenshots so it can double as a fast introduction to workflows in SharePoint for the uninitiated, and then provide a quick hacky solution.

The Multi-Value Column Problem

Here’s how it should work. You create a list (e.g. Performance Review) and add a column (e.g. “Peers To Review”) and select a type of “Person or Group” with “Allow multiple selections” set to Yes.

(click any image to enlarge)

Now if you add a blank workflow in SharePoint Designer (from the SharePoint Content tab):

And you select the list you created (Performance Review):

Then you should be able to

  1. Add a “Send an Email” action
  2. View the message’s properties
  3. Select the recipient
  4. Add a function by clicking “Workflow Lookup”
  5. Select the Current Item (aka the current list item, which is like a row in a spreadsheet and in my example this would be the current performance review that the workflow is being run on)
  6. Then select the new column “Peers to Review”

But wait. Where’s your column? And here is the problem. The UI of SharePoint Designer filters out all columns that are marked with “Allow multiple selections.”

Simple Hacky Solution #1

Believe it or not the Workflow Engine knows how to send e-mail to a column with “Allow multiple selections” but it’s the SharePoint Designer UI that doesn’t. This means that a simple solution to the problem is this:

  1. Turn off “Allow multiple selections” for your column (note the warning “This will remove all person values except the first one”)
  2. Close and reopen the workflow in SharePoint Designer and follow steps above and magically your column will appear in the dropdown

  3. Finish creating your workflow
  4. Turn back on “Allow multiple selections”
  5. Create an item for the list and run the workflow you created in SharePoint Designer

And voila, it works! Both (or all) recipients have received an e-mail.

Sadly, this simple technique isn’t an acceptable solution for the long term. What happens if you want to change your workflow down the road? You may need to turn “Allow multiple selections” back off, thus deleting your multi-user data. A better solution is to create a custom action in Visual Studio and get it to plug into SharePoint Designer. And that will be the topic of my next post:

Multi Value Columns Solution #2 - Custom Activities in SPD

---

Note 1: this series is largely in response to the several users having similar problems at: http://forums.microsoft.com/MSDN/showpost.aspx?postid=1443799&siteid=1

Note 2: If you’re interested in the ERD for the topics in this SharePoint post this diagram might be useful to get you up to speed:

16 comments:

Anonymous said...

Hi,

A very simple solution to the problem is to create a string variable (eg var_multiuser) in the workflow. Then you just do the following:
1. Set var_multiuser to "Peers to Review"
2. Send e-mail To: Variable:var_multiuser

/paanvst

Lee Richardson said...

Wow, that's much easier. Thanks!

Anonymous said...

Any idea on how can I export to a spreadsheet all of the values in a multivalue column? Currently, it only exports the most current one.

Anonymous said...

Hi,

I have little problem with MOSS... how can I get autonumber column in MOSS or how can I create something similar using e.g. workflows

Lee Richardson said...

"Any idea on how can I export to a spreadsheet all of the values in a multivalue column"

Each value of the multi-value field should be be separated by semicolons. You should be able to just do a String.Split()

"how can I get autonumber column in MOSS"

All lists have an autonumber column by default. If you want to get access to it, I believe you just use the "id" field.

Anonymous said...

Thank you so much! Solution 1 worked perfect

Ross McNab said...

This also works for the "Assign Form to a Group" SP Designer activity. It will create multiple tasks, one for each user.

Anonymous said...

Great article thanks! I used the solution mentioned in the comments, but as a way to route a task to multiple people (simple approval). Unfortunately while it works, it seems that Sharepoint sees this as somehow different and does not auto-magically display the "Complete Task" button when you hit Edit, as it does with other workflow tasks. Any idea why or how to fix this? I can't mark the task complete due to this.

Anonymous said...

Can you expand on your solution for export data, which has multiple values, to spreadsheet?

How does one implement the String.Split()optioN?

Anonymous said...

"Any idea on how can I export to a spreadsheet all of the values in a multivalue column? Currently, it only exports the most current one."

Apparently you can't because Sharepoint is a POS!

Anonymous said...

I found that the suggested simple method within the first post does not actually work as the mail addresses cannot be resolved (User ID numbers precede email addresses)(unless perhaps it's due to me using Outlook/Exchange 2003 rather than 2007?).

Anonymous said...
Hi,

A very simple solution to the problem is to create a string variable (eg var_multiuser) in the workflow. Then you just do the following:
1. Set var_multiuser to "Peers to Review"
2. Send e-mail To: Variable:var_multiuser

/paanvst

Pallavi said...

Hi,
I have a problem of filtering based on the custom metadata lookup field.. The lookup field is saved as ; separated
List A has the meta data of lookup column field with multiple values.
I need to display the items in a dataview where the filter is only a i.e. Fetch only articles with metadata of a i.e. A4 should not be returned.

A1 > a;b;c;d;ab;bc;cd
A2 > a
A3 > a;b;ab
A4 > ab;bci will greatly appreciate if someone has a solution for this

dsouza said...

is it possible to export all in .csv

Zeki said...

I have another simple solution for this.
First I define two columns, the first as person/group allowing multiple selection and show the email address, and the second as a simple text column.
In the workflow the first action is to set the simple text column with the value of the firs column, containing all the email address selected with the comma separator.
Next action is sending email with a workflow lookup on the simple text coulumn.
:)

Anonymous said...

The article mentions a third article in this series. Can't find it. Was it done? Looks like it would be something I am looking for. (Or am I looking in the wrong place.) Thanks, Paul S., Chantilly, VA

Lee Richardson said...

Sorry, no 3rd article in the series :(.