List Alerts Rule

When the Microsoft Lists app was introduced I was a bit apprehensive, as I did not really know what all the fuss was about. But now that I have worked with Lists, I am starting to see the light! A few things that I like:

  • You can create personal Lists, which appear to live on OneDrive (as the URL for a list starts similarly, but I have no clue where to access them on OneDrive)
  • The options for colour and icons (trivial, but nice)
  • The ability to create a list from an Excel file, see my earlier post
  • The templates with content
  • Rules. You can create Rules to send yourself an email when something happens in your list.

But…you can also set an oldfashioned Alert. So, guess what I am going to do in this post? Ah, you know me by now. ๐Ÿ™‚

Setup:

I used an Issue Tracker list in a personal and a SharePoint version. (In SharePoint, you can use “Add an app” from the gear wheel menu, or “New > List” or “New > App” from the Site Contents page)

I have set one Alert (for new items) as I know how that works

I have also set all the Rules, as I am curious what I will see, two in the Personal version and two in SharePoint.

How does the Alert work?

  • You can do this from the Lists app and from SharePoint
  • You can do this on a personal list (๐Ÿ‘) and on SharePoint
  • Microsoft information
  • In the top bar, click “Alert Me” or the … at the right of the other commands and select “Alert Me”
Setting an Alert in SharePoint. This can also be done from the Lists app and in personal Lists.
  • Adjust the popup to your purpose and click “Save”
  • You will receive an email confirmation
  • When the desired change happens in the list, you will get an email
  • The sender will be yourself if the Alert is from a personal list, or the site name if it is from a SharePoint list.
The familiar Alerts functionality
This is the email body from the Alert

How do Rules work?

  • You can do this from the Lists app and from SharePoint
  • You can do this on a personal list and on SharePoint
  • You can find the Rule option in the top bar under “Automate”
Creating a rule in SharePoint (this can also be done from the Lists app, and for personal Lists)

You have 4 options:

  • A column changes
  • A column value changes
  • A new item is created
  • An item is deleted
The available options for a rule

Creating the rule is pretty easy – click on the desired change and in most cases you just select the column and/or enter the email adress of the person(s) you want to send the change to (including a Me option).

The most complicated one is “a column value changes” as this will ask you the column, e.g. “Status”, the condition (“is” or “is not”) and the value, e.g. “Completed”, and then the email address.

Setting the Rule for when a column value changes
  • You do not get a confirmation email
  • The sender is SharePoint Online
  • When the conditions are met, you will get the following emails:
The notifications from Rules; for Personal Lists they are in Dutch
The email body from this Rule; please note that it uses the known document management icons
Another mail, deleted this time.

But wait, there’s more!

The Reminder, of course! That is a long-desired option that has always been missing in Alerts.

The long-awaited reminder function!

This reminder option will send a notification x days before a certain date. This date needs to be a Time and Date field and can not be a calculated field, so any calculated Due Dates can not be done.
In this case, a reminder before the Data reported is also quite silly, as this is an Issue tracker and the Date Reported is at best Today and sometimes even in the past.

The reminders are Power Automate, and you can find them under My Flows.

The reminders are based on Power Automate

I have set a reminder for 1 day before the Estimated Close date on May 4. So I expected the mail on May 3, but it only arrived on May 4, 01.00 hours. So you have to select the interval carefully.

The Reminders
And this is the reminder mail, the other one is similar. Note the time sent!

