Flow is a great (one day) replacement for SharePoint Designer workflows, however at the moment the Project Online connectors are still in “preview” and as such as woefully inadequate. In my view the biggest omission is the inability to set custom fields, among many other failings this one is pretty much always step one or two on any workflow I have ever needed to create for Project Server.
You may have seen some MS demos where they show a quick easy way to circumvent this by using the HTTP action, unfortunately I’ve yet to find any online examples of that so I’m going to write one myself. This article focuses on Enterprise Resource custom fields but applies equally well to Task custom fields. However, the process is actually simpler for Project level custom fields and Paul Mather has blogged on the topic before here. Although he doesn’t cover Flow in that article, you can convert his JavaScript code to a JSON body and use it as as below. There’s also this StackExchange post that covers Project fields using a different method.
Updating Enterprise Resource fields via REST
It turns out this is not supported!
Please correct me if I’m wrong here, I’ve spent many hours looking for a solution for this from MS to no avail!
Well for me I figure if MS released an API 5 years ago and never got around to finishing it then supported or not I need a way to do my job. Fortunately it is possible via CSOM/JSOM to do this, and by understanding how all the client side API’s work you will see that it is possible and (arguably) fully supported to do this via REST using the internal methods used by CSOM/JSOM.
Updated Enterprise Resource default fields via REST
Before we look at custom fields lets cover how to set the default fields such as; Group, Email, etc. These can be set via the normal REST endpoint, and frankly this is how it also *should* be possible to set custom fields.
Assuming the resource exists and you want to update it by GUID, the following HTTP PATCH request will do it:
PATCH /sites/pwa/_api/ProjectServer/EnterpriseResources('[guid]') HTTP/1.1
Host: contoso.sharepoint.com
Accept: application/json; odata=verbose
Content-Type: application/json; odata=verbose
{
"__metadata": {
"type": "PS.EnterpriseResource"
},
"Group": "Test group",
"Email": "[email protected]"
}
That’s a raw HTTP request from PostMan, the important bits are the JSON and the PATCH type of request, yes a standard POST or PUT request will not work. In that JSON body of the request you can list multiple custom fields as long as you adhere to correct JSON syntax, for the full list of field names, browse to the endpoint in your browser: /sites/pwa/_api/ProjectServer/EnterpriseResources.
However if you look at the endpoints you’ll see the custom field values referenced by internal name, e.g.: Custom_x005f_000039b78bbe4ceb82c4fa8c0c400284
Unfortunately it is not possible to add to your JSON the CF value, like: “Custom_x005f_000039b78bbe4ceb82c4fa8c0c400284″: “Text value for field” as a result we have to work a bit harder.
Enter the ProcessQuery endpoint
(If you’re not interested in the why, but just the how to do this, you may want to skip to the next section!)
If you’re familiar with Fiddler traces of any of the SharePoint client side object models then you will have seen that all of the API calls made are translated into HTTP calls to an internal endpoint: _vti_bin/client.svc/ProcessQuery while not documented anywhere I have found, you can see that basically this is where the ClientContext.ExecuteQueryAsync(…) call from either CSOM or JSOM is performing the requested operation.
The ProcessQuery endpoint accepts a HTTP POST request with a body that looks something like this:
<Request xmlns="http://schemas.microsoft.com/sharepoint/clientquery/2009" SchemaVersion="15.0.0.0" LibraryVersion="16.0.0.0" ApplicationName="Javascript Library">
<Actions>
<Method Name="SetCustomFieldValue" Id="9" ObjectPathId="6">
<Parameters>
<Parameter Type="String">Custom_f825c154928ae81180da00155df8aa23</Parameter>
<Parameter Type="String">text field value</Parameter>
</Parameters>
</Method>
<Method Name="Update" Id="10" ObjectPathId="6" />
</Actions>
<ObjectPaths>
<Method Id="6" ParentId="4" Name="GetById">
<Parameters>
<Parameter Type="String">5e658680-838a-e811-80df-00155df8b01b</Parameter>
</Parameters>
</Method>
<Property Id="4" ParentId="0" Name="EnterpriseResources" />
<Constructor Id="0" TypeId="{3a609e5f-e2a1-497c-87a2-e5e8a781c096}" />
</ObjectPaths>
</Request>
That happens to be the query syntax to set a single text custom field for a resource. If you read through the content you can see (in order of Id properties) what is happening, in short the above translates to the following (pseudo) CSOM code:
var res = EnterpriseResources.GetById([guid]);
res.SetCustomFieldValue("Custom_f825...", "text field value");
EnterpriseResources.Update();
Pretty cool huh? :)
It get’s better, as if you trace in fiddler some code that does multiple things, like for example setting 3 or 4 custom field values, you will see that they are all batched into a single POST query just as you’d expect when using ExecuteQueryAsync in your code. The change in the method above is simply additional <Action> elements in the XML, one for each call to SetCustomFieldValue (note: incrementing the Id property is also necessary).
The remainder of that XML deserves a short explanation, ignoring the XML schema stuff the only other variable defined in the above example is a GUID: {3a609e5f-e2a1-497c-87a2-e5e8a781c096}. This GUID refers to the ServerTypeId that is defined in the Microsoft.ProjectServer.Client.dll file specifically the PS.ProjectServer constructor, and importantly “EnterpriseResources” is a public property of that class. So with that in mind you could re-use this GUID and make calls to any other public property to construct the call you need.
I prefer to cheat, and simply write a small snippet of JSOM (or CSOM) code that makes the call I want in REST, then use fiddler to get the XML body created when I make that call.
Calling a Project Online API from Flow
Now that we know what the HTTP request needs to look like we can start building a flow. Not so long ago this was much more difficult as we had to worry about OAuth2 and AzureAD apps and such, but now thankfully MS have added a new action to the SharePoint actions list which we are going to use:
I’ve created a new flow with a Project Online trigger “When a new resource is created”, and then added the above HTTP request action.
You need to populate the following values:
- Site Address: URL to your PWA
- Method: POST (for custom fields, or if out of the box fields PATCH)
- Uri: _vti_bin/client.svc/ProcessQuery
- Headers:
- Accept: application/json; odata=verbose
- Content-Type: text/xml
- Body: [Our XML body from the previous step]
Note: The header for Content-Type must be “text/xml” to match the content, if you were updating a default field using the JSON body from the beginning of this article, this would also be set to “application/json; odata=verbose”.
Thanks to the SharePoint HTTP action we don’t have to worry about authentication here, so actually that is it! Well almost.
Parameterizing the Flow action
Adding some variables to the flow allows us to parameterize the action based on whatever logic you want, once that’s done replace the values in the XML like this:
Important: Notice I have stripped the XML of tabs, spaces and line endings! This is required, if you don’t do that you will get an Unknown Error on larger query bodies.
Done.
What about default fields?
For completeness if you wanted to update the internal Group field for a resource, the http request action would look like this:
Note the resource GUID is in the URI and both the Method and Headers are different!
What about Lookup Table Values?
Lookup table value updates via REST have the same requirements as updating those values via JSOM (or CSOM), see my earlier post – Updating Lookup Table values using JSOM – for more on that.
In summary though, we need to first identify the lookup entry internal name, something like “Entry_412bb4bd5661e711a989000d3a28f1ff” then we need to pass that to the request as an array.
The request body now will look like this:
<Request xmlns="http://schemas.microsoft.com/sharepoint/clientquery/2009" SchemaVersion="15.0.0.0" LibraryVersion="16.0.0.0" ApplicationName="Javascript Library">
<Actions>
<Method Name="SetCustomFieldValue" Id="13" ObjectPathId="8">
<Parameters>
<Parameter Type="String">Custom_f825c154928ae81180da00155df8aa23</Parameter>
<Parameter Type="String">text field value</Parameter>
</Parameters>
</Method>
<Method Name="SetCustomFieldValue" Id="14" ObjectPathId="8">
<Parameters>
<Parameter Type="String">Custom_000039b78bbe4ceb82c4fa8c0c400284</Parameter>
<Parameter Type="Array">
<Object Type="String">Entry_412bb4bd5661e711a989000d3a28f1ff</Object>
</Parameter>
</Parameters>
</Method>
<Method Name="Update" Id="15" ObjectPathId="6" />
</Actions>
<ObjectPaths>
<Property Id="6" ParentId="0" Name="EnterpriseResources" />
<Method Id="8" ParentId="6" Name="GetById">
<Parameters>
<Parameter Type="String">0bd67eed-538e-e811-aadb-000d3a28f1ff</Parameter>
</Parameters>
</Method>
<Constructor Id="0" TypeId="{3a609e5f-e2a1-497c-87a2-e5e8a781c096}" />
</ObjectPaths>
</Request>
I’ve left in the previous example to demonstrate how to set two custom fields at once, but the important part here is lines 9 – 16 which include the additional syntax you will need to set an array type value. Once again passing the value as an array is required for both single value lookups as well as multi-value.
Final words
This ProcessQuery endpoint is not publicly documented by Microsoft but a long time ago I discussed this with some people from the product group and their response was that while not officially supported, it is supported in the sense that it will not change until JSOM and CSOM are both disabled, as that’s how they internally work.
So use it with the usual warnings, but biggest of all: I take no responsibility or offer no support for how you use the above. Feel free to ask a question in the comments below, if I can help I will, but no guarantees.
Thanks for your post. I wrote something related on this topic: https://techcommunity.microsoft.com/t5/Project/Project-workflows-How-the-quot-Send-an-HTTP-request-to/m-p/217812#M288
I just used the standard fields of the project, but I will try the custom fields for the project as well.
Hi,
Thanks for sharing!
I just tested your method out for some project fields and it works nicely like you said for the default fields, but it didn’t accept custom fields unfortunately. It seems it works in the same way as with updating resource fields. I guess one of the above approaches is required.
Cheers,
Martin
How do you access the local project lookup table for a custom field with the REST API? I can get to a list of lookup entries for enterprise fields by ProjectServer/LookupTables(‘a7e72b5f-ef3c-e911-afc3-00155d0c6c29’)/Entries. Is there a way to get to project level custom fields? I can see the entries in my tasks, but I can’t find the corresponding lookup tables to figure out what the entry values actually are for local custom fields.
Hi Eric,
Local field values will not show in the REST (or even PSI) APIs, any fields must be created as enterprise custom fields to be set via REST or any server side API.
The only way it is possible to work with local fields / lookups is via the client object model using VBA or .NET (VSTO).
Regards,
Martin
We are using project server 2016 on premise. We need create projects and update project custom fields using CSOM. setCustomFieldValue is not exist in 2016 version. Is there any option please let me know.
Not available? I use the JSOM API all the time with 2016 (actually 2013 -> Project Online) in my apps, so if you can’t find the method using CSOM, check the version of the CSOM NuGet package you are using as it is possible.
Hi Martin,
Can you provide an example of updating a custom resource lookup field value with Flow? I have a customer need and happy to engage you as a consultant. I have other Project Online customizatations needed as well.
Hi, Martin!
Nice job. Do you know how to update Base Calendar of a resource? I try a patch request to “_api/projectServer/EnterpriseResource(‘ResourceGuid’)/BaseCalendar” with body:
{“__metadata”:{“type”: “PS.Calendar”},
“Id”:””
}
It seems to work with 204 code status. But it isn’t change the resouce’s base calendar.
Could you help me, please?
Hi,
I haven’t tried that via REST myself and from much experience with other Project endpoints it takes some trial and error. Looking on MSDN you might be able to use the same REST syntax (using MERGE and PUT) as the PS.Calendar object:
https://docs.microsoft.com/en-us/previous-versions/office/project-javascript-api/jj669502(v%3doffice.15)
The PUT syntax would be something like:
PUT _api/projectServer/EnterpriseResource(‘ResourceGuid’)/BaseCalendar
[Other headers here]
{
“calendarid”: “GUID of Calendar”
}
I’m just guessing but that’s worth a try.
HTH,
Martin
Hello very good contribution, but I have a question, how to extract the contents of a custom field to save it in a SharePoint list
Hi, you should be able to use the out of the box List Projects Flow action to retrieve custom fields. The syntax it takes for the “Select Query” is an OData query, so through that you can select any custom field for a project. You can test your query easily enough by browsing the OData endpoint under PWA, in the browser try something like /sites/pwa/_api/ProjectData/Projects/ to see all the fields.
HTH,
Martin
Martin,
I’ve been trying with no luck to update a Task level field. I using the Update Task action and have both the Project ID and Task ID. I’ve tried various ways to update a field e.g },
“IssueKey”:”@{variables(‘IssueKey’)}”
}
That I found in another blog and this
},
“IssueKey”:”@{variables(‘IssueKey’)}”
}
However, I get the error
The parameter IssueKey does not exist in method GetById.
clientRequestId: 2eaffa04-15ce-4ace-beb1-aacead44f713
serviceRequestId: 979d129f-60a8-9000-05df-1baf84ca3223
Any pointer would be greatly appreciated.
Brian
Hi Brian,
I have not tried to update Task custom fields before, only creating tasks.. From the lack of MS documentation on the topic I can only assume that the process is the same as in this post above with updating Resource custom fields which required the use of the ProcessQuery endpoint. This is because the documented Update method (which maps to the REST, JSOM, CSOM and eventually the Flow actions) only supports OOB fields. So I guess the only option would be some reverse engineering to get the XML payload required for the ProcessQuery endpoint, then using the Send HTTP Request to SharePoint action with that method.
Sorry I can’t be any more help.
Martin
Martin,
I have been searching on how to update a Task level field using flow but very little out there Can you advise how to update a Task level field?
Thanks in advance, Brian.
HI Martin
i am trying to update the resource lookup field and passing the value as showing in your example, the flow is successful completed but its not updating the value in the fields.
i verified the action output and its showing unknown error. ErrorTypeName”: “Microsoft.SharePoint.Client.UnknownError.
From using this method many times I can suggest trying the following: Remove any unnecessary spacing or tabs from the xml, that fixes most issues. Also I’ve seen cases where the xml namespace is not correctly interpreted (why I can’t say!), so for example this works:
<Request xmlns="http://schemas.microsoft.com/sharepoint/clientquery/2009" SchemaVersion="15.0.0.0" LibraryVersion="16.0.0.0" ApplicationName="Javascript Library">
But for no good reason this doesn’t:
<Request xmlns="http://schemas.microsoft.com/sharepoint/clientquery/2009" LibraryVersion="16.0.0.0" SchemaVersion="15.0.0.0" ApplicationName="Javascript Library">
(Note the order of properties)
Like I said that makes no sense, but it fixed a problem for me once before.
HTH,
Martin
Hi Martin,
Thanks,
i tried to update the code as per your suggestion above but now its showing different error
Code:
@{items(‘Apply_to_each_2’)[‘InternalName’]}
@{items(‘Apply_to_each_3’)[‘InternalName’]}
@{items(‘Apply_to_each’)[‘ResourceId’]}
Error:
“SchemaVersion”: “15.0.0.0”,
“LibraryVersion”: “16.0.20113.12008”,
“ErrorInfo”: {
“ErrorMessage”: “Name cannot begin with the ‘1’ character, hexadecimal value 0x31. Line 1, position 95.”,
“ErrorValue”: null,
“TraceCorrelationId”: “b0e5569f-0079-b000-8e08-6fbb8131acf9”,
“ErrorCode”: -1,
“ErrorTypeName”: “System.Xml.XmlException”
},
“TraceCorrelationId”: “b0e5569f-0079-b000-8e08-6fbb8131acf9”
}
]
Hi Martin,
Thanks,
i tried to update the code as per your suggestion above but now its showing different error
Code used:
@{items(‘Apply_to_each_2’)[‘InternalName’]}
@{items(‘Apply_to_each_3’)[‘InternalName’]}
@{items(‘Apply_to_each’)[‘ResourceId’]}
Error:
“SchemaVersion”: “15.0.0.0”,
“LibraryVersion”: “16.0.20113.12008”,
“ErrorInfo”: {
“ErrorMessage”: “Name cannot begin with the ‘1’ character, hexadecimal value 0x31. Line 1, position 95.”,
“ErrorValue”: null,
“TraceCorrelationId”: “b0e5569f-0079-b000-8e08-6fbb8131acf9”,
“ErrorCode”: -1,
“ErrorTypeName”: “System.Xml.XmlException”
},
“TraceCorrelationId”: “b0e5569f-0079-b000-8e08-6fbb8131acf9”
}
]
Will this work for Document Libraries?
Hi Martin,
Thanks for the tutorial. I’ve set up a flow with this code, but when I run it, no changes are made. I tried making many alteration to see if I can get it to work. The error message I keep getting is:
“SchemaVersion”: “15.0.0.0”,
“LibraryVersion”: “16.0.20203.12005”,
“ErrorInfo”: {
“ErrorMessage”: “Unknown Error”,
“ErrorValue”: null,
“TraceCorrelationId”: “20495d9f-1006-b000-2102-44cd24394456”,
“ErrorCode”: -1,
“ErrorTypeName”: “Microsoft.SharePoint.Client.UnknownError”
},
“TraceCorrelationId”: “20495d9f-1006-b000-2102-44cd24394456”
And this is what my code currently is:
65041374-ad2f-e456-afad-00155d6c680b
fcb7e097-932f-e911-afae-00345d862c02
a98b9384-3dsb-e911-b087-00155de49008
Hi Martin,
How about setting the Timesheet Manager, Default Assignment Owner or Base Calendar?