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

[Anonymous]

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 @Anonymous 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.

[Anonymous · Accepted solution]

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. 

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.  

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)

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.

[cwesley]

@Anonymous 

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!

[cwesley]

@Anonymous 

Update! 

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

[Birdy718]

Hi BillPricePMP,

                           Are you able to provide some more detail on this? I am looking to implement the same system that you have. Bulk updating project custom fields with financial data. Mainly in the step after you filter the financial array.

What is the purpose of checking the financial data for 0?

[Anonymous]

@Birdy718 I'm not checking to see if the Financial Data is zero, I'm checking to see if the length of the RESULT from the filter is zero.  In words...I filter the array based on a criteria, if it finds it then the length of the result would not be zero, the length would be something >0.  Length might equal 10 or 25 or 100...that part I dont care about.  But if I filter and I dont have anything in my array which meets that criteria then the length will be zero, full stop.  So that is a common technique for looking for a certain value in an array...filter it and look for zero.

[jgedward95]

Thank you so much for this, I cannot begin to thank you enough for the time you will be saving me in the future. I do have one question though. How would I format the body for updating a Lookup Table field which needs to have multiple values selected? I found the following documentation which seems to indicate I would parse the two values with ;# but I can't seem to get it correct:

Ex:

{"customFieldDictionary":
[{"Key":"Custom_Id","Value":"ValueId1;#ValueId2","ValueType":"Edm.String"}]}

Thank you again for this!