Posted in Correspondence Management, Flow, Information Management, Microsoft Teams, Office 365 Groups, Products and applications, SharePoint Online, Solutions

Managing correspondence in SharePoint Online including using Teams and Flow

Many (if not most) organisations receive and respond to correspondence in the form of letters or emails. They may also respond to social media messages.

Correspondence may be managed in different ways. For example, some organisations may have a dedicated business correspondence unit while in others individuals or business areas may respond.

This post describes how SharePoint Online with MS Teams and MS Flow could be used to manage letters and emails that require a formal ‘organisational’ response. It does not look at managing responses to social media messages.

Core elements

The management of correspondence that requires a formal response generally involves the following elements:

  • Somewhere to store the incoming correspondence (including scanned paper documents) and responses.
  • A description of the correspondence (naming conventions and/or metadata).
  • Some form of workflow, including emails, used to notify people of actions they must take.
  • Various methods use to report on and track progress, and closure/completion rates.

SharePoint includes all these elements. The requirement for workflow can be met using emails, the built-in workflows, workflows built in SharePoint Designer, or MS Flow. MS Teams provides the opportunity to add additional value to the communication response process by allowing messaging, co-authoring of responses, approval processes, and more.

A model correspondence system

The model correspondence system described below is based on a correspondence management system that I developed for a large public sector organisation a few years ago, but using a different system.

The core elements of the model system are:

  • An Office 365 (O365) Group. The O365 Group has an email address and an associated SharePoint site.
  • The Office 365 Group’s SharePoint site.
  • A Team in MS Teams, connected to the O365 Group.
  • Microsoft Flow.

Office 365 Group

The O365 Group should have a name that reflects its purpose and makes it easy to recognise as it also becomes the email address name.

  • For example ‘GRP_Correspondence’ – the prefix ‘GRP’ is used as it defines it as an O365 Group, as opposed to a Security Group (SG) or Distribution List (DL) created under the same ‘Groups’ section in the O365 Admin portal.

The Members of the Group should be the group of people primarily responsible for managing responses to the correspondence. Other people can be invited to the SharePoint site directly (without having access to the Group’s Team) as Members or Visitors.

SharePoint site

The O365 Group’s SharePoint site has the same name in the URL – GRP_Correspondence.

The SPO site should have at least one document library with an obvious name, for example ‘Correspondence 2019’.

If there is concern about potentially long URL lengths, the library name could be reduced to, say ‘Corro2019’; the display name can still be ‘Correspondence 2019’.

Consideration might also be given to having a ‘drop off library’ in the Group site where anyone can save correspondence that may require a response.

The metadata required in the document library will vary between organisations. The core default metadata (for every new document library) already includes all the following:

  • Title
  • Created (date)
  • Created by
  • Modified (date)
  • Modified by
  • Version
  • Document ID (when enabled as a Site Collection feature, which is recommended)
  • Shared with
  • Shared with details
  • Check In Comment
  • Checked Out To
  • File Size
  • Folder Child Count
  • Item Child Count (shows how many documents in a folder)
  • Label setting
  • Retention label
  • Retention label Applied
  • Sensitivity

Additional metadata may include any of the following:

  • Sender (free text or potentially drop down choice)
  • Response Due date (Date)
  • Urgency (Choice – Routine, Urgent)
  • Description (free text)
  • Reply Status (Choice – Not required, Draft, Approved, Sent, Cancelled)
  • Response Type (Not required, By email, By letter)
  • Approved by (internal Active Directory name)
  • Date Completed (Date) < This date should correspond to the date on any reply.

Once the library has been created, content can be added.

  • Paper correspondence can be scanned and saved to the library. Many MFD (printers) now have the ability to save directly to SharePoint, perhaps to a drop-off library for categorisation and review before being moved to the primary library. The original paper can then be boxed and destroyed after a given period (3 – 6 months).
  • Emails can (and should) be copied from the Group’s inbox to the library (see screenshot below). To do this, sync the library to File Explorer and drag and drop.

EmailtoSyncLibrary

A new folder can be created for each new correspondence, as indicated in the screenshot below:

SPOCorroLibrary.JPG

Naming conventions

Both the correspondence and the folders where it is stored should be named according to naming conventions. Naming conventions can also be used instead of folders, to indicate the connection between the original and the reply. My preference is to use folders to group the original and the reply, and also because they are ubiquitous in the digital workplace.

