Mass Manipulation Of Multiple Values Lookup Field
Here’s the scenario (was actually a question on the SharePoint forums, but we are making it more complicated here): you have a list or document library with one lookup field that allows multiple values. You have a few tens of items and you remembered you should tag all items with one additional value from the lookup list. Now if you can base a calculated column off a lookup field then this is all you need, but life is not that easy with lookup fields as they tend to store their values internally in a special format as we saw in the previous post (Conditional Formatting Based On A lookup Field). We can do that using the datasheet view and a couple temporary columns.
First, we need to get the contents of the lookup field, so we create a single line text field and switch to datasheet view and copy the lookup field values into the text field (that’s copying MyStatus into TempValue in our example). Now, we have the values of the lookup field in a normal field and can do calculations. Add a new field as calculated field based on TempValue. The formula is: =IF(COUNTA(TempValue),TempValue&”;#Add me;#4″,”Add me;#4″)
Notice that this takes care of empty fields (like our first item) and it follows the trend of how the lookup field is stored internally (if it is the only value or not and uses the ID of the choice from the parent list – in this example “Add me” was item ID 4 and you can try to add it manually to one item to get that). Now you do have the correct column but it is not the lookup field you need to fix. All you need to do is to switch to datasheet again and copy the calculated field over the lookup field and it gets formatted normally. Of course, you do not need the two fields you added anymore, so back into list or library settings and delete them.