What do I think?

  • ๐Ÿ‘ You can set Alerts and Rules in personal Lists. It can be useful when you are sharing a List with someone.
  • ๐Ÿ‘ Rules are easy to set up – you can use “Me” to send an email to yourself
  • ๐Ÿ‘ Rules use a familiar look and feel for emails – it looks like sharing emails and uses the regular document management icons
  • ๐Ÿ‘‰ The Reminder option can be useful, but it only works on dates in the future that you pick yourself. An option to work on calculated dates would be nice!
  • ๐Ÿ‘‰ The Reminder option works, but you have to test whether your reminder arrives on the desired time. In this experiment, 1 day turned out to be “on the day itself”.
  • ๐Ÿ‘Ž Rules do not take a change of list name into account. I changed the personal list to “Issue tracker Personal” but the email from the Rule did not adjust. The email from the Alert did, so did the mail from the Reminder.
  • ๐Ÿ‘Ž I miss a Rule for: “any changes in the List”. Quite often more than one column is changed, so that would mean you will need to set more Rules in order to be informed properly. You can set 15 rules on any List.
  • ๐Ÿ‘Ž The information in the email from Rules is minimal – you have to go to the List to see what has changed. This makes Alerts more useful for any changes except Deletions
  • ๐Ÿ‘Ž The emails could benefit from more visual (typographic or otherwise) distinction between the actions and values, e.g. ” Ellen van Aken changed Assigned To to Ellen van Aken for SharePoint News does not show the latest items
  • ๐Ÿ‘Ž The sender of a Rule notification is always SharePoint Online – that gives less information than the sender of Alerts, which is yourself (for a personal list) or the SharePoint site name (for a SharePoint list). Especially when you have created many Rules, it may be hard to see what’s what.

Conclusion

I think this is very promising functionality, but I think it can be improved, especially on “information scent”. For the time being I prefer the good old fashioned Alert. It does not look as nice, but it gives you more information!

What are your thoughts/experiences?

3 ways to share a Form with colleagues

We are using Forms for almost everything these days. We have used it to sign up for webinars, to register who enters the building, to request research and that is outside the “formal” use as a way to get people’s opinion.

As Forms is a personal tool, all Forms you create are by default for your eyes only. You are the only one who can adjust your Form and can view the responses. Moreover, when you leave the organization, your Forms are deleted. And while your Form appears to be a sort of document (it appears in your All files on your Microsoft365 start page), it does not live in your OneDrive nor SharePoint so your manager can not save it when you leave.

Fortunately, there are a few options to share Forms with others (in or outside your organization).

1. Just the responses

If you would like your colleagues to view results, you can share the responses page with them.

  • Click on the Responses tab and then the … on the right
  • Click “Create a summary link” from the popup
  • Share the resulting link with the person(s) you would like to share with.
How to give your colleagues insight in the responses

The people you share the link with will only see the response page, and can not see anything more, such as all results or individual results. In case you have asked text- or date-questions, they will only see the 3 latest responses.

Colleagues will only see responses and nothing more.
This is what you see if you are the owner of the Form – you have many extra options.

So, this can be useful if you want to share preliminary results with your colleague or team. They can watch progress and get an idea of the responses. But they will only get the complete picture when you share the final results with them.

2. Share the Form to collaborate

My colleague and I organize webinars, and one of us creates the Form to sign up and then shares it with the other. We can both adjust the texts, questions and settings, which allows us to make modifications where needed, e.g. add a warning that a certain webinar is fully booked, share the link or close the Form when we are at the end of a series.
Additionally, we can both see all the results, and export them to Excel, so we can both see who we need to invite for each webinar.
This is useful when your Form is part of a team project, or when the Owner is busy or on a longer leave during the run.

The Form will still be deleted once the Owner leaves the organization and their account is deleted. If the Form needs to run after their departure, the new owner will have to make sure to download the Excel, make a copy or use the template (see option 3), and share the link to the copy before the original Form is removed.

How do you share to collaborate?

  • Click the Share button, and in the popup go to “Share to collaborate”
  • Click “Get a link to view and edit”
  • Select the option on the bottom: “Specific people in my organization can view and edit” (assuming you do not want the whole world to be able to edit your survey)
  • Start typing the name of the person(s) or Group you want to share the Form with, and click on the correct suggestion
  • If it is urgent, copy the link and share it with your colleague(s), but the Form will show up on their Forms page anyway under “Shared with me”.
This is the place to be when you want to share a Form to collaborate
Enter the name and click on the correct suggestion.

3. Share as a template

If you have created a nice Form, and someone else wants to use it for their own purposes, you can share your Form as a template. They will then create one or more stand-alone copies of your Form. I often use this when creating a “solution” for a specific question. In that case I create the Form and after testing and approval from the requester, I share it as a template. They are then free to do with the Form whatever they want.

  • Click the “Share” button, and in the popup go to “Share as a template”
  • Click “Get a link to duplicate”
  • Share the resulting link with the people you want to share it with – these can also be outside of your organization
How to share your Form as a template – this will create a stand-alone copy

The receiver clicks on the link and sees the Form with a text on top: “Duplicate this form to use as your own”. If they then click “Duplicate it” they will have a copy of the Form. The word “Copy” is added to title, but the new owner can remove that easily.

The new owner of the Form will get this message – easy-peasy!

Stop sharing

As far as I know, you can not stop sharing the response page or the template, but you can stop the sharing to collaborate.

  • Click the “Share” button
  • Click the arrow to the right of the people that have access
  • In the next pane, hover over the person or group to be removed and click the trash can symbol
You can easily see who has access, and manage access from here as well
Just click on the trash can symbol to remove access

What type of link did I receive?

You can see from the first part of the link what your colleague shared with you:

  1. The responses page: https://forms.office.com/Pages/AnalysisPage.aspx
  2. The link to collaborate: https://forms.office.com/Pages/DesignPage.aspx
  3. The template: https://forms.office.com/Pages/ShareFormPage.aspx

Thoughts?

Do you often share your Forms or do you mostly keep them to yourself? If you share, what do you use most often?

10 things to know about storing Teams meeting recordings

Around this time the default location to store Teams meeting recordings will change from Microsoft Stream to

  1. SharePoint if this is a Channel meeting (In the SharePoint folder of the Channel, in a new folder called “Recordings”.)
  2. OneDrive of the person who records, for any other meeting (in a new folder called “Recordings”)
  3. 20 days download option for the person who records, if they have an F3 license

According to Microsoft this is because a video is a document and should therefore live with and managed like a document. A recording will benefit from regular document management options, security and permissions, and the ability for external sharing.

In theory, this makes sense. In practice, there are some things you may want to know. In my organization we have chosen to switch early so we could control the switch date and make sure to have proper communication and support available. We have a few months of experience by now.

1. Stream is still not accessible for externals. Will it ever be?

When I was working with Office365 video (it must have been around 2015), we were told that it’s follow-up Stream would be accessible for externals. Until now, I have not seen any evidence or announcement of that. So storing meeting recordings in Stream has always been unpleasant for webinars and meetings with externals. You had to download the video from Stream and send or share it from OneDrive or SharePoint.

2. Spreading videos over 4 different locations is confusing (Stream, OneDrive, SharePoint, download)

We have been using the new locations for a few months now and boy, does this confuse people! You need a lot of words to explain where videos are stored. Many people are not aware of their license, or of the meeting type, or of their power when they record the meeting. And then you have all your earlier recordings still living in Stream. ๐Ÿ˜ฆ

3. Storing recordings on the OneDrive of the “button-pusher” is confusing

We have had a few questions from meeting organizers about the recordings, where someone other than the organizer recorded the meeting. When something needed to be done, e.g. sharing the video with external attendees afterwards, or a bit of editing, or removing the video, the organizer did not know where to go. When I explained the situation, it happened that the “button-pusher” was on holiday, had no time or was unwilling to cooperate.

As the organizer already has so many unique powers in a Teams meeting, would it not make sense if the recording was always stored in the OneDrive of the organizer? And perhaps with a 20-day download option in case the meeting is organized by a group mailbox? (Which does not have a license, hence no OneDrive)

4. Your OneDrive and/or SharePoint must be accessible for externals

That is, if you want to share recordings with external parties. As you know, external participants can not watch the recording of meetings by default, even if they have a Microsoft365 account.
In my organization the OneDrives have the option to share with externals, but not all our SharePoint sites allow external access. In some cases we can make external access available with little effort, but not for every site type. If your organization does not want that, you will be unable to share with externals, so you will have to send large files around. ๐Ÿ˜ฆ
This is not a big difference with the earlier situation on Stream, but Microsoft assumes external access by default, making it sound easier than it sometimes is.

Do you know the sharing settings for your SharePoint and OneDrive applications? (This is from the Admin Center).

5. This will add another folder to your systems

