Tuesday, December 23, 2008

SPD Workflow: Display Full Name Instead of Domain\username

I recently setup a SharePoint list and used the Person or Group list column type. I wanted to use proper names in emails sent by a workflow associated with this particular list. The only value I could get out of the field was domain\username. Unfortunately, using WSS 3.0, I have not found an OOTB way to grab the proper name of the individual users stored in this column type. So I created my own way to parse this data utilizing some other tips and tricks I’ve learned along the way. The basic premise is that we will be parsing the domain\user.name text string to get First Name and Last Name.

  1. Create a text column that will mirror the Person or Group column: hiddenColumn. We need this because we will be parsing the text string in this field. SharePoint will not allow you to reference a Person or Group column in a calculated field (see Step 2). I noticed that when I created my hidden columns if I unchecked the “Add to all content types” option then this column would not appear in the New Item or Edit Item forms.

  2. Create 3 calculated columns (see formulas below): First Name, Last Name, Full Name. I suppose you don’t have to create the Full Name calculated column since you can concatenate the other 2 columns whenever you need a full name but I chose to do so anyway. The formulas for the calculated columns: I learned that a SharePoint calculated column can contain almost every formula and/or function that exists in MS Excel. With that knowledge I was armed and dangerous and created the formulas below.
    Note: ## = the number of letters in your domain name including the "\"
    $$ = add 1 to value of ##, used as a starting poistion for string manipulation

    First Name =PROPER(LEFT(MID([hiddenColumn],$$,LEN([hiddenColumn])-##),FIND(".",MID([hiddenColumn],$$,LEN([hiddenColumn])-##),1)-1))

    Last Name =PROPER(RIGHT(MID([hiddenColumn],$$,LEN([hiddenColumn])-##),LEN([hiddenColumn])-FIND(".",[hiddenColumn],1)))

    Full Name =PROPER(CONCATENATE(LEFT(MID([hiddenColumn],$$,LEN([hiddenColumn])-##),FIND(".",MID([hiddenColumn],$$,LEN([hiddenColumn])-##),1)-1)," ",RIGHT(MID([hiddenColumn],$$,LEN([hiddenColumn])-##),LEN([hiddenColumn])-FIND(".",[hiddenColumn],1))))

  3. Create a workflow to store a “working” value in the hidden column that mirrors the Person or Group column. This is needed so we can manipulate or parse the text string value that is stored in the Person or Group column within the calculated columns. SharePoint will not allow you to perform calculations on a Person or Group co;umn type. This is a great video that explains exactly how to do this: Run a workflow when a specific field changes

This is a screenshot of my workflow