In working with client SharePoint intranet sites, I occasionally have to add a new field to an existing list and populate it with data. Sometimes this new item needs to be filled with values from a soon to be deprecated field and sometimes with data from another list. Recently I had to update a list, changing one field from a to a new, custom type with a paired textbox and dropdown. The client originally had a text field in which units were entered and now want a user to enter a value in a text box and also assign a unit of measure to the value (ml, oz, table, g, mg etc). In a future post I will describe creating the custom field with a textbox/list pair. This post is concerned with the console application used to populate the field once it has been added to the list.

The site in question is pretty cool. It is a place where pharmacists go to find out what drugs need to be made that day. There are recipes for each drug and it is this list we will be updating. Below is a screen shot of one drug record before the batch update.

 

 
At this point I have already created a custom field and added it to the list. These fields are the Ingredient Qy/Unit items under the original Ingredient Qty fields. Once I have moved the data from one field to another, Ingredient Qty will be deleted.

A console application will be used to update the new fields. In this app, we could do something like grab the list, iterate through each row, updating items individual with each pass. But we want to be as efficient as possible and will use SPWeb. ProcessBatchData to create one update statement for all records. The MSDN site describes SPWeb.ProcessBatchData this way: Processes the specified batch string of commands for sending multiple requests to the server per transaction. This is exactly what is required for this part of the project.

Start Visual Studio and created a New Project --> Windows --> Console Application. On the form, we add a button that will be used to fire off the update process. We set variable for the site and list names. The purpose of all this it to create one big XML statement to be processed in a single transaction. The batchFormat string is the start of the XML. Each row’s individual command statement will be appended to this string using a StringBuilder. 

 

Next we create the batch method. This is the XML for each individual update statement. When we iterate through the list, we will be creating a statement for each row and appending it to the last. We set the Method ID as the Item ID, the Set list uses the list GUID, then the ID of the item in the update. We then set the list the fields to update.

For this app, we are updating five fields in the list: Ingredient_x0020_1_x0020_Qty_x00, Ingredient_x0020_2_x0020_Qty_x00,Ingredient_x0020_3_x0020_Qty_x00, Ingredient_x0020_1_x0020_Qty_x000 and Ingredient_x0020_5_x0020_Qty_x00
 

With the update statement finished we just need to populate the values and call ProcessBatchData. We set the site and open the web and then query the list to get all items using getListItems (web, listName, out listGuid, out itemsToProcess):

 

Now we can iterate through all the items in the list. We have to take the current value for each of the five Ingredient Qty fields and update the corresponding Ingredient Qty/Unit with that value. I called these new five fields ingredientUnitMeasureOne, ingredientUnitMeasureTwo etc. With each pass, clear the values and then call a method to set them again with the corresponding Ingredient Qty value. The SetUnitMeasuerValue method checks each field for a null value. Not all recipes use five ingredient fields and many are null. Also, because our new filed is going to contain two values – a value from the old field and then the new unit in the drop down – we have to format the item to have an empty value using the SharePoint delimiter, otherwise we will get an error when loading a list items telling us the index is out of range for the field.  In an update where there was a one to one relationship, the line would read ingredientUnitMeasuer = originalUnit instead of ingredientUnitMeasuer = ";#" + originalUnit + ";#;#" .
 

 

Here is everything from setting the site, getting the list items, iterating through each row and building the command method:

 

 

 

We are almost done. After looping through each row and appending an string for each update, we just connect the batch format with our methods and call ProcessBatchData.This is part of the XML that constitutes the transaction - the bath method xml from the start of the app with one row's method statement appended:

And here is the list item after the update with our new fields populated: