Ticketing System in SharePoint with Power Automate

Ticketing System in SharePoint
Create an End to End Ticketing System using only Microsoft Office Apps. This modern looking advanced ticketing system will allow you to create, approve, update, send notifications and close tickets in Microsoft Forms & SharePoint with Power Automate in just a few clicks.

Table of Contents

Ticketing System using Microsoft Office Apps

Today, I’m excited to share a new project with you a SharePoint-based ticketing system. If you’re like me, you’ve spent countless hours building and maintaining complex systems using SharePoint as the backend.

While SharePoint is great for storing documents and records, creating a fully functional ticketing system in SharePoint is a complicated and time-consuming process.
Most organizations start with a Minimum Viable Product (MVP) and build out the system from there, with little to no regard for scalability or future needs.

Fortunately, there is an easier way: use Power Automate and Forms to build a scalable, customizable, and robust ticketing system. Using the power of automation and the Forms feature in Power Automate, I was able to build a modern ticketing system in SharePoint that’s simple to use and easy to scale.

This will allow you to create, approve, update, send notifications and close tickets in SharePoint with just a few clicks. You can customize the look and feel of the system to match your brand, and add additional functionality as needed.

SharePoint Ticketing System Overview

An end to end ticketing system with approval process and that can be used by multiple users within the organization for submitting their support queries.

This ticketing system should be able to provide automatic Status updates and visibility towards the tickets submitted by them anytime.

Support team should be able to open the tickets, work on it, update status, add comments and close the ticket.

Required Microsoft Office Apps

  1. Microsoft Forms
  2. Microsoft OneDrive
  3. Microsoft Power Automate
  4. Microsoft SharePoint
  5. Microsoft Outlook
  6. Microsoft Lists
  7. Microsoft Teams (Optional)

Please check your Office 365 portal to make sure you have access to the required apps.

In case you are missing access to any of the above, you can buy any office products licences from our Partner Website at a cost that is much lower than the normal price.

Terms Frequently Used In This tutorial

Okay, now let’s get familiar with some of the below terms, as we will be using it more frequently through out the development process.

  1. Forms: Microsoft Forms
  2. Submitter/Responder/User: Person who submits the Ticket (Responder to the Forms)
  3. Response: Data submitted through the Forms (Information that will be used for the Ticket creation)
  4. Approver: Person who approves the Response before moving it to the SharePoint List as a New Ticket.
  5. SharePoint List: A List that contains all the Tickets with unique ID (Ticket No.)
  6. SharePoint List Item/Item: A single Approved Response (Ticket) in the SharePoint List.

How to Create a Ticketing System in SharePoint :

Below is the Process Flow for how this SharePoint ticketing system works. Then followed by a detailed step by step instructions on what we are going to do to make this ticketing system work.

Create A Ticketing System In Sharepoint With Power Automate And FormsPin

3 Major components of the ticketing system:

  1. Microsoft Form (Frontend)
  2. SharePoint (Backend)
  3. Power Automate Workflow (Automation & Integration)

Ticketing System: Power Automate Development Flow

Please note that, you do not need to have any prior programming skills to build this ticketing system. Please follow the instructions below and you will be able to build this.

1. Microsoft forms with attachments

Create a Microsoft Form with all required fields like, Ticket Title, Issue Description etc. along with an ‘Attach File’ Option. This will serve as the Front End of our Ticketing System, where users can fill in all the information required to create a ticket. 

For this example name the Form as ‘Support Ticket

How to create Microsoft forms with attachments
  1. Click Add new.
  2. Click the drop down menu for additional options (if file upload is not shown)
  3. Click on file upload.

Below is an example video shows how to create Microsoft Forms with attachments.

2. Create SharePoint Lists

Create Two SharePoint Lists. One for storing all tickets (Eg.: ‘Support Tickets‘), this list should contain all corresponding Columns/Fields same as in the Form.

Then create another list with the all people who can submit the ticket (Eg.: ‘Ticket Submitters‘) and if they need approval from their manager  – Yes or No.

Share Point List ExamplePin
Sharepoint List Example - Ticket Submitters
How to create sharepoint list
  1. Select Settings. …
  2. Select + New, and then select List.
  3. Enter a Name for the list, and optionally, a Description. …
  4. Select Create.
  5. When your list opens, to add room for more types of information to the list, select + or + Add column.
Below is an example video shows how to create lists in Sharepoint

3. Microsoft forms approval workflow

Create a User specific Approval Process for the responses submitted through Forms.

In this example we are creating an Approval process where ‘some users require approval from manager‘ and ‘some do not require approval‘ before it being saved to the SharePoint List (submitted to the Support Team).

How to create a Power Automate Workflow for Ticketing System

