How to create a Helpdesk App using Forms, SharePoint, Power Automate and a Power App

April Dunnam

0 comments

In this eBook, we will talk about the three essential tools you need with screens that provide an informative guide on how to specifically use all the tools together to create a holistic solution. This guide will be centred on three core tools:

  • Flow
  • Microsoft Forms and
  • PowerApps

Now Without further ado, let’s get the ball rolling in the right direction.

If you look at these three solutions in the intersecting circles from the screen above, the common thing amongst them all is; they are all “no to low” code tools available to you in the Office 365 platform.

Microsoft Forms is a good tool to use for feedback, signup Forms, surveys, and quizzes. The benefit of Microsoft Forms is it can be for internal and external use and it allows you to have anonymous responses. Microsoft Forms also are very simple to use; they are probably the easiest to get started with and require the least amount of knowledge. There’s no code involved at all; it’s just literally configuring some fields and rearranging them on your Form.

PowerApps can go beyond your basic Form requirement; it is for a robust desktop and mobile applications scenario and not just for simple Forms. Although you can do a simple Form with PowerApps, it is intended to be used for much more than that. It is also geared toward internal use.

With Microsoft Flow alone you can actually do some simple input Forms using Flow buttons. So it is a good solution if you need input forms, as part of a Flow driven process. At its core, Flow is a workflow business process tool, so obviously it is great for automating processes and for approvals. In a nutshell; Flow is like glue that ties all these tools together (see the above picture).

Take PowerApps, for example, you can build a great PowerApp that lets employees request time off, but how usable is that app without some kind of business process behind it? So, you want your employees to be able to request time off, but typically there’s some kind of approval process involved in that. You can’t do that without Microsoft Flow. Take the same thing with Microsoft Forms. It’s super simple to set up a Form but you probably want to be notified when someone submits a form and also probably do something with the data they submitted. You can’t really do anything with the data or get those notifications without a Microsoft Flow.

End to End Help Desk App

To showcase how these tools can work together and complement each other, let’s take a look at specific use cases on how we can use PowerApps, Forms and Flow to create an end-to-end helpdesk application.

Now let’s go over some requirements that are needed for the application to work.

First: we need to allow external customers to submit a trouble ticket.

So, take my business for example we are an IT consulting company, and we offer consulting services for a core set of products like SharePoint, PowerApps, and Flow, sometimes our end-users might run into an issue. So, we formalize the process and let our clients submit the issue. When the issue is submitted we store that information in our SharePoint lists and it would be nice if we Auto-assign a technician to that trouble ticket, based on the issue. So, if the problem relates to SharePoint the ticket is assigned to one person, and if it is a PowerApps query it will go to the next person. We also would like to utilize a mobile application and a desktop application, so technicians can access and manage these tickets and update them.

And of course, when the status changes on any of these tickets, we want to notify the original requester. To complete the process when this status is complete, we’d like to send up a follow-up survey to the customer to see how their experience was with the ticket.

So now that we have the general requirements the picture below summarises which tools are going to be used for what.

Currently, there’s no great way to allow an external user to submit the trouble ticket data via PowerApps. The easiest way to get that information is to use Microsoft Forms. We can allow anonymous access that will require users to enter their names and any information related to the trouble ticket into a Form and submit that data. We then need Flow to get that data from the Microsoft Form into our SharePoint list. Another Flow can be used to auto-assign the relevant technicians (to do this we need to have configured in the back end a SharePoint list mapping technicians’ names versus their skills).

We’re going to use Microsoft PowerApps to manage and update the tickets on mobile devices and via a desktop. PowerApps can read data easily from a SharePoint list, and you can execute Flows from PowerApps so this is going to be the best option to create the UI for the technician to manage the tickets. The technicians will be interacting with this PowerApp to view their tickets and update them. When the ticket is updated, and the status is changed, we’re going to use Flow again to email that customer updates about the status.

