{"id":234,"date":"2011-02-08T10:05:17","date_gmt":"2011-02-08T10:05:17","guid":{"rendered":"http:\/\/www.myfatblog.co.uk\/?p=234"},"modified":"2011-02-08T10:05:17","modified_gmt":"2011-02-08T10:05:17","slug":"how-to-get-around-the-calculated-fields-with-people-pickers","status":"publish","type":"post","link":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/","title":{"rendered":"How to get around the calculated fields with People pickers!"},"content":{"rendered":"

If you\u00e2\u20ac\u2122ve spent any time using SharePoint and the calculated columns features that are available to you, it won\u00e2\u20ac\u2122t 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.<\/p>\n

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\u00e2\u20ac\u2122s 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\u00e2\u20ac\u2122t 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\u00e2\u20ac\u2122ll use another SPD workflow instead.<\/p>\n

Ok, so this is really a bit of a fudge, but let\u00e2\u20ac\u2122s face it, what are you really doing when you configure a calculated column in SharePoint? The answer, you\u00e2\u20ac\u2122re configuring an event handler of sorts to do something during the creation of the item. Instead, we\u00e2\u20ac\u2122ll 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.<\/p>\n

So our workflow (Only runs on item creation) on the course list is as follows:-<\/p>\n

First we grab the Student name from the people picker column, remove the leading \u00e2\u20ac\u0153Domain\\\u00e2\u20ac\u009d from the front of the string, and output it to the workflow variable StudentName (I\u00e2\u20ac\u2122m showing the workflow lookup to grab the StudentName as we\u00e2\u20ac\u2122re returning the string, NOT the display name.)<\/p>\n

\"image\"<\/a><\/p>\n

And for the sake of the demo, we\u00e2\u20ac\u2122ll do the same with the course id, although you could do it in the next step too<\/p>\n

\"image\"<\/a><\/p>\n

Now we use the \u00e2\u20ac\u02dcSet Field in current item\u00e2\u20ac\u2122 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.<\/p>\n

\"image\"<\/a><\/p>\n

The workflow is then published to our list and on creation, we\u00e2\u20ac\u2122ll 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.<\/p>\n

Hope this helps.<\/p>\n

Paul.<\/p>\n","protected":false},"excerpt":{"rendered":"

If you\u00e2\u20ac\u2122ve spent any time using SharePoint and the calculated columns features that are available to you, it won\u00e2\u20ac\u2122t 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 … <\/p>\n

Continue reading<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"yoast_head":"\nHow to get around the calculated fields with People pickers! - Blog of an overweight SharePoint addict<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to get around the calculated fields with People pickers! - Blog of an overweight SharePoint addict\" \/>\n<meta property=\"og:description\" content=\"If you\u00e2\u20ac\u2122ve spent any time using SharePoint and the calculated columns features that are available to you, it won\u00e2\u20ac\u2122t 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 … Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog of an overweight SharePoint addict\" \/>\n<meta property=\"article:published_time\" content=\"2011-02-08T10:05:17+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Cimares\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cimares\" \/>\n<meta name=\"twitter:site\" content=\"@cimares\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Cimares\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/\",\"url\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/\",\"name\":\"How to get around the calculated fields with People pickers! - Blog of an overweight SharePoint addict\",\"isPartOf\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/#primaryimage\"},\"image\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png\",\"datePublished\":\"2011-02-08T10:05:17+00:00\",\"dateModified\":\"2011-02-08T10:05:17+00:00\",\"author\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/#primaryimage\",\"url\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png\",\"contentUrl\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png\"},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#website\",\"url\":\"http:\/\/www.myfatblog.co.uk\/\",\"name\":\"Blog of an overweight SharePoint addict\",\"description\":\"The rantings of a (not so) food obsessed IT consultant!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.myfatblog.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6\",\"name\":\"Cimares\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg\",\"contentUrl\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg\",\"caption\":\"Cimares\"},\"sameAs\":[\"http:\/\/www.myfatblog.co.uk\"],\"url\":\"http:\/\/www.myfatblog.co.uk\/index.php\/author\/reginald\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to get around the calculated fields with People pickers! - Blog of an overweight SharePoint addict","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/","og_locale":"en_US","og_type":"article","og_title":"How to get around the calculated fields with People pickers! - Blog of an overweight SharePoint addict","og_description":"If you\u00e2\u20ac\u2122ve spent any time using SharePoint and the calculated columns features that are available to you, it won\u00e2\u20ac\u2122t 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 … Continue reading","og_url":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/","og_site_name":"Blog of an overweight SharePoint addict","article_published_time":"2011-02-08T10:05:17+00:00","og_image":[{"url":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png"}],"author":"Cimares","twitter_card":"summary_large_image","twitter_creator":"@cimares","twitter_site":"@cimares","twitter_misc":{"Written by":"Cimares","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/","url":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/","name":"How to get around the calculated fields with People pickers! - Blog of an overweight SharePoint addict","isPartOf":{"@id":"http:\/\/www.myfatblog.co.uk\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/#primaryimage"},"image":{"@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/#primaryimage"},"thumbnailUrl":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png","datePublished":"2011-02-08T10:05:17+00:00","dateModified":"2011-02-08T10:05:17+00:00","author":{"@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2011\/02\/how-to-get-around-the-calculated-fields-with-people-pickers\/#primaryimage","url":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png","contentUrl":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/How-to-get-around-the-calculated-fields-_DBFA\/image_thumb.png"},{"@type":"WebSite","@id":"http:\/\/www.myfatblog.co.uk\/#website","url":"http:\/\/www.myfatblog.co.uk\/","name":"Blog of an overweight SharePoint addict","description":"The rantings of a (not so) food obsessed IT consultant!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.myfatblog.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6","name":"Cimares","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/image\/","url":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg","contentUrl":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg","caption":"Cimares"},"sameAs":["http:\/\/www.myfatblog.co.uk"],"url":"http:\/\/www.myfatblog.co.uk\/index.php\/author\/reginald\/"}]}},"_links":{"self":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/234"}],"collection":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/comments?post=234"}],"version-history":[{"count":0,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/234\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/media?parent=234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/categories?post=234"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/tags?post=234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}