The recordings from a regular meeting will create a new folder in the OneDrive of the person who records, called “Recordings”. A Channel meeting recording will create a new subfolder in your Channel documents, also called “Recordings”.
This is not a big deal but you may want to tell people about all those folders that they themselves have not created. Some time ago I wrote an overview of all system-created folders in your OneDrive.

Example of a “Recordings” folder in a Teams channel

6. Stream can play videos at different speed

If you have a video in Stream, you can play it at different speeds. Click on the gear wheel under the video, click “Playback speed” and select the speed.
You can not do this in OneDrive or SharePoint.

Playing a video at different speeds is a Stream feature.

7. Stream has (limited) video editing options

If you want to do something with your video for the long term, you will probably want to use the best part only. In Stream, you can trim the beginning and end off your video if needed. There are no other editing options, but this option is useful AND more than what OneDrive and SharePoint have to offer. So, if you want to remove the start and end bits of a recording, you will have to upload your meeting recording to Stream OR use another video editing tool.
Please, can we have some editing options in OneDrive and SharePoint too?

Trimming is another unique Stream feature

8. Stream has caption, subtitle and other video options

This is perhaps not very relevant for the meetings that you record for people who could not attend, or to make sure you have captured the notes correctly, but these functionalities certainly add value for a webinar or an instruction video.

The video details in Stream. You can access them by clicking the … below the video and selecting “Update video details”. See the earlier screenshot.

9. Adding a Form works better in SharePoint

In Stream you can add a Forms, as a poll or a survey. In is rather strange – there is a side panel where I would expect the Poll to show up, but it is actually shown on the location of the video. Also, you have to specify a time, which could be nice for a quiz, but not for a general question.

I do not quite get this functionality in Stream. There’s a ugly box next to the video and the Form itself is shown on the video.

In SharePoint, you can create a nice page with an embedded video and a Form on the side.

It is easy to create a nice-looking page on SharePoint with additional information and interactive elements to your video.

10. Currently there are two messages when you save a Channel meeting.

My own tenant is still in Stream mode (mostly because I don’t do PowerShell ๐Ÿ™‚ ) and I am currently getting a message when I stop the recording.

On Teams desktop it says: “After the meeting, you can find this recording in the channel conversation or on Microsoft Stream.”

This is the message that you get when you stop recording in the Teams desktop app

On Teams for the web you get: “Recording is being saved. Recording has stopped. You can find the link to the recording in meeting chat history.”

This is the message when you stop recording in the Teams web app

Not a big deal, but something you may want to be aware of.

Conclusion:

Although the sharing and management of meeting recordings will be better when the recordings are stored on OneDrive or SharePoint, you miss out on specific video functionality. I would appreciate to have some basic video editing capabilities for OneDrive and SharePoint.

Has your organization made the switch to OneDrive and SharePoint? Do you have something else to add?

SharePoint Holmes and the No-show News

The case

One of our Communication sites has changed hands recently. The former owner was very active in her site and we often referred people to that site as a showcase for Communication sites.
She left and someone else took over. The new owner attended one of our Webinars and told us she felt confident managing the site. Last week she asked for help – she had published a News item for the first time and although it showed on the SharePoint start page she could not find it in her site.

On the SharePoint start page, news item are displayed in chronological (First published) order. The item on the left is the most recent.
The homepage of the site in question. The latest item is nowhere to be seen.

The investigation

I remember seeing the post on my SharePoint start page and thinking “Ah, she did it! Good to see that that site is active again”. But I had not looked at the site.

  • I went to the site and there was the homepage, seemingly untouched since the last owner left. (see screenshot above)
  • I created a new page with the News web part and noticed that the new item was displayed in first position.
A new page with the News web part as is – the new item is in first position.
  • I checked the web part settings of the News web part on the home page.
  • After checking the web part layout and settings, I checked the bottom of the web part menu. And yes, what I suspected was true: ALL news items had been fixed on the page, blocking display of all new News items.
All 4 News items are pinned in a fixed position. This means that new items will be displayed below these.

The solution

That was relatively simple:

In web part editing mode, I clicked on each x after the News item’s title. The chronological order revealed itself, and the latest News became visible.

