Manage form invitations and responses

[gbenoit]

I have a situation in which I need to collect information from a specific sub-set of company employees.  Let's say I need information collected from 10 specific employees.  I collect the data via a Form and then use a Flow to send the responses to a SharePoint list.  This part works fine.  However, it is difficult to keep track of which of those 10 employees actually completed the form.  I have to manually go through the form responses and compare them to a separate list of the invited employees.  So, I tried by creating a list in SharePoint of the employees who were invited to complete the Form.  But, I can't see a way to link the form invitation to the item in the "invited employees" SharePoint list, in order to indicate whether or not the employee actually completed the form.  Any ideas?  I'm not stuck on the "invited employees" SharePoint list method, I just need a way to keep track of which employees need to complete the form, and which employees have already completed the form and which haven't, so that I know who to chase...  

[#2]

Edited: best response below. 

Would like to see this ability natively in Teams, so raise on the uservoice, but this is a great solution which has been provided for now

Best, Chris

[#3]

Hi @Christopher Hoard,

Thank you for your guidance.  Strange that more people aren't voting for this.  People must be using another avenue, rather than Forms, to collect information from their users.  In each business application for which I think Forms would be the solution, I would need this type of functionality.  So, I get stuck manually managing the responses or just not using Forms.  I also tried to see if I could auto populate a field in the form with a SharePoint line item (the invitation), so that I could use a Flow to mark it complete.  But, that doesn't seem to be an option in Forms yet either. In any case, thank you so much for the response.  Fingers crossed there's some progress on this point soon.  

Best regards,

Gwen 

[#4 · Accepted solution]

Hi Gwen (@gbenoit ), this can all be done with a Form, a single SharePoint list and a single flow in Power Automate. 

In this example the questions in the form are shown below:

Next,  create your SharePoint list which will hold the list of employee invitees and their answers to the questions on the form.

• For the title column I'll just add the title of the event.
• The Employee column is a person column so you will look up your employees from Office 365.
• The BuildingPass, Session, Diet and DietRequirements columns (all single line of text) hold the answers to the questions in the form.
  
  

We then need to add the event employee invitees into the list. Only the Title and Employee columns are filled in of course.

Now let's build the flow. The trigger is a Forms When a new response is submitted.  We then need to add an Initialise a string variable which will later hold the employees display name from the SharePoint list. Give the variable a name (I've called it varInvitedEmployee) and leave the value empty.

Then we add a Forms Get response details action, selecting the only option from the dynamic content box which is List of response notifications Response Id. 

Next add an Office 365 Users Get user profile (V2) action and select Responder's email from the dynamic content.

The next step is add a Compose control and select Display name from the Get user profile (V2) section of the dynamic content box.

We then add a SharePoint Get items action to get the list of invitees:

Add an Apply to each control and select value from the Get items section of dynamic content. 

Next, add an Append to string variable and select varInvitedEmployee from the dropdown. In the value box select Employee DisplayName from the Get items section of the dynamic content box.

Next add a Condition control. In the left hand box select varInvitedEmployee from the dynamic content. In the middle box select is equal to and in the right hand box select the output from the Compose Responder control.

What will happen is that the flow will loop through the list of attendees and will compare the display name of each row in the list with the display name of the responder. If they are not the same then we don't want it to do anything, so in the red If no channel just add a Set variable and in the value box add a null expression from the Expression tab in the dynamic content box. This will just reset the variable.

But if the display name from the SharePoint list does match the display name of the responder then we want to update the SharePoint list with the responses. So in the green If yes channel add another Set variable and add a null expression from the Expression tab in the dynamic content box to reset the variable back to null.

Then add a SharePoint Update item action, select the site and the list and in the different fields we select the ID from the Get items section of dynamic content, select Title for the title field. Then for each of the other columns we select the relevant Forms question from the Get response details of the dynamic content box.

The SharePoint list will then be updated for the employee who responded with their answers from the form:

If there aren't very many invitees then you can see quickly who has still to respond to the invitation. But if you've got a hundred or more you might want to add a view to your SharePoint list for those where, for example, the BuildingPass question is empty:

So that you don't have to manually chase invitees to complete the form, you could have another flow that automatically sends them a reminder email. For example let's suppose you want to send them an email reminder 3 days before the event if they haven't responded. The trigger would be a recurrence schedule which could set the flow to run once a day at 9am and would check the event date against today's date. If today isn't 3 days before the event then do nothing. But if it is 3 days before the event, for those employees who haven't responded it would send them a reminder email automatically. Let me know if you want screenshots of this.

Hopefully I've understood what your requirements are. But do come back with questions about any of this.

Rob

Los Gallardos

Microsoft Power Automate Community Super User