1,804 articles and 14,649 comments as of Tuesday, March 8th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint (Joel Oleson) content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

NothingButSharePoint.com
Friday, October 29, 2010

Get More Columns in SharePoint Lookup Lists

Guest Author: Olaf Didszun
SERVICEPORTALS IT-Systeme GmbH

Last week we posted a Question of the Day from Stump the Panel regarding lookup lists in SharePoint. Olaf has sent over this article to help us get more columns from a lookup list. Thanks, Olaf.

We have two lists in our site. The first is called Projects and contains only information about the project and the project manager.


The second list is called Project Documents and should contain several documents. This library also has a column called Project, which is a lookup column into our projects list. In the library we also want to have the project manager of the selected project from the projects list. Therefore we have created a second column in the document library with the information type ‘Person or Group’.


To get the value of the project manager into the item of the library, we need to create a simple workflow using SharePoint Designer. Start SharePoint Designer and open the site, which is used to store our project information. This is where we create a new workflow.


Our workflow is attached to our library, which is called ‘Project Documents’. The workflow should run whenever a document is added or updated.

To keep it simple we just have one step. In this step we have a condition that checks the current value of the project manager column. If it is the same value as the project manager in the looked up item, we do nothing.


If the values are not equal, we update the item in our document library. We need to set the value of the column Project Manager to a value we get from the list Projects.


This is a simple lookup within our workflow. We take the value of the Project field in our library element, and search in the Projects list. From the element we find, we get the value of the Project Manager column.


These are the necessary actions in our workflow. After checking the syntax and saving the workflow in SharePoint Designer, we can switch to our library and upload a document.

When the upload has finished, we get an edit form to complete the metadata of the uploaded document. This happens, because I’ve set the Project field in the library to mandatory.


When we click the <Check In> button, our workflow fires and does what it should. It reads the project manager from the Projects list and puts the value into the column of our library.


So, our task is done. When a new document is uploaded to the library, we can select a project in the lookup field. The workflow will take the value we have chosen to add the project manager to the item.

But maybe you have notice one problem. In the forms we can see (and edit) the field for the project manager. That’s not very user friendly, because the value would be overwritten by our workflow.

To solve this, we just need 2 steps:

1. Allow management of content types in the advanced settings of the document library. This will show the content type ‘Document’ in the settings of our library.


2. Click the content type ‘Document’ in the list settings, and on the List Content Type Settings page click the column ‘Project Manager’. In the Column Settings section select Hidden and click <OK>. Setting to hidden will hide this field on the new-, the edit- and the display-form. But we can still use and see the field in views.


Let’s upload a new document to our library. We do the same steps as in the first example, but this time we just see the Project column, the Project Manager Column is gone.


This setting does not impact our workflow. It is fired, when we click <Check In> on the form and writes the Project Manager of the selected project to the column.


We have seen that the job can be done with SharePoint out-of-the-box tools; creating the list and library with the fields we need, and doing a simple workflow with SharePoint Designer. As a goody we have configured the project manager column, so it’s not shown on any form.

Guest Author: Olaf Didszun
SERVICEPORTALS IT-Systeme GmbH

Olaf has worked as a SharePoint consultant and solution architect for SERVICEPORTALS.DE in Karlsruhe, Germany since 2005. Previously Olaf worked as a system engineer, implementing Active Directory, Exchange Server and SQL Server. His main business is implementing SharePoint in companies of any size. Olaf is an ITpro, but he also has a very deep knowledge of development using SharePoint. You can read more from Olaf on his blog at http://olafd.wordpress.com (SharePoint and more…)

 

Please Join the Discussion

7 Responses to “Get More Columns in SharePoint Lookup Lists”
  1. Jason Parrish says:

    Should you not also have a workflow on the Projects list to update the Project Manager fields in the Projects Document library when/if the project manager ever changes?

  2. Nice work, Olaf. Thanks for sharing this solution.

  3. Romain says:

    Really nice solution Olaf, thank you for sharing. I may use it on our environment.

    @Jason : You may be right, but my opinion would be to evaluate the added value of this extra workflow depending on the context. If Project Managers tend to often change within projects in your organisation, yes it would be great. In the other case, the first workflow would be enough because it updates itself when the document item is updated : “The workflow should run whenever a document is added or updated”.

    • greg says:

      @romain: to be more accuarate – the workflow will run on the specific document record that is being either uploaded or updated. But really once you upload a doc and add metadata, would you really updated aof its metadata?
      Depending of the number of records you have, maybe you can use an ‘admin’ field, tie the star of your workflow to its change and nartificialy change it in datasheet view for example…
      Greg

  4. Stuart Blank says:

    Nice and easy solution Olaf, thanks!

  5. Ron Kats says:

    Can this be done without Sharepoint Designer? At our company we are not allowed to use Sharepoint Designer for our MOSS2007 Intranet.

  6. Holger says:

    Hello, I tried to do the same based on two very simple lists, but the result is always: ‘Error occured’.
    When I try the same with ‘Set field in current item’, the workflow status is ‘Completed’, but the destination field is always cleared.
    Do you have an idea why this happens?


Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!