“Native web part” (in chronological order).

I think pinning all News items is not a good idea. I can imagine that you will want to keep one or (at the very most) two things visible for some time, but to fix them all in their place is defying the purpose of having a News functionality. Default is best, because it is news and it is supposed to change! Also, it can really puzzle your successor ๐Ÿ˜‰
And I personally keep forgetting how it is done; the functionality is a bit weird.

More on creating SharePoint News in my earlier post:

16 things to know about creating SharePoint News

And by the way…

I noticed that the web part picker no longer has an expand option. That means you can no longer have a large overview of available web parts. You can now choose between a grid and a list view, and you can search, or scroll.

About SharePoint Holmes:
Part of my role is solving user issues. Sometimes they are so common that I have a standard response, but sometimes I need to do some sleuthing to understand and solve it.
As many of my readers are in a similar position, I thought Iโ€™d introduceย SharePoint Holmes, SharePointย investigator, who will go through a few cases while working out loud.

Some intranet promotion videos – part 7

It has been some time since I shared some interesting videos, so that is what I will do today!

1. New intranet for an Australian building and furnishing organization

This is a nice intro, by the COO, about the new intranet. The intranet has three main aims:

  • Communication and engagement (news and featured stories)
  • Corporate memory and ideas (which is a form of Knowledge Management – yay!)
  • Employee support (forms and info)

Pleasant combo of a personal intro, the main goals of the intranet, and a walkthrough. It is SharePoint/Microsoft365-based.
Do you notice what I noticed at the end of the video? ๐Ÿ˜
Uploaded February 2021.

2. Teasers and launch video for a car lease organization.

Very conveniently these teasers and introduction video were uploaded at the same time.

The teasers are in very different styles. I wish I knew the why and how!

The promotion video itself is very short and text-based. I would have liked to see what the intranet looks like. This is for a car leasing organization in the UK.
Uploaded February 2021.


3. New intranet for a Canadian investment company.

Smooth promo video for this organization. There’s a lot of content available from the homepage, lots of tabs and buttons and menu’s. Still it manages to look quite clean. You can personalize parts, e.g. select your location. There are some Microsoft365 hints, but the intranet does not look SharePoint-based.
Uploaded February 2021.

4. WEAC – Wisconsin Education intranet

This video is 10 minutes long and needs some time to properly start, due to some internal discussions. This organization has actually retired their old, and introduced this new SharePoint-based intranet due to COVID-19. They envisaged a lot of working and studying from home, and their old intranet could not handle that. While Microsoft365 is of course accessible from anywhere on any device.

At around 4.30 the tour starts. There’s a big focus on links and documents (nicely grouped) and less on the news functionality, which is called Information and is SharePoint News in a list view. Perhaps this intranet page is intermediary, because the URL states that this is the IT-site.
Uploaded March 2021.


5. Vertical Video! Intro for new social intranet at an Education recruitment organization.

This short intro by the managing director of this recruitment organization in the UK focuses on sharing good news on the new app Workvivo. The whole message looks a bit rehearsed, or maybe he is reading from an autocue. No screenshots or demo, sadly.
Uploaded February 2021.

That’s it for this time! Enjoy!

Photo byย Donald Tongย fromย Pexels – with some additions of my own.

New! The SharePoint app bar

“Hey, that’s weird, there’s only five News items on my SharePoint start page, not six,” I noticed the other day. When I looked a bit better at the page, I saw that something had been added on the left side of the page: the SharePoint app bar. At that moment, the only item that worked was the Home button, which led me to the SharePoint start page. Which, seeing that I already was on that page, was not a real advantage ๐Ÿ˜. The other options showed an empty popup.

So, after asking around on Twitter, it turned out that more people had it and that they also saw empty popups, but Melissa Torres, the developer of this functionality at Microsoft, was so kind to confirm that it was populating, and indeed, the next day everything worked as it should.

Thank you, everyone who responded to my Tweet! ๐Ÿ‘‡

Many people responded to my Tweet, including Melissa Torres from Microsoft.

What does it do?

