And even more about Forms

Forms appears to be top of my mind these days. It’s near the end of the year and lots of evaluations need to be done 🙂 so I am searching the organization for opportunities to make the switch from other tools or processes to Forms.

Another “win”

Every year my organization has a week where you can spend (half) a day with another department. This has mutual benefits as you can well imagine. This process involves:

1. Making an inventory of which departments are open for receiving visitors that week
2. Asking employees which one of the “open” departments they are interested to visit and when

When I joined the organization last year, I had seen the Google form for item 2. and I made a resolution to try and help the organizer move that to Microsoft Forms this year. So recently we sat together to  create the Form and it turned out she had some doubts about the inventory. Last year she had sent out emails to various departments with some questions, and it was a lot of hassle to collect the data and get the overview. So I suggested she create a Form instead in order to

• make sure that everyone replies to all questions
• get everything collated into a tidy Excel sheet without having to copy it all manually

So, we created a Form and she sent an email with the request to complete the Form.  (Which is additional advertising and awareness for Forms, I hope!)

Then we set about to create the Form where employees can specify their interest and preferred date. As this had already been set up as an online form, this was rather simple. We could even shorten the form because we decided to collect people’s name and email address automatically.
When the inventory is completed, she just needs to add the dates and departments to the Form and the announcement can be posted on the intranet with a link to the Form.

So, unexpectedly we could move two processes to Forms! Yay! (Those little wins always put a big smile on my face)

Handing over ownership of a Form before it has been distributed

According to Microsoft, you need to add a Form to an Office365 Group if you want to hand over ownership. This will make sure that the data will be handed over as well.
If you do not have a Group for this purpose, and the Form has not been distributed yet, the original owner can Share the form with the new one, and the new Owner can then Copy it to make it his/her own.
This may come in useful when someone is leaving the organization (and whose account and content will be deleted) has created a Form that still needs to be introduced to the organization.

Step 1: As the current owner, use “Share to Collaborate” if you want to share or handover Form ownership

Step 2. As the new owner, on the “Shared with Me” tab, click the 3 little dots top right of the Form to be copied, and click Copy

Top 3 differences between Forms and Forms Pro

In my earlier post I mentioned I did not quite get the benefits of Forms Pro, but then Doug Allen wrote a post that made things clearer to me. Especially the options for sharing of the survey, and the analysis and follow-up of survey results are much better than regular Forms. Those examples were quite an eye-opener for me.

Be a quiz-master!

I have done some experiments with the Quiz option but I was not overly impressed. It only works well for multiple-choice questions. I think it needs some more work, either from Microsoft or from myself 🙂

However, Rebecca Jackson has dug a bit deeper and she says you can be quite a quizmaster, so I guess the “more work” is on MY ToDo list 🙂

So, what have you found out regarding quizzes?

Photo by Gigi on Unsplash

SharePoint Holmes and the disappearing Datasheet View

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.

The first case is about a Datasheet View.

The case

One of the users of a site did not see the items in a list. Having access to the data was a requirement for his role and he had always been able to see this content before it was migrated to SharePoint Online.

So, I put on my SharePoint Holmes cap and rolled up my sleeves.

The investigation

1. I logged in with my Admin account and went into the site.
2. I saw the items perfectly well. Just items in a Datasheet view.
3. Permissions check – the user had Read permissions to the site.
4. Items with unique permissions check – the list had unique permissions but the user had Read access.
5. Item-level permissions check – in the Advanced List Settings it showed that all items were visible to all users of the site.
6. Workflow check – no workflow reducing permissions after going through a process.

Right, that was an interesting one.

1. It was time to look through the eyes of the user, so I added myself to the same user group and checked. An empty list stared back at me.
2. I went through the other views and found a standard one. I could see the items in there, and so could my user.
3. One of my colleagues mentioned that issues with the latest IE update had been reported, which might have influenced the Datasheet view.  We tried different browsers. That made no difference, but there was always that difference between user and admin.

Hmmm….

The solution

Search engine to the rescue! One of the results surprised me, and I had to test that.

I created a datasheet view in my own tenant. It looked like this:

What the Admin sees

Then I logged in with Contribute permissions. It looked like this:

What a Contributor sees

Then I logged in with Read permissions. It looked like this:

What a Reader sees

You need at least Contribute permissions before you can see items in a Datasheet view.

The Datasheet view is meant for editing, so only people with edit permissions can see items in it. It makes sense and I have always told people to use the Datasheet view very sparingly as it is only too easy to change something. The many Excel-addicts in my user base however loved it and have also used it for display purposes in our SharePoint 2007 intranet.
Now they either have to elevate permissions or recreate their views.

Interestingly enough this was a permissions issue, but different from what I have ever seen before!

Image courtesy of Geerati at FreeDigitalPhotos.net

Monthly Reporting in a Team Site

I thought it was time to show you another real-life example of facilitation of a small process.

What was the situation?

All units had to deliver a certain report at the end of the reporting period, before consolidation in their business. It turned out that the units reported different things and delivered their reports at different times.
Of course this was not the way to go, so someone made an overview of the different scenarios that were in use, and from that (and with many discussions with everyone involved) they created the desired process that all units would have to follow for consistent reporting and consolidation.
The process described all actions that had to be completed at certain days around the end of the period. Every unit had to mark the action as completed on the day specified.

What was the solution?

The next step was to think of a place where every unit could monitor their progress. We expected to be many items (x actions * y units), and the items would be edited regularly by many different persons, which might lead to messing up each other’s data, so we first thought we would create a list per unit. That would also make it easy for people to find their own dataset.
However, that would make it harder to see if everyone was on the right track, and it would mean we would have to make changes to many lists if there ever would be a change in the setup.
We decided to go for one central list.  A custom list, of course! I would add some safety measures to prevents accidental deletion or editing of other people’s items. (Few Datasheet views, a custom set of permissions and targeting – see also Dangers of the Datasheet)

Every unit had their own identical set of actions. Next to the actions, there were fields for unit, the responsible, the number of days before or after the end of the period that every step had to be taken (from -3 to +15), a free text box for comments and of course a Yes/No box to note if the action had been completed.

All units have the same set of actions to complete.

I then created one Datasheet view (Update View) with as few columns as possible to make it easy for all participants to update the list. I created a number of Standard Views  for the process owners to keep an eye on general progress.

This is the set of identical actions that every unit had to report. Before the start of the cycle, it would look like this.

Progress View at day 0. It is immediately clear which unit is behind with their reporting.

I also created a SharePoint group for each unit. Every group got a set of custom permissions: “Contribute without Delete”. This was one of the measures to avoid accidents that may occur when a big list is frequently edited by many people.

Targeting option in the web part.

For each unit I created a page, and added the list webpart on it with the Update View. I filtered for the unit, sorted by day, and then targeted the webpart to the SharePoint user group of that unit. That way we made it easier for people to see and update their own actions only – if they accidentally clicked on the wrong page, they would see a blank page.

This page shows all actions that have to be completed for this unit.

After each period had been reported in the correct fashion, the site owner made an export of the list into an Excel file for archiving, set the Completed fields to “No”, and the system was ready for a new reporting cycle. When “action time” neared, she would add an announcement on the Homepage that the new cycle was ready to start.

What was the result?

After a trial run in a few units, it turned out that this worked well. The pages per unit and the Excel-type update made it easy for people to find and update their own items. It was also easy for management to track progress – the Progress View (showing only items where “Completed is not equal to Yes”)  showed if every unit was on track.

From now on, reporting was done in a uniform fashion and consolidation of the reports was a lot easier than it had been. After some months, another business asked me for a copy of the setup. So I guess it was a success  :-).

Image courtesy of Stuart Miles at FreeDigitalPhotos.net

7 Tips for copying from Excel into a Datasheet View

Some time ago I was working with an Excel-loving crowd who had to share their data in a SharePoint list.
I wanted to make the transition as smoothly as possible, so I created a special “Data Import View” (a Datasheet View) to make it as easy as possible to copy their Excel data into the list.

There were too many people involved with too many different PC configurations, to trust a Data Connection. So a one-time copy-paste action it had to be!

After some tests I gave them the following instructions:

1. Sort the columns in your Excel sheet in exactly the same order (left-to-right) as the Data Import View (I listed all columns in the correct order)

2. Use the exact same words, spelling and capitalization as the values in the dropdown fields in the SharePoint list.
I listed all the words – we used “color”  and not “colour”, for instance.
If you use different capitalization in a Choice column (e.g. “Preferred Supplier” instead of “Preferred supplier”) the Choice column will accept your value without error message, but when you edit the item, the value will change to the first of the Choice values.

3. Check your cells have exactly the same formatting as the SharePoint list.
Sometimes text fields gave errors while copying – copying the text from the dropdown field and paste that into the Excel file often solved that.