To complete the process, when the ticket is closed, we’re going to use our Flow once again to email out a link to a survey for that customer, where the survey will be a Microsoft Form. Now let’s dive in and build this thing and see how it all comes together.

Collect Trouble Ticket Info

The primary driver for this initial process is the Microsoft Form that the end customer will fill out to submit a trouble ticket. We’re going to start by logging into Microsoft Forms and create a simple form requesting a person’s name, their email address so we can get back in touch with them, the company that they’re with, the technology the issue relates to (so we can auto-assign the correct technician), urgency level and a space for them to describe the problem.

This will allow us to get the data initially, and we can see responses from Forms itself using Microsoft Excel. Ideally, we want to have these stored in SharePoint so that we can utilize the responses and the data in our PowerApp.

Add Trouble Ticket Data to SharePoint

To add the Forms responses to SharePoint, we need to use Microsoft Flow. (One thing worth pointing out first is if you aren’t using templates on Flow, you will need to). Although there might be a few templates that relate to what you want to do, you may not find a template that gets you all the way there. I recommend starting with the template that gets you closest to what you want and just modifying it to suit your needs.

In this case, we want to take responses from Microsoft Forms and put those responses in SharePoint. So in the template section of Flow do a search for Microsoft Forms.

The first option that pops up is to “Record Form responses in SharePoint.” Select this template and click ‘continue’ to create. This is going to do everything for us.

Use the drop-down option to select the correct Form you want to get the responses from, in our case, click on the ‘trouble ticket’ Form.

It already has this built-in “Apply to each” for us to get the response details, so you don’t have to worry about iterating through each one. Then for the “Get response details” action click on the ‘trouble ticket’ Form’ again.

And lastly, you will have to tell it where you want to store your data. To do this; select your site address from the drop-down.

And the name of the list that you want the information to be saved to; your “trouble tickets list.”

Once you select your list, you need to map the metadata to the values from your Forms input. If you click on the title, for example, you can map that to the company name that they entered by selecting the correct response type from the Dynamic content list.

Repeat this process for the other data elements.

‘System’ can map to the question about which system is this trouble related to.

‘Urgency value’: This is actually a drop-down in your SharePoint Source list, so you can just enter a custom value, and map that to the question of how urgent is the request?

The ‘status value’ is also a drop-down, however as we are just creating the ticket the status is going to be ‘new’.

For ‘submitted date’ you can use an expression and use the UTC Now function to get the current date.

To “submittedBy” will map to the name from your Form and the “email” field will map to the email details the user provided.

Finally, “details” need to map to “Please describe this issue”. The other fields do not need to be filled out at this stage because they will be filled in by the technician. Once completed your metadata list should look like this.

Now click save at the top right-hand corner.

Auto Assign Technician

To do this we need to go to the SharePoint list and work out how to assign the technician based on the problem reported.

On my SharePoint site, we need a list called technicians with two fields, one for the system and one for the technician. The list needs to contain all of the systems used in our Microsoft Form and against each one we need to name the most appropriate technician to help with problems with that particular system.

You will need to read this list and compare it to the Forms data that was submitted, then update the trouble ticket. Let’s look at the trouble ticket list in SharePoint.

So from the above picture, you can see I have some items in progress. We want the “TechnicianAssigned” field to be automatically assigned based on the type of system. So let’s get back to Flow and look at how we can do that.

We’re already using Flow to take the Form submissions and move those to our SharePoint trouble ticket list. To do that auto-assignment, we will have to create a new Flow “TroubleTicketAssignment” that’s triggered when a SharePoint item is added.

The Trigger to the above Flow is when an item is created in SharePoint. As soon as the initial Flow kicks off to move the Form submissions to the SharePoint list, then this Flow will start.

We need to use the SharePoint “Get items” option. We want the “Technicians” list to be updated with the value we are going to find using the “Filter Query”; we’re filtering the items where the system equals the system included in the SharePoint trouble ticket list.

