SharePoint 2013 and PowerShell: Working with Project Site Issues List columns and Content Query Web Part

Article
1/17/2024

Introduction

Recently while working on SharePoint and Project Server, a request came from the PMO department to make changes to the project site issues list available when new project is created in Project Server. Similar question was also posted on MSDN forums. Here is what the request entailed:

Scenario

We need to update the choices for the category field in the project site issue list. We need a centralized place to display all open issues reported on each project site for one of these category. We need to update the existing issues with the updated category. New projects should include the updated categories.

Out of the box, the category field has three choices which are not very intuitive:

(1) Category1
(2) Category2
(3) Category3

We need to change it to:

(1) Category1 to Sales
(2) Category2 to Support
(3) Category3 to Engineering

Sypnosis

Content Query Web Part (CQWP) is a great way to aggregate and display list items from a single site or from multiple sites in the same site collection. It is only supported in publishing sites and the site collection that contains the site must have the publishing features enabled.

To enable this feature, go to top level site. From Site Settings, to Site collection features and activate "SharePoint Server Publishing Infrastructure".

Now we can activate "SharePoint Server Publishing" from Site Settings -> Manage site features:

After enabling CQWP, I thought that the rest of it would be straight forward. Find the content type, find the column, update the choices, select update all list using this column and save. I would then go to the CQWP, filter by the category column and status column, and display all open issues for a specific category. Swift eh? I don't think so. This might work with all other SharePoint content types (I say might since I have not tested this with all different content types) but for Project Sites, that was not the case. Neither does the content type exist in the site collection, nor does it have the category and status columns available.

Ok, time to rethink the strategy. There are over 50 project sites and manually updating the list on each site is not an option. Even if I am super fast and it takes 5 minutes per site (factoring in the load time for going through at least five different pages), this would take 5 x 50 = 250 mins, approx. 5 hours. And then open each item to select the correct category, no sir, that is not viable.

Hold up let's back up a bit. The issue and risk list are part of Project Web App, and I cannot disturb that functionality either and even if I change the choices, they would not be available in CQWP.

The Solution

PowerShell and Calculated columns to the rescue. Using PowerShell create two calculated columns in the top level site of the site collection: one based on status column and the other based on category column, add calculated columns to list, fetch each item and update category as needed. Here is the final script:

if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) 
{ 
   Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
} 
 
Start-SPAssignment -Global
 
$site = Get-SPSite "http://my.mainsite.com/projectwebapp"
$get_rootWeb = $site.RootWeb
if (($get_rootWeb.Fields["Project Status"]) -eq $null ) {
    $fieldXML = '<Field Type="Calculated" DisplayName="Project Status"
    EnforceUniqueValues="FALSE" Indexed="FALSE" LCID="1033"
    ResultType="Text" ReadOnly="TRUE" StaticName="Project_x0020_Status"
    Name="Project_x0020_Status"><Formula>=Status</Formula>
    <FieldRefs><FieldRef Name="Status"/></FieldRefs></Field>'
 
    $get_rootWeb.Fields.AddFieldAsXml($fieldXML)
}
 
if (($get_rootWeb.Fields["Project Category"]) -eq $null ) {
    $fieldXML = '<Field Type="Calculated" DisplayName="Project Category"
    EnforceUniqueValues="FALSE" Indexed="FALSE" LCID="1033"
    ResultType="Text" ReadOnly="TRUE" StaticName="Project_x0020_Category"
    Name="Project_x0020_Category"><Formula>=Category</Formula>
    <FieldRefs><FieldRef Name="Category"/></FieldRefs></Field>'
 
    $get_rootWeb.Fields.AddFieldAsXml($fieldXML)
}
 
$site | Get-SPWeb -Limit ALL | ForEach-Object {
    $get_list = $_.Lists["Issues"]
 
    if ($get_list -ne $null){
        Write-Output "Found List in $_"
 
        if (($get_list.Fields["Project Status"]) -eq $null){
            $site_field = $get_rootWeb.Fields["Project Status"]
            $get_list.Fields.Add($site_field)
        }
 
        $get_field = $get_list.Fields["Category"]
        if (!$get_field.Choices.Contains("Sales")){
            $choiceArray = @("Sales","Support","Engineering")
            $get_field.Choices.Clear()
            $get_field.Choices.AddRange($choiceArray)
            $get_field.Update()
 
            foreach($eachItem in  $get_list.Items){
                if ($eachItem["Category"] -eq "(1) Category1"){
                    $eachItem["Category"] = "Sales"
                    $eachItem.Update()
                }
                elseif ($eachItem["Category"] -eq "(2) Category2"){
                    $eachItem["Category"] = "Support"
                    $eachItem.Update()
                }
                elseif ($eachItem["Category"] -eq "(3) Category3"){
                    $eachItem["Category"] = "Engineering"
                    $eachItem.Update()
                }
            }
        }
 
        if (($get_list.Fields["Project Category"]) -eq $null){
            $site_field = $get_rootWeb.Fields["Project Category"]
            $get_list.Fields.Add($site_field)
        }
    }
 
    $_.Dispose()
}
 
$get_rootWeb.Dispose()
$site.Dispose()
 
Stop-SPAssignment -Global

Once the script successfully executed, we created a web part page, added CQWP, set filter using the project status and project category calculated columns, and display by site. Also, so that we don't have to go through this activity again, we saved one of the project sites as a template after making the changes and associated the template with the EPT.

Conclusion

PowerShell can make life wonderful and happy.

Share via