none
Project Server 2019 Mass change values of Project ID fields RRS feed

  • Question

  • Hello everybody!

    Here the situation: need to change Project ID values of a lot of projects by changing to IDs from another system.

    Is it possible to change it like a custom fields? (For example how change custom field values from here)


    MCSA: Security; MCTP: Microsoft ISA 2006

    Monday, April 20, 2020 1:53 PM

Answers

All replies

  • Hi Andrey,

    Paul's script you mentionned should allow bulk updating any field in PWA. So it should work with the project ID. Of course, test it first on a test tenant.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Monday, April 20, 2020 2:00 PM
    Moderator
  • Hello Guillaume Rouyre!

    yes, I think that too about Paul's script, it should work with system field too.

    But where I can find the field's GUID?

    A GUIDs of Custom Fields I can find at customfields.xml page, but there are no system field's GUID on this page.

    Maybe you know?


    MCSA: Security; MCTP: Microsoft ISA 2006

    Monday, April 20, 2020 2:17 PM
  • Hi,

    The ProjectUID is a native UI automatically generated by the system at project creation. You should (and can) not change it. That being said, you have a custom field named Project ID which could be used instead for your purpose.

    Project ID are configurable in the EPT settings with a mask but can be override with a script.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Monday, April 20, 2020 2:42 PM
    Moderator
  • Thank you for the answer.

    Well, Project ID field is able to change its value, look at the screenshot

    Of course, it is unavailable to delete the value, but it's able to changes in numeric type.


    MCSA: Security; MCTP: Microsoft ISA 2006

    Monday, April 20, 2020 2:59 PM
  • Hi Andrey,

    Yes you can change the value. Is this a problem for you?

    Maybe I didn't get your question... I understood that you need to bulk change the project ID in order to match or communicate with another system. I told you that the default PROJET GUID field is not an option this it is generated by default and cannot be overwriten. The option for you is to use the PROJET ID which is meant to do what you need: manually (or using a script) updating this field for referencing purpose or communicating with another system.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Tuesday, April 21, 2020 8:11 AM
    Moderator
  • Hello Guillaume Rouyre!

    Yes, I told about Project ID field and changing value in this field. But I mentioned GUID like it was in Paul's powershel script with custom field's GUIDs. I don't planing changes in GUIDS, jist only values in ID.

    So, I found the scripts of Ian Bruckner here, but it for Online version.

    After little customizes the scripts have next code:

    1-st

    #Add in libraries - update for the correct location
    #SharePoint Online CSOM DLL
    Add-Type -Path 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
    Add-Type -Path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
    #Project Online CSOM DLL
    Add-Type -Path 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.ProjectServer.Client.dll'
    
    $PWAInstanceURL = "http://sr-f2019-fe1/pwa"
    
    $Credentials = [System.Net.CredentialCache]::DefaultCredentials  #Current User Credentials
    
    
    $LookupTablesAndEntries = @()
    $allFields = @()
    
    
    
    # Get the Native Fields
    $url = $PWAInstanceURL +"/_api/ProjectServer/Projects"
        $webrequest = New-Object Microsoft.ProjectServer.Client.ProjectContext($PWAInstanceURL) 
        $webrequest.Credentials = $credentials
    
        $webrequest = [System.Net.WebRequest]::Create($url)
        $webrequest.Credentials = $credentials
        $webrequest.Accept = "application/json;odata=verbose"
        $webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
     
        $response = $webrequest.GetResponse()
        $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
        $data = $reader.ReadToEnd()
        $results = ConvertFrom-Json -InputObject $data
        $NativeFields = $results.d.results | Get-Member -MemberType NoteProperty | Select-Object Name,@{name="FieldType";expression={"Native"}}
        $allFields += $NativeFields 
    
    $allFields | Where-Object -Property HasLookup -EQ $true | %{
        Write-Host -BackgroundColor Gray $_.Name
        $_.Entries.keys
    }

    2-nd

    #Add in libraries - update for the correct location
    #SharePoint Online CSOM DLL
    Add-Type -Path 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
    Add-Type -Path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
    #Project Online CSOM DLL
    Add-Type -Path 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.ProjectServer.Client.dll'
    
    
    #Set PWA details - update for correct URL, username and password
    $PWAInstanceURL = "http://sr-f2019-fe1/pwa"
    $Credentials = [System.Net.CredentialCache]::DefaultCredentials  #Current User Credentials
    $csvPath = "C:\PowerShell\bulk\NativeProjectID.csv"
    
    #Set up the context
    $projContext = New-Object Microsoft.ProjectServer.Client.ProjectContext($PWAInstanceURL) 
    $projContext.Credentials = $Credentials
    
    #load projects
    $projContext.Load($projContext.Projects)
    $projContext.ExecuteQuery() 
    
    # Protect Native attributes from update which should be project pro controlled
    $allowedNativeFields = "ProjectIdentifier"
    
    #Import CSV file and update associated project - update location for CSV file 
    $updates = Import-Csv $csvPath
    
    $fieldsToUpdate = @()
    $fieldsInFile = $updates | Get-Member -MemberType NoteProperty | select -ExpandProperty Name
    if ($fieldsInFile -contains "Project ID") {$fieldsInFile +="ProjectIdentifier"} else {$false}
    $fieldsInFile | %{
        $fieldInFile = $allFields | Where-Object -Property Name -EQ -Value $_
        If (($fieldInFile.FieldType -eq "Native") -and ($allowedNativeFields -contains $fieldInFile.Name)) {
            $fieldsToUpdate +=$fieldInFile
        } 
    
    }
    
    $updates | ForEach-Object {
        $update = $_
        try {        
            $projectName = $update."Project Name"
            $project = $projContext.Projects | select Id, Name | where {$_.Name -eq $projectName}
            if($project -ne $null){
                $proj = $projContext.Projects.GetByGuid($project.Id)
                $draftProject = $proj.CheckOut()
                
                $fieldsToUpdate | Foreach-Object {
                    $fieldToUpdate = $_
                    If ($fieldToUpdate.FieldType -eq "Native"){
                        If ($fieldToUpdate.Name -eq "ProjectIdentifier") {
                            $draftProject.$($fieldToUpdate.Name) = $update."Project ID"
                        } else {$draftProject.$($fieldToUpdate.Name) = $($update.$($fieldToUpdate.Name))}
                    } 
                               }
                $draftProject.Publish($true) | Out-Null
                $projContext.ExecuteQuery()
            }
        } catch {
            write-host -ForegroundColor Red "Add error occurred whilst attempting to update project: '$projectName'. The error details are: $($_)"
        }
    }
    
    

    So, after running the 1st script I found succsess auth events in the Event Viewer in the Security section. It means that 1st script almost works, but there no native fields on dispay output.

    So, what's wrong?


    MCSA: Security; MCTP: Microsoft ISA 2006

    Tuesday, April 21, 2020 11:53 AM
  • Well, finally it was done by MS SQL.

    Command UPDATE in the tables pjpub.MSP_PROJECTS, pjdraft.MSP_PROJECTS and pjrep.MSP_EmpProject and columns PROJ_IDENTIFIER, PROJ_IDENTIFIER and ProjectIdentifier in the each table.


    MCSA: Security; MCTP: Microsoft ISA 2006






    Tuesday, April 21, 2020 2:47 PM