Use PowerShell to manipulate the values of a SharePoint choice field.

Thanks to Knoots for suggesting the idea for this post, from a comment on Using PowerShell to play with SharePoint Items.

Using PowerShell, we’re going to walk through handling a Choice field in a list. Specifically, this is a calendar list using the Category field. This may come in handy if you want to automate changing the values from another data source that BCS can’t connect to, or is too much work to get it to connect. I always prefer using SharePoint’s features, but sometimes we need to stretch it to make it work.

As a heads up, if you remove a value that someone has used in their item, the value will remain in the item UNTIL they come back in to edit the item. Since the value no longer exists in the option list, their value will be lost. However, if you have the option enabled on the field to specify their own value, then the previous value will be saved there.

Let’s get to it, open SharePoint 2010 Management Shell

$web = get-spweb http://site/web
$list = $web.lists["Calendar"]
$list

I like to run the $list just to ensure we have the list properly. Errors aren’t always displayed in PowerShell. Running $list should return the list name.

$choice = $list.fields["Category"]
$choice.choices

Again, make sure we have the right field, calling $choice.choices will list all of the current values. The Choices property is a StringCollection, so use typical commands to add/remove items.

Important. If you’re going to clear items then add items, or after any .update(), you’ll need to get the field again (type in $choice = $list.fields[“Category”] again) to access the new values properly.

remove all items

$choice.choices.clear()
$choice.update()

remove one item

$choice.choices.remove("Name")
$choice.update()

(remember get the field again to add items after clearing it)

add an array of choices

$choiceArray = @("Meeting","Work Hours","Business","Holiday","Get-together")
$choice.choices.addrange($choiceArray)
$choice.update()

add one at a time

$choice.choices.add("Name")
$choice.choices.add("Name Of Another")
$choice.update()

Happy SharePointing!

Advertisements

8 thoughts on “Use PowerShell to manipulate the values of a SharePoint choice field.

  1. Knoots

    David, you deffinetly have my thoughts flowing now. Thank you for posting this. So if I want to use information from a SQL table to populate the choices how is that done? Do I export the fields to a text file and them pipe them into the powershell command? Thank you for putting this article togther!

    Reply
  2. Knoots

    Also, when I run the $choice.choices.update() command I get the following error:

    PS C:\Users\princelawadmin> $choice.choices.update()
    Method invocation failed because [System.Collections.Specialized.StringCollecti
    on] doesn’t contain a method named ‘update’.
    At line:1 char:23
    + $choice.choices.update <<<< ()
    + CategoryInfo : InvalidOperation: (update:String) [], RuntimeExc
    eption
    + FullyQualifiedErrorId : MethodNotFound

    Reply
  3. Knoots

    David,
    What I plan on doing is exporting the list to a text file using a sql script once I have the text file I want to update the choices with whatever is in the export. I dont belive I can do a merge so I figure clear the list and re-import is the best way to do this. Based on what you wrote in the second paragraph above I do not believe that this will impact any choices people made in the past. If they do go to edit either the choice will be there or they need to choose something else anyway.

    For reference, here is the powershell script I am using to do this. If you see anything wrong please let me know. If I got it right then maybe it will help some other “Newbie” out there.

    Script:
    $web = get-spweb http://myportal
    $list = $web.lists[“Client Documents”]
    $choice = $list.fields[“Client Name”]
    $choice.choices.clear()
    $choice.update()
    $web = get-spweb http://myportal
    $list = $web.lists[“Client Documents”]
    $choice = $list.fields[“Client Name”]
    Get-Content c:\clients.txt | Sort-Object | ForEach-Object {
    $choice.choices.add(“$_”)
    }
    $choice.update()

    Reply
    1. David Lozzi Post author

      From my experience, I don’t think you need to call $web and $list again, just $choice = $list.fields[“Client Name”]. Otherwise looks great!

      Reply
  4. Poova

    Hi David Lozzi,

    Thanks for this post it helped me alot.

    I modified it a little to be able add items from a source list.
    Hopefully this will help someone in return.
    I used these sites to create the full script

    http://letitknow.wordpress.com/2011/03/17/100/
    http://stackoverflow.com/questions/1391853/removing-duplicate-values-from-a-powershell-array

    Script:
    #Site that holds both lists
    $web = get-spweb https://Site/Site/

    #Source list for the choices
    $sList = $web.lists[“sourceList”]

    #Destination list
    $Choicelist = $web.lists[“destinationList”]

    #Field in the destination list that will be updated
    $Choice = $Choicelist.fields[“destinationField”]

    #Array that will store all values from Source list column
    $updateArray = @();

    #Clear current choices in the destination list.
    $Choice.choices.clear();

    $items = $sList.items

    foreach ($item in $items)
    {
    #Store the actual source Title column Items in the array
    $updateArray += $item[“Title”]
    }
    #ensure that all values in the array are unique
    $updateArray = $updateArray | select -uniq;

    #Add values to choice field
    $Choice.choices.addrange($updateArray);

    #Commit values
    $Choice.update();

    #Display the values as a check to see if it was committed
    $Choice.choices;

    Reply
  5. chudzu

    Hi👍
    great post.
    can you help me to change the value of list column(choice type with 2 value in progress and completed) automatically based on due date column.using powershell should be great.
    thank you so much!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s