string;# Before Values in a SharePoint List’s Lookup Column Based on a Calculated Value

I’ve seen lots of posts discussing this, but no workarounds.  Here’s the situation:

  • One list (List 1) contains a list of values that are calculated (say ColumnC = ColumnA & ColumnB)
  • Another list (List 2) uses ColumnC above as a lookup for one of its columns
  • If you go into Datasheet view for List 2, you will get the following error every time you try to change an item (with your column name mentioned instead of mine):

image

  • If you click on the dropdown for the column in List 2 that contains the lookup, every value will be preceded by ‘string;#’.
  • If you select the entry that looks exactly like the current value (but with the ‘string;#’ preceding it), you can then save the item.

This seems to be an annoying “feature” of how a calculated lookup column is represented in the Datasheet view.  We’re running SP1 with all current patches at this point, so I don’t hold out hope of a fix anytime soon.

The best workaround that I’ve come up with is to not calculate the lookup column, but to have a simple workflow which does the calculation and updates the column whenever the item changes.  This should work just fine, but since my calculated column is already embedded as a source for many other lists (yes, I inherited a solution, yet again), it’s problematic to make the change.

Anyone seen a better workaround?  This really gets in the way if you need to do some wholesale changes on a long list that contains this problematic type of column.  I’ll certainly post anything I find that gets around this better.

 

Similar Posts

6 Comments

  1. That hotfix does fix the problem; apparently it is because of the 2007 Office install on the client viewing that data view with lookup columns for calculated columns that has the problem…I’m not sure why that would be; and would much prefer it be fixed on the server side; but as of right now they still have not included a fully supported patch; ie u have to request a password protected hotfix and apply it to each computer that you want to be able to use the data view correctly; kinda lame, but i did it and it does work.

  2. I’m having the same problem with Office 2010, and I can’t run this hotfix because “There are no products affected by this package installed on this system.”

    1. Jocelyn:

      The version of Office shouldn’t matter. This was a “feature” of SharePoint 2007 at the time of my post. I don’t believe that it was ever fixed, so the workaround should still be a valid solution.

      M.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.