Here’s a nice explainer video from Melissa Torres. (Thank you, Phil Worrell, for sharing that)

  • This is the Microsoft documentation.
  • The app bar is displayed on the SharePoint start page and all sites with modern pages. We have some “classic” sites at work (well…modern but with a classic homepage) and it does not show on the home page but when you navigate to a document library you see it.
  • The app bar allows you to quickly move between content and sites, so when you in one site and need to go to another, you do not necessarily have to go via the SharePoint start page.
  • There are 4 buttons, see Tweet above, top to bottom:
    • ๐Ÿ  Home, which takes you back to the SharePoint start page, or, if your organization has a Home site specified, to the global navigation.
    • ๐ŸŒ My Sites, which shows your frequently visited sites and your followed sites
    • ๐Ÿ“ฐ My News, which shows you about 8 recent News items
    • ๐Ÿ“„ My Files, which are recent documents you opened or worked on

Is the SharePoint app bar something to celebrate?

๐ŸŒ I was especially interested in My Sites. We do not have a SharePoint-based intranet yet, and we currently use SharePoint mainly for document management, generally in sites with limited permissions. We are slowly starting with communication sites and News, but the majority is “closed” sites. We have a custom web part showing “My Sites”, showing all sites you have access to, and I recently got some feedback that this was no longer displaying all sites. I wondered if this would solve that problem.

There is a Microsoft User Voice (what are these rumours I hear about closing that down?) asking for an out-of-the-box functionality that shows all sites you have access to, but that has been on the waiting list since 2017. Microsoft indicates that with a SharePoint intranet, there will be many sites everyone will have access to, so a list like that will be confusing rather than helpful. They suggest to use Following and looking at Recently visited sites, in order to create your own list. I will have some convincing to do, as the “All my Sites” is apparently popular functionality and now I need to tell people to “DIY”.
I know “Do It Yourself” is a Microsoft thing, but that does not necessarily resonate with everyone. ๐Ÿ˜‰

So, in that way My Sites is a bit of a disappointment, as it does not show “All My Sites” but rather “Recently/Frequently visited” and “Followed sites”. I think this is definitely useful but we will have to help people to make this useful for them. I can see the benefits of a quick switch between sites.

๐Ÿ  The Home button is at present not really useful, as there is a SharePoint button in every site to take you back to the SharePoint start page. When we have a Home site, and a global navigation, this button will be more interesting.

Every site has a link that takes you back to the SharePoint homepage

๐Ÿ“ฐ The My News option is nice, but to be honest, News is not THAT important that I need to be able to switch to it quickly when doing something else.

๐Ÿ“„ My documents can be useful. I may need to find a certain document when working in a SharePoint site.

And this…

โ“ I also wonder about what happened to plans for the SharePoint start page – about a year ago we were told that the left column would go away, but that never happened. I then thought that every “application landing page” would get the full list of Microsoft 365 apps, as we currently see on the Forms homepage, but that may also not be true.
So, perhaps the app bar will replace the left column? Oh well, we will see.

๐Ÿ‘ All this may be a reason to get rid of classic pages!

๐Ÿ‘Ž I am less happy with the fact that I can only see 5 news items on the start page.

What do you think?

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?

Create a List based on Forms responses – 1

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.

  1. 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)
  2. I completed one request to create the Excel
  3. I downloaded the Excel file to my PC – you can also save it to OneDrive
  4. I then went to the Lists homepage, clicked on “New List” and then “From Excel”
  5. I uploaded the Excel (or select from OneDrive)
  6. For each column I had the option to “Do not import” or check and adjust the column type
  7. 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.
  8. 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.
  9. I then checked the result
The import screen. For each field you get a proposed column type that you can change. “Do not import” is also an option.
You scroll to the right to map each field to a column.

Findings:

๐Ÿ‘‰ 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.

If you do not adjust column types, this is what you will get. The blue columns have not been set correctly.

๐Ÿ‘Ž 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.

The title field, which is a single-line-of-text column with a weird start time notation. Completion time is a number column.

๐Ÿ‘‰ Changing the Choice fields into Choice columns during import made the columns into default choice columns, with dropdown and no values.

Suggestions:

  • 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.
