Update Project Online Resource Custom Field with Lookup Value Using PowerShell

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, How to, Project Online