
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 |