Jan 07 2020 11:25 AM
Jan 07 2020 11:25 AM
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...
Jan 08 2020 12:02 AM - edited Jan 09 2020 05:05 AM
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
Jan 08 2020 05:27 AM
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.
Jan 09 2020 04:35 AM - edited Jan 09 2020 06:25 AMSolution
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.
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: