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:
- Create a custom list
- Add some rows to your list. In my case I added values like "Quick Pick 1", "Quick Pick 2"…
- Add a calculated column with the formula of [Title]. Leave the calculated type as single line of text
- 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.
- When you edit the properties for a document via the browser, the Lookup field values display as expected:
- Now, edit a Word 2007 document in the library
- 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.
5 comments:
@grogal: Ahhh Blog Spammers. Is there any lower life form?
Hi Ivan.
When we used a custom lookup field in Word we experienced the same problem. What you will also see is if you try and use the drop-down list in Datagrid view, the same thing happens. Unfortunately the client was not interested in looking into a solution, so we never did any further investigation on this. Interested if anyone comes up with an elegant solution for this problem.
I ran into the same issue some time ago. What i did to work around the issue was to configure a data connection in Infopath that would consume an xml view of the sharepoint list (see http://www.sharepointblogs.com/dwise/archive/2008/01/10/accessing-sharepoint-list-data-as-xml.aspx ). The value of the calculated column returned in infopath will still be preceded by ="string;# . In order to work around it choose the calculated column as the value and name of the list item as display name. So when choosing the element to display in Infopath choose the whole list (rather than just calculated column, then for values choose the caluclated column and for display name choose the title. In my Infopath form this would look like this 1) Value = @ows_InfopathValues, Display name= @ows_LinkTitle.
The above is just my pasted notes but should allow you to achieve what you're after. The solution is rather clunky but it's the only way I managed to get it to work.
Best regards,
Greg Osimowicz
I would create a workflow to fill a regular textfield instead of a calculated field.
A bit of overhead, but I don't think the workaround for a bug needs to be nice..;
Hi Ivan,
thanks for this post.
I was just configuring up a task list using a lookup to calculated field and got the render failed error.
At least I know that I'm not the only one out there with this problem.
Cheers,
Gavin
This is an unfortunate bug, there is a hot fix available from microsoft. http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=948952&kbln=en-us#step2
However, I was not able to install this on every client machine.
The relucant but customisation free way to get around this problem is to have your lookup field contact all possible text and remove it via calcultion on the other side.
For example
Your Library is looking up a calculated field called Full Name in a Contacts list. the calculated field is a product of First Name and Last Name .
The answer would be to have one column called Full Name that is a text entry that the user puts in.
When you look up this feild in your Library the field will come through fine. You can then use a calculation to seperate Last Name & Full Name in the Library rather than in the Contacts List.
This may be difficult or not applicable to some applications of the Calculated field but as a general rule it will remove the need for yet more customisation that has to be applied to many sites to keep consistancy.
Post a Comment