Now that we have all the values where the system matches we need to use “Initialize variable” to hold the results of the technician name that matches.

Here we have initialized variable option we’re calling that technician, and it’s a string type value.

The next step is to use an “Apply to each” option so we can Loop through the values of our get items for that technicians’ list and append the results to the variable that we just initialized. We also want to append the technician email so we can notify them that they have a new ticket to review.

Now that we have our Technicians product system. All we have to do is insert a SharePoint update item action and point it to a trouble ticket list, and map the technician assigned to our technician variable. The cool thing about this approach is you can customize it to your needs. So you can take any technician that has the skills you need and assign it to them. But you can also use a round-robin approach; where you will automatically be assigned this randomly to technicians.

Manage and Update Ticket

Now we have collected all of the relevant information we can manage and update these tickets with PowerApps. As we are focussing on the Flow elements of this solution, I’m only going to give an overview of the PowerApp solution used.

Visit web.powerapp.com and click on the all templates option, then select Help Desk.

I made a couple of tweaks to the basic App to point the data sources to our SharePoint list we created and removed the option for internal users to create tickets. When the technician logs into the App we want it to show them all of the tickets that have been assigned to them.

The technician can then edit the ticket by clicking the edit button to update the status, percent complete, change or add anything to the description, like details that they get from the client. When they click update we want this to update the SharePoint list.

We also want a new Flow to kick off to check if the status is changed and if so, update the customer.

Email customer with Updates

We need a new Flow called “TroubleTicketUpdate” which is triggered when an item is created or modified.

We need to use a “Switch” statement on the “Status Value”.

We use cases to define what action to take depending on the status of the ticket. If it is a new ticket, you can ignore it by terminating the action.

Case 2 can be used if the status is complete; at which point we want to send the customer an email.

If the case is not new or complete, we use the default case; this case allows you to specify your action for other circumstances. In this scenario, if the ticket is delayed or in progress, we want to send a different email to the customer to keep them updated.

For both of these email actions, we can find the customers email address from the Trouble Ticket SharePoint list data which is saved as the submitter’s email address. The body of the emails can include some information from the ticket itself, letting them know what has been updated in their ticket.

Email the customer a survey link

Once the ticket is completed, we want to email the customer a survey link to find out what they thought of the service they received.

First, we need to create a customer survey form containing questions to collect the feedback you want. Suggestions are, ticket number (so we can link the responses back to the relevant ticket), star rating to reflect their satisfaction score and fields for the customer to add comments.

Next, we need to add a couple of steps to the case 2 email process. First, we want to add a delay action to allow them time to receive and read the ticket completed email, then send them the survey (this is reliant on having Forms Pro).

Forms Pro is a new version of Forms that have extended capabilities; one of them being the ability to send out survey emails and to integrate with Flow like this.

In the “Send a survey” action, add the email address of who the survey should be sent to (the customer who raised the ticket) so map this to the email address from our SharePoint list and select the survey that you want to send out.

You can also designate the email template to use (this is a Forms Pro feature only). Click the drop-down to review the default templates that are already created for you. It has a specific message for Employee Engagement, event feedback etc. but the one we are going to be using is the Service Feedback Message.

If you click on the “Show advanced options”, you have the ability to customize the message more encompassing their first and last name if you want to along with other details about what this email is regarding. If you’re new to FormsPro, you can sign up for a free trial of that to check it out.

Customer Completes the Survey

After a customer completes the survey, we want to update the corresponding ticket with the survey data so we need another Flow for that. Let’s start by taking a look at our survey Form to remind ourselves of the questions we asked;

  1. satisfaction with the technician’s response
  2. satisfaction with the response time
  3. an additional comment box.

So before we move on to the Flow, we need to create the relevant fields in our SharePoint TroubleTicket list to hold the data from surveys.

