Create a List based on Forms responses – 2

In last week’s episode, we learned that you can use the Excel spreadsheet you get as a result from your Form, to create a Microsoft List. In this case I needed the easy data entry in Forms, but wanted to move the data (using a workflow) into a corresponding SharePoint list in order to facilitate a process.

This time I checked what happens if I use the other answer types (Date, Ranking, Likert, File Upload and Net Promotor Score) as a basis for a new List. I set up a Form with those fields, entered one response, downloaded the Excel and imported that into Microsoft Lists.

Let me share the full “Translation” here, so you have everything in one place.

Field type in FormsSuggested Column in Lists
IDDo not import
Start TimeTitle
Completion TimeNumber
Choice Single line of text
Text shortSingle line of text
Text longSingle line of text
Text numberNumber
RatingNumber
DateNumber
RankingSingle line of text
LikertSingle line of text (one per statement)
File UploadSingle line of text
Net Promotor ScoreNumber

Please note that the “Number” columns have more options to select from than the columns identified as “Single line of text”.

A number column and its options
A Single Line of Text column and its options

More findings:

  • By default, the Date answer from Forms is translated into a Number column in the List. If you want a proper date in your List, make sure you change these during import, as you can not change into a Date and Time column after import.
  • A Likert scale answer will provide you with one column for every statement (=row). I have never liked these question types, as they are a lot of work, but they also provide a ton of clutter in your list 😁
  • The NPS gives you just a number, not the calculation of course.
  • The File Upload option in Forms gives you an ugly URL. Sadly there is no option to change this column into a Hyperlink column.
Yikes (The link to the file that has been uploaded in Forms and now lives in my OneDrive)

Suggestions

I will repeat my suggestions from last time, and have added some new ones, so you have them in one place.

Forms design suggestions:

  • Collect requesters’ email addresses (and names) by default in the Form. Those will be captured in the Excel automatically and can be pushed to the List, saving your users 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.

Import suggestions:

  • When you enter your first item to create the Excel, use short dummy text to avoid scrolling when importing the Excel. (How do I know that, you ask? 😉)
  • Select “Do not import” for the Excel columns “ID”, “Start Time” and “Completion Time” unless you really need those. (see next item)
  • Make sure you map the Title column first when you create your List, or Lists will keep making suggestions until that field is mapped.
  • If you have a Date column in your Form, other than the Start and Completion time, change that into a Date and Time column during import, as you will be unable to change it later.
  • The Net Promotor Score will only return the number of each response, so think carefully if you really want to import this column into the list. The complete calculation, and the graphic, is nicely done in Forms and it may be easier to check that.
The NPS is a calculation based on all responses – you can not capture this in a Calculated Column.

List suggestions:

  • Is the Start Time of the Form entry important, e.g. if these are requests and you need to sort those 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 during import.
  • 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. You can do this after import.

Conclusion:

Yes, it is certainly possible to use the Excel spreadsheet that is produced from your Form, as a basis for a Microsoft List. However, the import is pretty basic (Numbers and Single Line of Text fields by default), so you will need to think carefully about how to import each answer, because you can not change all of them afterwards.

If your Form is very long it can certainly help, but if your Form only has a few questions, I think you can just as quickly make a list from scratch and make sure that all columns are correct from the start. But of course one wonders if a scenario like this was in scope when developing all this functionality.

Do you have any experience with this kind of set-up, and if yes, do you have any tips or tricks to share?

2 thoughts on “Create a List based on Forms responses – 2

  1. Jordan March 19, 2021 / 12:52 am

    I found your post after searching for a way to remove the “asset tag” field. Thank you so much for pointing out that the “asset tag” field is called “title” in the settings! I was going buckwild trying to figure out why the field wasn’t in the list settings.

    • Ellen van Aken March 19, 2021 / 3:18 pm

      Hi Jordan, I am happy it was helpful. Yes, an annoying inconsistency really. Cheers, Ellen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s