Follow the below steps to create the Power Automate workflow to automate the ticketing system.

  • Go to Microsoft Power Automate and
  • Click on + Create
  • Select Automated cloud flow
  • Add a name for your flow Flow name
  • Choose a flow trigger, in our case ‘When a new response is submitted‘ Microsoft Forms

This is the first step where we are asking the power automate to starting the process whenever someone submits a New Response/Ticket to the Form.
Select the Form Id which is the name of the Form (Support Ticket)

When A New Response Is Submitted
  • Add Action ‘Get response‘ details from the Form

Now we are asking power automate to get all the details from the response submitted by the user through the Form

Get ResponsePin
  • Add Action ‘Get items‘ from the SharePoint List ‘Ticket Submitters

At this step we are asking Power Automate to Get items from a Sharepoint List ‘Ticket Sumbitters’ and checking it against the Form Responders’ Email Id

Get ItemsPin
  • Add a ‘Condition‘ Check if Approval required is equal to Yes or No

Here we are checking whether the Responders’ email Id has been marked as Approval required Yes or No from the ‘Ticket Submitters‘ List

Power Automate Condition - Approval CheckPin
  • If Yes :

4. Power Automate Approval Flow

Responses submitted by Users who Require Approval goes through an Approval Process.

Now we will create a set of actions that need to be followed since the Approval status is equal to Yes

  •  Start Microsoft forms approval workflow action ‘Start and wait for an approval

Since this responder was marked as Approval Yes, we are starting an Approval flow here, where the Power Automate will send an Approval request email to the Approver (Responder’s manager)

Approval Type: Approve/Reject – First to respond 
Title: Subject of the approval email
Assigned to: Approver/Manager’s email id

Start And Wait For An ApprovalPin
  • Add another ‘Condition‘ to check Approval status

Now we are adding another condition to check the Response of the manager, whether the manager Approve the request or Reject it.

Power Automate ApprovalPin
  • If Approve

Now will create another set of actions If the the Response is Yes

5. Microsoft forms to SharePoint List

If Approver approves the Response then it will get saved to the SharePoint List as a New SharePoint List Item with a unique ID (Ticket No.).
  • Add Action ‘Create item’ in SharePoint List

At this stage we will ask power automate to create a new SharePoint List Item with all information received from the From submitted by the User.

Site Address: From the drop down list select the SharePoint site address where the Lists are created.
List Name: Select the name of the List where the ticket details should be saved (in our case ‘Support Tickets‘).

Once the List Name is selected, then all the fields available in the List will be populated automatically below the List Name with Field/Column names on the left. Now you need to map the corresponding field value from the Form against each field from the List. 

You can simply place the cursor on the blank field and then a pop-up window will show all the fields available in the Form and then click to select the column name to map it.

Sharepoint Create ItemPin

6. Change 'Created by' In SharePoint List

  • Change ‘Created By’ using Send HTTP request to SharePoint

Created by‘ is a default field in SharePoint which corresponds to the person who created that particular item.

In our case item is not manually created, it is being created by the Power Automate Flow using the information from the Forms.

So while creating a new Item in the SharePoint List we will modify the Value for ‘Created by’ in SharePoint using some JSON code. Don’t worry about the JSON code, its not rocket science, I will exactly show you how to do it and provide you the JSON code also.

SharePoint automatically takes SharePoint Owner ID (basically the creator of the SharePoint List) as the Value for ‘Created by’ but we need to have this changed to the Submitter of the Ticket.

By doing this you can provide access to the Submitter to this SharePoint List and they can see the information of all the tickets submitted by them anytime. For this we need to enable ‘Item-level Permission’ on the SharePoint List from List Settings, and provide ‘Read Only’ access, so that they will see only tickets submitted by them and not by the others (“Read items that were created by the user”).

Change Created By SharepointPin

JSON Code Compose User

				
					[
  {
    "Key": "i:0#.f|membership|@{outputs('Get_response')?['body/responder']}"
  }
]
				
			

JSON Code CreatedBy

				
					[
  {
    "FieldName": "Author",
    "FieldValue": "@{outputs('Compose_User_2')}"
  }
]
				
			

Uri – Send HTTP request to SharePoint

				
					_api/web/lists/getbytitle('Support Tickets')/items(@{outputs('Create_item_2')?['body/ID']})/validateUpdateListItem
				
			

Body – Send HTTP request to SharePoint

				
					{
"formValues":@{outputs('CreatedBy')},
"bNewDocumentUpdate":false
}
				
			

7. Microsoft Forms Attachments To SharePoint List

As I have mentioned in Step 1, we will create a Form with “Attach File” option. This will enable the user to attach any file or documents while submitting the Form.

For example, Error Screenshot, An Excel file, PDF document etc.