4. Use the correct date-and-time format. (I had to test a few different notations to know what worked and what not in our multinational organization)

5. Select the first (utter left) cell before you start pasting. (The Data Import View contained many columns, and needed horizontal scrolling to see all columns, and sometimes people forgot to scroll back).

This is the error message if you do not start on the correct cell

6. Start pasting only when the first cell is ready. It shows a bold line around it.

This is what the bold line looks like

If your first cell looks like this, please click it, otherwise you get the below error message when you paste.

Error message if you paste into a cell without a bold line around it

7. Do a test-run with one line. Adjust your data and check again when you encounter issues.

Next time your users say that “they can not copy -paste their Excel data into a Datasheet View”, these tips may point you towards a solution.

I could have made things slightly easier by using a single-line-of-text for each column. Single-line-of-text is like bloodtype AB:  it can receive anything :-). But because I had originally designed the list for adding new items via “New Item” in the Standard View we had to create these instructions. And single-line-of-text columns lead to more deviations in spelling.

David Young has also found that you need to refresh your screen after deleting data and trying again.

Do you have any other tips or experienced other errors? Please share!

You may also like:

Dangers of the Datasheet

Design Fault, Scope Creep, Moving Insight or Unexpected Success? (The project that inspired this post)

10 tips for Choice or Lookup Columns

Dangers of the Datasheet

The Datasheet view is a favourite with Excel fan boys. I have seen many a controller or purchaser create one Datasheet view after another after they had learned about this fabulous functionality. And I have subsequently seen lots of accidents with Datasheets where I had to come to the rescue…

Because Datasheet Views can make it too easy to add, edit and especially delete content. Besides, they have some annoying habits…or should I call that “specific functionality”? 🙂

Let me show you the differences. This is a Standard View of a list:

A list in Standard View

And this is the same list in Datasheet View. You will see some differences immediately, but there are invisible differences as well.

Datasheet View of the same list

[Update September 2017: In SharePoint Online, you need at least Contribute permissions to even see the Datasheet View.]

It is important to design your Datasheet Views to minimize accidents and irritation. Think about purpose, process, audience, update frequency, backups and more.

Datasheet Design Do’s and Don’ts:

1. Make sure your column names are easily understood.
The Datasheet view does not show the description in New or Edit mode, so the column name is all information your users will get.

2. Do not use a Yes/No checkbox in a datasheet view.
Without a proper description it can be very unclear what a checked box means.  A “Yes/No” or “Open/Completed” choice field may be a better option.

To jog your memory, this is what the New or Edit Item looks like in Standard View:

New Item – look at the description field and the mandatory columns

3. Show empty required fields in your view.
This is especially important when people are expected to add new items.
Required fields that are not completed will not allow you to save your item. Besides, you can not move to the next line item before you have completed all information in the current line item. This makes quick “vertical copy” (as in Excel) impossible. See also item 4.

You have not completed the mandatory field.

4. Add an * to the column name if the column is required.
If people only ever see the Datasheet View they will not know a column needs to be filled – it does not show as in the Standard View.

5. Avoid content approval and multiple-lines-of text fields in “enhanced rich text” format.
These can make your Datasheet View read-only. This post sums it up nicely.

This is the format to avoid:

Do not use this format for your multiple-lines-of-text column

6. Never make a Datasheet View your default view.
It is too easy to accidentally overwrite or delete items. It shows only a limited number of items on a page and you do not see any context such as hyperlinks and the item menu.
Instead, use a Standard View and inform your audience where they can find the Datasheet View.
[Update September 2017: In SharePoint Online, people who can only Read can not see the Datasheet View so an extra reason not to do this].

7. It is not possible to group in a Datasheet View.
If you want to show your content in groups, add the grouping column to the view, and sort first by this column. This way you have already used one sorting option.

You have to add the column that you group on

8. If you need to bulk-edit on a regular basis, create one Standard View for editing purposes.
Use only those columns that need to be edited, so you can quickly turn it into the correct Datasheet View.
You can also create a personal view for this purpose.

9. Think 4 times about a Datasheet View if it is going to be edited by a large and unknown audience.
A large audience of unknown people is impossible to properly train or influence, so you will have to take extra preventative measures.
– Use a “Created By”, “Modified By”, or “Assigned to” =  [Me]  filter for the view.
– Create a page for each audience, filter by audience, and target the list webpart on the page, to the audience.
– Limit the public Datasheet Views in your list and hide Edit options from Standard Views.
– Remove the “delete” permissions from the Contribute role.
If those measures can not be taken, use a Standard View and ask people to edit their items individually.

