If I understand correctly, you want to add the same "LocateNumber" to each item in your original array, if that's the case, I managed to get something to work for you BUT, it does require a specific setting to be in place to work.
I created a new array object and then added each updated/new item into that new array. The Excel side of it, I haven't tackled but this answers the direct question regarding the issue you have.
The easiest way to show you is to give you the JSON definition so you can recreate it in your own tenant.
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"For_Each_Array_Item": {
"actions": {
"Append_Updated_Array_Item_Object_to_New_JSON_Array": {
"inputs": {
"name": "Updated JSON Array",
"value": "@variables('New Array Item Object')"
},
"runAfter": {
"Update_New_Array_Item_Object": [
"Succeeded"
]
},
"type": "AppendToArrayVariable"
},
"Set_Array_Item_Object": {
"inputs": {
"name": "Array Item Object",
"value": "@items('For_Each_Array_Item')"
},
"runAfter": {},
"type": "SetVariable"
},
"Update_New_Array_Item_Object": {
"inputs": {
"name": "New Array Item Object",
"value": "@addProperty(variables('Array Item Object'), 'LocateNumber', '2022054487')"
},
"runAfter": {
"Set_Array_Item_Object": [
"Succeeded"
]
},
"type": "SetVariable"
}
},
"foreach": "@variables('JSON')",
"runAfter": {
"Initialize_Updated_JSON_Array": [
"Succeeded"
]
},
"runtimeConfiguration": {
"concurrency": {
"repetitions": 1
}
},
"type": "Foreach"
},
"Initialize_Array_Item_Object": {
"inputs": {
"variables": [
{
"name": "Array Item Object",
"type": "object"
}
]
},
"runAfter": {
"Initialize_JSON_Array": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_Debug_Array": {
"inputs": {
"variables": [
{
"name": "Debug Array",
"type": "array",
"value": "@variables('Updated JSON Array')"
}
]
},
"runAfter": {
"For_Each_Array_Item": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_JSON_Array": {
"inputs": {
"variables": [
{
"name": "JSON",
"type": "array",
"value": [
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR ENBRIDGE GAS (LEGACY UNION GAS) (ENOW01)",
"stationCode": "ENOW01"
},
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR CITY OF BRANTFORD - STREETLIGHTS (BRASL01)",
"stationCode": "BRASL01"
},
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR BRANTFORD POWER (LOCAL HYDRO) (BRAP01)",
"stationCode": "BRAP01"
},
{
"initialStatus": "Cleared",
"memberName": "G-TEL FOR CITY OF BRANTFORD - SEWER/WATER/STORM (BRAS01)",
"stationCode": "BRAS01"
},
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR BELL CANADA (BCOW01)",
"stationCode": "BCOW01"
}
]
}
]
},
"runAfter": {},
"type": "InitializeVariable"
},
"Initialize_New_Array_Item_Object": {
"inputs": {
"variables": [
{
"name": "New Array Item Object",
"type": "object"
}
]
},
"runAfter": {
"Initialize_Array_Item_Object": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_Updated_JSON_Array": {
"inputs": {
"variables": [
{
"name": "Updated JSON Array",
"type": "array"
}
]
},
"runAfter": {
"Initialize_New_Array_Item_Object": [
"Succeeded"
]
},
"type": "InitializeVariable"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {},
"triggers": {
"Recurrence": {
"evaluatedRecurrence": {
"frequency": "Month",
"interval": 3
},
"recurrence": {
"frequency": "Month",
"interval": 3
},
"type": "Recurrence"
}
}
},
"parameters": {}
}
The basis of the flow is ...
- Create variables that store the existing array, the array item being processed, the new/updated item and then the new array.
- Process each item in the array, store the current item in a new variable (have included this for debugging/visibility), then update that item with the new property and finally, add it to the new array.
The expression in that formula (although found in the attached definition) looks like this ...
addProperty(variables('Array Item Object'), 'LocateNumber', '2022054487')
Critical Setting
The For Each must have concurrency switched to 1. It didn't like having it on, it kept coming up with the wrong output in the new array where items were duplicated.
Go to to the Settings on the action and make it look like the below.
The last step in the flow is just a new variable which helps visualise the output after the flow runs.
[
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR ENBRIDGE GAS (LEGACY UNION GAS) (ENOW01)",
"stationCode": "ENOW01",
"LocateNumber": "2022054487"
},
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR CITY OF BRANTFORD - STREETLIGHTS (BRASL01)",
"stationCode": "BRASL01",
"LocateNumber": "2022054487"
},
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR BRANTFORD POWER (LOCAL HYDRO) (BRAP01)",
"stationCode": "BRAP01",
"LocateNumber": "2022054487"
},
{
"initialStatus": "Cleared",
"memberName": "G-TEL FOR CITY OF BRANTFORD - SEWER/WATER/STORM (BRAS01)",
"stationCode": "BRAS01",
"LocateNumber": "2022054487"
},
{
"initialStatus": "Notification sent",
"memberName": "G-TEL FOR BELL CANADA (BCOW01)",
"stationCode": "BCOW01",
"LocateNumber": "2022054487"
}
]
I trust you can adapt this into your own flow if you think that solution is acceptable.
