Showcasing the possible with SharePoint, Forms, Power Automate and Teams

Matt Weston

0 comments

This eBook documents how a manual and time-consuming business process was revolutionised using a variety of techniques. Whilst the initial challenges were faced by the Collab365 team, the solution can be adapted to solve many more use cases and provides an insight into what is possible using standard Microsoft Technologies.

For me, SharePoint still seems to be misunderstood by a large percentage of users. It is still seen as “just somewhere we store documents” or “that clunky thing that hosts the intranet.” This poor reputation is what has been a major blocker in terms of adoption for the later versions of SharePoint, and certainly for SharePoint Online.

As a Consultant, this is a constant battle that I face, and the way that I quite often win is by showing the users what they can achieve if they look at the other capabilities that SharePoint can offer. This is the reason that in this companion eBook to the SharePoint Summit Presentation, we will walk through some of those capabilities.

I was challenged by the team at Collab365 to help them with their Call for Speaker process, which in previous summits has all been done via email, Facebook messenger, linked in, and has led to several challenges:

  • No way of centrally tracking who has volunteered to present
  • Inconsistencies in the way that speakers were submitting
  • Inconsistencies in the information that speakers were supplying, i.e., not providing an overview of the session
  • Lots of time taken undertaking manual processes

Throughout this eBook, I will share with you the solution which was implemented for Collab365 to help them manage this process more efficiently. Everything that you see within the session and also within this eBook has been built on an Enterprise E3 license; there are no premium components involved. Let’s start with a high-level look at how everything works.

There are a number of key components that are required for everything to work within this process:

  • SharePoint Online – The area that is going to be the repository for the data, and where the summits and speakers will be managed.
  • Microsoft Forms – Providing the capability to take input from an external user to feed the submissions into the process
  • Microsoft Flow – The process engine which will move sessions from submission, through approvals and to handle communications with speakers
  • Microsoft Teams – The collaboration and communication area where chat and management can take place in a single portal

As you can see, there are four key technologies all at play, but SharePoint sits at the heart of them all.

Starting the Process

The first part of the build for the solution was to consider how we were going to capture data from the speakers. The submission form would have to be available externally to Collab365, capture all of the data required with some basic validation, and would then need to feed the data to somewhere that the submissions could be managed.

We initially considered several options as to how we would do this:

  • Anonymous access to the hosting SharePoint site – this would mean that anyone would be able to access the site as well as the list that would contain the

submissions. This was not going to be ideal for a number of reasons, not only would it be considerable effort to manage the permissions on the site, the actual list entry experience would not be the best as the speakers would just be filling in the basic SharePoint list form. While PowerApps can be used to create a custom SharePoint list form, these are not available to external users so they could not be considered for the solution.

  • We considered sharing the site, much like in the previous option, but using PowerApps to create a custom interface so that users would have a much better experience when submitting sessions. PowerApps are a great way of embedding rich experiences into a SharePoint page, but at the time of writing, they are not available for external users. I should point out that it is in the roadmap to make them externally available; however it is not clear at the moment how this will work for anonymous users.
  • Microsoft Forms gives us the ability to create and manage a survey within Office 365 and then allow it to be filled in anonymously by external users. While this doesn’t integrate directly with SharePoint, it is fairly easy to be able to build the Flow to do this, which will be covered later.

Introducing Microsoft Forms

Microsoft Forms is included with your Office 365 license and allows you to create surveys through an extremely easy and intuitive interface. Forms can also be used to produce quizzes, which when I first speak about quizzes the usual reaction is “Why would I want to create a quiz?” I use a Microsoft Forms Quiz as part of my interviews, having created a technical test that automatically gets scored and results emailed to me so that I can walk back into the room I have the test to discuss with the candidate. But for now, back to the survey.

