Set a #ProjectOnline Project custom lookup table field value using #PowerAutomate #MSProject #SharePoint #REST #Flow #Microsoft365 #LowCode #PowerPlatform

This was a flow I developed a few years ago now but never got around to publishing a blog post for it. It is quite a simple Power Automate flow that demonstrates updating a Project Online project level enterprise custom field value for a field that uses a lookup table. The full Power Automate flow can be seen below:

Full Flow

We will walkthrough this flow.

This flow is set to just run on a schedule as it is just a demo / test flow but set the flow trigger as required. The next action is to set the target Project GUID in the variable, as this is demo / test flow we are just updating one demo project using the hard coded GUID. In a real world flow this would be dynamic.

The third action is a Project Online Checkout project action, here we set the PWA URL and pass in the the project ID value from the ProjGUID variable. The checkout action is required as any updates to projects in Project Online require the project to be checked out first. Next up we use a SharePoint HTTP action to call the Project Online REST API, in this case the UpdateCustomFields method on the draft project. We pass in the ProjGUID variable in the Uri. The headers are then set and finally the body, this details the changes we are sending to the server. I will break the example down as this will need updating for your environment. The Key value is the internal name of the custom field you want to update. To find the correct internal field name grab the field ID by editing the enterprise custom field in PWA and copying the GUID as seen below:

field Guid

Then using the REST API in the browser, query the Custom Fields method passing in the field GUID:

{pwaSite}/_api/ProjectServer/CustomFields(‘{fieldGuid}’)?$Select=InternalName

This will give you the correct internal name:

REST API

This is the internal name you need for the Key property. Next you need the Value, as we are setting a lookup table value, we need to pass in the internal name for the lookup table entry we want to set. As this is a demo flow, the value here has been hard coded to one of the entries in my lookup table, in this case “Red”. To get the correct internal name for the lookup table entry you can use the REST API in the browser, query the Custom Fields method passing in the field GUID and using the related LookupEntries method:

{pwaSite}/_api/ProjectServer/CustomFields(‘{fieldGuid}’)/LookupEntries()?$Filter=FullValue eq ‘Red’&$Select=InternalName

This will give you the correct internal name for the entry – in this case ‘Red’ from my lookup table:

REST API

This is the internal name to set in the Value property.

The ValueType might also need to be updated depending on what type of custom field was being updated. As this field is a lookup table, Edm.String is correct. The correct ValueType can be confirmed here: Bulk update custom fields and create project sites in Project Online | Microsoft Docs

The final action is a Project Online checkin and publish project action as all project changes need to be published and we want the process to check the project in.

A nice simple low code example for updating a Project Online enterprise custom lookup table field using Power Automate!

Comments are closed.

Blog at WordPress.com.

Up ↑

%d bloggers like this: