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

17 comments:

  1. Great idea! Thanks for sharing.

    --Paul Galvin @ http://paulgalvin.spaces.live.com

    ReplyDelete
  2. hi Kevin,

    I have watched this video about Run a workflow when a specific field changes, but I do not know how to get and convert the Full Name to text string value via workflow, can you give me any help or comments? thank you very much!!!

    ReplyDelete
  3. my doman user name like: doman\use(3 letters)
    i guess your doman user name like this: doman\user.name , can you help me with my state?

    ReplyDelete
  4. ma zhe,
    The workflow is simply used to update the hiddenColumn with a working value of the "Person or Group" column. Then you use the string function formulas in calculated columns (First Name, Last Name, Full Name) to parse the respective parts of the domain\user.name value in the hiddenColumn.

    You are correct that my organization uses the firstname.lastname user account format. If your organization only uses 3 letters for a user account then that is all you will be able to extract.

    Hope this helps!

    ReplyDelete
  5. I did a quick summary of how to do this using InfoPath and web services: http://surepoint.wordpress.com/2008/12/29/get-users-full-name-in-infopath-using-sharepoint-profile-web-service/

    I imagine it could be done with a custom coded column as well.

    ReplyDelete
  6. fromonesource,
    Your summary is a nice suggestion. Thanks for adding your comment! I am certain I will be reviewing your blog again if my organization upgrades from WSS 3.0 as it looks like this solution only works with MOSS.

    ReplyDelete
  7. Very creative use of calculated columns. You can also achieve the same result using custom SPD workflow activities from CodePlex. Just blogged about it earlier today:
    http://sharepointapplied.wordpress.com/2009/01/08/get-full-name-in-sharepoint-designer-workflow/


    Good luck with your blog!
    Greg Osimowicz

    ReplyDelete
  8. Great idea.
    I have a column name "Resource" type Person or Group. I created another column as "Copy of Resource" type as single line text.
    I created workflow and copy the user name from "Resource" to "Copy of Resource" column.
    In "Resource" column name are displayed as LastName, FirstName i.e Doe, Joe but after copy the name in "Copy of Resource" column now name are displaying as domain\JDoe. Now question is how can I get name as Doe, Joe ?

    Thanks
    Mas

    ReplyDelete
  9. masood,
    Thanks for your comment! What you see in your Resource column is the display name from your LDAP. I do not know of a way to get that info from LDAP via WSS. My solution simply manipulates the information available to me. My company keeps its user accounts in the format firstname.lastname and I could parse that to get useful information (firstname & lastname). If your company keeps its user accounts in a different format (first initial + lastname) then you will need to modify the string manipulation formula above accordingly. You should be able to retrieve J Doe from domain\JDoe. Unfortunately, that's the best you will be able to do. But fortunately, you can at least do that! Hope this helps.

    ReplyDelete
  10. Hi,
    When I create a workflow for copying the value from People and Groups field into a text field, it copies domain\username for some reason. So basically I do't have anything useful to extract in the formula step..
    Does anyone has an idea how to workaround this?
    Many thanks,
    Erik

    ReplyDelete
  11. Ok, how do you mirror the Person or Group column?

    ReplyDelete
  12. michael,
    apologies for the delay...just follow steps 1 and 3 to mirror a column.

    ReplyDelete
  13. how will you create mirror column for the Created By column?

    ReplyDelete
  14. arun, i haven't tried it but i assume the same step (1) to create a mirror for the the person or group column might work for the created by and modified by columns. if you try it please let us know the outcome.

    ReplyDelete
  15. Hi,

    I have created a column called HCreated(Single Line) and through the SPD WF im assigning the CReated By value to new Colum ,here in new column is filled with domain\UserID

    How can i mirror or how will i get the full UserName

    Thanks

    ReplyDelete
  16. well, arun, you're only going to be able retrieve and manipulate the userid part of the domain\UserID. fortunately, in my case, my organization's policy on userid is firstname.lastname. if yours is not in that format then you will need to manipulate the userid string of data that benefits you most, or this solution may not benefit you at all. unfortunately, you are limited by the data you are given. good luck!

    ReplyDelete