When we create a new survey within Microsoft Forms, we don’t have a huge number of different data types to select from, which makes this tool simple to use as it hasn’t been overcomplicated. The basic data types I can select from are:

  • Choice – This is your basic closed response type question, where you are providing a number of possible answers to your users. It will normally be shown as a radio button which means that the user can only select one answer. This can be changed, however, to allow multiple options to be selected, or to be displayed as a drop-down box instead.
  • Text – The mainstay of any form, the control to allow the user to enter textual information into the field. The two options you have with this type of field is to either be a single line of text or to allow multiple lines of text.
  • Rating – A simple mechanism to allow users to supply a rating for something. This can be done as either a Star Rating or a standard numeric rating. Ratings can have a maximum of 10 levels from which you can choose.
  • Date – A simple date picker that allows you to take in a date value from the Form.
  • Ranking – This field is becoming more useful for customers using Microsoft Forms to gather feedback, usually around services provided. It allows you to supply several options which your users can then rank up and down depending on their answer.
  • Likert – These fields are driven from the Likert Scale which is used to measure a user’s attitude towards a statement. These are quite often used in Employee Engagement surveys where you’re looking for whether a user agrees with a statement or not.
  • Net Promoter Score – The final data type is the Net Promoter Score (NPS), which is used more and more with organisations who are looking for feedback on their services. It feels much like the rating; however, it is in a format that is well known for being used for gathering service feedback.

As well as being able to create questions with different types, we can also create sections to separate the questions in our survey. Sections, when viewed in a Form, act as pages. With regards to the Request for Speakers solution produced for Collab365, the questions were broken down into two sections: the first being related to the session, and the second gathering information about the speaker.

Once we had the basic information being captured from the Form, we then needed to add some branding to it.

Branding Forms

Like most areas of Office 365, branding is a very light touch and is done within quite strict guidelines. Microsoft Forms is no different; however, the branding which you apply can have quite an impact.

Theme configuration for your Form is found in the top right corner of the screen and will bring up a number of design ideas which you can apply to your Form.

If you don’t want to use one of the suggested themes, you can create your own by clicking on the very last square that has a + icon. As I previously mentioned, the branding options are extremely limited, allowing only the modification of either the background image or the background colour. But with some good imagery in the background, your Form can make a huge impact.

The background image will remain static behind the form; therefore, you should concentrate on creating a background that will adequately fill a browser window when viewed with a high resolution.

Now that we have created and branded our Form, we need to share it externally so that people can fill it in.

Sharing Forms

Everything that you do in Microsoft Forms is designed to be limited in terms of the options to reduce the complexity of the application. Sharing is no different.

Sharing is done by clicking on the Sharing button in the top right corner of the screen, which will open a blade on the right side of the screen. There are different reasons that you would want to share the Form; it could be either for the collection of responses or could be that you wish to collaborate.

With regards to sending and collecting responses, you can define whether “Anyone with the link can respond” or whether “Only people in my organisation can respond.” The first option here allows anonymous responses, which is what allows us to publish a link out onto the internet to collect the speaker’s information without them having to register. I can also choose how I am going to share the link to the form, ranging from a standard hyperlink to a QR code.

Now that we have created our Form, we need to build our data repository, which is going to live within SharePoint Online.

Storing the Information

SharePoint has come a very long way over the last couple of years, making a journey from being the rather clunky Classic user experience, to the new responsive Modern experience. The modern experience is still maturing, and not everything from Classic is available yet; however, it is improving at a fantastic rate. The key thing to remember with the Modern Experience is that it is designed to work on a mobile device, so gone are the days of having to write complex style sheets to make a SharePoint site work in both a browser and on a phone.

There are a number of different pieces of information that we needed to store within SharePoint to support the solution.

  • A list to store information related to the Summits (name, start and end dates, etc.)
  • A list to store submissions which will be fed from Microsoft Forms
  • A document library to hold any documents which are related to a session

To clarify the difference between Lists and Libraries, as this is a common question. A list is simply a list of data, compare it to a table within Microsoft Excel, where you can fill in several columns containing different types of data. A document library, on the other hand, contains a physical file to which you can apply metadata to assist with the identification and searching of a document.

Summits

The Summits list is designed to hold high-level information about the summit, such as the name, the start and end date, and the dates in which the various collateral is due from the speaker. This list proved to be an important element of the planning process where the deadlines could be stored and reviewed by the Collab365 Team to make sure everything was being planned in time.

This was quite a simple list created using the modern list template within SharePoint Online. A modern list is created with just the Item content type, and from there, I added some basic fields. In Modern SharePoint, this is really simple to do, as there is an “Add column” link in the last column in every list. This will allow you to create your fields directly in the list without ever having to move away to another screen.

Most fields are now available in the modern user interface, and there is even a new one that has been added. There is now the ability to create a Location column type, which will take a location input and automatically break out the constituent parts of the address into additional columns. Likewise, some columns are still considered classic, and while they work with the modern interface, they will appear on the “More…” link and take you back to the classic create column page. These columns include the calculated column, lookup and managed metadata fields.

The columns which were needed within the Summits list:

  • Summit Start Date – a date field which will allow planning to be conducted up to the start of the summit. This field can also be used for contextual information in the correspondence sent to speakers.
  • Summit End Date – a date field which allows the summit team to plan how many sessions they will be able to get within the summit. This field can also be used for contextual information in the correspondence sent to speakers.
  • Video Submission Deadline – a date field which would help the Summit team to govern when the video submissions should be received.
  • eBook Submission Deadline – a date field which would help the Summit team to govern when the eBook submissions should be received.

The thing that I should point out here is that I haven’t created the columns within this list as part of a content type. The reason for this is because I don’t need to reuse the column combination elsewhere within the solution. Whilst it appears that content types are no longer part of a modern SharePoint site, let’s face it unless you know to go looking for them then you don’t know they’re there, they are still extremely useful if you wish to use the same columns across multiple lists and want to reduce the overhead of maintaining them in the future.

The key thing to do next was to create the list which was going to receive the submissions from our Microsoft Form.

Submissions

The list which will contain the submissions received from Microsoft Forms will contain the main working data within the Virtual Summit Manager solution. The idea is that it will take the submission information and drive the approval process as well as provide management information for the Collab365 team. The data contained within the list would be used for planning the summits and managing the post-selection activities to ensure that all tasks had been completed.

This time, I have created a Content-Type, as it’s feasible in the future that the columns within the list could be reused. Therefore, I have used the Site Content Type gallery under Site Settings to manage the column creation.

The Site Settings link within Modern SharePoint is now hidden away slightly. From the settings cog within a SharePoint site, select “Site information”, and then you can select the “View all site settings” link to get to your main site settings page.

Some of the key fields within this list are:

  • Title – The title of the session to be presented which will be fed into the promotional activities for the summit
  • Abstract – The outline of the session which will be used to feed into the promotional website
  • Email – Used to contact the speaker through the automated processes or through manual emails
  • Session Status – A choice field which specifies whether the session has been reviewed during by the team, and finally if the session has been accepted or rejected
  • Video Expected – A date field, which is set initially, to the Video Submission Deadline date from the Summits list. This can be changed based on communication with the speaker, so if there are mitigating circumstances then a revised date can be set on an individual basis
  • Video Received – A simple yes or no to state that the video has been received by the Collab365 team
  • Video Checked – A yes or no field to state that the video has been checked by the Collab365 team
  • Video Uploaded – A yes or no field to state that the video has been uploaded to the streaming platform

There are additional fields that make up the composition of this list that provide additional detail to the overall management of the session, but these are the key fields.

Documents (and Microsoft Teams)

Finally, the Collab365 team needed somewhere to store all of the collateral collected for the summit, in one place and where they could manage it as a team. This includes video files, eBooks, promotional imagery and other data related to each of the sessions. This is where, after a brief discussion, we decided to bring Microsoft Teams into the solution to provide the collaboration skin across the rest of the solution.

When a team is created from a SharePoint site, the default Document library becomes the repository that can be seen in Files when you look in a Microsoft Teams Channel. Within the document library, each channel has a Folder, so we decided that each summit would have a channel created for it. This would mean that there would be an individual conversation area for each summit as well as a separate file store.

We then continued that thinking and considered bringing other aspects of the SharePoint site into Teams on tabs to make it even easier to communicate and collaborate in a single space. Therefore, we added the Summits list and the Submissions list into Teams as addition tabs in the General Channel.

Now we had got several technologies being used within the solution; it was time to turn our attention to creating automation to make the life of the team much easier.

Building the Process Flow

In order to keep within the Office 365 E3 licensing, we made the decision to use Microsoft Flow as the automation engine. As a brief introduction, Microsoft Flow is an automation and workflow tool that is intended to be used by business users, not just developers. It is browser-based and allows you to graphically create business logic, which will interact with a number of Microsoft and non-Microsoft data sources.

There were three Flows that were created to support the solution:

  • Summit Asset Creation
  • Summit Submission Processing
  • Chase Video Submissions

Let’s have a closer look at each one:

Summit Asset Creation

This Flow is designed to help set all of the key areas up when a new Summit goes into the Summits list. It is triggered when a new item is added to the Summits list and will create a new channel with Microsoft Teams for the Summit.

Trigger: When an item is created

The trigger fires when a new item is added to the Summits list and this uses the “When an item is created” trigger which is classed as an automated trigger. It is a common misconception that this will fire immediately. In truth, the trigger polls on a schedule to see if it needs to react to the creation of a new item, which in our O365 licensing means that it can take up to 1 minute for the Flow to fire.

There were two actions added to the Flow:

  • Create a channel
  • Send an email (V2)

Action: Create a Channel

This action is provided by the Microsoft Teams connector and allows us to specify the creation of a channel in a specific Team. It would simply take the Title field from the Summits list and use it to create the new channel.

Action: Send an email (V2)

This action is designed to let the Summit creator know that everything has been provisioned and that they can now go ahead and update the Microsoft Form used for the submissions to include the new summit. As you’ll see in the next session it is important that the Channel is in place prior to any submissions being entered.

Fixing a Bug

When we were testing we found that special characters such as an Ampersand (&) can’t be used within the name of a Channel. Therefore, we needed to include an action at the beginning to replace the ampersand with the word “and.” So PowerBI & Excel would be transformed into “Power BI and Excel.”

An “initialize variable” action was added at the start of the Flow to immediately replace any ampersands within the Title field with the word “and.” This was done by using the “replace” expression, which allows me to take a string, find a string, and then replace it with another.

Replace(triggerBody()?[‘Title’],’&’,’and’)

This variable was then used everywhere else within the Flow that was previously referencing the Title dynamic content, e.g. in the Channel Name or the Email sent to the creator.

Finally, to ensure that this new title was reflected in the Summit list, we use the “Update item” action to update the summit list item.

Now that I can be sure that my Teams channel is being created for me, and that the creator has email instructions on updating the Microsoft Form, I can turn my attention to getting the data from Microsoft Forms into my SharePoint list so that we can process it.

Summit Submission Processing

The Summit Submission Processing Flow is the largest of the Flows as it deals with the collection of data from Microsoft Forms, puts it into SharePoint and then deals with the approval of the session. Within this Flow, we will be dealing with Approvals, but looking at how we can use Microsoft Teams to manage our approvals rather than the standard way of relying on email. The one thing I’ll point out here is that I use Scopes a lot within my Flows.

Scopes can be compared to Workflow Steps in SharePoint Designer Workflows, as they allow me to group actions in my Flow into logical groups. In the screenshots, Scopes are red/brown in colour.

Trigger: When a new response is submitted

Microsoft Forms has an automated trigger that will fire when a response has been submitted. The key thing to be aware of here is that the trigger will only capture the response ID, it won’t actually capture any answers to the questions at this point. So we need to get that information using a different action.

Action: Get Response Details

Within this action, from the Microsoft Forms connector, we need to provide the ID of the form and then the response ID, which is available from the trigger. The Trigger returns an array of responses, which means that when I reference the Response ID, a loop is created even though there’s only one ID returned. If you want to leave it like that, then this will still work; however, I like to keep it tidy, so again I use an expression to pull out the ID without looping.

triggerBody()?[‘value’][0]?[‘resourceData’]?[‘responseId’]

In the above expression, I am pulling out the response ID from where it sits within the JSON body response from the Form. The key thing to point out is [0] denotes the array index which I’m retrieving. As I know there’s only going to be one response ID; I can confidently just grab the first node.

Once we have the response details within the Flow, we can start to process all of the questions that were entered in the Form. But before I can start to create the submission list item, I need to get some details about the Summit in which they want to speak.

Action: Get Items

The next key action is to use Get Items to get information from the Summits list so that I can use some of the summit information later in the Flow. This would include elements such as the dates that the summit is running and the deadline date for the videos.

The reason that I am using “Get Items” rather than “Get Item” is because I don’t know the exact list item ID of the summit, I need to filter Summits based on the title (which was selected from the Form). So for that, I need to use the Filter Query.

Filter queries need a little bit of effort to get into, but once you understand them they can be quite a powerful tool in your Flow toolbox. If you need a little bit more of an explanation about Filter Queries then feel free to check out this video:

Now that I have the information about the summit, I can go ahead and create the entry in the Submissions list.

Action: Create Item

I will now create the entry in the submission list using the Create item action from the SharePoint Connector. This will take the answers to the questions and map them to the relevant fields within the SharePoint list. It will also take the relevant information from the Summit, such as the Video Submission Deadline date, and place that into the submissions list. The keen-eyed readers of this eBook will spot that I’m actually duplicating data here to an extent, by holding the video submission in the Summits list as well as the Submissions list. This is purely so that each person can be managed individually, so if one person requires an extension then that is possible.

Next in the process was to retrieve the details from Microsoft Teams so that we can put the information for approval in the right place.

Action: Send an email

Now that we have created the item in the submission list, we need to let the prospective speaker know that the submission has been received and that it is being processed by the Collab365 team. For those who watched the video, you will see that I used the Send an Email (V2) action, which is still in preview. The reason why I always use this is that the default email type for the V2 action is HTML, which means that it’s easier for me to put some information in and have it formatted. The original Send an Email action will format the email as Plain Text unless you explicitly change the format in the advanced settings.

Within the email, we included dynamic content (content available from the other actions) throughout the body to make the email as personal as possible. Information such as the name, summit, and session title were used within the body to give the email context.

Once the email has been sent, the focus of the submission process turns to the internal approval process for the Collab365 team. In order to start that, we need to figure out where to post it.

Action: List Channels

Posting to a channel is straightforward as long as you are selecting the channel name from the Flow user interface. When you select the channel, you actually provide Flow with a channel ID which is a long string of random characters resembling an email address.

In order to dynamically find this ID, we need to List the Channels contained in the relevant Team, which will return an array of channels that can then be filtered by the name to get the relevant information.

Action: Filter Array

The Filter Array action is really useful, especially if Filter Queries are not available, or they’re outside of your comfort zone. They allow you to take the array output from a number of actions and perform a filter based on a visual Flow action.

I can, therefore, use this web part to take the array which was returned by the List Channels action, and filtering it by the title to the exact channel which I want. I can then use the ID returned from the filter when I need to post to Teams later.

First of all, though, I want to start the approval process.

Action: Create an Approval

There are two ways of being able to start an approval process. You can either use the action called “Start and Wait for Approval” which will manage the entire approval process in a single action. You provide it with the type of approval it requires, e.g. first to respond, unanimous approval, or custom options if you pay for the premium licensing.

The second way is to use the “Create an Approval” action, which will begin the process of getting approval, and I can set the same settings as the first way; however, I then need to add a separate action to tell the Flow to wait for a response. The reason that you would do it this way is if you want more control over when the Flow comes to a pause, for example, if you want to add some additional steps after the approval has started.

The perfect example of this is posting an adaptive card to Microsoft Teams.

Once the action to generate the adaptive card has been created, we then need to post it to Teams.

Action: Post your adaptive card as the Flow bot to a channel

As Microsoft Teams has featured so highly as an interface to the data that we’re storing

within SharePoint, we decided that this would be the perfect place to review the

submissions. This is a relatively new action for Microsoft Teams. If you’re interested in adaptive cards then you can consider looking at the designer https://adaptivecards.io/designer/. However, in the case of Approvals, the adaptive card is provided as dynamic content, so we don’t need to design our own.

The only thing we need to do here is to change the Send Notifications option to No so that the approval email is not sent. The approvals in Microsoft Teams and Outlook are unrelated, so if someone approves in Outlook, the Teams approval would not be updated.

Once we had posted the adaptive card to teams, we would then pause the Flow until a response was received, and then we could process the outcome.

Condition: If the Session is Approved

If the speaker is successfully selected to speak at the Summit, then we would generate an email to that person to inform them that they will be presenting. However, we were able to take content from the Summit list as well as detail from the submission to supply additional contextual information.

As we neared the end of the project, we thought that a huge value add would be to generate a folder in SharePoint to store the content for the session and then provide a sharing link to the speaker. The folder generated to store the collateral for the session would be shared with the speaker so that they could upload their video and any other supporting content directly into the SharePoint site. This would remove the need for the Collab365 team to receive huge attachments, and also provide a consistent way for speakers to supply their content. Rather than using OneDive, Dropbox, emails, etc, they would simply upload the content directly to their folder.

This was done using the Create Folder action to, first of all, create the folder based on the name of the session.

Once the folder has been created the sharing link can be generated using the “Create sharing link to file or folder” action.

The key thing to remember with this action is that I have only two options in terms of creating the link. I can either share the link internally, which means that anyone with the link has to be able to authenticate against Azure AD, or I can generate an anonymous sharing link, which means that anyone with the link can access the folder.

Condition: If the Session is Not Approved

If the session is not successful in being selected, then we would send an email to the person who submitted the session to inform them of the outcome.

The final process automation which we implemented for the solution was to send reminders automatically when videos were due for submission.

Chase Video Process Flow

One of the key processes which are done manually, and currently takes the Collab365 team a huge amount of time to do, is to send out the reminders to speakers that their videos are due. This involves gathering information about the speaker, the session and the due date to send out reminders just before the deadline date, on the deadline date and then subsequently afterwards.

This was the process that would save the Collab365 team the most amount of time as it took anywhere between 5 and 10 minutes, per submission, to gather the information, write the email and then send it. If you consider that there may be 12 speakers, then the team was losing between 1 and 2 hours per day just sending out reminders. Therefore, we automated it!

Trigger: Recurrence

Recurrence triggers allow you to build Flows that run on a schedule. Using this trigger, you could have a Flow running anywhere from every few seconds, to once per month. I have previously written a blog for Collab365 which explains how a recurrence can be used

to replace scheduled tasks, and which talks more about the various configuration options:

https://collab365.community/replace-scheduled-tasks-flow-recurrence/

We have configured our recurrence trigger to run once per day at approx. Midnight, Pacific Time. This is to ensure that everyone receives the notification on an intended day.

Action: Get Items – Get Upcoming Summits

The first thing that I need to do is to get the list of summits from the summit list. However, the only summits I’m interested in processing are those that are in the future as there is no point in processing ones that have finished.

For this I have used Get Items and used it to interrogate my Summits list; however, I have also used the Filter Query field to provide it with criteria to return. I.e. where the start date of the summit is not in the past. For that I have used the following query:

SummitStartDate ge ‘utcNow()’

Once I have got my list of Summits I can then loop through each summit and start to process the submissions for each one.

Action: Get Items – Get Accepted Submissions

The Apply to each loop will always be created when I use some dynamic content from an action that returns multiple items. In this case, I wanted to get the items stored in the submission list where the session met specific criteria:

  • Where the summit is equal to the summit provided by Get Items – Summits
  • Process only the sessions marked as accepted by the Collab365 team during the approval process – this is so we are not chasing those not accepted
  • Video has not been received – not fair to chase those that are on top of their admin!

Now that we have the summit and the sessions, we just need to determine if we need to chase them. This is all done on the deadline date for the video as specified in their submission. By default it will be the deadline date for the summit; however, there may be extenuating circumstances for some people meaning the date has been modified on their submission.

Sending the Reminders

Sending the reminders takes a few steps, so I’ll walk you through each part. The first thing we need to do is figure out how long is left before the video is expected to be submitted. When dealing with dates, the underlying unit of measurement is called a Tick

and is expressed as the number of 100-nano second intervals that have elapsed since midnight, January 1, 0001 (Gregorian calendar). Therefore, the calculation which we use is to work out the number of ticks between the current date and the date that the video is due. For ease, I always like to use variables to break out the various pieces of information. I, therefore, have a couple of variables:

  • Video Expected Date – the dynamic content taken from the submission list item
  • Date Today – calculated using the expression utcNow() which will return the current UTC date and time

Once I have both pieces of information, I can calculate the number of days before the deadline with the following calculation:

Video Expected Date – Date Today, all divided by 864000000000

div(sub(variables(‘Video Expected Date’),variables(‘Date Today’)),864000000000)

This date was then taken and used to set another variable that would figure out if the deadline is in the future, two days away, today, or in the past. This would then determine whether contact was needed, and if so, what that email would contain.

The conditional logic for figuring out if the video was in the future could have been done as a number of consecutive If statements, however in order to keep things neat I

chose to do it as an expression, using the various mathematical expressions to do my comparisons:

if(equals(variables(‘Date Difference’),2),’Pending’,if(less(variables(‘Date Difference’),0),’Late’,if(equals(variables(‘Date Difference’),0),’Today’,’OK’)))

The following conditions were therefore set by this expression:

  • If the date difference is 2, then the deadline is pending
  • If the date difference is less than 0, then the video is late
  • If the date difference is equal to 0, then the video is due today
  • Otherwise, it is ok

With the status of the video now known, we could use that within a Switch statement to ensure that we sent the correct email to the speaker. A switch statement is similar to a Condition, whereby I can branch the logic within my Flow. The difference, however, is that you are not just working with Boolean (true/false) values; instead you can work with other types of data and can branch in several different directions.

Each leg, except for the Default (OK) leg, sends an email to “remind” the speakers that

they need to submit their videos.

There is a lot of heavy lifting being done by Flow to enable the SharePoint based solution to drive efficiencies for the users. There have been a lot of different topics covered here with regards to connections, techniques, and practices, and if you’re interested in learning more about Flow then I would recommend checking out the Collab365 eBook collection from the Flow summit which covers many of these topics.

Now that we have completed the automation aspect of the solutions, the last thing is to look at the bells and whistles which were added to the solution to round it all off.

The Bells and Whistles

The solution as it stands at the moment will do everything that the Collab365 team wants it to do, however from my experience as a consultant, there are a few additional things that I can do to add just that little extra value to the solution. Starting with how the information is presented within the lists.

SharePoint Conditional Formatting

Conditional formatting in SharePoint used to be the job of developers as it involved writing XLST (SharePoint 2010), JavaScript (SharePoint 2013/16), or writing JSON (earlier versions of SharePoint Online). However, now a business user can apply some basic formatting to a column within SharePoint to do things such as changing the colour when a specific value is provided.

I have done this specifically on columns that are displaying a key piece of information, such as the column denoting if a video has been received, checked, uploaded, etc. The idea behind this is to give the Collab365 team a very basic dashboard for the summit, which they can review to see exactly where they are in terms of gathering all of their content.

The same was also applied to the approval status of the sessions so again, with a very quick glance the team can see what is waiting for approval and what has been approved or rejected.

This can be achieved from within a list view by interacting with the column header. When you select the dropdown, you will have the option to view column settings, which will fly out to a menu allowing you to select “Format this column”

When you select Format this column, the formatting blade will appear on the right-hand side. You can now apply some simple formatting here, by selecting the relevant template (normally Fill background colors) and selecting edit template.

You will then be able to set the colours for the different values. In this example, the options are Yes or No, therefore I have selected a shade of red for No and a shade of light green for Yes.

This now means that you can apply basic visualisations to what would otherwise be a textual list, and that small change can make a huge difference between your users loving a solution and using it, or forcing them to go back to what they have always used (probably Excel!).

Summary

We have now covered the key elements of the solution which is being actively used by the Collab365 Team to manage and plan their Virtual Summits. As you can see there are a lot of moving parts, and a lot of pieces of Office 365 all being brought together like a jigsaw.

We used Microsoft Forms to provide the initial capture for new speaker submissions as that would allow the team to get inputs from external users without having to invite them in as guests. It also meant that they could do this without incurring any additional costs or any custom web development to allow people to submit their ideas.

The data was taken from Microsoft Forms and placed into SharePoint using Microsoft Flow. Using an automated trigger based on the Form being completed, the Flow would get the response details and place it into a SharePoint list. It would automatically send a response to the speaker to thank them for submitting, and would then send the submission to the Summit team to assess whether the session would be included. The management of the Approval was done by using Adaptive Cards in Microsoft Teams so that the team could chat about the session before making their decision.

If the session was approved, then it would then create a folder in the SharePoint document library to hold the files related to the session e.g. presentations, graphics, videos and eBooks. Flow automatically created a sharing link, utilising the power of SharePoint external sharing to allow the speakers to automatically upload their content directly into the Collab365 solution reducing the need for large attachments or gathering data from a wide range of file sharing solutions.

Throughout the approval process until the actual summit, Flow checks to make sure that the speakers are meeting their deadlines, sending reminders just before their videos are due, on the day and then every day afterwards until the video is marked as being received.

All of these parts of the solution have a single driving presence, SharePoint Online, just showing that while it is no longer the centre of the productivity universe, it is the glue that holds it together.

0000-00-00 00:00:00


Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}