Thursday, June 27, 2013

SharePoint Designer 2010 Workflow - Lookup User Information in SharePoint Foundation 2010

Problem: In Foundation 2010, SharePoint Designer only allows you to perform a lookup on a user's Name, Display Name, Email Address, and ID...what about custom fields that were added to the User Information List? What about Department, Phone number, or any other field that works fine with a People Picker? Can I do ANYTHING without going into dreaded code or installing a third-party app or upgrading to Enterprise's User Profile Service? The answer is yes, in VERY limited circumstances (so don't get your hopes too high yet)

Resolution:
Caveats:
  • This will ONLY work on the root site of a site collection (believe me, tried this in many ways but it seems you cannot change the WEB property of a workflow context on the fly)
  • This requires SharePoint Designer, should be obvious but wanted to mention just in case
  • The getting of your User Information List GUID (first step) can be accomplished in several ways but almost all of them will require site collection administration privileges
  • What you are essentially going to do is create a dummy User Information List with the columns you want in it, get your workflow working beautifully with this dummy list based on someone's username being entered in a text field, and then do a bait-n-switch and tell SPD that the list you are using in your lookup isn't your dummy list but actually the User Information List
  • You may be able to play with it to be able to pass the User's ID to the User Info List instead of the text of the username, but it was easier for me to send it just the username from a text field
Setup:
  • Custom List that I titled "TestUserInfoList" (this is my Dummy List) with 3 columns
    • Title (didn't change it at all)
    • Username (Single Line Of Text)
    • Manager (People Picker) <-- This is my custom field to populate
  • Custom List with these columns:
    • Employee (Person or Group)
    • Manager (Person or Group)
    • Username (Single Line of Text)
  • Workflow with 2 variables:
    • varEmployee (string)
    • varManager (string)
  • Steps to workflow
    • Set varEmployee to CurrentItem:Username
    • Set varManager to (workflow lookup):
      • Data Source: TestUserInfoList
      • Field from source: Manager
      • Return Field As: As String
      • Field: TestUserInfoList:Username
      • (Fx button) -> Workflow Variables -> Variable: varEmployee
    • Set Employee to varEmployee
    • Set Manager to varManager
Steps:
  1. Get your User Information List GUID (you *might* be able to get away without this but I haven't tested it yet) 
    1. Go to your site collection homepage, and add '/_catalogs/users/simple.aspx' to the URL, and hit Enter (so your full URL would look something like http://yoursite/_catalogs/users/simple.aspx')
    2. Click on the "List View" at the top (which is a dropdown) and click "Modify this view"
    3. Take a look at the URL, you will see ViewEdit.aspx?List=BLAH&View=BLAH&Source=BLAH. What you want is to grab the 'List=BLAH', nothing before or after it
    4. Take the BLAH part and replace all the '%2D's in it with dashes (-), the '%7B' with a left curly brace ({) and the '%7D' with a right curly brace (})
    5. So you should now have the GUID of your User Information List which should look like {00000000-0000-0000-0000-000000000000} except yours will not be all zeroes
  2. Create dummy list in the root site (where you HAVE to be working), ignore the title field, create a Username field that is a single line of text, and a field with the exact same name and type as the field you wish to return (most likely a single line of text field). For my purposes, I had added a "Manager" field to my User Information List that was a people picker so that's what I recreated in my dummy list
  3. Add a couple of entries to your dummy list
  4. Create your workflow on your custom list (NOT your dummy list) and, in your first step, use the workflow to set a workflow variable (I created one called varManager) and then, in a separate step, set the field(s) in your custom list to the appropriate workflow variable and PUBLISH your workflow
  5. VERIFY THAT IT WORKS RIGHT...if you screw this part up then you'll spend hours troubleshooting later. Just make sure it works, m'kay?
  6. Again, as long as your workflow works beautifully, you are ready to trick SharePoint into using the hidden User Information List: in SPD, click "All Files" at the bottom-left, then click the "Workflows" folder in the main window, then click your workflow, and you should see at least two files, maybe 3, but the one you are looking for has the workflow symbol (a circle with a red checkbox in the middle)...RIGHT-click on this file and then click "Open With..." and choose the SahrePoint Designer -> Open as XML option
  7. You will see a bunch of gobbledeegook. It's ok, don't panic. What you are looking for is this and it will be somewhere around the 20th line of XML:
    <SequenceActivity x:Name="ID##" Description="Your Step Description where you set the workflow variables">
  8. Inside this sequence activity (which is just your step) you will look for your first ns1:FindValueActivity and it has an attribute that looks like ExternalFieldName="Username" and, further to the right, an ExternalListId="{}{0000000-0000-0000-0000-000000000000}"
  9. Replace the GUID there (leaving that first set of curly braces) with the GUID you got in step 1
  10. Go down around 5 lines and you'll see an ns1:LookupActivity where the ListId attribute looks just like the ExternalListId you just fixed...you guessed it, do the same thing to this list ID.
  11. Repeat steps 8 thru 10 for each variable that you looked up in your workflow within this SequenceActivity. You will be essentially replacing all ExternalListId's and their corresponding lookupActivity listID's to the User Information List
  12. Save this file and close SPD.
  13. Re-Open SPD, open your site, and open this workflow
  14. Add a single action to the workflow like "Log to history list" or something, ANYTHING, and you can publish this updated workflow to your site
  15. You should be good now. To verify, delete your entries in your dummy list and create some new items in your custom list and all should work beautifully - you are now using the User Information List to set variables and use them to do your bidding...MWAHAHAHAHAHA