A colleague asked if we could make his process easier by collecting requests through Forms instead of completing a Word document and then emailing it.
After discussing his process it appeared that the regular Forms output (the graphs and the Excel file) was not sufficient for his ongoing process. So we decided on a different approach:
- use Forms to collect the requests from colleagues across the organization.
- use Power Automate to send the responses into a List in a (restricted) SharePoint site. We will not go into details about the workflow itself, but please be aware it is part of the process.
- the team can process the requests from their SharePoint site.
This has advantages and some risks:
- 👍 Forms has nice interface for the requester
- 👍 Requests can be made from phone if desired
- 👍 Form can be accessed by QR code if needed
- 👍 Branching in Forms (skipping questions based on earlier answers) is possible, making the workload for the requester as small as possible
- 👍 As the workflow is user-based, there is no need to manage extra permissions to the SharePoint list (the requests can be entered by more people than currently have access to the SharePoint site)
- 👍 Many options to slice and dice the requests into reports: open and completed, most popular request types, how many requests in a year, etc.
- 👎 The workflow can break
- 👎 Workflow and list need to be adjusted when the Form changes
Using the Excel file to create the List
I wanted to see whether I could use the Excel file from the Form as the basis for the List, as I was curious if this would save time.
- I created a Form, using a sample of each question/response option in Choice, Text, Rating. (In my next post I will use the other response options)
- I completed one request to create the Excel
- I downloaded the Excel file to my PC – you can also save it to OneDrive
- I then went to the Lists homepage, clicked on “New List” and then “From Excel”
- I uploaded the Excel (or select from OneDrive)
- For each column I had the option to “Do not import” or check and adjust the column type
- As any List needs a Title field, the system proposed to use the “Start time” (which is unique, so although not very informative, I used it). I can imagine for a real life situation, you will need to think about this.
- When I was done adjusting column types, I clicked “Next” and then I could adjust the title, add a list description, select colour and icon, and determine whether it will “live” in my OneDrive (personal list) or in a SharePoint site.
- I then checked the result
👉 The columns proposed were moderately adequate. The Ratings were all Number columns (good), but the Multiple Lines of Text and the Choice columns were all proposed as Single Line of Text.
👎 The “Start Time” and “Completion Time” are in a regular date/time format in the Excel, but if you do nothing they turn into a sort of strange calculated number during import. It is a Number column that you can not change after creating the list. I am sure it is extremely unique to the millisecond, but not usable for real humans, so I would suggest to “Do not import” this column unless absolutely necessary. In that case, make sure you turn it into a Date/Time column while importing your Excel file.
👉 Changing the Choice fields into Choice columns during import made the columns into default choice columns, with dropdown and no values.
- Collect requesters’ email addresses (and names) by default in the Form. Those will be captured in the Excel and can be pushed to the List, saving time in entering this info manually.
- Try to think of a unique identifier in your Form that you can use to fill the Title field in the List.
- When you enter your first item to create the Excel, use short dummy text to avoid scrolling when importing the Excel. (I entered a ton of text into the Multiple Line of Text field, but that was not a good idea 🥴)
- Is the date of the request important, e.g. if you need to sort the requests in order of entry, or calculate a response time? Use the default “Created” date/time of the item in the List. The workflow may have a few seconds delay, but it is usually the date that is important, not the exact time. This allows you to skip the date columns from the Forms/Excel.
- Make sure you select the Title column first when you create your List, or Lists will keep making suggestions until that field is mapped.
- Select “Do not import” for the Excel columns “ID”, “Start Time” and “Completion Time” unless you really need those
- If you have Choice fields in your Form, it makes sense to configure the corresponding columns in the List as Choice fields and add the values. This will allow you to make use of List column formatting, such as displaying each value as a “coloured choice pill” for easy recognition.
I am not so sure if using the Excel file as the basis for the list saves much time. You need to carefully select and adjust the column type during and after import. I am sure that practice will make perfect, and I will test that in my next experiment with the other Forms-options, but if you are a practiced List creator (and I am one) you may be faster when you create your list from scratch in your SharePoint site.
It was one of my first experiences with the Lists app, however, and I have seen a few things that I like! 😍