Posted in Products and applications, Sharepoint 2010, SharePoint 2013, SharePoint Online

Using color-coded calendars in SharePoint to manage resources

Color-coded calendars in SharePoint are a simple to create, visually appealing, popular and easy way to manage resources that, for whatever reason, cannot be managed using Outlook calendars.

A common use case for this option is to manage pool cars in ‘local’ business areas (as opposed to the entire organisation). This avoids having to add tens of pool cars as resources in Exchange and allows business areas to manage the resources independently.

What is a color-coded calendar?

A color-coded calendar is a SharePoint calendar that allows end users to view calendar entries visually. When configured (usually takes <30 minutes for 10 resources), color-coded calendars look something like the view below.

O365_SPO_ColorCodeCalendar1.JPG

Why not use Outlook calendars?

As noted above, resources can be created in Exchange so they appear in Outlook (and can also be used in SharePoint calendars). This may be fine for a small business, but for larger companies (we had 9,000 geographically dispersed staff), adding and managing (a lot of) vehicles for ‘local’ business areas was considered too much.

One of our business areas used color-coded calendars for multiple purposes:

  • To book meeting rooms. Each meeting room (which had its own calendar) used color-coding to indicate if the meeting was for internal or external staff.
  • To book and manage pool cars.
  • To book and manage other pool vehicles (such as minibuses).

How to create a color-coded calendar

The three steps to create color-coding in a SharePoint calendar are described below. If you plan to create these types of calendars, it is recommended that you create a dedicated calendar for the purpose, e.g., ‘Pool Car Bookings’. In summary the steps are:

  • Create multiple choices in a SharePoint calendar choice column.
  • For each choice, create a view.
  • From the master calendar view, create a calendar overlay for each view, changing the color for each.

Keep in mind that these calendar can only have ten calendar overlays, each of which maps to a choice in a drop down list.

Step 1 – Create the choice options

  • Go to the calendar list settings and scroll down to the column settings.
  • Change the name of the choice column ‘Category’ to the name that you want to use – e.g., ‘Pool Car’.
  • Change the choice options to the options you want the users to select from, for example ‘XYZ123 Red Toyota Corolla’. Try to keep these with a consistent naming structure. Record each of the options in Notepad or some other text editor as we will come back to these shortly to add the list view URLs.
  • Remove the option in this column for end-users to select their own value.
  • Click OK.

While you are in this section, you might consider changing the default ‘Title’ column to ‘Driver’, and the default ‘Location’ column to another value. See below for how to change the order in which these options will appear.

Step 2 – Create the list views

Create a list view for each of the choices in the list column in Step 1. There are a couple of ways to do this, I suggest this one for anyone doing this for the first time. Another way to do it is via the ribbon menu.

  • Go to the calendar list settings and scroll down to Views. The default options are ‘All Events’ (which shows the calendar items in a list), ‘Calendar’, and ‘Current Events’.
  • Click on ‘Create view’ beneath the existing options and repeat this for each new view.
  • Click on ‘Calendar View’.
  • Give the new view a name. As this view is generally not going to be used, the name can be a short version of the choice option, without spaces, e.g., ‘YYZ123RedCorolla’. This will ensure that you get a clean URL without any spaces. URLs with spaces can be the cause of problems if the URL is copied but it doesn’t copy past the first space.
  • Scroll down to the Filters section and change the filter to sort by the Pool Car (column) to equal the exact name in the choice column – ‘XYZ123 Red Toyota Corolla’. (The example below shows a different choice option, for demonstration only).

O365_SPO_ColorCodeCalendarFilter

Step 3 – Create the calendar overlays

Now return to your calendar main view making sure that you have ‘calendar.aspx’ in the URL.

https://tenantname.sharepoint.com/teams/sitename/Lists/PoolCars/calendar.aspx

IMPORTANT note – It is quite easy to accidentally open one of the views you just created and create calendar overlays there. If you do this, you may create the calendar overlays ‘under’ that view instead of the main calendar view. If you do this, you will need to go back to the calendar view and delete all those calendar overlays.

Assuming the URL now has ‘calendar.aspx’, following the next steps.

  • Click on CALENDAR in the ribbon menu. (Note, you can create the views above here as well). The ‘Current View’ should show as ‘Calendar’.

O365_SPO_ColorCodeCalendarMenu.JPG

  • Click on ‘Calendars Overlay’. In the next screen, click on ‘New calendar’. You will repeat this process for each of the calendars you create.

