SharePoint Designer Workflow to parse a string with common separator

Scenario:

Gathering a list of User Emails from a People Picker Part, Pre-Populating a survey Response with initial values and sending an individual email with custom link for survey response to each user.

Use Case:

                HelpDesk FAQs list with workflow to send a custom email to a user with the contents of the FAQ (Subject: Question, Answer: Body). The email will contain links to the list and to a Survey entry for the user. The Survey entry will consist of: Technician whom sent the email, User that it was sent too and the Question that was sent. The Survey will be used to collect usefulness of the FAQ as it was written.

Problem(s):

1. Not knowing or being able to limit the number of user names entered.
2. Creating a Loop without a predetermined number of executions.
3. Data coercion errors when attempting to transfer people picker values to people picker list/survey columns.
4. 2013 Designer Workflow cannot write to a 2013 survey list.
5. Parsing string list for separator

Initial Setup:

                Create a custom list and survey.

1. FAQ list fields: Title=Question, Answer=Multiple line of text.
2. Survey list fields: Recipient=PeoplePicker, Technician=PeoplePicker, Question=Lookup from FAQ, Was it Helpful=dropdown (Yes, No), Comments=Multiple lines of text.

Solution:

Create a SharePoint Designer 2013 Workflow that parses the input string of Email addresses and calls a SharePoint Designer 2010 workflow passing it the parsed values.

Let’s create the 2010 workflow that will be used to send the custom email to the user and create the survey entry. Workflow should be set for Manual Start

1.       Initiation Variable(s): Tech, Recipient

a.       Type: Person or Group

b.      Show Field: Work Email (This is not available in 2013 workflow Initiation var.)

c.       Allow Selection: People Only

d.      Choose From: All Users

e.      Allow Blank Values: False

f.        Allow Multiple Values: False

2.       Now Let’s setup our other variables we will need:

a.       CurrentItem – Integer: Used to hold the ID number of the current Item.

b.      Recipient – String: Used to hold the email address of the user that it is being sent to.

c.       Create – List Item Id: Contains the ID number of the survey entry that was created.

3.       First Step assign variable Recipient the value of the Recipient Parameter as an Email Address.

4.       Set CurrentItem to the value of Current Item: ID

5.       Create Survey entry:

6.       Create and send Email:

a.       To avoid coercion errors in the email send function you need to set the recipient to the value of the String Variable Recipient

b.      Set the Subject to: Current Item – Question/Title

c.       (I use html emails so the advanced builder is the best choice for me) for the email links that lead to the survey you must use the “create” variable. For email links that lead back to the FAQ that was sent you must use the “CurrentItem” variable (this is necessary to avoid another coercion error.).

Now let’s create the 2013 workflow that will be used to parse the user names from the people picker web part and call the 2010 workflow passing it our collected parameters. Workflow should be set for manual start.

1.       Initiation Variable:

a.       Type: Person or Group

b.      Allow Selection: People Only

c.       Choose From: All Users

d.      Allow Blank Values: False

e.      Allow Multiple Values: True

Now let’s create our Other Needed variables:

2.       Local Variables:

a.       CurrentUser – string:  Used to hold the contents of the People Picker

b.      Index – Integer: Holds the integer value of the first separator position.

c.       Substring – string: Holds a single parsed email address

d.      Substring2 – string: Used to hold the remaining string from CurrentUser minus the value of Substring.

e.      Calc – Number: Adds one to the Index value.

3.       Now that we have are variables we can start building our workflow

a.       The first step will be to set a workflow variable value. We will be setting the value of CurrentUser to the value of our parameter Recipient (People Picker) as a string of semi-colon separated email addresses. For our loop to work correctly we must append the last semi-colon ourselves at this step. (SharePoint does not add the trailing semi-colon in the people picker)

4.       Now insert a While Loop: Loop with Condition

a.       Our condition will be: While CurrentUser Contains “Seperator”

b.      Set The Index: Find “Seperator” in CurrentUser and Output to Index

c.       Extract Substring of String from Index with Length: So From CurrentUser Starting at 0, for Index, Output to Substring.

5.       Start “2010 Workflow” with “Parameters” on Current Item: Here we will use our first Substring value for the Recipient and Workflow Context: Initiator for the Technician.

6.       Do a Calculation: Add 1 to Index and assign to Calc

7.       Extract Substring from Index of String: So from CurrentUser Starting at Calc and assign to Substring2.

8.       Now assign value of Substring2 to CurrentUser and Loop.

I have designed this so that the HelpDesk tech’s will be able to execute in as few clicks as possible by adding a “Custom Action” that calls the 2013 workflow and starts the process off. I have not tested with other strings that are separated by a common character but I assume that with some minor adjustments to the Loop it should work to parse any string with a common separator.