If you configure your Choice values as Choice columns and enter the values, you can give the options a different colour each, using Column formatting.

Conclusion

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! ๐Ÿ˜

Recent updates of my posts

With all those rapid changes in the Microsoft 365 suite, some posts age quickly. I have recently updated a number of my most-read posts to keep up with the current situation.

Office Home

Did you know I keep screenshots of all the varieties of Office Homepage since 2016? It is great to see subtle and not so subtle changes. And since there is a new Homepage being rolled out right now, featuring some more filter options and with new Tabs for your files, I updated The New Microsoft365/Office365 Homepage.

Teams

Most changes occur within Teams at the moment, so I have updated the following articles:

The importance of being Organizer, with an Excel spreadsheet (download if you like) of what each role can do in a Teams meeting. I have updated this for breakout rooms and the option to turn off chat.

Take control of your Teams meeting, where I added the options to allow attendees to unmute and the option to control the chat. I also updated some screenshots.

Forms

I just found out that you can create a QR code from Microsoft Edge so I added that to Long live the QR Code or you can read this:

SharePoint

In my most recent post I noticed that the elusive “See all” info, that allows you to create a lovely SharePoint News Digest, does not always appear on your web part even if you have posted the required 5 items.
I wrote 10 things to know about the SharePoint News Digest in 2019 and I am shocked that it took me until 2021 to find the quirk to fill item #10! ๐Ÿ˜‰

Delve

With the new Office homepage, the tab “Discover” has gone and with it the option for F3-licensed users, who have no Delve icon, to see suggested documents. So I also had to update An alternative way to dive into Delve.

SharePoint Holmes and the Disappearing Digest link

It has been some time that SharePoint Holmes’ skills were required to deal with a strange issue. But here he is again – this time with a mysterious issue with SharePoint News!

The case

Last time I mentioned a colleague who had started a new site, and who is very happy with the SharePoint News and the News digest. I had given her a short demo of creating both and emphasized that she needed to have at least 5 News articles before the link “See all” would appear on her News web part.

She called me some time after to ask why she did not see the “See all”. She had 5 news items but the link did not show.

No “See all” link on top of the News web part (left)

The investigation

I counted the News articles but yes, she had 6, so the link should be visible.

Perhaps it had to do with the page, one way or the other? So I checked:

  • Section colour – a wide shot but as she had used the dark section background perhaps there was a contrast issue?
  • The web part settings – you have a number of layouts for News items, such as a list or a Hub, and there are also some options per layout.
  • Number of columns – did perhaps a narrow column hide the link?

I just played around, changing the layouts and fiddling with the various options. Current SharePoint pages are so easy to change, that it is really not a lot of work, compared to the old pages!

The solution

Of course I should have trusted Microsoft that they know how to make sufficient contrast – the background colour had no negative influence.
A narrow column made no difference either – there is always room for “See all”. You see it in other web parts as well.
The web part itself was the culprit. There are actually TWO situations in the Layout when the “See all” does not appear:

  1. You use the Hub News layout – for one reason or another this NEVER shows the “See all”
  2. You deselect “Show title and commands“.
If you switch off “Show title and commands” you will not get the “See all” link

The latter was the problem in this case. My colleague did not like the extra space that the title took, so she had decided not to show that when she configured the page. When she hit 5 articles, she expected the link to show, as she did not realize that she had turned it off.
I can’t blame her, I only found out after she had called me!

The desired end result. You see it takes more space.

The tip

So, if you like the Hub News layout, or you want to hide the web part title, and you still want to create a News digest, you may want to do either of this:

  • Change the web part layout temporarily into another style, and go back to it when you have created your Newsletter
  • Create an additional page with a layout that supports See all, to create your Newsletters from.

I knew something would turn up as my 10th “Things to know about the SharePoint News digest“, so I have added it just now! ๐Ÿ˜

About SharePoint Holmes:
Part of my role is solving user issues. Sometimes they are so common that I have a standard response, but sometimes I need to do some sleuthing to understand and solve it.
As many of my readers are in a similar position, I thought Iโ€™d introduce SharePoint Holmes, SharePoint investigator, who will go through a few cases while working out loud.