Suggested naming conventions:

  • Folders: Surname-Subject-Date
  • Emails: EMAIL-Surname-Subject-Date
  • Documents: LETTER-Surname-Subject-Date
  • Replies: REPLY-Surname-Subject-Date-DRAFT or FINAL (the final version may be ‘signed’ and then saved as a PDF)

Always exclude spaces in names, as these will be replaced by ‘%20’ in the URL path.

If one or more standard templates are required for replies:

  • Create the new Site Content Type in the Site Content Types area.
  • Enable the management of content types in the ‘Advanced’ section of the document library settings.
  • Add the new Site CT to the library
  • Add or edit the Word template to be used by clicking on the Content Type in the ‘Content Types’ section, then clicking on ‘Advanced settings’ and ‘upload’. The template can continue to be modified as required directly from this area and ensures consistency in replies.

The new Content Type will appear in the ‘New’ menu in the document library but not in the MS Teams ‘New’ dialogue (see below). This means that standard replies using a template can only (currently) be created via the SharePoint library. Drafts, however, could be created with a standard document template first (same with emails, see below).

StandardReplyCT
SharePoint New options

MS Team

The Team in MS Teams can be connected to the O365 Group (it’s a one to one relationship, you cannot connected multiple O365 Groups with a single MS Team).

The Team can have multiple channels, and the SPO document library can be presented in a channel. For example, the channel named ‘Correspondence 2019’ includes the ‘Correspondence 2019’ document library as a tab, as shown below.

MSTeams_CorroExample.JPG

The use of Teams means allows drafts to be co-authored and chats to take place about the correspondence and other matters.

If the SharePoint site includes an ‘incoming correspondence’ drop off library, the Team could have a channel with that library as a tab. The channel could then be used to review and decide on what to do with the correspondence.

Routing incoming correspondence for a reply

Once the correspondence is saved in a SharePoint document library, a decision must be taken by someone whether a reply is required.

  • If no reply is required, this can be reflected in the metadata (‘No reply required’).
  • If a reply is required, the correspondence must be ‘sent’ or otherwise made available (see below) to someone to draft a reply. This can be a simple or complex process.
    • In some cases, a standard reply may be possible. The SharePoint site should contain at least one library that contains examples of standard replies to certain types of, or common, questions.

Simple routing

The easiest way to ‘send’ someone the correspondence for action is to use the ‘Share’ option on the folder where the incoming correspondence is stored. As the same ‘Share’ option appears in File Explorer when the library is synced, the sharing process can also be managed from File Explorer.

This means that the recipient only has to click on the folder link in the email they receive to see the content of the folder. As they have access to the folder, they can then use the ‘New’ option to create a draft reply, including to an email.

FolderLinkSent.JPG

Once the draft has been finalised, it can also be sent via the ‘Share’ option. Alternatively, an alert on the library will notify anyone who needs to be notified that a change has been made to the library.

Routing using MS Flow

A more complex routing process may be required if the draft requires several steps, for example:

  • Send to someone to create the draft reply.
  • Draft reply gets sent to someone else for approval.
    • If not approved, goes back to the person who created it. (This can loop several times)
    • If approved, a message is sent to the person who needs to finalise it
  • Reply is finalised, metadata is updated, and reply sent.

All SharePoint Online libraries include an in-built Flow workflow ‘Request Sign Off’. When a document is selected and the ‘Request Sign Off’ option is selected the first time, the person must select the option to ‘Create Flow’. The ‘Run Flow’ dialogue then appears, requiring someone to be identified as the Approver and a Message to be included. The approver can be anyone in the organisation, for example the person’s manager.

 

RequestSignOffWho.JPG

The Approver receives an email, allowing them to approve or reject, and add a comment. If rejected, the ‘Sign Off Status’ column in the SharePoint library is updated to ‘Rejected’, and the sender receives a message to advice them that approval was rejected.

RequestSignOffEmail.JPG

If approved, the sender receives an email to notify them, and the ‘Sign off status’ column changes to ‘Approved’.

Once the reply has been approved, it can be finalised and sent to the person who wrote the correspondence. All versions of the draft reply are kept in the same folder, along with the final.

Email replies

Once approved, any email reply could be sent directly to the correspondent from the O365 Group’s mailbox.

Reporting

Metadata from the document library can be exported to Excel, or to business intelligence systems (or PowerBI) for analysis and reporting purposes.

Summing up

Correspondence can be managed in SharePoint, with MS Teams used to provide additional co-authoring and ‘chat’ options for the team, and MS Flow used for more complex approval requirements.

 

 

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.