Unanswered

Match MS form responses to email records in excel without chance for user error

(1) Share

Report

Posted on by TP-05101958-0

Hello, this question is coming from a technician working in a school, any advice/input is greatly appreciated.

Situation - Once a year Parents are contacted to answer 15 specific questions regarding photo permissions for their children e.g. Agree or disagree with content being posted on social media, school websites, internal sites, news sites etc. Currently this is a manual process wherein an admin staff member will individually contact these parents via email or phone for them to answer. This method is seriously flawed and leaves much to be desired, I want to automate it.

Goal - Using our MIS, an excel spreadsheet of all parent's emails will be created and with power automate, a form will be sent to all of these email addresses asking them to answer the questions, which is then added into a seperate excel document recording their answers for staff to consult in the following format:

Student Name	Tutor Group	Parent/Carer Name	Contact Email Address	Question 1	Question 2	Question 3	Question 4
John Doe	XX	Jane Doe	Jane.Doe@Email.com	yes	no	no	yes

Progress so far - I have created two flows so far, one triggered when a new response is submitted to record the responses into the excel file that will be used for staff to consult. The other, triggered manually (temporarily for testing purposes) that will consult the excel file to email the parents with the form attached.

The Question - I want to limit the amount of user input to the absolute minimum. In an ideal world, the only details that the form will need answering is the 15 options of photo permissions. Currently the form requires the "parent" to fill in the details of their child name, tutor group and their own email address. Is there a way to pull this information from table 1 and pair it with the form responses into table 2. Alternatively remove the whole process of having 2 tables and instead have the responses as empty fields in table 1 and have it input into there while still matching the responses with the correct email.

To better explain it. I am unable to find a way to match the form responses with the correct parent's child and email address. I did look into using pre-filled form fields however struggled with it and this still leaves space for the person filling out the form to unintentionally empty the field and screw up the system.

End goal - Once a year, several hundred emails are sent out automatically to gather 15 yes/no responses which are then put into an excel table paired with the correct email address of the person who answered the questions.

If the current method is undoable any alternatives are also appreciated.

Thank,

Categories:

General topics

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Michael E. Gernaey 53,335 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

So yes (and make sure I understand) (P.S. I would use SharePoint myself versus the Excel, long list of why)

Let's say you have Table 1 and it has the Contact Information for a Parent (aka the email address).

Then let's say have 20 other columns. so a total of 21 columns in Excel.

In the other 20, you would put in columns for the 15 questions and have 5 left over for student into

Essentially you would have Column 2 (named Answer 1 Details) or whatever..

As you say, they are all blank initially.

Now when someone submits, you will do a List Rows in a Table, and the fitler is

EmailColumnName eq 'The_Dynamic_FormProperty_Submitter', which has the email address

and then you would have a condition action

the left side, would be length(Dynamic body/value from your List Rows)

middle is equals

right side 1

This checks if you got 1 row back

in the Yes side of the condition

You would then use the data from the Forms to update the excel columns.

Bingo and done :-)

Was this reply helpful? Yes No
TP-05101958-0 6 on at

Like (0)

Report

Hi Mike,

I couldn't get the filter to work on the List rows present in a table unfortunately. I did attempt a Filter array which seems to have worked however the condition would still end up false.

Alternatively i didn't use the filter to see if that would work as I dont think i fully understand why it's necessary? Wouldnt just having the Condition for "if responder is equal to ContactEmail" with contact email being the column in the spreadsheet, wouldnt that work? I tried this however and while i didn't have any errors I am still in the same boat where the Condition is ending on a false even though the email im using matches one of the emails saved on the spreadsheet.

Was this reply helpful? Yes No