Create a Simple Ticketing System in SharePoint Online
I was recently tasked with creating a simple help desk ticketing system in SharePoint Online and Office 365 for a client. While I fully understood from the start that OOTB functionality in SharePoint wouldn’t provide the best means for accomplishing this task, I was pleased to discover that it is surprisingly capable of offering the core requirements of a ticketing system including the following functions:
Email notifications to both the submitter and the Help Desk for various stages of a ticket’s life cycle
The ability to easily contact the submitter for follow-up and create personalized dashboards using SharePoint Online list views
Minor reporting capabilities using Microsoft Forms
The ability to set follow-up dates and times
Filtering and personalized view capabilities to provide each Help Desk/IT employee an optimal, intuitive experience that aligns with their working style
Before I get into how to create a simple ticketing system using SharePoint Online and O365, I’d like to first point out some of the limitations of using this system:
There is no way to reopen a ticket that was erroneously closed (at least not in the boundaries of my imagination–please comment if you have any ideas!)
Reporting and auditing functions only touch user input in Microsoft Forms; tracking metrics and turnaround times is not easily achievable with this system
There is no clean way to track updates and progress on a ticket. If Jessica speaks with the end-user and learns new information about the request in the ticketing system, the only way she can include it in a ticket is by appending it to the original description
Limitations aside, and if I still have your attention, let’s get in to how to actually build this system. Here’s a list of things you will need before getting started:
A service account dedicated to Forms and Flow connections (I’ll likely blog about how important this is in the future)
Access to Microsoft Flow, Forms, and Edit permissions to the underlying SharePoint list
A stable SharePoint site accessible to at least the IT Help Desk of your organization–if not just everyone, depending on content sensitivity and compliance regulations
Got everything in line!? Great! Let’s get started.
The first step is to set up the SharePoint list that will eventually become the ticketing system repository (or database, if you will). A few things to consider while doing this:
What types of permissions do you want on this list? If you’re working in an industry that handles lots of sensitive information, it might be best to target this only to a specific group of users by making the most of SharePoint security groups.
Which columns will be created as choice columns and which will be text columns? In my example, ‘Priority’, ‘Category’, and ‘Status’ are choices because I leverage these columns for creating custom views (more on that later) and want standardization for reviewing and filtering.
Where will this live? Since computers and software will always break and end-users will always need professional assistance, a ticketing system should always be present. Therefore, find a place in your Intranet that is expected to have a happy, long life and place it in an area that is intuitive for both end-users and administrative IT staff. Imagine that Rhafiq’s computer starts on fire and he has a presentation to arrange with a deadline of two hours from now. You’d want him to know exactly where to go to submit his claim to the people equipped to fix it.
Other optional features include the following:
Implementing column formatting to increase recall and usability of the list views, demonstrated in the below image. NOTE: lots of the code needed for this is provided by Microsoft in this article
While many of these customizations exist to draw the Help Desk Admin’s attention to certain tickets over others (such as past-due follow-up dates showing as bold and red and high-priority tickets displaying a red ! icon), some provide some actual utility. For example, the mailbox icon next to the submitter’s name is a mailto link that automatically addresses the submitter. This is helpful for if the Help Desk Admin has follow-up questions or needs additional details.
Consider creating the following views for ease of use and access for multiple members of the team:
My View: filtered on ‘Assigned’ == [Me] and ‘Status’ != Completed
New and Unassigned: filtered on ‘Status’ == ‘New’ and ‘Assigned’ == ” (blank, or unassigned)
Archived: filtered on ‘Status’ == ‘Completed’
I’ve also created a view dubbed “My Requests” for end-users: filtered on ‘Created’ == [Me]
Next, we will create the form for user entry. PowerApps could be an approach for this, but for the purposes of this specific task, I resorted to Microsoft Forms to increase maintainability once we hand this solution off to the client since Forms is much easier to manage as an end-user than PowerApps. Include only the fields that the user is expected to fill out and leave out the rest; soon, we will look at how to pull the user input of this form into SharePoint.
And here we are! The next step is working out the workflow in Microsoft Flow. This will require two workflows for two different triggers; one for when a request is submitted through Forms and one for when the ticket is resolved. The first looks like this:
As you can see, when a new request is submitted through Forms, the inputted information is parsed to the relevant fields in the SharePoint list. The Form is used to prevent the user from manipulating the back-end fields, such as Assigned and Followup. The workflow creates the item in SharePoint, sends an email to the IT Help Desk (I recommend using an O365 Group email for this to ensure that all members of the team receive the request), and sends a confirmation email to the submitter that their ticket was received.
NOTE: The account you use to design this Flow should have “send as” access to a mailbox that is regularly monitored and the “send as” field in the Outlook connectors should list the appropriate mailbox. If someone responds to the receipt email, you’ll want to know that it will be reviewed by the appropriate channels.
From this point, the Help Desk will be notified of the new request and respond accordingly. This part takes a bit of user training; the Help Desk representative will need to edit the list item, assign it to a member on their team, and set a follow-up date (if necessary).
The second Flow is designed to notify the user that the ticket (or list item, technically speaking) has been marked as “Completed.” It is a simple workflow that checks to see if the ‘Status’ field contains the “Completed” value, but it is important nonetheless to ensure tickets are truly resolved. If they aren’t but they’ve been closed regardless, the end-user will at least know that they need to bring this to the Help Desk’s attention (and this is where you might want to get more creative with an automated re-opening procedure):
Finally–and this point is important–we need to share our creations with others. Share your Flow with someone else or another trusted group in your tenant to ensure the highest availability and do the same for your Form (although you can’t fully transfer ownership; this is why using a permanent service account is important). Lastly, you want to make sure that permissions on the SharePoint list behind the ticketing system has more than one trusted owner.
The last thing you’ll want to accomplish to stand up this basic ticketing system in SharePoint Online is surfacing the Form using the Forms web part on an accessible and intuitive SharePoint site. Again, if Rhafiq’s computer is on fire, you want him to be able to get ahold of IT well before the entire building catches on.
And there you have it! In 20 minutes, you can have a ticketing system for your organization’s help desk without having to spend any more than you already spend on your Office 365 subscription. This example is simple and could grow with greater requirements; for example, you could create a workflow to enable users to easily re-open tickets or use an Azure SQL database to track ticket updates and metrics. However, this strategy worked for my client and I hope it helps you at least get a start on your solution, too.