Update Project Online Resource Custom Field with Lookup Value Using PowerShell

Posted By Posted by: Ahmed Taee on April 29, 2019

Preface

There are plenty of useful blogs that discuss how to update custom fields using PowerShell (or .Net) in Project Online/Project Server 2013 or later, but most don’t discuss how to update custom fields which utilize a lookup table. As it turns out, this is rather tricky.

Objective

To update a custom field that utilizes a lookup table via the use of PowerShell. The script contained within this blog will:

  • Connect to the Project Online PWA instance
  • Import data from a CSV file
  • Iterate through the resources specified in the CSV file
  • Update the targeted resource custom field (CF), which has lookup value.

NOTE 1: The script only accommodates a lookup type custom field that is not a multi-value field. The script could be adjusted quite easily to accommodate a multi-value field as well.

NOTE 2: The script could also be easily modified to update project and task custom fields that were of the lookup table type.

Script

Please find the script here:

Prerequisites

The script will require:

  • an update to the PWA instance URL
  • an update to the username and password variables
    The account used should be a member of the Administrators group.
  • an update to the path to the CSV and log files
  • an update to the path to the CSOM dll files

Walkthrough the Solution

After creating the context, the following code loads the enterprise resources:

1
2
3
4
#Load Resources
$Eresources = $projContext.EnterpriseResources
$projContext.Load($Eresources)
$projContext.ExecuteQuery()

Thereafter the script loads the custom fields:

1
2
3
4
#Load Customfields
$customfields = $projContext.CustomFields
$projContext.Load($customfields)
$projContext.ExecuteQuery() 

The following lines selects the custom field that is targeted for update and get its internal name:

1
$customfield = $customfields | WHERE {$_.Name -eq $customfieldname} $customfield_internalid = $customfield.InternalName

The following line selects the resource:

1
$resource = $Eresources | where {$_.Name -eq $resName}

To update the custom field for the selected resource, typically it is just a matter of using code similar to what is found below:

1
2
$fieldvalue = hello
$resource[$customfield_internalid] = $fieldvalue

But this won’t work for lookup type custom fields as it expects the lookup table entry value. For example, if “Yes” was a valid value in the associated lookup table, then the internal name of that entry is required. The lookup table entry will have an internal name that looks similar to “Entry_3d8840a53e77e71180d200155d1c551d”.

With that in mind, the following code retrieves the lookup table entry value:

1
2
3
4
5
6
foreach($entry in $lookupEntries){
    if($entry.Value -eq $targetlookupvalue) {
        [string[]]$lookupentry_internalname = $entry.InternalName 
        write-host $customfield.name, $lookupentry_internalname, $entry.Value
    }
}

The important part of the code above, the bit that took quite a while to understand, is that the variable used to record the lookup table entry has to be a string array; not just a string. This makes sense, as the custom field could be a multi-value custom field, and to update such a custom field with multiple values, then an array of strings will certainly be needed.

To force the variable to be a string array, then the variable $lookupentry_internalname was prefixed with [string[]].

Found below is the code used to update the resource custom field:

1
$resource[$customfield_internalid] = $lookupentry_internalname

Blog Posted In Blog Posted In: Blog, How to, Project Online
Blog Posted In