10. Export your data to spreadsheet on a regular basis.
This will provide you with backup data and allows you to analyze the results and create graphs.

And do not forget training!

The Datasheet View may look like Excel, but it behaves differently. So provide training for your users to manage expectations and prevent accidents,

Training topics could be:

• Column description, purpose and peculiarities
• Editing frequency
• How to import or copy data from Excel
• Basic troubleshooting

Share your thoughts.

What are your experiences with the Datasheet View? Have I missed something? Please share!

Design Fault, Scope Creep, Moving Insight or Unexpected Success?

The design.
Once upon a time, I created a Team Site to facilitate a global project. It was based on a custom list where global and local people could enter project data, such as business unit, product, and a code from a choice column. The code represented a number. I suggested to the project owner that we add a column with the number corresponding with each code, to enable an automated calculation of the value of the item. *1)
The process owner did not think that was necessary. He wanted to keep things simple and would do the aggregated calculations via an Export to Excel.

Testing and feedback.
When the lists and specific views had been created, I asked the process owner to test and discuss this with a selected number of the 50 designated users. He came back with some good feedback and change requests. We ended up with 4 custom lists, each with the same structure, but with different values to select from.

So far, so good. We launched the site.

At first…
Shortly after introduction the site started to fill with entries. The only negative comment was when one person was flooded with Alert emails when someone  bulk-uploaded 500 entries. I showed him how to change the default “immediate” Alert into a “daily summary” and we happily agreed this was a sign of success.

Success!

But then…
After some weeks, I noticed that Excel files with the required information were being uploaded to the site and updated online. It turned out that many business units had been doing this same exercise already on the business unit level, and thought they would share and update their work (which had been done in Excel) in our site. I would have preferred them to add their data directly into the lists, but it was a good sign that the business units wanted to share.

You can imagine what followed: “How can we upload the content in those Excel files into the lists?” It was not too difficult to create an extra Datasheet View with all mandatory columns, export it into a Template, and write a short manual on how to copy the content from Excel into the lists. *2)

And then…
Meanwhile, requests for access kept coming in from across the world. There were now about 200 people who wanted to share their data instead of 50. Great!
The site also got senior management’s attention. They wanted to show the real-time project results on the site’s Homepage so everyone would be stimulated to add to the numbers. Yes, I could understand that wish, but it meant that I had to add that extra numerical column in each of the lists after all (that the process owner had decided to leave out in the design phase) as well as a calculated field, and change all the views and templates. That was not too bad, but someone had to update all existing 1500 entries with the corresponding number.
A volunteer was appointed to do just that. 🙂

But wait…there’s more!
Just when I thought the whole setup was stable, the process owner approached me and asked me if we could capture two other values in the list while we were collecting data.
For me this was 30 minutes work, but for him it would mean that he had to communicate a change, re-educate everyone, and revise the input template and the manual. And for all business people it meant they would have to find out those values, revisit their entries (by now we had about 4000 entries) and update them. The “volunteer” could not do it this time, because the values were not as straightforward as the code.
He decided not to do it.

What do we call this?
It all worked out well in the end, but I have wondered what this is.

• Is it Design Failure because we did not envisage properly how the site and project would develop? Should I have been more insistent on adding that numerical column from the start since I knew that would come up? Still, I could never have foreseen the wish to collect those two extra values.
• Is it Scope Creep because the process owner had not set enough boundaries for what he wanted to achieve?
• Is it Moving Insight because the organization learned what you can do with SharePoint as they went along and it was only natural that they wanted to make the most of it?
• Is this an Unexpected Success because it worked well and we should be happy that it sparked so many new ideas?
• Or is this just The Way These Projects Go?

This is something that I have experienced more often and I never know whether I should be happy about it (because it shows people learn about the possibilities of SharePoint) or sad (because it shows what we are not so good at project definition).

This is the reply when I asked the question on Twitter:

What is your experience and how have you dealt with this?

*1) The drop-down had too many items to enable an IF, THEN formula to calculate the number. Next to that, there was a “specify your own value” field if the project had a non-standard code.
*2) I thought connecting the Excel to the list would be too tricky for most users, and I also did not know how SharePoint or our system would react to so many simultaneous connections

Image courtesy of Ambro / FreeDigitalPhotos.net