none
Update custom field value from Powershell - Project Server 2013 RRS feed

  • Question

  • I have an environment with nearly 1000 project and the PMO just decided on a few new custom fields. They know the value they want and want the update scripted. 

    It should be noted that this is for a custom field with a lookup table

    I am having an issue with the update getting an error

    Exception calling "QueueUpdateProject" with "4" argument(s): "ProjectServerError(s) LastError=CustomFieldInvalidUID Instructions: Pass this into PSClientError constructor to access all error information"
    At line:50 char:13

    Eventually this will be puling the data from a CSV with both the custom field value and the project GUID. 

    Any thoughts would be helpful.

    #Lets open the CSV (CSV is faser than Excel) 
    #$ProjList = Import-CSV "F:\Proj.csv"
    $PriorProjGUID = ""
    $ProjectSite = "http://MYSITE/PWA"
    $svcPSProxy = New-WebServiceProxy -uri "$ProjectSite/_vti_bin/PSI/Project.asmx?wsdl" -useDefaultCredential 
    #$EPMTYGUID = [system.guid]::empty
    $CustGuid = "2a7f87c6-b4b4-e511-9442-0050569a591e"
    $DropGuid = "9CCA38A4-B4B4-E511-9442-0050569A591E"
    $ProjGUID = "727CCF93-85B1-E511-9442-0050569A591E"
    $sessionGuid = [System.Guid]::NewGuid()
    
    $projectUid = $ProjGUID
    
    write-host $projectUID
    
            #$project = $svcPSProxy.ReadProjectEntities($projectUid, 1 , "WorkingStore")
            $project = $svcPSProxy.ReadProject($Projectuid, 0)
                #$sessionGuid = [System.Guid]::NewGuid()
                #$jobGuid = [System.Guid]::NewGuid()       
                Write-Host $project.Project.Proj_Name "will be updated"
               $CustomField = $project.ProjectCustomFields.NewProjectCustomFieldsRow() 
                $CustomField.PROJ_UID = $projectUid #$sessionGuid
                $CustomField.CUSTOM_FIELD_UID = [System.Guid]::NewGuid()
                $CustomField.MD_PROP_UID = $DropGuid
                $CustomField.CODE_Value = $CustGuid
                #$CustomField.TEXT_VALUE = "Execution"
                $project.ProjectCustomFields.AddProjectCustomFieldsRow($CustomField) 
                
                $jobGuid = [System.Guid]::NewGuid()
                $svcPSProxy.CheckOutProject($projectUid, $sessionGuid, "Updating Project")
                
                $jobGuid = [System.Guid]::NewGuid()
                $svcPSProxy.QueueUpdateProject($jobGuid, $sessionGuid, $project, $FALSE);  
                
                $jobGuid = [System.Guid]::NewGuid()
                $svcPSProxy.QueuePublish($jobGuid, $projectUid, $FALSE, $EPMTYGUID);                   
               
                $svcPSProxy.QueueCheckInProject($jobGuid, $projectUid, $TRUE, $sessionGuid, "CheckIn Update...")       


            


    Tasks








    • Edited by Bob Leake Tuesday, January 12, 2016 12:21 AM
    Monday, January 11, 2016 8:35 PM

