cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Optimize updateCurrencyProperty (or updateTextProperty) POST to Project Online for multiple custom fields

Currently I have a working solution but I want to optimize it.

Background: I have projects in Project Online.  I have financial data in an Excel Spreadsheet from accounting.  I want to push the data from Excel into MS Project Enterprise Custom Fields (ECF) of the same name.

 

Discovery: I learned I can execute For Each project and use updateCurrencyProperty to pass data from the financial spreadsheet.  I learned I needed to add a delay b/c if I just execute the update operation to one ECF, then the next ECF, and continue through each ECF then Project Online cannot keep up.  The reason is because the update operation executes a "Project Check In" after each update.  Saw this in the Project Online logs.  This is not optimal to update one value, wait for a check-in, then update the next value for approximately 20 values.

 

Question: Is there a way, to pass multiple updates in one updateCurrencyProperty call?  In my example below, is there a correct syntax where I can have:

{"projectid": "12345",

"propertyid":"d88blur",

"value":"value for d88blur",

"propertyid":"848blur",

"value":"value for 848blur"}

Ideally I would like to pass about 10 updates in one updateCurrencyProperty call.  Can updateCurrencyProperty handle multiple updates and I just dont have my syntax correct when it builds the JSON call?

 

Question 2: Is there a different way to update Enterprise Custom Fields using straight JSON?  I saw a hint on docs.microosft.com using a dictionary but couldn't understand it enough to progress it (Bulk update custom fields and create project sites from a workflow in Project Online).  If that's the correct way then I will keep trying to get it to work.  I started with the @SamPo recommendation for ProcessQuery but could never get the Method Id's to match for multiple values either so I went the inefficient route posted below. 

 

Thank you for any assistance.  If Question 1 can work with a syntax change that would be my preferred option.  If I'm back to Question 2 and building a dictionary and using ProcessQuery, I can go back to trying to get it to work.  I'm looking for advice to improve.

 

 

update multiple currency values.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Optimize updateCurrencyProperty (or updateTextProperty) POST to Project Online for multiple custom fields

I ended up going a different direction based on something which caught my eye in a Paul Mather post.  I can say this does a better job with Project Online because its not constantly opening and closing the project like updateCurrencyProperty did.  It updates a ton of custom fields at once which is what I wanted.

 

Here is my optimized solution:

Set a recurrence, set an array to store the names of all the projects I update, list all the projects from Project Online, list all the data in the financial spreadsheets.  This builds all the arrays I need. 

Full Flow.png

Then for each project, I look to see if its already checked out and if its 100% complete (no need to try and update files which are checked out and don't waste time on completed projects)

If it passes that, I filter the spreadsheet based on the Project ID.  If the length is zero it means there was no financial match so move on to next project.  If they match, it means there is a valid entry in the spreadsheet with data.  So check out the project, update the fields, wait 5 second to make sure Project Online has finished, check it back in, and write some project information into an array for emailing.  Rinse and Repeat for each project.  

For Each 1.png

For Each 2.png

 

These are the details of how to update all the Project Enterprise Custom Fields.  Build a dictionary and use the URI /Draft/UpdateCustomFields with the project ID.  Follow that format {"Key": "Value": "ValueType":} and then comma after each one except the last one.  My screenshot was too long but it ends with {"Key": "Value": "ValueType":} ]}  (Bracket ends the array and curlybrace ends the whole body)

custom Field Dictionary.png

 

Sample Body: body('Filter_Financial_array')?[0]['Capex Budget']

addDays('1899-12-30',int(body('Filter_Financial_array')?[0]['Capex Completion Date']),'yyyy-MM-dd')
 
Let me know if you have any questions on what I did or any other optimizations.  Enjoy.

View solution in original post

3 REPLIES 3
Highlighted
Frequent Visitor

Re: Optimize updateCurrencyProperty (or updateTextProperty) POST to Project Online for multiple custom fields

I ended up going a different direction based on something which caught my eye in a Paul Mather post.  I can say this does a better job with Project Online because its not constantly opening and closing the project like updateCurrencyProperty did.  It updates a ton of custom fields at once which is what I wanted.

 

Here is my optimized solution:

Set a recurrence, set an array to store the names of all the projects I update, list all the projects from Project Online, list all the data in the financial spreadsheets.  This builds all the arrays I need. 

Full Flow.png

Then for each project, I look to see if its already checked out and if its 100% complete (no need to try and update files which are checked out and don't waste time on completed projects)

If it passes that, I filter the spreadsheet based on the Project ID.  If the length is zero it means there was no financial match so move on to next project.  If they match, it means there is a valid entry in the spreadsheet with data.  So check out the project, update the fields, wait 5 second to make sure Project Online has finished, check it back in, and write some project information into an array for emailing.  Rinse and Repeat for each project.  

For Each 1.png

For Each 2.png

 

These are the details of how to update all the Project Enterprise Custom Fields.  Build a dictionary and use the URI /Draft/UpdateCustomFields with the project ID.  Follow that format {"Key": "Value": "ValueType":} and then comma after each one except the last one.  My screenshot was too long but it ends with {"Key": "Value": "ValueType":} ]}  (Bracket ends the array and curlybrace ends the whole body)

custom Field Dictionary.png

 

Sample Body: body('Filter_Financial_array')?[0]['Capex Budget']

addDays('1899-12-30',int(body('Filter_Financial_array')?[0]['Capex Completion Date']),'yyyy-MM-dd')
 
Let me know if you have any questions on what I did or any other optimizations.  Enjoy.

View solution in original post

Highlighted
Frequent Visitor

Re: Optimize updateCurrencyProperty (or updateTextProperty) POST to Project Online for multiple custom fields

@BillPricePMP 

 

Hello, I keep getting the below error when trying to update the custom field, this happens on the send HTTP request action. 

 

"message""The parameter customFiledDictionary does not exist in method UpdateCustomFields.
 
This is what I am using in the body: 
{"customFiledDictionary":
[{"Key":"Custom_xxxxxx","Value":"updatefromflow","ValueType":"Edm.String"}]}
 
This is what I am using in the URI :
 _api/ProjectServer/Projects('xxxxxxx')/Draft/UpdateCustomFields
 
Any help would be great!
Highlighted
Frequent Visitor

Re: Optimize updateCurrencyProperty (or updateTextProperty) POST to Project Online for multiple custom fields

@BillPricePMP 

 

Update! 

 

It worked for me!!!! can't thank you enough for making this post. 

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,202)