Introduction
Before creating a flow, first, create a SharePoint list in your SharePoint site collection, with the following columns and data types. Thanks to Reza for explaining each minute detail of the actions. You can learn more from Reza’s youtube channel in the references section.
The completed form in SharePoint should look like below,
Steps
Please follow the below steps to record the form responses in the SharePoint list using Flow. To keep it a little simple, let's start building the flow using the template.
Step 1
Go to the URL https://flow.microsoft.com using your subscription from your organization or using a subscription from the developer portal.
Step 2
Select the option, create a flow and select ‘Template’.
Step 3
Search for ‘Form’ and select the template ‘Record form responses in SharePoint’.
Step 4
It will ask for your consent to connect to forms and SharePoint using your email account. Click on ‘Continue’.
Step 5
Update the title of ‘Flow’ on the top left side. Here I have updated it as ‘Record Workshop Feedback’. If you observed, it created a skeleton of the flow.
Step 6
Configure the flow to collect the responses from the Form, and then save it to the SharePoint list. Select the form that you created before. Here I am selecting ‘Workshop Feedback’ and selecting the list as ‘Workshop Feedback’.
- Select the ‘Form Id’ in both ‘When a new response is submitted' and in ‘Get response details’ as ‘Workshop Feedback’.
- Select the site where the response needs to be recorded. Here I am selecting the sites from my developer tenant.
- Notice that when you select the site and the list, it will have all the properties populated in flow designer dynamically.
Step 7
For the list title, I am selecting the ‘Responder’s email’ – ‘Submission Type’ to create a unique title value. These are dynamic values populated under ‘Get Response Details’.
The final ‘Title’ configuration should look like below.
Note
Each Output from the Form Responses is of type ‘String’. you should be able to configure almost anything of type ‘Single line’ and ‘multiline’ in the SharePoint list.
Notice that the output of the ‘Likert’ datatype response from the form is of string, which you can easily configure the response to record in the SharePoint list.
Step 8
You should be able to configure the SharePoint list item values easily, except for the columns of type choice, Datetime, and Rating.
The reason being the form response output is not understood by SharePoint. As we know, the output coming from ‘Form Responses’ is of type string. Now we need to transform the responses of the type which SharePoint understands.
| Column Type |
Format |
| Date Time |
Date Time |
| Choice (single, multichoice) |
Array |
| Rating |
Number |
Let's use compose which comes in handy while transforming the ‘Form Responses’
Step 9
Now for Datetime, let's use compose and copy-paste the value from compose to ‘Create Item’ action field ‘Training Date’.
Step 10
For the ‘Overall rating’ column, since it is expecting the numeric value, let's convert the form responses output to numeric value using the below expression. For this, you need to use another ‘Compose’ action rename it to ‘compose – overall rating’ and configure the expression to use float(form responses for the question ‘your overall rating for the Workskop’.
Click anywhere outside the action and your output should appear like this.
Step 11
Configure this ‘Compose – Overall rating’ outputs to ‘Create Item’ action to record the overall rating response in the SharePoint list.
The final configuration output for ‘overall rating’ should look like below,
Step 12
Similarly, the recommendation for the SharePoint list is expecting numeric. Transform the same way using the above steps for this ‘Recommendation’ column. Here I have used expression int(form outputs of how likely you recommend the workshop to a friend or colleague’?
The final configuration for the ‘Recommendation’ field under the ‘Create list item’ action should look like below.
Step 12
Now for the value ‘Avg Session time feedback’ if you select the drop-down it will have all the existing choice values. We do not want to hardcode, instead, we need to record the values getting from the form. Click on ‘Custom Value’ and select the form output response ‘Average suggested time for the Workshop.
The final configuration for the property Avg Session time feedback value should look like this.
Step 13
Now, let's configure the multichoice responses to the SharePoint list. SharePoint expects the format ‘Array’. But the form response output is of type string.
Let's use another compose and rename it to ‘Compose – best modules’ and since this is a multi-choice field there could be more than one value. convert the output of the best modules form response to JSON using JSON() expression. JSON(outputs from form responses ‘what training modules you liked most’).
Step 14
Now convert the JSON output to of type array, where SharePoint understands. for this use the ‘Select’ action inflow and transform the JSON to array output. Have the ‘From’ field configured to outputs from ‘Compose-Best Modules’?
Enter key as ‘Value’ and the value as expression item().
The final configuration of ‘Select’ should look like this.
Step 15
Go to create an action item, and for the ‘Favorite Modules value’ click on ‘Switch to input entire array’. select the output from the ‘Select’ data operation.
The final configuration for your ‘Favorite Module value’ should look like this.
Step 16
Now we are left with adding attachments to the list of items tracked from the responses. For this use, another compose operation and select the output from the form responses ‘upload the completed lab modules’.
The final compose action for form response attachment outputs should look like below,
As you may have noticed from the previous article, all the form attachments are stored in created users one drive. Now we need to grab the attachments from the OneDrive and then attach to SharePoint lists. Let’s see how to do that.
Step 17
Add an action of type ‘Logic’ after creating list item, and then select ‘Apply to each ‘ rename this to ‘Apply to each – attachments’.
Configure the ‘Select output from previous steps to the outputs from the previous ‘compose – attachments’ action which is a JSON file.
Step 18
The JSON contains ‘id’ which is unique to each attachment. You can use this property to get the content from ‘OneDrive for business. Now select the ‘Get file content’ action which is from OneDrive for the Business connector.
configure the ‘File’ property to item()?['id'].
Note: item() gets the context of the current item in the loop.
Step 19
Now inside the ‘Apply to each – attachment’ add an action ‘Add Attachment’ from ‘SharePoint’ Connector.
Site Address - select the site
List Name - Select the name from the list.
ID - this will be the ID from the ‘Create Item’ action outputs
Filename - This will be the name value from the attachments. Use expression item()?[‘name’].
Since the JSON output attachments have the value ‘name’ as you might have noticed from JSON output.
File Content
This is the output of getting file content action. Below is the screen capture for reference.
Below is the output of how it should look for the ‘Add Attachment’ action.
Testing the Flow
Step 1
Now, select ‘Test’ on the top right side of the flow designer, and manually test the flow.
Step 2
Go to your form and submit the response.
Step 3
Observe that if you have followed all the above steps, your flow should successfully run.
Step 4
Now observe the choice values, single-line text values, and date values, and attachments. It should appropriately record all the responses.
Conclusion
Thus, in this article, we have seen how to record the form responses of all supported datatypes in SharePoint lists. Hope you find this helpful. If you get stuck, you can refer to the flow package I have attached to compare with your flow design.
References
- https://www.youtube.com/watch?v=kbqXsKPTmZk
- https://www.youtube.com/channel/UCvBYTqRx-n_8KzFO0MJlUVw
- https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/
IT SolutionPosted Feb 24, 2023, 2:55 AM
Thank you for the sharing.