Unanswered

Excel email attachment pulls previous Form submission data

(0) Share

Report

Posted on by CaptJaneway

I have created a Quality Audit in Microsoft Forms that I've Power Automated. This is the first flow i've ever built and could use some help!

After the trigger of When a new response is submitted, the flow works as follows:

Get response details (using List of response notifications Response ID)
Add a row into a table (this works in parallel with the Update a Row below - i'm having no issues with this step)
Update a Row in a table to act as an individual scorecard - where the table has all of the Form questions and the row updates based on the corresponding Get Response Details. Basically all submissions would use the same base Excel file in Onedrive, that has a template that shows the Get Response Details and calculates a score based on an IF formula.
Copy File saves a copy of the template scorecard to the same Onedrive folder, with a unique file name based on the Get Response Details of employee name, date, and form ID.
Get File Metadata Using Path - identical to the path of the Copy File above.
Get File Content based on the ID of the Get File Metadata.
Send an email (V2) to the manager who submitted form with the excel attachment (using the Display Name of the Copy File & File Content).

As for the problem, after the Form is submitted, the email attachment reflects the File Content of the previous form submission and not the current. But what I interesting is that:

The name of the file is correct based on the Copy File step.
When I look at the actual copy of the file saved to Onedrive - it's correct with all the right info. It's only the email attachment that seems to be an issue.
When I manually re-submit the trigger from the 28 Day Run History, this time the File Content of the emailed scorecard is accurate. Even when it's re-submitted seconds to minutes of the initial submission.

To try to fix:

Added in a Delay (30 seconds to 2 minutes) at various steps thinking that perhaps the flow was completing too quickly - it was copying the file and sending the emailed attachment before the system could update the table. But this had no effect.
Used different dynamic content for the Get File Metadata/Content steps. In addition to ID, I also tried by File Path, Name, & Etag. I also tried the ID of the Copy File instead of the File Metadata. These had no effect.
Did the Copy File first - meaning I saved a copy of the initial blank scorecard to a unique file name and then did Update a Row based on said copied file. But because Update a Row works can only work off one single file and does not have an option for File Path - I could only use ID of Copied File, which did not work.
Tried using Sharepoint instead of Onedrive - same deal.
Used Share Link to Web URL - but when you click the link - it also takes you to a form with the right file name, but the previous submissions file content.

Ideally I need the initial Form submission to show the right data in the emailed scorecard the first time, because I'm not going to go through and manually re-submit the trigger each time a new form is submitted.

Any help and suggestions are greatly appreciated!!

Categories:

Building flows

Flow1.PNG

Flow2.PNG

I have the same question (0)

All responses (9)

Answers (1)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

Where did the Apply to Each come from?

EDIT: I looked up some previous questions and the Apply to Each on a Form submission seems to be older an no longer used. Can you find a way to get it out of the Apply to Each?
https://powerusers.microsoft.com/t5/Building-Flows/Forms-List-of-response-missing/td-p/715226

https://powerusers.microsoft.com/t5/Building-Flows/quot-List-of-response-notifications-quot-is-missing/td-p/406529

Can you show the Key value of your Update a Row as well as a screenshot of your Excel table?

Was this reply helpful? Yes No
CaptJaneway 13 on at

Like (0)

Report

I've tried rebuilding and each time I select Get response details and the output, it automatically nests it in Apply to Each. I also just tried doing the action of Update a Row outside of the Apply to Each, but it won't supply the form response data if I do.
See the Key Value of Update a row & my Excel table. Essentially I exported the Form questions to Excel and added it as a table to the scorecard. I also manually added in Table ID so I could add in a static key value that does not change. This table would normally be hidden so the end employee doesn't see it.
Thank you for your help!!

FlowExcelTable.PNG

FlowExcelUpdateaR...

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Ok thank you, I was curious about the need to Update a row.
Wouldn't all form responses just add a new row? You aren't going to Re-Submit a form. So the flow would always be triggered on a form submission which would be a new ResponseID which would add a new row.

Do you also plan on the new excel created document to have All responses? Since it will continually add rows each time it is submitted?

I'm working on an example, I just want to make sure I have to process to be the same.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Oh I am just realizing something.

Do you want it to copy a file and send an email ONLY when a row is updated?
Copy File with Update Only
OR
Do you want it to copy a file and send an email when a row is added OR a row is updated?
Copy File Add or Update

Let me know if that is the issue? Because your example will only copy a file if the row is updated since the branching is only on the update a row side.

Was this reply helpful? Yes No
CaptJaneway 13 on at

Like (0)

Report

I do have another Excel document for responses (that the Add a Row feeds to). This is updating with no issue. I plan to eventually use this to pull out historical data - like average score, most commonly missed question, etc.

The Update a row is solely for the scorecard template itself. I need something that I can have sent to the end employee that shows the question, the selection their manager chooses in Form, and the final score as calculated based on Get Response details. As far as I can tell, this part of the flow is working correctly. I've tested probably 50 form submissions now and the flow is ultimately successful - it's just the email attachment shows the wrong form data.

I only mentioned re-submitting manually, as when I do so, the email file content is accurate. When I check the Scorecard.xlsx after I do a test Form submission, the correct info is all there and the score is correct. Even the copy of the file saved to Onedrive with is accurate when I open it. It's the strangest thing and why i'm so baffled that the copy sent via email attachment has the wrong submission info. Hopefully i'm explaining the issue well, but let's say a manager completes the Form today on John Smith - Form ID 55. They get the automatic email with an attachment that is named John Smith_2023-02-14_55.xlsx. But when you actually open the excel file - all of the data pulled over from the table is actually for Form ID 54.

Was this reply helpful? Yes No
CaptJaneway 13 on at

Like (0)

Report

Sorry - I didn't see this reply before responding! Yes - I only want the copy of the file after the Update a row. Since Add a Row and Update a row affect two different excel files.

Was this reply helpful? Yes No
CaptJaneway 13 on at

Like (0)

Report

Just in case - here is a screenshot of my Add a row into a table vs Update a row. The table name itself is identical, but they are tables on two different Excel docs.

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (1)

Report
I am annoyed with this issue. I have set it up the same way you have and got the same results. The file attached to the email will not be updated correctly. The new copied file will be updated (kindof).

The new copied file with the updated row, when opened in file explorer, shows the last updated row incorrectly for about a second, and then it will populate with the correct information. It is almost like the data is stored within the file correctly, but it just needs to be opened up to fully refresh.

I have tried multiple methods to get this to work properly and the only way I am able to get a consistent result is if I put a delay of at least 3 minutes after the row is updated.
3 minutes seems to be the minimum amount of delay to get consistent results and I'm assuming longer would be ideal.
When I set the delay for 2 minutes, it would work sometimes correctly and sometimes not.
DELAY WILL NOT WORK WITH RAPID FORM SUBMISSIONS WITHIN 3 MINUTES OF PREVIOUS SUBMISSION
The update row has already happened from the next submission while the previous submission is waiting.
I'm putting an example of my flow below, but it is nothing much different. It only has the additional delay step.
Delay After Update

I hope this helps,

Below are all of the things I tried to do differently and could not get consistent results. Either the email file was still using the older information, or the attachment would actually have the correct information but it would not perform correctly every time:
Instead of Copy file, I Created a new File and then inserted the Content.
Instead of using the New File Content in the email attachment, I used the original source excel file as the content.
Tried to put a List Rows of Excel Table step after the Update a row hoping that would trigger the file to update properly.
Created a Do Until loop that constantly Get Row from excel and check the Response ID column until it matched the new response ID.
Copied the file then used an Update File to attempt to update the new copied file with the source data.
Put copied files into a separate folder hoping that would trigger an update file.
Turned off Asynchronous Pattern in the update row Settings attempting to prevent the flow moving without file being completely updated.
Put flow steps in different sections of the flow as well as created different Scope sections that would only run after the first Scope completed.
From my testing the only thing that helps is time delay.

Was this reply helpful? Yes No
CaptJaneway 13 on at

Like (0)

Report

Thank you so much for your time and assistance on this! I figured a delay would be the workaround - but didn't go past 2 minutes. I will add and do some further testing.

Was this reply helpful? Yes No