Update array column power automate flow

Question

In my cloud flow, I need to check each of the date columns and update "ActiveDate" with the column name that is within 7 days of today's date.

So for the sample below I'd hope to populate ActiveDate with the value "Date1"

{
    "RPTID": "RPT4072",
    "Date1": "2021-07-02",
    "Date2": "2021-09-17",
    "Date3": "2021-10-22",
    "Date4": "2022-02-25",
    "Date5": "2022-05-20",
    "Date6": "2023-03-07",
    "ActiveDate": "",
    "ProjectManager": "TBC",
    "QuantitySurveyor": "Mr A / Mr B"
  }

I feel like I'm going round in circles trying to figure out how to do this. I've tried IF syntax and also the Condition action but can't seem to get the correct logic. Any help is much appreciated.

Below is the source and SELECT from excel.

I have then added a loop and tried to update the date calculation with a variety of options and none have worked.

The screenshot above throws this error:

InvalidTemplate. Unable to process template language expressions in action 'Compose_2' inputs at line '1' and column '43252': 'In function 'formatDateTime', the value provided for date time string 'Date3' was not valid. The datetime string must match ISO 8601 format.'.

Here are a couple errors that let me on to trying the integer pointer above:

'The template language expression 'div(sub(ticks(formatDateTime(body('Select')[item()], 'yyyy-MM-dd')),ticks(formatDateTime(substring(utcNow(),0,10),'yyyy-MM-dd'))),864000000000)' cannot be evaluated because property 'Date1' cannot be selected. Array elements can only be selected using an integer index.

'The template language expression 'div(sub(ticks(formatDateTime(variables('dates')[item()], 'yyyy-MM-dd')),ticks(formatDateTime(substring(utcNow(),0,10),'yyyy-MM-dd'))),864000000000)' cannot be evaluated because property 'Date1' cannot be selected. Array elements can only be selected using an integer index.

Unable to process template language expressions in action 'Compose_4' inputs at line '1' and column '43252': 'The template language expression 'items('Apply_to_each')?[item()]' cannot be evaluated because property 'Date1' cannot be selected. Property selection is not supported on values of type 'String'

I feel like it needs some sort of item().value but for the life of me I can't get it..

1) Do you always get all 7 keys (Date1...6 and ActiveDate) in your JSON structure or is it dynamic? 2) What to do if none of the date met your criterion? — Gandalf
– Gandalf, Commented Jul 9, 2021 at 10:37
Hello! Yes the columns are static so all 7 keys will be present. One of the 6 dates will definitely meet the criteria as it has previously been filtered to ensure that. — PeaceAndJam
– PeaceAndJam, Commented Jul 9, 2021 at 11:05
You totally misunderstood the concept of item() and variables here. You see, item() will give you 'Date1', 'Date2' ... etc. as the loop go over Date array. We wanted to get value from variable "Result" so we wrote like variables('Result')[item()] which is equivalent to variables('Result')['Date1'] and so on for all the dates. Now your "variable('Result')" should be replaced with "items('Apply_to_each_2')" inside my date diff formula; rest all remains same. — Gandalf
– Gandalf, Commented Jul 13, 2021 at 18:02

Gandalf · Accepted Answer · 2021-07-09 12:18:14Z

3

The tricky part is to calculate the date difference for each date and then update the property in your json object.

Date Difference Calculation

div(sub(ticks(formatDateTime(variables('Result')[item()], 'yyyy-MM-dd')),ticks(formatDateTime(substring(utcNow(),0,10),'yyyy-MM-dd'))),864000000000)

Update Property in Json object

setProperty(variables('result'), 'ActiveDate', variables('FinalDate'))

Let's assume that you have your json object in a variable. I am initialising a variable Result for that. Apart from this you need one variable FinalDate for storing the desired date and another variable Dates for storing your 6 date keys.

Now you need to loop over all date keys and find the date difference for each date and check if it falls in the range -7 <= Dif <= 7. If yes assign the FinalDate object to that particular date. Check the image below.

Now you just need to update you original json object. You ca simply use setProperty method to do so as depicted below.

answered Jul 9, 2021 at 12:18

Gandalf

2,4372 gold badges16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Ethan Over a year ago

Excellent job Gandalf, I just created a very inefficient workflow that took my a while to figure out and create. Your process is very efficient!

PeaceAndJam Over a year ago

Hi Gandalf. Firstly, thank you very much for taking the time to answer. I have implemented your solution and only have one question: is there a way to set the FinalDate variable to be "Date1" instead of "2021-07-02".

PeaceAndJam Over a year ago

Also, one more question :) When initialising the Result variable, instead of the hardcoded values would putting "item()?['Date1']" etc mean that it would iterate? I've tried to implement it but think there is a missing step.

Gandalf Over a year ago

1) Yes, you can simply assign FinalDate variable to item() instead of variables('Result')['item'] inside Loop condition. 2) item() is a property inside Loop and it iterate over array only. Object can't be iterated yet in Power Automate. I initialised variable with hardcoded values to do this PoC. In you case this json object should be coming dynamically from somewhere else, maybe output of previous step or http response.

PeaceAndJam Over a year ago

Hi Gandalf. Thank you, using item() worked for me. My source data is multiple rows that have been pulled from an excel using a SELECT action. Should the setting of the variables be happening in a bigger Apply To Each in the order to iterate through each row in my source data?

|

Collectives™ on Stack Overflow

Update array column power automate flow

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related