Tuesday, October 15, 2013

Projected Lookup Field - Lookup Column Additional Fields Missing

Scenario
  • You want to project a lookup column (add an additional field or related field).
  • Example:
    • You have a Clients list and a Projects list
    • The Projects list includes a Client lookup column
    • You have a Timesheet list which includes a Project lookup column
    • You would like to project the Client lookup column into the Timesheet view via the Project lookup field.
Issue
  • SharePoint 2010 does not support lookup field types as a projected/additional field
    • Some other column types are also not supported
  • Example
    • The Client column will not show up in the "add a column to show each of these additional fields:" checkbox area under the Project lookup column settings, under additional column settings
Resolution
  • One workaround is to synchronize the fields using SharePoint Designer Workflows with an extra Client Name column in the Projects list.  This requires 1 workflow.
  • This will work with Office 365, SharePoint 2010, or 2013.  Additionally this can be done on SharePoint 2007 except projected columns are not available, so you'd have to sync another column within the Timesheet list as well (meaning 1 more workflow)
  • Note that this workaround will not automatically sync down to the Projects list from the Clients list
    • If you need to sync renames of a client down to the project and timesheet levels, you will need to do one of the following:
      • Create a sandboxed event receiver that will propagate the changes from the Clients list to the Projects list Client Name column
      • Create another workflow on the Clients list or at the site level that updates multiple list items by one of the following means:
        • Acquire or develop a custom workflow action/tool that does this looping
          • Possibly CodePlex
          • Possibly Nintex, Bamboo, K2, or HarePoint
        • SharePoint 2013 REST solution
      • Use a scheduled synchronization utility
Workaround (Only synchronizes on Projects item change)
Add a Client Name field (Single line of text) to the Projects list (do not display in view)
Hide the Client Name field by modifying the Projects list content type
clip_image006
clip_image007
Now create a workflow on the Projects list to update the field whenever a Project item is changed.  
In this case the field was required, if it's not required, add in a condition to check for null values.
clip_image008
clip_image009
Use the Set Field in Current Item action
clip_image010
Remove the workflow status column from the views on this list.

2 comments:

  1. I have followed this to a 'T' and I am not having any luck. What could I possibly be doing wrong?

    ReplyDelete
    Replies
    1. Sorry Tyler, I have updated the article to indicate that this only syncs on changes to the Projects list. SharePoint designer workflows do not include an "update multiple list items" workflow action. The options for this are now listed in the article.

      Delete