O365_SPO_ColorCodeCalendarOverlay

  • For ‘Calendar Name’, copy the same text as the choice. e.g., XYZ123 Red Toyota Corolla. Leave the option as ‘SharePoint’.
  • In the Calendar Overlay Settings section:
    • Enter description if required.
    • Choose a color. You have 10 colors to choose from and these cannot be changed.
    • In the ‘Web URL’, delete the existing URL and copy the URL of the view that matches the name. Click ‘Resolve’ and wait a second or two.
    • The ‘List’ drop down should now show the Pool Car calendar name (e.g., PoolCars’); if it doesn’t, click the down down and select it.
    • In the ‘List View’ drop down, choose the List view (which should be the same as in the URL you pasted in Web URL).

After you have created each calendar overlay, the main calendar should now show each choice in a color box.

One last step – very easy to forget!

After you have created each calendar overlay, the main calendar is still ‘visible’. This means that any time you add a choice option, the calendar will show both the ‘master’ calendar entry as well as the colored-calendar entry.

To remove the master calendar from view, go to the primary ‘calendar.aspx’ view, click on CALENDAR, and click ‘Modify View’ in the ribbon menu.

Scroll down to the Filters section and change the choice to show only if the ‘PoolCar’ column is equal to ” ” (blank). As only completed items will be displayed, this removes any pool car from the master calendar view.

How to change or remove a choice option

Removing a choice option is the exact reverse of the process described below:

  • Go to the calendar overlays, click on the overlay and click on ‘Delete’.
  • Go to the calendar list settings, scroll down to the list views, click the list view, and click on ‘Delete’.
  • Go to the calendar list settings, scroll down to the columns and click on the choice column. Remove the choice option.

Changing a choice option is possible but you must:

  • Change the choice option. (Keep a note of the choice)
  • Change the view name and filter in the view. (Keep a note of the view URL)
  • Change the name of the calendar overlay, delete the existing view URL and replace it with the new one, click ‘Resolve’, and fix the List and List view options.

It is VERY easy to break these calendars if you don’t follow the exact steps. On the other hand, these are very easy to troubleshoot. The main ‘problems’ that arise are when a Site Owner or end-user with contribute permissions:

  • Doesn’t create the overlays from the main ‘calendar.aspx’ view.
  • Changes or adds a choice column without changing or creating a new view and calendar overlay.
  • Changes or adds a view with or without changing the choice column.
  • Changes a calendar overlay but forgets to click Resolve or doesn’t select the correct options.
  • Forgets to change the main ‘master’ calendar view to filter by ‘pool cars’ is equal to ‘blank’.

Four important additional points to note:

  • Removing or changing a choice option does NOT remove the option retrospectively. This means that all previous history of that vehicle
  • If you enable versioning on the list (via list settings), you will have a record of changes made to the list.
  • You can display (and edit) the complete listing via the ‘All events’ view.
  • Train your users to:
    • NOT use the ‘All Day Event’ option as this carries the booking to the next day. Instead, remind them to choose ONLY the timeframes they need.
    • NOT use the ‘Recurrence’ option. However, if you decide to use this, train users to not to change the option from ‘No end date’ to an actual date.

It is possible to add a simple formula to a calendar column to prevent bookings being made more than x days in the future.

How to change the order of the options that appear

The default calendar data entry fields are listed below. To change the order, click on the calendar’s List Settings, scroll down to ‘Content Types’ and click on ‘Event’.

The ‘Column order’ option appears at the bottom beneath the list of columns. Note that you cannot remove the ‘All Day Event’ or ‘Recurrence’ option.

O365_SPO_DefaultCalendarEntry

Enjoy color-coding, or for my British and Australian readers, colour-coding.

Posted in Flow, Information Management, Microsoft Forms, Office 365, Products and applications, SharePoint Designer, SharePoint Online

Capture data in Microsoft Forms to create a new template agreement in SharePoint

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

O365_MSForms_NewExample

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.

O365_MSForms_Settings.JPG

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.

O365_SPO_ClientRegistrationColumns

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’.

O365_Flows_FormsToSPO

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).

O365_Flow_FormToSPO_SettingsA.JPG

  • 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.

SPD_NewSP2010_Workflow_Ribbon.JPG

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.

SPD_CreateListItem1.JPG

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.

SPD_PathandNameListtoLibrary

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:SPD_CreateNewListItemOptions

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.