In my previous post I described how to auto-create and populate Word template agreements or other similar types of standard documents in SharePoint from a SharePoint list. This post describes how to capture data in a form in Microsoft (MS) Forms – including from external users – to create a template document. It assumes you have an E3 or E5 licence.
Overview
In simple terms the model works as follows:
- A person completes a form in MS Forms. When saved, a Microsoft Flow workflow copies all or some of that data to a SharePoint list.
- A workflow created in SharePoint Designer copies all or some of the data in the list to a SharePoint library.
- After the data is copied to the SharePoint library, another SharePoint Designer workflow auto-creates and populates the Word template as described in my previous post. (This step will not be described again in this post).
Use case
This example model is based on a live working example where:
- The potential (internal or external) participants of a business area program could register an interest in participating in the program. This was named the ‘Registration of interest‘. Internal participants could register their interest directly in the internal SharePoint list.
- The data from the MS Form was copied to a SharePoint list called ‘Client Registration‘. Internal users could also register their interest by creating a new item in the list (via newform.aspx). Internal users could only see the items they created, controlled via the list settings. The list included a choice field to indicate if the person had accepted into the program; if they had, some of the data was then copied to the next stage, the ‘Client Register’.
- The Client Register list, the primary client record which included additional columns used to keep track of the progress of each client, was used to keep a record of anyone who had been accepted into the program. If the person was ready to start the program, the workflow would create the required agreement in a document library.
- The document library named ‘Client Agreements‘ was used solely to create new agreements and store signed (scanned or saved-as PDF) agreements.
In the details below I describe only three steps, from Forms (via Flow) to a SPO list, then from the list to a SPO library.
Creating the form in MS Forms
The first step is to create the form in MS Forms. Note that the person who creates the form is the default owner and administrator. To change this, see below.
Note that the format ‘type’ of each question must match the format type in the SharePoint list – text to text, date to date, choice to choice. In this example, the form has four fields:
- First name
- Surname
- Address
- Date of birth – date field
- Registration reason – ‘Learning’ or ‘Social’ choice options
By default, internal users with Office 365 licences can respond to the new form without any changes. However, it is possible to allow external users access to the form by changing the settings as shown in the screenshot below. Other options are also visible.
The ‘Share’ option in the ribbon menu does the following, once the form is created:
- Displays the shareable hyperlink to the new form.
- Allows the form to be shared as a template, allowing it to be duplicated.
- Allows the form owner to ‘share to collaborate’, which allows another person access to modify and edit the form. It is good practice to allow at least one other person access.
Create the associated SharePoint list
The associated SharePoint list must have the same columns, and column types, as the form. It may of course have other columns that do not appear in the Form – and the Form may also have other columns that aren’t copied to the list. In this example, the columns are the same.
For the list (named ‘Client Registration’), the original ‘Title’ field was renamed ‘Surname’, and the next three columns are site columns used in the client agreements library.
Create the Flow to link the Form and the list
From the Office 365 application menu, choose Flow. Note that the Flow that is created, like the Form, is ‘owned’ by the person who creates it. Once again, it may be necessary to ensure that others can access the Flow if required.
From the list of available templates, choose ‘Record form responses in SharePoint’.
When this is selected, the next page confirms that the person creating the Flow is authenticated for both Forms and SharePoint. If this is not checked, the Flow will not work. Otherwise, click Continue to create the Flow.
The blank Flow shows a number of options as shown below (described below the image).
- When a new response is submitted. This will show the list of Forms that the user has access to. In this case, the ‘New Client Registration’ form is selected.
- Do not change ‘Apply to each’.
- In ‘Get response details’, select the name of the Form – in this case ‘New Client Registration’.
- In the ‘Create item’ section, choose the Site Address from the drop down list (again, only what the user has access to), then the List name. This does NOT work for document libraries. When the list is selected, each of the available columns from the list appears below the list name.
- Click on each column field and a list of fields from the Form now appear on the right hand side. For each list column field, select the matching Form field.
- Note, for date fields, first click on the search option ‘Search dynamic content’ and type in the first letter of the field (e.g., ‘d’ for Date)
Save the Flow. Now, when a person completes the form, a new list item will appear.
You can now close both MS Forms and also Flow as these only relate to the first part.
List to library workflow – SharePoint Designer
In the example described above, a workflow created in SharePoint Designer first copies the data to one SharePoint list (Client Registration), and that data (or some of it) is copied to a new list (Client Register). Here, we will simply copy the data from the first list (Client Registration) that has been populated from a Form via Flow, to the document library (from where an agreement will be created). The workflow is almost identical whether it copies to the second list or to a library.
Note that it is not yet possible to copy data using Flow from a Form directly to a SPO document library, it must be to a list first.
For this example we are going to create a very simple one-line workflow instruction with no variables. Additional, more complex options are described further below.
- Open SharePoint Designer and click on the list from where the data will be copied to the library – in this case, ‘Client Registration’ list.
- Click on ‘List Workflow’ and give the workflow a meaningful name – for example, ‘Copy client data to client agreements’.
- Choose SharePoint 2010 workflow and click OK.
Now go to the Workflow settings to modify – if required – whether the workflow will only run manually (default) or automatically when a new item is created or modified. In most cases it will be better to run this manually as otherwise the submission of a new form will automatically created client documents, and this may not always be required.
For the purpose of this example, we will leave the workflow to run manually, which means the end user must click on the three dot ‘ellipsis’ menu and choose More – Workflows, choose the workflow and click ‘Start’.
The new SPD workflow is ready to create.
Note that we are copying data from the list to create a new document set in the library NOT a document.
Choose ‘Create List Item’ from the ‘Action’ menu.
When that option is selected, and the new option appears (as shown below), select ‘this list’ and from the drop down, choose the name of the library (in this case ‘Client Agreements’. For the ‘Content Type ID’, change the default option (if required) to the name of the document set content type (in this case ‘Client Folder’). You don’t need to change the variable.
Set the ‘Path and Name (*)’ to the Current Item and select a column. This column will be the name given to the document set so it’s probably a good idea to choose a column that makes some sense.
Now, for each of the columns that need to be copied from the list to the library, click ‘Add’ then set or ‘match’ the columns in both the list and the library document set, as shown:
Click OK and Publish. When the workflow is run (manually) by the end user, this will create a new document set but NOT the document. You will need to have and run the workflow described in my previous post to do that.
Additional options
Workflow settings – Create the document set automatically
If you select the option in the list to library SPD workflow described above to run when a new item is added (from the Flow), it will create the document set automatically. You would still have to run the document set-linked SPD workflow for the documents to be created.
Conditional start – Make the creation process subject to conditions
You may wish to make the creation process subject to certain conditions, using a combination of ‘IF – ELSE’ statements and variables. For example, you could set the workflow to run only if certain metadata conditions are met, or only if the workflow was created or modified by a specific person.
Other actions including email notifications
There is a long list of actions that can be added to the workflow. You can read all about them in this Microsoft guidance.
One common example is to send someone an email provided the email address is copied to the list. The email can contain other column content copied to the list. We used this option regularly to (a) send an acknowledgement to the person who submitted the Form (or list item) and (b) alert the a person or persons managing the list that a new item had been added.
Records about the world 