This will help the Support team and anyone who has access to the List to see the attachments right from the SharePoint List and open it by click of a button.

By default Microsoft Forms saves all attachments submitted through the Forms in the OneDrive. And the attachments won’t be automatically saved to the SharePoint List while a new Item is created in the SharePoint List.

We need to have this extracted from OneDrive and attach to the list by passing a JSON code. As I mentioned earlier don’t worry about the JSON code, I will exactly show you how to do it and provide you the JSON code also.

  • Add another Condition to check if there are any Attachments to the Form

Add the Form field to the Condition, in our case ‘Upload Attachments‘ and check is not equal to blank

Microsoft Forms Attachments To Sharepoint ListPin
  • If Yes

The Condition is not equal to blank – True means Contains attachment, then need to start the process to extract the file from the OneDrive location and attach it to the SharePoint List

  • Get the file from OneDrive and Attach to the SharePaoint List Item
Microsoft Forms Attachments To Sharepoint List - Parse JsonPin
Microsoft Forms Attachments To Sharepoint List Add AttachmentPin

Below video shows the detailed process that you can follow to do this step.

Parse JSON Schema code for the Power Automate Microsoft Forms Attachments To SharePoint List

				
					{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "name": {
                "type": "string"
            },
            "link": {
                "type": "string"
            },
            "id": {
                "type": "string"
            },
            "type": {},
            "size": {
                "type": "integer"
            },
            "referenceId": {
                "type": "string"
            },
            "driveId": {
                "type": "string"
            },
            "status": {
                "type": "integer"
            },
            "uploadSessionUrl": {}
        },
        "required": [
            "name",
            "link",
            "id",
            "type",
            "size",
            "referenceId",
            "driveId",
            "status",
            "uploadSessionUrl"
        ]
    }
}
				
			
  • If No

What if the The Condition is not equal to blank – False means the Form submission doesn’t contain any attachment, in that case you don’t need to add any action under the If no section and leave it blank. 
The List Item will be created without any attachment. 

9. Microsoft forms Approval email notification

Now that the ticket has been successfully created after approval from the manager in the SharePoint List as a New SharePoint List Item with a unique Ticket No, we can send an automatic email confirmation to the requester.

10. Power Automate send email from shared mailbox

An automatic E-Mail notification will be send to the Submitter from a Generic Mailbox to confirm the successful Submission of the Ticket. We will configure the Power Automate to send email from a shared mailbox and not from the person who created the flow.

Instead of using the Personal Email ID ([email protected]) of the flow creator, use a generic mail box ([email protected]).

  • Send Microsoft forms approval email to the User
  1. Add action Send an email (V2)
  2. Click the three dots at the right side.
  3. Under My connections add the Generic Mailbox from which you want to send the email
  4. To: Form field ‘Responder’s Email’
  5. Subject: Add the subject for the email
  6. Body: Add mail content in the body section
Power Automate Send Email From Shared MailboxPin

11. Microsoft Teams alert from SharePoint list

  • Add Action ‘Post adaptive card in a chat or channel

Notify the team Members via Teams about the New ticket when a new item is created in a SharePoint List. So that they can be aware of the new Ticket and start working on it.

Microsoft Teams Alert From Sharepoint ListPin

When a Condition is used the result can be True or False (If yes and If no).

IF Yes — Do something
IF No — Do Something Else

So in our case we used 1 condition at Step 3 (To check Submitter requires Approval Yes or No) and another Condition at Step 4 (What happens if the Approver Approves or Rejects the response).

So far we have created Power Automate Actions for when the condition is ‘IF YES’ for Step 3 & Step 4.  Now below 2 steps are for creating the steps when condition scenario is ‘IF NO’.

Please refer back to the Power Automate Flow Structure shown at the beginning of this tutorial under the section How To Create SharePoint Ticketing System using Power Automate, to understand complete development structure of this ticketing system. 

12. Microsoft forms Rejection email notification

  • If Rejected (Step 4)

This step now goes back to the Condition – Approval (What happens if the Approver Approves or Rejects) which we mentioned in the Step 4. All the above mentioned configurations we did from Step 4 to Step 11 are for the scenario in case when the manager approves the request.

Now we need to configure what happens when the manager Rejects the request.

  • Send Microsoft forms Rejection notification email to the User

If Approver Rejects the Response then the Submitter/User gets an automatic E-Mail notifications with Comments from the Approver for the rejection reason.

The setup of the Rejection email is same as the process explained in Step 10

  • Under the If no (Step 4) add a new Action Send an email (V2) and configure it same as in Step 10, only need to modify the Subject and the comments in the Body section to notify the rejection reason.

13. Update SharePoint List From Microsoft Forms

  • If No (Step 3)

This step goes back to Step 3 where we checked for the Condition Is Approval Required Yes or No. All configurations we did from Step 4 to Step 12 are for the scenario if the Submitters requires Approval Yes.

Now we need to add actions for the scenario No, that means when the Submitters doesn’t require an Approval from manager.

Responses submitted by Users who Doesn’t Require Approval gets saved directly to the SharePoint List as a New SharePoint List Item with a unique Ticket No, without going through the Approval Flow (Step 4).

We simply need to replicate the same Actions from Step 5 to Step 11 as it is under the section If no from the Approval Required condition from Step 3.

Summary: Power Automate Flow First Trigger

  • When a new response is submitted:

So that brings us to the end of First Section of the Ticketing System Power Automate Flow. Once configured correctly as explained above then your completed Power Automate Flow should look something like the below:

Ticketing System In Sharepoint With Power Automate 1Pin

14. Send email when specific field changes in SharePoint List

This is the Second section of the Flow Development and this will enhance the overall user experience with the Ticketing System.

This is more like a status update to the end user whenever there is an update in the Tickets submitted. There are 2 ways to configure it,

Either user gets notification email whenever there is a change in any Column values in the SharePoint List.
Or
Update the user only when a specific Column value changes in the SharePoint List, for example Column value for Ticket Status changes From New to In Progress or In Progress to Completed etc.

  • Send email when specific field changes in SharePoint using flow

In our example we are looking into the second scenario where users will be notified when the ticket Status changes.

This Section need to be Created as a separate New Flow and not in continuation to the First Flow. Because:

The automatic trigger to start the flow here is ‘When an existing item is modified‘ where as in the First Section the automatic trigger to start the flow was ‘When a new response is submitted‘ in the Microsoft Forms.

  • Start trigger ‘When an existing item is modified’

Site Address: From the drop down list select the SharePoint site address where the Lists are created.
List Name: Select the name of the List where the tickets are saved, in our case ‘Support Tickets

When An Existing Item Is ModifiedPin
  • Get changes for an item or a file (properties only)’
Send Email When Specific Field Changes In Sharepoint Using FlowPin
  • URI – Send an HTTP request to SharePoint Action
				
					_api/web/lists/getByTitle('Support Tickets')/items(@{triggerOutputs()?['body/ID']})/versions(@{outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']
})
				
			
  • Check if certain column value changed from the previous version
Send Email When Specific Field Changes In Sharepoint Using Flow - 2Pin
Note: In This Sample Image You Will Notice There Are Two Conditions But You Need To Use Only One (Has Column Changed: Status)
  • If Yes: If the column value changed equals True (Changed)
  • Send modification details to the User with details of what was changed.
Send Email Using Flow When Sharepoint Column ChangesPin
  • Compose – Inputs: To get the previous value of Status from the HTTP request
				
					body('Send_an_HTTP_request_to_SharePoint')['d']['Status']

				
			
  • If No: If the column value changed equals False (No Changes)

Ignore and No action required

Below video explains in detail on how to send email when specific column value changes in SharePoint List

Summary: Power Automate Flow Second Trigger

  • When an existing item is modified:

So if you have configured correctly then your flow should look something like the below.

Power Automate Sharepoint Get Column ChangesPin

That covers all the steps that you need to follow to to Create a Ticketing System in SharePoint with Power Automate and Forms.

If you are interested to Learn and Improve your Microsoft Power Automate Development skills then I would recommend you to Join the below Complete Guide to Microsoft Power Automate Course. 

It will be a worth investment! And Right Now you can register with upto  80% Off 
(Only when you click and join from here )

This course starts from the basic level, so no prerequisites are required.

Automate all your tasks using Microsoft Power Automate (Microsoft Flow) with the complete guide (from basic to advanced).

Anson Antony

Anson Antony

Anson is a contributing author and founder at ASKEYGEEK.com. Learning anything new has always been his passion, ASKEYGEEK.com is an outcome of his passion for technology and business. He has got a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. During the tenure, he had worked for organizations like Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities. Outside business and technology, he is a movie buff who spends hours together watching and learning Cinema and a Film Maker too!

Leave a Reply

Your email address will not be published.

Launching soon...

E-book price at Amazon will be $14.99 

A Practical Guide To Online Business
Congratulations!
You Made It,
Don't Close!

Enter your chance to win our Premium Membership License for FREE! ( Normal Price $199/Year)

Thank you for your visit!

This popup won’t show up to you again!!!

Premium Membership Giveaway - Popup
small_c_popup.png

Let's have a chat

Fill out the form below to consult with us

*Please check your Inbox or Junk Folder for the Confirmation Email. You must confirm your Company email address before we could revert.

1
Share to...