Tag Archives: List

Cataloging Choice Columns

It’s been a while but it’s time for a new post.

Someone asked on TechNet () how to get a summary of all the custom choice columns and the options they have. I didn’t have the time to put it together at that moment but I thought It’d be a good exercise.

This will work for 2010 or 2013 but will not work for Office 365 or SharePoint online. For that a different approach would be needed.

“I inherited a SharePoint 2010 Foundation site that contains about 40 custom columns and about 10 of those custom columns are of the type “Choice”. Is there a way using Powershell or something else to export to a .csv file a list of the custom columns and if they are the type “choice” to show the list of what the various choices are for each column?”

So, let’s assume that we’re only interested in the site columns. To do that we’ll have to grab the SPWeb object and loop through the columns there that are of the appropriate type and list them out.

#Get the web
$web = Get-SPWeb "http://sharepoint.domain.com/sites/sitecollection/subsite"

#Get the columns (note, these are named fields)
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice"}

#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Loop through each choice and print the name
foreach ($entry in $columns)
{
Write-Host ("Field Name: {0}" -f $entry.InternalName)
#Loop through the choices and print those out
foreach ($choice in $entry.Choices)
{
Write-Host ("  Choice: {0}" -f $choice)}
}

From here

That’ll list out the columns to the screen but it’s not a great solution. It’s printing out too many columns, it’s also just printing them to the screen. We need it to serialise this into a format that we can use.

Let’s start with serialisation.

There’s loads of ways to do this but my preference is to create custom objects to contain the information we collect, then assign them to an array which we can process later.

For a quick guide to PSObjects have a look here
So, after changing the write-hosts to write verbose and putting in our custom objects we get this!

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice"}

#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Choices" = $choicesArray
                        }
}

Which actually makes things worse as we no longer get any results! Let’s add that in some xml work. I’m still not entirely happy with the way PowerShell and XML work together so this example is a bit clunky but it works.

#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

#loop through the results and genearate an xml object
foreach ($column in $ColumnDetailsArray)
{
    #Create an element to hold the top level item
    $columnElement = $xml.CreateElement("Choice")
    $columnElement.SetAttribute("Name", $column.Name) 

    #Loop through the choices and add entries for each
    foreach ($choice in $column.Choices)
    {
        $choiceElement = $xml.CreateElement("Choice")
        
        #Note that you need this Pipe Out-Null to prevent it writing to the console
        $choiceElement.InnerText = $choice
        $columnElement.AppendChild($choiceElement) | Out-Null
    }
    #Once it's built add the element to the root node
    $xml.root.AppendChild($columnElement)  | Out-Null
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

So, this now dumps out the data we’ve asked for but it also dumps out all the pre-packaged columns. This is a problem that isn’t easily fixed, there isn’t an ‘OOTB’ flag on fields but there are a few we can use to filter them out.

If we grab a column from the results and run Get-Members on it there are a couple of fields that should be useful for filtering the results:

Sealed – This shows if the column is not supposed to be edited by human hands. Note that this could give false negatives in scenarios where a column has been deployed via the CTHub which I think seals columns (it definitely seals Content Types) as it pushes to consuming site collections

Hidden – Not relevant in this case but often handy. In this case we’ll filter out groups that are part of the ‘_hidden’ group earlier.

So if we now add that criteria to the earlier $columns filtering process we get

$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }

But that’s still not perfect, so instead of filtering the terms out right now let’s make it a bit more useful first. When you look at columns in SharePoint they are organised in Groups. We can see those properties in PowerShell and group our elements using that field.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
#Also filter out the sealed fields
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }


#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Group" = $entry.Group
                        "Choices" = $choicesArray
                    }
}

#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

#Get a unique list of the groups in use in the site
foreach ($group in $ColumnDetailsArray | select Group -Unique)
{
    $groupText = $group.Group
    Write-Host "Group name: " $groupText
    $groupElement = $xml.CreateElement("Group")
    $groupElement.SetAttribute("Name", $groupText)
    
    #loop through the results and add them to the xml object
    foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
    {
        #Create an element to hold the top level item
        $columnElement = $xml.CreateElement("Choice")
        $columnElement.SetAttribute("Name", $column.Name)

        #Loop through the choices and add entries for each
        foreach ($choice in $column.Choices)
        {
            $choiceElement = $xml.CreateElement("Choice")
        
            #Note that you need this Pipe Out-Null to prevent it writing to the console
            $choiceElement.InnerText = $choice
            $columnElement.AppendChild($choiceElement) | Out-Null
        }
        #Once it's built add the element to the root node
        $groupElement.AppendChild($columnElement)  | Out-Null
    }
    $xml.root.AppendChild($groupElement)  | Out-Null
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

Of course once you have the group name you can filter those options out by using a blacklist of groups to avoid reporting on.


Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#BlackList Group Names
#These are the known groups you get in a non publishing team site:
$blackList = @(
    "_Hidden",                                                                         
    "Base Columns",                                                                    
    "Content Feedback",                                                   
    "Core Contact and Calendar Columns",                                               
    "Core Document Columns",                                                         
    "Core Task and Issue Columns",                                                          
    "Custom Columns",                                                            
    "Display Template Columns",                                                          
    "Document and Record Management Columns",                                                
    "Enterprise Keywords Group",                                                             
    "Extended Columns",                                                             
    "JavaScript Display Template Columns",                                                   
    "Reports",                                                                  
    "Status Indicators"
)

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
#Also filter out the sealed fields
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }


#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Group" = $entry.Group
                        "Choices" = $choicesArray
                    }
}
#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

foreach ($group in $ColumnDetailsArray | select Group -Unique)
{
    $groupText = $group.Group

    #Check to see if the group name is in our blacklist
    if (-not $blackList.Contains($groupText))
    {
        Write-Verbose "Group name: " $groupText
        $groupElement = $xml.CreateElement("Group")
        $groupElement.SetAttribute("Name", $groupText)
    
        #loop through the results and genearate an xml
        foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
        {
            #Create an element to hold the top level item
            $columnElement = $xml.CreateElement("Choice")
            $columnElement.SetAttribute("Name", $column.Name)

            #Loop through the choices and add entries for each
            foreach ($choice in $column.Choices)
            {
                $choiceElement = $xml.CreateElement("Choice")
        
                $choiceElement.InnerText = $choice
                #Note that you need this Pipe Out-Null to prevent it writing to the console
                $columnElement.AppendChild($choiceElement) | Out-Null
            }
            #Once it's built add the element to the root node
            $groupElement.AppendChild($columnElement)  | Out-Null
        }
        $xml.root.AppendChild($groupElement)  | Out-Null
    }
    else
    {
        Write-Verbose "Group skipped:" $groupText
    }
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

And there you have it. A working report that will summarise all custom choice columns in a SPWeb object and save them in an XML file.

Adding Content Types to the New button on a document library with PowerShell

Background
I was at a customer site and they wanted to remove a load of document types from the “New” button on their document libraries. I tried using the SPContentType.Hidden = $true parameter but realised that wasn’t the one. I then spent some more time banging my head against it and just did it by hand and moved on.

Another person asked how to do something similar on PowerShell.org (here: http://powershell.org/discuss/viewtopic.php?f=12&t=1407). I had some time and was irked by my failure before hand so I gave it another go. I met some success but thought that since it’s something that annoyed me, and since there’s no easily found PowerShell specific posts about this, it’s worth doing properly and blogging.

It turns out that the new button is determined by SPList.rootFolder.UniqueContentTypeOrder property. This is an ordered list of content types to display, any item in the list must be in the lists’ content types but not vice versa. Modify this and you modify the same property you set in the GUI. Happy days.

The first step is to see if a content type is available in the new button or not:

Is-ContentTypeInNewButton

Function Is-ContentTypeInNewButton {

[CmdletBinding()]
Param ([parameter(Mandatory=$true)][string] $ContentTypeName,
       [parameter(Mandatory=$true)][Microsoft.SharePoint.SPList] $SPList)
BEGIN   {  Write-Verbose "Begining Is-ContentTypeInNewButton" }
       PROCESS{
            #get the uniquecontenttypes from the list root folder
            $rootFolder = $SPList.RootFolder
            $contentTypesInPlace = [Microsoft.SharePoint.SPContentType[]] $rootFolder.UniqueContentTypeOrder
            
            #Check if any of them are the same as the test content type
            $results = $contentTypesInPlace | where { $_.Name -eq $ContentTypeName} 
            if ($results -ne $null)
            {
                Write-Verbose "$ContentTypeName Found"
                return $true
            }
            else
            {
                Write-Verbose "$ContentTypeName Not Found"
                return $false
            }
    }
    
END   {  Write-Verbose "Exiting Is-ContentTypeInNewButton" }
}

Of course there’s a possible gotcha. What if the Content type isn’t even added to the list at all?

Ensure-ContentTypeInList

Function Ensure-ContentTypeInList{

[CmdletBinding()]
Param ( [parameter(Mandatory=$true,ValueFromPipeline=$true)][string] $ContentTypeName,
       [parameter(Mandatory=$true)][Microsoft.SharePoint.SPList] $SPList)

BEGIN   {  Write-Verbose "Begining Ensure-ContentTypeInList" }
PROCESS { 

     #Check to see if the content type is already in the list
     $contentType = $SPList.ContentTypes[$ContentTypeName]
     if ($ContentType -ne $null)
     {
        #Content type already present
        Write-Verbose "$ContentTypeName already present in list"
        Return $true
     }
     else
     {
        Write-Verbose "$ContentTypeName not in list. Attempting to add"
        if (!$SPList.ContentTypesEnabled)
        {
            Write-Verbose "Content Types disabled in list $SPList, Enabling"
            $SPList.ContentTypesEnabled = $true
            $SPList.Update()
        }
         #Add site content types to the list from the site collection root
         $ctToAdd = $SPList.ParentWeb.Site.RootWeb.ContentTypes[$ContentTypeName]
         if($ctToAdd -eq $null)
         {
            Write-Error "Error - Content Type could not be found in the Site Collection"
            #I don't believe this will be called.
            return $false
         }
         $SPList.ContentTypes.Add($ctToAdd) | Out-Null
         $SPList.Update()
         Write-Verbose "$ContentTypeName added to list"
         return $true
     }
    }
END {
     Write-Verbose "Exiting Ensure-ContentTypeInList"
    }
}

Well that’s a start. Now we can tell if the content type already exsits, and can add the content type to the list if it doesn’t, let’s put that into something useful:

Ensure-ContentTypeInNewButton

Function Ensure-ContentTypeInNewButton{

[CmdletBinding()]
Param ( [parameter(Mandatory=$true,ValueFromPipeline=$true)][string] $ContentTypeName,
        [parameter(Mandatory=$true)][Microsoft.SharePoint.SPList] $SPList)
    BEGIN   { 
                Write-Verbose "Begining  Ensure-ContentTypeInNewButton"
                #get the uniquecontenttypes from the list root folder
                $contentTypesInPlace = New-Object 'System.Collections.Generic.List[Microsoft.SharePoint.SPContentType]'
                $contentTypesInPlace = $SPList.RootFolder.UniqueContentTypeOrder
                $dirtyFlag = $false
            }
    PROCESS { 
                
        #Check the content type isn't already present in the content type
        $AlreadyPresent = Is-ContentTypeInNewButton -ContentTypeName $ContentTypeName -SPList $SPList
        if ($AlreadyPresent)
        {
            Write-Verbose "$ContentTypeName is already present in the new button"
        }
        else
        {
            #Check that there really is such a content type
            $ContentTypePresent = Ensure-ContentTypeInList $ContentTypeName $SPList
            #Catch error events
            if ($ContentTypePresent)
            {
                #We now know that the content type is not in the new button and is present in the list. Carry on adding the content type
                
                $ctToAdd = $SPList.ContentTypes[$ContentTypeName]
                
                #add our content type to the unique content type list
                $contentTypesInPlace  =  $contentTypesInPlace + $ctToAdd
                $dirtyFlag = $true
                Write-Verbose "$ContentTypeName queued to add to the new button"
            }
            else
            {
                Write-Error -Message "Content type could not be added to the list."
            }
        }
    }
    End{
        #Set the UniqueContentTypeOrder to the collection we made above
        if ($dirtyFlag)
        {
           $SPList = $SPList.ParentWeb.Lists[$SPList.ID]
            $rootFolder = $SPList.RootFolder
            $rootFolder.UniqueContentTypeOrder = [Microsoft.SharePoint.SPContentType[]]  $contentTypesInPlace
        
             #Update the root folder
             $rootFolder.Update()
             Write-Verbose "ContentType(s) added to the new button in list $($SPList.Name)"
        }
        else
        {
                Write-Verbose "No changes"
        }
         Write-Verbose "Exiting  Ensure-ContentTypeInNewButton"
                
    }
}

Awesome. On the other hand the stuff above didn’t lend itself to testing. I had to go into the GUI each time to remove my content types. So let’s have something to help make unwind our changes:

Remove-ContentTypeFromNewButton

Function Remove-ContentTypeFromNewButton{

[CmdletBinding()]
Param ( [parameter(Mandatory=$true,ValueFromPipeline=$true)][string] $ContentTypeName,
        [parameter(Mandatory=$true)][Microsoft.SharePoint.SPList] $SPList)
    
BEGIN   { Write-Verbose "Begining Remove-ContentTypeFromNewButton" }
PROCESS { 
   
            #Check the content type isn't already present in the content type
            $AlreadyPresent = Is-ContentTypeInNewButton -ContentTypeName $ContentTypeName -SPList $SPList
            if ($AlreadyPresent)
            {
                Write-Verbose "$ContentTypeName is present in the new button - removing"
                
                #get the uniquecontenttypes from the list root folder
                $rootFolder = $SPList.RootFolder
                
                #Get the content types where the names are different to our content type
                $contentTypesInPlace = [System.Collections.ArrayList] $rootFolder.UniqueContentTypeOrder
                $contentTypesInPlace = $contentTypesInPlace | where {$_.Name -ne $contentTypeName}
                
                #Set the UniqueContentTypeOrder to the collection we made above
                $rootFolder.UniqueContentTypeOrder = [Microsoft.SharePoint.SPContentType[]]  $contentTypesInPlace
                
                #Update the root folder
                $rootFolder.Update()
                Write-Verbose "$ContentTypeName removed from the new button in list $($SPList.Name)"
            }
            else
            {
                Write-Verbose "$ContentTypeName is not present in the new button. No further action required."
            }
        }
END     { Write-Verbose "Exiting Remove-ContentTypeFromNewButton" }

}

Done.

So we now have the functions to take a list and content type, run a single command which will add a content type, ensuring it’s added to the new button. Further to that we’ve got some basic help (which WordPress has stripped out), error handling and it’ll take piplines and multiple content types. I love PowerShell.

Tests and examples of code

$CTHubSiteCollectionURL = "http://sharepoint/sites/cthub"
$singleContentType = "AlexB_Document"
$contentTypesToAddToNewButton = @("AlexB_Document1b","AlexB_Docudddment2")

$SPWeb = Get-SPWeb $CTHubSiteCollectionURL
$docLib = $spweb.Lists["TestDocLib"]


Write-Host "Is Content Type $ContentTypeName in the new button already? $(Is-ContentTypeInNewButton $singleContentType $doclib )"
Write-Host "Adding the content type to the new button (using the wonderful Ensure method which won't throw errors if already present)"
Ensure-ContentTypeInNewButton -ContentTypeName $singleContentType -SPList $doclib
Write-Host "Is Content Type $ContentTypeName in the new button already? $(Is-ContentTypeInNewButton $singleContentType $doclib )"
#Victory!

"Removing"
#$contentTypesToUpdate | Remove-ContentTypeFromNewButton -SPList $doclib 
Write-Host "Is Content Type in the new button already? $(Is-ContentTypeInNewButton $singleContentType $doclib )"
#Also Victory!

#Let's try a more interesting example
foreach ($contentTypeName in $contentTypesToAddToNewButton)
{
Write-Host "Is Content Type: $ContentTypeName in the new button already? $(Is-ContentTypeInNewButton $contentTypename $doclib)"
}
Write-Host "Adding the content types to the new button (using the wonderful Ensure method which won't throw errors if already present)"
$contentTypesToAddToNewButton | Ensure-ContentTypeInNewButton -SPList $doclib
foreach ($contentTypeName in $contentTypesToAddToNewButton)
{
Write-Host "Is Content Type: $ContentTypeName in the new button already? $(Is-ContentTypeInNewButton $contentTypename $doclib)"
}
#Victory!

And now i can rest. Any critiques of the powershell welcomed.

Just for those that are interested in the bit that makes this all possible, error handling etc. stripped out:

#Get the Web that holds the list
$SPWeb = Get-SPWeb "http://sharepoint/sites/cthub"
#get the library
$list = $SPWeb.Lists["Shared Documents"]

#Get a content type
$contentType = $docLib.ContentTypes | where { $_.Name -eq "AlexB_Document"}

#Get the root folder object
$rootFolder = $list.RootFolder

#Get the current list of content types available
$contentTypesInPlace = [Microsoft.SharePoint.SPContentType[]] $rootFolder.UniqueContentTypeOrder

#add our content type
$contentTypesInPlace  =  $contentTypesInPlace + $ContentType

#set the list to our new list
$rootFolder.UniqueContentTypeOrder = [Microsoft.SharePoint.SPContentType[]]  $contentTypesInPlace

#Update the folder
$rootFolder.Update()

References:
Thanks to Praveen Battula who’s blog post pointed me in the right direction and has some nice C# for doing a similar task.
http://praveenbattula.blogspot.co.uk/2011/01/change-content-type-order-in-new-button.html
Link to TechNet article on the UniqueContentTypeOrder: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfolder.uniquecontenttypeorder(v=office.14).aspx.

Thoughts for the future:
It’d be nice to be able to order the items. Not difficult technically but what would the best way to use such a process be?
It seems you can change the new button for different folders in the hierarchy. That’d be handy