My 2p about ERP Solutions, Information Worker Solutions and other software products (mainly Microsoft Dynamics AX and Microsoft SharePoint).

20 August 2008

Bulk update of a field for existing SharePoint list items

by Patrik Luca 13 comments

Tag



Business requirement: Bulk update of a field for existing SharePoint list items.


Suppose you have created a SharePoint list or document library with some metadata columns. End-users start adding list items to this SharePoint list. After a while, they want to change the value for a particular column of already created list items. They could do this manually through the Edit in Datasheet mode, but suppose they have already created hundreds of list items in the SharePoint list... It would save a lot of time if an option would exist to bulk update a SharePoint field from one value to another in all list items for a particular SharePoint list.

An ideal scenario is off course a SharePoint column created with as type of information Lookup. Changing the value of the item where the Lookup column is getting its information from would update in mass all already existing SharePoint list items. But if this isn't the case, there is no real out-of-the-box SharePoint functionality to bulk update a field (except the Edit in Datasheet mode).

Solution: Use the SharePoint SUSHI application to bulk update SharePoint list fields.



Joseph Fluckiger created a solution called SharePoint SUSHI. SUSHI (SharePoint Utility with a Smart, Helpful Interface) is a user-friendly SharePoint application enabling you to accomplish SharePoint administrative and development tasks.

The following step-by-step description is a guide how you can do a mass update on a SharePoint list field with the SharePoint SUSHI application.
  1. Create an Issue Tracking list.
  2. Create several list items in the Issue Tracking list and make sure there are different values present for the field Issue Status, as this is the field we are going to use to do the bulk update.
    Your Issue Tracking list should look like this:

  3. Suppose the end-users now want to change value for the Issue Status column. They want the Issue Status Active to be called Started from now on. And off course, they want to change the column value for all existing issues as well to have a consistent look and feel over all issues.
  4. Edit the column Issue Status: change the choice value Active to Started. As such, upon creation of new Issue Tracking list items, end-users will have to choose the value Started in stead of Active for the Issue Status column. However, for existing list items, the value Active isn't changed towards Started. The next steps describe how this can be achieved.

  5. Download the SharePoint SUSHI application from CodePlex and install the executable on your SharePoint server. Start up the application.
  6. Select Meta Data in the left menu and enter the url of your SharePoint site containing the Issue Tracking list to the right.
  7. Click Retrieve Lists and choose the Issue Tracking list in the Target Document Library drop down box.
  8. Choose the Issue Status column in the Metadata Column Name drop down box.
  9. Choose Active in the Current Value drop down box.
  10. Type Started in the text box New Value.

  11. Click Update values in column to new value.

  12. Go back to the Issue Tracking list on your SharePoint site: all values are updated.

Comments 13 comments
Joseph Fluckiger said...

Thanks for the great tutorial of how to use the update metadata feature of SUSHI. I have added a link on the SUSHI wiki to your page.
http://www.codeplex.com/sushi/Wiki/View.aspx?title=Meta%20Data.

Thanks again!

-Joseph Fluckiger

Joseph Fluckiger said...

Question for you Patrik, would you like to participate in the SUSHI project? I could use some good additional documentation and feature suggestions for the project, I'd love to have your help (and give you credit for your help). Use the contact user form on codeplex if you are interested.

-Joseph

Patrik Luca said...

A problem arises when you are trying to update a column to a blank value.
The problem is not the update itself, but the logging which is written to the console output window while it is updating. It cannot handle null values (to which you are updating in fact, as you are setting a columns value to blank).

This can be fixed by editing the source code of the SushiNS.ActionMetadata.updateSingleColumn method, to be able to write this correctly to the output console.


// Patrik BEGIN
if (txtNewValue.Text.Trim() == "")
{
if (listitem[fnwc.Field.Id] == null)
{
AddToRtbLocal(" updated to ", StyleType.bodyBlack);
SmartStepUtil.AddToRTB(rtbDisplay, "\"" + txtNewValue.Text + "\"\r\n", Color.DarkBlue, 8, false);
counterUpdated++;
}
else
SmartStepUtil.AddToRTB(rtbDisplay, " NOT successfully updated\r\n", Color.Red, 8, false);
}
// Patrik END
else
{
if (listitem[fnwc.Field.Id].ToString() == txtNewValue.Text)
{
AddToRtbLocal(" updated to ", StyleType.bodyBlack);
SmartStepUtil.AddToRTB(rtbDisplay, "\"" + txtNewValue.Text + "\"\r\n", Color.DarkBlue, 8, false);
counterUpdated++;
}
else
SmartStepUtil.AddToRTB(rtbDisplay, " NOT successfully updated\r\n", Color.Red, 8, false);
}


Adapt the source code and do a rebuild.

I've prepared a rebuild where this is already fixed which can be downloaded here. It is based on SUSHI build 22363.

Support said...

Pretty well designed and documented. Works like a charm.

Thanks,
Patrik

Trey said...

Patrik-

I seem to be getting the object reference error for all my meta data updates regardless of list or value. It hasn't always been this way. Is there anything you can think of that would cause SUSHI to bail like this all of a sudden? I have tried 3.4 and all of my older copies... they are all just a single binary right? no cache to flush or anything to make sure its pulling from SQL properly?

let me know what you think.

trey.white -AT- fire.ca.gov

Patrik Luca said...

Did you try my fixed version which can be downloaded here?

There is a bug in the 3.4 version with the writing to the console. I fixed it in the previously mentioned fixed version.

Anonymous said...

Unfortunately, it bombs on me with my favorite null exception... agh!

Oh well. It's very typical from Catapult systems. They are not the best SharePoint consultants out there..

LOVE_MOSS_NOT

RD Covington said...

Two questions. Is it possible to use this on the desktop side rather than on the server, and is it possible to use this to update all the records in a list so that the calculated columns go ahead and recalc?

Patrik Luca said...

This tool can't be used desktop side, only server side.

If you update all records in the list, your calculated columns will be recalculated accordingly I guess, don't they?

Issac said...

Hi Patrik,
Any other method for update using a desktop?

Patrik Luca said...

Hi Issac,
I don't know about a desktop solution for this functionality.

dan rogy said...

My current project requires ability to display choice column value via color or image associated with a choice

But Sharepoint standard packaged misses that control

I am looking for available solutions on market

I came across

http://sharepointfields.com

Does anybody has experiece using it?

Michael Clarke said...

You saved my bacon! The fixed version worked perfectly for me. Thank you so much for providing this tool!

Mike

Patrik Luca, Ieper, BELGIUM
Feel free to use or spread all of the content on my blog. In return, linking back to my blog would be greatly appreciated. All my posts and articles are provided "AS IS" with no warranties.

Subscribe feeds via e-mail
Subscribe in your preferred RSS reader

Subscribe feeds rss Most Read Entries

Subscribe feeds rss Recent Entries

Categories

Recommended Books


Subscribe feeds rss Recent Comments

This Blog is part of the U Comment I Follow movement in blogosphere. Means the comment field of this blog is made DOFOLLOW. Spam wont be tolerated.

Live Traffic Feed

Recent Visitors

Blog Archive

My Blog List

Followers

Guest Links