Wednesday, April 16, 2008

Problem with using a Calculated Field in a Lookup property

I've been playing around with using a calculated field as the source for a lookup column in a document library. I've run into a few annoying issues that I thought I'd share. Try this:

  1. Create a custom list
  2. Add some rows to your list. In my case I added values like "Quick Pick 1", "Quick Pick 2"…
  3. Add a calculated column with the formula of [Title]. Leave the calculated type as single line of text
  4. In your Shared Documents library, add a lookup column and point it to the calculated field you created in the previous step

I agree – not the most productive piece of SharePoint work you will do, but bear with me.

  1. When you edit the properties for a document via the browser, the Lookup field values display as expected:

  2. Now, edit a Word 2007 document in the library
  3. View the Word 2007 Document Information Panel

If your environment is anything like mine, this is what you will get:

I don't know why the "string;#" text is visible in the Document Information Panel view. I know that the Lookup column does store an ID along with the displayed value, but I definitely do not want this displayed in Word 2007.

It's a shame that this doesn't work. My search through the newsgroups and blogs didn't bring back any details about this specific issue, but there were some related questions about not being able to reference certain column types as lookup fields. I'd be interested to hear if anyone has found a fix for this. I can work around this issue by developing a simple event handler on my list to set the value of a text box to what I need. That way I can just use an out-of-the-box lookup column on a text field.

There was also another issue that I saw mentioned in relation to calculated lookup fields. If you try to use one of these in a collapsed Group By view, you will see a "Render Failed" message for each column when you expand the group:

My event-handler work-around will also avoid this issue. However, another alternative is to select the "Show groupings expanded" option for the view. You can then expand/collapse the views as you need.