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.
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.
Please find the script here:
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:
Thereafter the script loads the custom fields:
The following lines selects the custom field that is targeted for update and get its internal name:
The following line selects the resource:
To update the custom field for the selected resource, typically it is just a matter of using code similar to what is found below:
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:
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: