Question: Sometimes my variable pulls in incompletely, why?

Susan Fioribello

I have built a multi-step approval process. I have an action in the last step “update a row.” For this step, I am simply pulling in the information for the 2 approvals. example:

Approval 1 - Outcome

Approval 1 - Completion Date

Approval 1 - Approver Name

Approval 1 - Comments

Approval 2 - Outcome

Approval 2 - Completion Date

Approval 2 - Approver Name

Approval 2 - Comments

I am using Dynamic content to map the fields the Excel file; however, the fields are coming in with this format:

item()?['approverResponse']

however, the completion date is pulling in as:

body('Approval 1)'?['completionDate']

What is Completion date the only field pulling in correctly?

Austen Brown

@Susan Fioribello - I recommend looking at the output of the actions that are producing the data that you want to utilize within your ‘update a row’ action. This will help you figure out what dynamic content field to include or if an expression is needed to accomplish this.

For instance, can you share the output of the action that returns Approver Response?

Susan Fioribello

@Austen Brown
Hi Austen,

The flow is triggered by submitting a form. When submitted, Approval 1 is initiated. Then there is a “For each” followed by a condition.

If the approval is rejected, then the row is updated

Here is a close up of the code:

But this one seems to be correct

Alex Wong

@Susan Fioribello
Approval that is done will have result like this:

"body": {

"responses": [

{

"responder": {

"id": "722331b3-a19d-4a20-8dae-bb96810b0ebd",

"displayName": "Responder Name",

"email": "responder@some.org",

"tenantId": "bbbbbbb-cccc-0000-aaaa-ddddbbbbbdbd",

"userPrincipalName": "responder@some.org"

},

"requestDate": "2024-08-12T21:48:58Z",

"responseDate": "2024-08-12T21:49:47Z",

"approverResponse": "Approve"

}

],

"responseSummary": "Approver: Responder Name, responder@some.org\\r\\nResponse: Approve\\r\\nRequest Date: Monday, August 12, 2024 9:48:58 PM\\r\\nResponse Date: Monday, August 12, 2024 9:49:47 PM",

"completionDate": "2024-08-12T21:49:48Z",

"outcome": "Approve",

"name": "77b29810-2c15-442c-89f0-005a34c5b208",

"title": "DIN24WT- 11/10/2024 - Event Registration Form Request Approval Needed",

"details": "approval details",

"requestDate": "2024-08-12T21:48:49Z",

"expirationDate": "9999-12-31T23:59:59Z",

"priority": "Medium"

}

note that responses is an array, so access to its properties: responder, requestDate, responseDate, and approverResponse is going to put your action into Apply to Each and the property will be listed as item()?['responder']

completionDate is not in an array, so access to this dynamic content will be more direct: outputs('Start_Approval')?['body/completionDate']

do you have ONE approval action in the flow and expect 2 people to approve, OR is it TWO approval action, person one approve, THEN a 2nd approval action go to 2nd person to approve? (this matters on how you will get the data)

Susan Fioribello

@Alex Wong

This explains a lot. Thank you.

I have 2 separate approvals that are approved by 2 different departments.

For example, Approval 1 is the “verification team”. They verify that the information in the form is correct, if not they reject and there is no second approval.

If the verification team approves, a Approval 2 is sent to the finance department. After they approve or reject the information from both approvals is updated to the row.

Alex Wong

@Susan Fioribello
then you can make use of the first() expression to get the first response in the array, as you expect only one person's response.

Approval 1 approver response/outcome: (can use either of the following)
first(outputs('approval_1_name_replaced_space_with_underscore')?['body/responses'])?['approverResponse']
outputs('approval_1_name_replaced_space_with_underscore')?['body/outcome']

Approval 1 name:
first(outputs('approval_1_name_replaced_space_with_underscore')?['body/responses'])?['displayName']

Approval 1 completion date:
outputs('approval_1_name_replaced_space_with_underscore')?['body/completionDate']

Approval 1 comments:
first(outputs('approval_1_name_replaced_space_with_underscore')?['body/responses'])?['comments']

do the same with approval 2

Susan Fioribello

@Alex Wong

I am still struggling to get the second approval to copy the approver's name and the comments. Everything else is working.

Am I missing a step?

Alex Wong

@Susan Fioribello
you will need to give more info on what you mean by “struggling to get the second approval".

if there's error (when saving flow, or when running flow), then please show the error message and/or screenshot.

Susan Fioribello

@Alex Wong
The flow itself is running successfully. I ran a test again this morning and the now everything from all of the approvals are now updating correctly with the exception of the second approval Responder Name.

It looks like the “Approved by” data is not being pulled in. I don't know why that would happen.

Here is the raw test data I put through:

{

"host": {

"connectionReferenceName": "shared_excelonlinebusiness",

"operationId": "PatchItem"

},

"parameters": {

"source": "sites/XXXXXX.sharepoint.com,2666ac85-7786-42b3-87b3-f8360c1a9674,0748092d-c5c9-45d7-8748-a928586a4da5",

"drive": "b!haxmJoZ3s0KHs_g2DBqWdC0JSAfJxddFh0ipKFhqTaXmXUf39q-XR4mdjLDll7xR",

"file": "01N64KASECIVBQAK5WARDK7F7IRL6ESBG7",

"table": "{00000000-000C-0000-FFFF-FFFF00000000}",

"idColumn": "ID",

"id": 150,

"item/Finance Approved?": "Approve",

"item/Finance Approval Date": "2024-08-15T13:38:57Z",

"item/Approved By": null,

"item/Finance Comments": "Did the comments update in Excel?"

}

}

But you can see that there is data in the Approved by field of the Approval:

The comment “Did the comments update in Excel?” did update in Excel but my name did not get updated.

In case this is helpful, here is the raw data output:

{

"statusCode": 200,

"headers": {

"Cache-Control": "no-store, no-cache",

"Pragma": "no-cache",

"Transfer-Encoding": "chunked",

"Vary": "Accept-Encoding",

"Strict-Transport-Security": "max-age=31536000; includeSubDomains",

"x-ms-request-id": "a42886cf-3a72-4779-8489-ce10cc3b2e4c;3f34b659-d139-4223-b40e-f94f66c79a19;eb11b3f3-4882-4f2f-b18a-4d828a0d5905;0764f23c-430f-41ee-bf6d-c44ff099a907",

"OData-Version": "4.0",

"X-Content-Type-Options": "nosniff",

"X-Frame-Options": "DENY",

"x-ms-tenant-id": "e97cf2ea-7bdd-45f0-882e-0f1d87edb37a",

"Timing-Allow-Origin": "*",

"x-ms-apihub-cached-response": "true",

"x-ms-apihub-obo": "false",

"Date": "Thu, 15 Aug 2024 13:38:58 GMT",

"Content-Type": "application/json; odata.metadata=minimal",

"Content-Length": "2284",

"Expires": "-1"

},

"body": {

"@odata.context": "https://excelonline-wus.azconn-wus-001.p.azurewebsites.net/$metadata#drives('b!haxmJoZ3s0KHs_g2DBqWdC0JSAfJxddFh0ipKFhqTaXmXUf39q-XR4mdjLDll7xR')/Files('01N64KASECIVBQAK5WARDK7F7IRL6ESBG7')/Tables('{00000000-000C-0000-FFFF-FFFF00000000}')/items/$entity",

"@odata.etag": "",

"ItemInternalId": "150",

"ID": "150",

"Start time": "45518.746724537",

"Completion time": "45518.7480555556",

"Email": "anonymous",

"Name": "",

"Date": "45518",

"Are you donating on behalf of an organization or group?": "No",

"Name of Organization or Group examples_x003a_ (Scout Troop 186 in New Hyde Park or The Generous Company)\\nPlease use the proper name of organization_x002e_ DO NOT use abbreviations_x002e_": "",

"Donor/Group Leader First Name": "Testing",

"Donor/Group Leader Last Name": "Tester",

"I wish to remain an anonymous donor": "No",

"Street Address": "12345 Main Street",

"City": "Queens",

"State": "NY",

"Zip Code": "12345",

"Phone number": "5554567",

"Email address": "email@rmhcnym.org",

"Program Location": "Ronald McDonald House in New Hyde Park;",

"Type of Donation (if selecting more than one category please enter value for each category in the description section_x002e_": "Meals from the Heart Dinner Program;",

"Provide description of donated items\\n- If more than one donation type was selected above, please provide the value for each type": "",

"Donation Value\\nBy providing this information you are helping RMHC NYM to reduce costs and better track our community impact_x002e_ If multiple donation types were selected above enter the total amount h_x002e__x002e__x002e_": "123",

"Click on the \\"upload file\\" button to take a picture of your donation_x002e_ ": "",

"Verified?": "Approve",

"Verified Date": "2024-08-15T13:38:12Z",

"Verified By": "Susan Fioribello",

"Verified Comments": "Testing the Approval updates from Finance",

"Finance Approved?": "Approve",

"Finance Approval Date": "2024-08-15T13:38:57Z",

"Approved By": "",

"Finance Comments": "Did the comments update in Excel?",

"RE Entered Date": "",

"Sign up for emails from RMHC NYM and stay up to date on our initiatives in New Hyde Park, Stony Brook, volunteer opportunities, family stories and more_x002e_": "No",

"RMHC NYM Staff/Volunteer Accepting In-kind Donation (Enter Name)": "Susan Fioribello (Testing)",

"Community Service (optional)": ""

}

}

Alex Wong

@Susan Fioribello
you are providing info on the excel action, which isn't useful to see what your flow problem is.

screenshot your flow actions on how it flows from approval 1 to approval 2 and where your update excel action is.

then screenshot the edit mode of the excel action, and provide the expression you used

Susan Fioribello

@Alex Wong
Hopefully this what you need and can see it. The flow is pretty large, this is the section with the two Approvals. There are several update row actions due to the various conditions, but they are all mapped the same way.

All of the fields in the Excel file update with the exception of the Approved by field.

Here is the code from the test run. Maybe this is more helpful:

{

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"source": "sites/XXXXXX.sharepoint.com,2666ac85-7786-42b3-87b3-f8360c1a9674,0748092d-c5c9-45d7-8748-a928586a4da5",

"drive": "b!haxmJoZ3s0KHs_g2DBqWdC0JSAfJxddFh0ipKFhqTaXmXUf39q-XR4mdjLDll7xR",

"file": "01N64KASECIVBQAK5WARDK7F7IRL6ESBG7",

"table": "{00000000-000C-0000-FFFF-FFFF00000000}",

"idColumn": "ID",

"id": "@triggerOutputs()?['body/resourceData/responseId']",

Here is the code for the “for each” (I don't know if it is relevant)

{

"type": "Foreach",

"foreach": "@outputs('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['body']?['responses']",

"actions": {

"Condition_1_Finance_Approves_the_gift": {

"type": "If",

"description": "Finance approved the gift.",

"expression": {

"or": [

{

"contains": [

"@item()?['approverResponse']",

"Approve"

]

}

]

},

"actions": {

"Update_a_row_3": {

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"source": "sites/rmhcnymetro.sharepoint.com,2666ac85-7786-42b3-87b3-f8360c1a9674,0748092d-c5c9-45d7-8748-a928586a4da5",

"drive": "b!haxmJoZ3s0KHs_g2DBqWdC0JSAfJxddFh0ipKFhqTaXmXUf39q-XR4mdjLDll7xR",

"file": "01N64KASECIVBQAK5WARDK7F7IRL6ESBG7",

"table": "{00000000-000C-0000-FFFF-FFFF00000000}",

"idColumn": "ID",

"id": "@triggerOutputs()?['body/resourceData/responseId']",

"item/Finance Approved?": "@body('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['outcome']",

"item/Finance Approval Date": "@body('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['completionDate']",

"item/Approved By": "@first(outputs('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['body/responses'])?['displayName']",

"item/Finance Comments": "@first(outputs('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['body/responses'])?['comments']"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness",

"connection": "shared_excelonlinebusiness",

"operationId": "PatchItem"

}

},

"metadata": {

"01N64KASECIVBQAK5WARDK7F7IRL6ESBG7": "/In-Kind Donation Receipt.xlsx",

"tableId": "{00000000-000C-0000-FFFF-FFFF00000000}",

"operationMetadataId": "ac868711-23cc-44ff-bc70-7b92d8cbabb1"

}

},

"Condition_3": {

"type": "If",

"expression": {

"and": [

{

"equals": [

"@body('Get_response_details')?['re007e9df219c48c5854dd8b03a13bf25']",

""

]

}

]

},

"actions": {

"Send_an_email_(V2)_Thank_You_for_Your_Donation": {

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"emailMessage/To": "@body('Get_response_details')?['raf9c6d467c414dddab0763c04c607195']",

"emailMessage/Subject": "Thank You For Your Donation!",

"emailMessage/Body": "<p>Dear @{body('Get_response_details')?['rc445a659195843aabf4ac6ed9f77663a']} @{body('Get_response_details')?['r86dafd92b1aa41b18573a0099e1d48e9']}:</p><p>@{variables('Email Content Accepted')}</p>",

"emailMessage/From": "mail@rmhcnym.org",

"emailMessage/Bcc": "@variables('BBC Mailing List')",

"emailMessage/Importance": "Normal"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365",

"connection": "shared_office365",

"operationId": "SendEmailV2"

}

},

"metadata": {

"operationMetadataId": "55049f13-3f26-4682-bd7d-8f9d076914f6"

}

}

},

"else": {

"actions": {

"Condition_4_community_service": {

"type": "If",

"expression": {

"and": [

{

"equals": [

"@body('Get_response_details')?['r794be25b1269438189ab5fb4d4bc202a']",

""

]

}

]

},

"actions": {

"Send_an_email_(V2)_Thank_You_for_Your_Donation-Org_or_Group": {

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"emailMessage/To": "@body('Get_response_details')?['raf9c6d467c414dddab0763c04c607195']",

"emailMessage/Subject": "Thank You For Your Donation!",

"emailMessage/Body": "<p>Dear @{body('Get_response_details')?['rc445a659195843aabf4ac6ed9f77663a']} @{body('Get_response_details')?['r86dafd92b1aa41b18573a0099e1d48e9']} of @{body('Get_response_details')?['re007e9df219c48c5854dd8b03a13bf25']}:</p><p>@{variables('Email Content Accepted')}</p>",

"emailMessage/From": "mail@rmhcnym.org",

"emailMessage/Bcc": "@variables('BBC Mailing List')",

"emailMessage/Importance": "Normal"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365",

"connection": "shared_office365",

"operationId": "SendEmailV2"

}

},

"metadata": {

"operationMetadataId": "55049f13-3f26-4682-bd7d-8f9d076914f6"

}

}

},

"else": {

"actions": {

"Send_an_Acknowledgement_email_(V2)_2_to_Donor_Org_Group-community_service": {

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"emailMessage/To": "@body('Get_response_details')?['raf9c6d467c414dddab0763c04c607195']",

"emailMessage/Subject": "Thank You For Your Donation!",

"emailMessage/Body": "<p class=\\"editor-paragraph\\">Dear @{body('Get_response_details')?['rc445a659195843aabf4ac6ed9f77663a']} @{body('Get_response_details')?['r86dafd92b1aa41b18573a0099e1d48e9']} and @{body('Get_response_details')?['r794be25b1269438189ab5fb4d4bc202a']} of @{body('Get_response_details')?['re007e9df219c48c5854dd8b03a13bf25']}:</p><p class=\\"editor-paragraph\\">@{variables('Email Content Accepted')}</p>",

"emailMessage/From": "mail@rmhcnym.org",

"emailMessage/Bcc": "@variables('BBC Mailing List')",

"emailMessage/Importance": "Normal"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365",

"connection": "shared_office365",

"operationId": "SendEmailV2"

}

},

"metadata": {

"operationMetadataId": "ab1fd5b8-117a-44ba-96b5-39807a5938c3"

}

}

}

},

"metadata": {

"operationMetadataId": "82768ee7-85ee-4b44-ab9f-11b110d25b6a"

}

}

}

},

