cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
Helper I
Helper I

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
Helper I
Helper I

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

@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!

@BillPricePMP 

 

Update! 

 

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

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (26,060)