SharePoint 2010 Help

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.

In a couple previous posts, I mentioned about the lack of looping capability in SharePoint Designer 2010 actions (activities) and the Datasheet option for lists and libraries. If you haven’t installed any of the SharePoint Designer extensions and you need to run a workflow on all items in a list, here’s a work around.
Create a dummy column in your list or library (the default single line of text would do). Then, go to SharePoint Designer and create your workflow for that list or library. In the workflow settings, select to start the workflow when an item is changed (uncheck others, if any). Save and publish your workflow.
Now, back into SharePoint, put your list or library in datasheet view (instead of the standard view). Highlight your dummy variable and as you do in Excel, Auto Fill the column (or Fill Down, it doesn’t really matter). After a while (depending on your list size), all cells under the dummy column will change and this will fire up your workflow for each row (item). You can now switch back to standard view and watch the newly added column for the workflow. When all is completed, you can go back to list or library settings and just delete that dummy column.
In the next post, I will outline a workflow that will rename all files in a library replacing a special character in the file name. Stay tuned.

The datasheet view is a convenient and compact way to view and edit lists and libraries (properties). You can find it next to the default (Standard) view under the List or Library tab and any row you edit will sync back to Sharepoint once you move to a different row. Under the hood (and also obvious on the top left of the sheet), Access data engine powers this feature. You can even use traditional tricks like the drag and fill you used to do in Excel (assuming the rows are similar in content type). Notice that some fields are read only and those can’t be edited, in addition to some other types like managed metadata which are incompatible with the sheet view.
However, if you already installed the 64 bit of MS Office 2010, the latest and greatest, you’ll be surprised that Datasheet view generates an error.
“The list cannot be displayed in Datasheet view for one or more of the following reasons:

• A datasheet component compatible with Microsoft SharePoint Foundation is not installed.
• Your Web browser does not support ActiveX controls.
• A component is not properly configured for 32-bit or 64-bit support.”

The options are to either install the 32 Bit version or download the 2007 driver: http://www.microsoft.com/downloads/en/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en This fix should work in general (it may kick native Access files back to 2007 version).
There is also a MS KB article on this issue at: http://support.microsoft.com/kb/2266203
It is also worth mentioning that 64 bit browsers will not work and you should install all office 2010 64 bit components before you apply the fix (this includes SharePoint Designer 2010 as well!).
In fact, the driver may be enough to get the datasheet even if office is not installed on the client.