CODEWITHSUNDEEP
jsonpower-automate
PeaceAndJam
PeaceAndJam

Reputation: 101

Update array column power automate flow

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.

first section

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

second part

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

Upvotes: 0

Views: 5770

Answers (1)

Gandalf
Gandalf

Reputation: 2417

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.

enter image description here

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. enter image description here

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

Upvotes: 2

Related Questions