Now we can build a new Flow to take the responses and store them in SharePoint, we can use the same template we used before for trouble ticket responses. So let’s go to our template option and search for Forms, and use the “Record form responses in SharePoint” template again.

The First thing you will have to do is rename this Flow (I’ve called it “TroubleTicketSurveytoSP”). Now select the Form you want to get responses from (the trouble ticket satisfaction survey) and within “Apply to each” we also need to refer to the satisfaction survey.

At this point we need to amend the template because we don’t want to create a new item in the share Point list, we actually want to append the new metadata details to the existing ticket.

Delete the “Create item” action (from the ellipsis menu) and then add an action for SharePoint, “Update item” (located at the bottom).

In the Update item action, point to your site address and your Trouble-ticket-list. The Id field needs to refer to the trouble ticket number, which is the SharePoint list item ID.

Notice that when you click on the ID field; I don’t see the corresponding field in my Form. This is because the ID field is an integer and in this Form, responses are strings, so they don’t match.

To get around that, go into another one of your fields that you know is a string (like title, for example) and you will notice the option shows now. Select the Enter Your Ticket ID option (so that it is added to the Title field) and then you need to know a little trick to move it to the correct place.

Here is the trick; now that you have that value in the title field, if you click on the dots located at the top right corner of your action, it will display a peak code option. When you click the peak code option, it will display a JSON code which might appear scary (but bear with me!). Copy the value as shown below but exclude the @ symbol from the beginning.

Go back to your ID and click on the Expression tab and paste in the value you just copied and click OK (if you get an error, ensure you remove the @ symbol from the beginning of the expression).

Now you can delete the ticket number from the title field as we only put it there temporarily to help us complete the ID field.

Then map the correct data to the new fields we added to the SharePoint list for technician satisfaction etc.

When you click ‘save’ it’s actually not going to let you save, it’s will prompt an error that the title is required. So if you have any required fields in your SharePoint list, Flow is going to make you fill those out.

Now, in this case, to complete the title field we need to go back to SharePoint to check which field is required. We actually used Company Name but this isn’t currently available.

We need to insert a new step before the update item to get this information. Select SharePoint and then “Get item” action, point this to our site address and our TroubleTickets list again.

For the ID field, we need to use our trick again, hopefully, you still have that Formula on your clipboard, so go to expression and paste it in then click OK.

So now this is going to get all the metadata for us from that particular item. Now we can return to the title field in our Update Action and select the title from our SharePoint list for that particular item. Okay, so that’s really it for this Flow, don’t forget to click Save.

Test our process

Great, so we successfully fulfilled all the requirements, now, it’s time to test our application. Let’s submit a ticket and see how it all comes together.

The first step in this process is for your end-user to submit a trouble ticket in Microsoft Forms. You probably should have a link to this Form on your public website for users to access. Once the form is submitted the first flow should work.

If the Flow is successful, you should see green checkmarks everywhere and that is a great sign. However, I recommend that you still go to the SharePoint list to verify that the ticket was added.

Next, our second Flow should begin to assign the technician, again green checkmarks means that it has worked.

Return to SharePoint, to check that a technician has been assigned. At this point, our Flow should also send an email back to the client letting them know that their trouble ticket was assigned giving them their ticket ID so that they can reference that in the survey later and giving them the name of their technician that was assigned.

The technician then needs to use the PowerApp to update the ticket, which will trigger the next Flow to notify the end-user that the status has been changed.

Log in to your email as the end-user to ensure the update email has been sent.

With your technician hat back on, set the per cent complete to one hundred and mark the status as complete. This should kick off our last Flow which will send out the email letting the user know the status is complete and the follow-up email with the survey link.

Remember this Flow has a delay, so the end-user should receive an immediate email, followed by the survey after the correct amount of time. Let’s go back to our end-user email and complete the survey.

Finally, our last Flow should work and we can check that the SharePoint list is updated with the satisfaction data.

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"}