Question: Sometimes my variable pulls in incompletely, why?
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?
Comments
-
@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?1 -
@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
0 -
@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)
2 -
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.
1 -
@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
1 -
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?
0 -
@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.
1 -
@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)": ""
}
}
0 -
@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
0 -
@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."
}
]
}
0 -
@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.
0
Categories
- All Categories
- 6 Blackbaud Community Help
- High Education Program Advisory Group (HE PAG)
- BBCRM PAG Discussions
- Luminate CRM DC Users Group
- DC Luminate CRM Users Group
- Luminate PAG
- 186 bbcon®
- 1.4K Blackbaud Altru®
- 389 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- 14 donorCentrics®
- 355 Blackbaud eTapestry®
- 2.4K Blackbaud Financial Edge NXT®
- 616 Blackbaud Grantmaking™
- 542 Blackbaud Education Management Solutions for Higher Education
- 33 Blackbaud Impact Edge™
- 3.1K Blackbaud Education Management Solutions for K-12 Schools
- 909 Blackbaud Luminate Online® and Blackbaud TeamRaiser®
- 207 JustGiving® from Blackbaud®
- 6.2K Blackbaud Raiser's Edge NXT®
- 3.5K SKY Developer
- 236 ResearchPoint™
- 116 Blackbaud Tuition Management™
- 375 YourCause® from Blackbaud®
- 160 Organizational Best Practices
- 232 The Tap (Just for Fun)
- 31 Blackbaud Community Challenges
- Blackbaud Consultant’s Community
- 19 PowerUp Challenges
- 3 Raiser's Edge NXT PowerUp Challenge: Gift Management
- 4 Raiser's Edge NXT PowerUp Challenge: Events
- 3 Raiser's Edge NXT PowerUp Challenge: Home Page
- 4 Raiser's Edge NXT PowerUp Challenge: Standard Reports
- 4 Raiser's Edge NXT PowerUp Challenge #1 (Query)
- 71 Blackbaud Community All-Stars Discussions
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- 743 Community News
- 2.8K Jobs Board
- Community Help Blogs
- 52 Blackbaud SKY® Reporting Announcements
- Blackbaud Consultant’s Community
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)
- Blackbaud Francophone Group
- Blackbaud Community™ Discussions
- Blackbaud Francophone Group