"metadata": {

"operationMetadataId": "4a23a5ca-95a6-4bad-b994-df6877021f17"

}

}

},

"else": {

"actions": {

"Send_an_email_(V2)_2_Donation_Not_Accepted": {

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"emailMessage/To": "@body('Get_response_details')?['raf9c6d467c414dddab0763c04c607195']",

"emailMessage/Subject": "Action Needed: Additional Info Required for RMHC NYM",

"emailMessage/Body": "<p>@{variables('Email Content Not Verified')}</p><p>@{item()?['comments']}</p><p>@{outputs('Compose_Email_Not_Accepted_Closing_Finance')}</p>",

"emailMessage/From": "mail@rmhcnym.org",

"emailMessage/Bcc": "@variables('BBC Mailing List')",

"emailMessage/Importance": "Normal"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365",

"connection": "shared_office365",

"operationId": "SendEmailV2"

}

},

"metadata": {

"operationMetadataId": "c548bff5-6f13-4a65-ba10-7d6275fcbf22"

}

},

"Update_a_row_4": {

"type": "OpenApiConnection",

"inputs": {

"parameters": {

"source": "sites/rmhcnymetro.sharepoint.com,2666ac85-7786-42b3-87b3-f8360c1a9674,0748092d-c5c9-45d7-8748-a928586a4da5",

"drive": "b!haxmJoZ3s0KHs_g2DBqWdC0JSAfJxddFh0ipKFhqTaXmXUf39q-XR4mdjLDll7xR",

"file": "01N64KASECIVBQAK5WARDK7F7IRL6ESBG7",

"table": "{00000000-000C-0000-FFFF-FFFF00000000}",

"idColumn": "ID",

"id": "@triggerOutputs()?['body/resourceData/responseId']",

"item/Finance Approved?": "@body('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['outcome']",

"item/Finance Approval Date": "@body('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['completionDate']",

"item/Approved By": "@item()?['responder']?['displayName']",

"item/Finance Comments": "@item()?['comments']"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness",

"connection": "shared_excelonlinebusiness",

"operationId": "PatchItem"

}

},

"metadata": {

"01N64KASECIVBQAK5WARDK7F7IRL6ESBG7": "/In-Kind Donation Receipt.xlsx",

"tableId": "{00000000-000C-0000-FFFF-FFFF00000000}"

}

}

}

},

