How to get around the calculated fields with People pickers!

If you’ve spent any time using SharePoint and the calculated columns features that are available to you, it won’t have taken long to notice that their are some caveats to their use. The primary cause of pain being that lookup fields cannot be used in calculated columns. In my case the People Picker.

Just to paint the picture. In another list workflow, I want to be able to pull up some history on a person based on their name and the ID of the course they attended. However in SharePoint Designer, it’s not possible to define a workflow lookup that can take 2 parameters to search on. Usually, I just use a calculated field to join two together, and then search on this field. But, with any form of lookup field, the people picker especially, this isn’t possible as you cannot define the column in the calculated formula. So instead of a calculated field, we could use an event handler through code, But my client wants a quick no code solution, so we’ll use another SPD workflow instead.

Ok, so this is really a bit of a fudge, but let’s face it, what are you really doing when you configure a calculated column in SharePoint? The answer, you’re configuring an event handler of sorts to do something during the creation of the item. Instead, we’ll our workflow will take the role of the event handler and concatenate the Username (minus the domain and the \) and the Course code, then place the new value into an extra hidden field. We can then search on that hidden field.

So our workflow (Only runs on item creation) on the course list is as follows:-

First we grab the Student name from the people picker column, remove the leading “Domain\” from the front of the string, and output it to the workflow variable StudentName (I’m showing the workflow lookup to grab the StudentName as we’re returning the string, NOT the display name.)

And for the sake of the demo, we’ll do the same with the course id, although you could do it in the next step too

Now we use the ‘Set Field in current item’ action to store our new key, Using the String builder to create the new value. (Note: When adding the lookup, you have to put a space between the values initially to get the add lookup button working for the second lookup. This is then deleted afterwards.

The workflow is then published to our list and on creation, we’ll get a nice concatenation of the two chosen fields into our new key field. This can now be used by another workflow to recover information with a more specific search pattern.

Hope this helps.

Paul.