All replies

  • It seems like the issue is around the fact that there is a value there. If I try to update a custom field that is not from a lookup table that does have a value I get the same error. If I remove the value then I am fine. So is there an "Update" rather than add new? 

    Tasks

    Tuesday, January 12, 2016 11:37 AM
  • Hi Bob,

    I know that this is a little old, but I've only started working with PowerShell Scripts to perform Project Updates in the last month or so and I've learnt and lot, but also have way more to learn.

    With respect to Enterprise Custom Fields that are supported by a Lookup Table, the TEXT_VALUE update will not work. For each Lookup Table entry, there is a corresponding GUID that is generated. You'll need to locate the associated Lookup Table GUID value using a SQL Query against the ProjectWebApp Views/Tables. Which one, I'm not sure, but you should be able to work that our via the information available on the database schema in the Project Server 2013 SDK.

    Alternatively (and this may be easier), you can get the Lookup Table values by navigating to the page in PWA where you edit the Lookup Tables and for the Lookup Table in question, opening that in the Browser Source Code page format. You should be able to identify the GUID's for each Lookup Table vale on that source code page.

    Once you have the GUID for the Lookup Table value that you need updated, then you will be able to update that as one of the Enterprise Custom Field attributes (LT_STRUCT_UID).

    So in the case of your example code, you might have a variable of $LUTGuid = "nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn". Then instead of the line of code listed as $CustomField.TEXT_VALUE ' "Execution", you would rather have $CustomField.LT_STRUCT_UID = $LUTGuid

    Hope this make sense. You've probably worked this out in the meantime, but perhaps it will help someone else out who may stumble over this posting looking for information.

    Regards,

    Wayne

    Thursday, April 7, 2016 4:37 AM
  • Hi Bob,

    Is the above Issue resolved? I am facing the same error. Please help



    • Edited by Rathwin Tuesday, February 28, 2017 11:04 AM
    Tuesday, February 28, 2017 6:22 AM
  • Hi Rathwin,

    As it turns out, I continued working on this process after I posted my last message and wrote a script that updates a Lookup Table that was, in our case, named 'Program'. This may not be the exact same process that you need, but it should provide you with general details on interrogating an Enterprise Custom Field and in this case updating the Lookup Table entries. If it is the Enterprise Custom Field values that need to be update, then what I mentioned previously comes into play where the GUID for the Lookup Table entries need to be located, before updating the Enterprise Custom Field value, based on that GUID.

    So, in this example, our actual requirement was to examine a Project Server 2013 Enterprise Custom Field as a source of values and then compare those values to what may already be in a target Lookup Table, before updating the Lookup Table with values that are not already present. The values to be added to the Lookup Table are identified by a SQL Server Query that generates a differences dataset between the Enterprise Custom Field & Lookup Table.

    The code that I had to submit in a separate entry below, because of the 6000 char limit, should do this. The actual script I developed was very long, as it included logging all events to a log file, attaching the file to an automated email and sending it out to the tech support team. It then did memory cleanup before finishing. So the code was too long to paste here. I hope I didn't prune too much, but this should be everything you need.

    If not, ping me at wayneatwalkerboutdotcom and I can send you the full script, especially if you need the email notification, event logging and memory clean-up as well.

    Good luck,

    Wayne

    Tuesday, February 28, 2017 11:16 PM
  • Sorry, had to remove color coded text, as too me=any formatting chars to get below 6000 chars.
    ~Wayne

    # ==== Lookup Table Values Update - Program ====
    # VARIABLES
    # Server running PowerShell.
    $Svr = $env:COMPUTERNAME
    # SMTP Server Name.
    $SmtpServer = “smtp.dmain.com”
    # Project Web App Instance URL.
    $PWAUrl = "http://domain.com/pwa"
    # SharePoint Farm Database Server Name.
    $SPDbSvr = "ServerDB"
    # Project Server Database Name.
    $PSDb = "ProjectWebApp"
    # Lookup table for update.
    $LookupTableName = "Program"
    # SQL Query to build the Dataset for Target entries.
    $SqlQuery =
    @"
    SELECT             p.ProjectName
    FROM               [dbo].[MSP_EpmProject_UserView] AS p FULL JOIN
                       [dbo].[MSP_EpmEnterpriseProjectType] AS et ON
                       p.EnterpriseProjectTypeUID LIKE et.EnterpriseProjectTypeUID
    WHERE              et.EnterpriseProjectTypeName LIKE 'Program'
    EXCEPT
    SELECT             l.MemberValue
    FROM               dbo.MSPLT_Program_UserView AS l
    WHERE              l.MemberValue IS NOT NULL
    "@
    # FUNCTIONS
    CLS
    # Get Service Application URL's.
    Function GetServiceApplicationUrls
    {
        # Project Web Service.
        $ProjSvcURL = $PWAUrl + "/_vti_bin/PSI/Project.asmx?wsdl"
        $global:ProjSvcProxy = New-WebServiceProxy -uri $ProjSvcURL -useDefaultCredential # -credential $Credential
        # Lookup Table Web Service.
        $LUTSvcURL = $PWAUrl + "/_vti_bin/PSI/LookupTable.asmx?wsdl"
        $global:LUTsvcProxy = New-WebServiceProxy -uri $LUTSvcURL -useDefaultCredential # -credential $Credential
        # Queue Web Service.
        $QSvcUrl = $PWAUrl + "/_vti_bin/PSI/QueueSystem.asmx?wsdl"
        $global:QSvcProxy = New-WebServiceProxy -uri $QSvcUrl -useDefaultCredential # -credential $Credential
    }
    # Obtain new ECF values in a Dataset.
    Function GetNewECFValues
    {
        #Read New Program Names from ProjectWebApp Db.
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = “Server=" + $SPDbSvr + ";Database=" + $PSDb + ";Integrated Security=True”
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $global:TargetDataSet = New-Object System.Data.DataSet
        $global:RowCnt = $SqlAdapter.Fill($global:TargetDataSet)
        $SqlConnection.Close()
    }
    # Get the current Lookup Table values.
    Function GetCurrentLUTValues
    {
        $EPMTYString = [system.string]::empty
        # Update Language Locale ID if NOT US English (1033), in 3 locations.
        $global:LookupTables = $global:LUTsvcProxy.ReadLookupTables($EPMTYString, 0 , 1033)
        $global:LookupTableGuid = $global:LookupTables.LookupTables  | where {$_.LT_NAME -eq $LookupTableName }
        $global:LookupTable = $LUTsvcProxy.ReadLookupTablesbyUids($global:LookupTableGuid.LT_UID, 1 , 1033)
        $global:LookupTableValues = $global:LUTsvcProxy.ReadLookupTablesbyUids($global:LookupTableGuid.LT_UID, 0 , 1033).LookupTableTrees
        $global:Count = $global:LookupTableValues.Count +1
    }
    # Update Lookup Table.
    Function LUTUpdate
    {
        # Load SharePoint PowerShell cmdlets.
        "Testing for the Microsoft.SharePoint.PowerShell module" | Write-Verbose
        if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ea silentlycontinue) -eq $null)
        {
            Add-PSSNapin Microsoft.SharePoint.Powershell
        }
        # Get the Sever App URLs.
        GetServiceApplicationUrls
        # Get the New ECF values.
        GetNewECFValues
        If ($global:RowCnt -eq 0)
        {
            # No records so skip.
        }
        # Else, process.
        Else
        {
            # Get the current Lookup Table values.
            GetCurrentLUTValues
            # Based on the Target Dataset, add new.
            foreach($LUTValue in $global:TargetDataSet.Tables[0].Rows)
            {
                $ECFEntry = $LUTValue.ProjectName
                $GUID = [System.Guid]::NewGuid()
                $LookupRow = $LookupTable.LookupTableTrees.NewLookupTableTreesRow()
                $LookupRow.LT_STRUCT_UID = $GUID
                $LookupRow.LT_UID = $global:lookupTableGuid.LT_UID
                $LookupRow.LT_VALUE_TEXT = $LUTValue.ProjectName
                $LookupRow.LT_VALUE_SORT_INDEX =  ($global:Count ++)
                $LookupTable.LookupTableTrees.AddLookupTableTreesRow($LookupRow)
            }
        }
    }
    # MAIN PROCESSING
    # Check permissions. If insufficient, get elevated rights.
    If (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator"))
    {
        Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs
        exit
    }
    # If security rights are insufficient, stop.
    If (-not ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator"))
    {
        break
    }
        else
    {
        LUTUpdate
    }

    <o:p></o:p>

    <o:p></o:p>

    <o:p></o:p>

    Tuesday, February 28, 2017 11:37 PM