"metadata": {

"operationMetadataId": "1303efed-0451-4bf2-8e63-aa77d8910ca2"

}

}

},

"runAfter": {

"Start_and_wait_for_an_approval_from_Finance_No_Picture": [

"Succeeded"

]

},

"metadata": {

"operationMetadataId": "50f7003f-3a5f-4c9d-bba9-6bf96347c7d3"

}

}

"item/Finance Approved?": "@body('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['outcome']",

"item/Finance Approval Date": "@body('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['completionDate']",

"item/Approved By": "@first(outputs('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['body/responses'])?['displayName']",

"item/Finance Comments": "@first(outputs('Start_and_wait_for_an_approval_from_Finance_No_Picture')?['body/responses'])?['comments']"

},

"host": {

"apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness",

"connection": "shared_excelonlinebusiness",

"operationId": "PatchItem"

}

},

"metadata": {

"01N64KASECIVBQAK5WARDK7F7IRL6ESBG7": "/In-Kind Donation Receipt.xlsx",

"tableId": "{00000000-000C-0000-FFFF-FFFF00000000}",

"operationMetadataId": "ac868711-23cc-44ff-bc70-7b92d8cbabb1"

}

}

This is from the Parameters section of the “for each”

{

"foreachItems": [

{

"responder": {

"id": "503bf3b4-38ee-4435-b57f-d735009e1e46",

"displayName": "Susan Fioribello",

"email": "sfioribello@xxxx.org",

"tenantId": "e97cf2ea-7bdd-45f0-882e-0f1d87edb37a",

"userPrincipalName": "sfioribello@xxxx.org"

},

"requestDate": "2024-08-15T19:15:20Z",

"responseDate": "2024-08-15T19:15:43Z",

"approverResponse": "Approve",

"comments": "This comment should now be updated in Excel."

}

]

}

Alex Wong

@Susan Fioribello
images are way too blurry to make anything out. @Crystal Bruce really need better image pasting on these post.

I will check the code when i get a chance, but probably best if you can attend next Tuesday power auotmate user group and can help you diagnose it there.