This chapter continues our case study of using the Power Platform to create a Help Desk ticketing system and then visualize and track its data. These types of systems can very effectively fill the gaps between spreadsheets and paper forms on the low end and elaborate and expensive systems like Remedy and ServiceNow.
The new help desk group for our area initially only had paper forms for in-person visits, which was certainly not optimal. With the advent of Microsoft 365, I wanted to see what its new capabilities could do. I introduced the SharePoint list data source for a new solution in an earlier chapter.
New Ticket Form
At that time, all our users did not yet have Microsoft 365 accounts.1 But that wasn’t a problem because a “kiosk” computer2 would itself have a M365 account and would be “signed in.” Figure 8-1 shows my initial look for a New Ticket form.
A screenshot of the Microsoft 365 window. On the left is a new help ticket and on the right are options for academics with a submit ticket button. The column for issue is highlighted.
Figure 8-1
New Ticket form showing the lookup of users in Microsoft 365 as well as cues for proper entry
Setting Up in Power Apps
For this form, we choose to create a Blank canvas app in Power Apps and choose the Tablet format.3 Our next task is to add a SharePoint data source and point it to our Help Tickets list. We also add an Office365Users data source we will use to look up users (for those who have Microsoft 365 accounts). Finally, we add an Office365 Outlook data source for sending email. These data connections are shown in Figure 8-2.
A screenshot titled data with following options: office 365 users, outlook, and help tickets. It has a search bar at the top with an option to add data.
Figure 8-2
Office 365 Users, Outlook, and SharePoint data sources
Our first big task is to allow the user to find himself or herself in Office 365. We add a Text Input for the search and an icon for the magnifying glass. We use a Gallery control to display the results so users can type in their name and find their record. To make that work, we set the Gallery’s Itemsproperty to:
This gives us a Table that we then “bind” to the Gallery control with each record displaying in a separate cell. As we discussed in our opening chapter, I like how Power App’s web-based editor uses a single interface for both setting properties and programming. In traditional editors like Visual Studio and languages like C#, you set properties in one window and then put the programming into a separate “code behind” file. In Power Apps, we can add programming right into the same place where we set the property values.4 In the preceding code, we set the Items property of the control by calling the SearchUserV2method of the Office365Users data connection. We set the searchTerm parameter to be the Text property of our searchUser Text Input control. We limit our values to the top 10.
We set the Gallery to be a Title layout and associate the controls with the DisplayName, Mail, and Department attributes from Office 365. To make it easier to tell which user we have selected, we put this logic in the Fill property of the DisplayName label:
If(
ThisItem.IsSelected,
Color.Yellow,
RGBA(0, 0, 0, 0)
)
This means that if the IsSelectedproperty of that record is true, then the background fill color is yellow. Otherwise, it is white.
Similarly, we set the Textproperty of the UserName label at the bottom left of Figure 8-1 to be:
This code makes more sense if we refer to Figure 8-3, which shows the form when the user checks the I don’t have an Office 365 Account box. When that box is true, we concatenate the first name and last name the user enters. If not, we see if the UsersGallery is visible (we hide it until the user starts searching since it otherwise defaults to the first user) and, if so, concatenate the GivenName and Surname properties. Otherwise, we leave the name part blank.
A screenshot of a window. It has options for first and last name, and email. The email pane is highlighted. Name Joe Tester is visible at the bottom.
Figure 8-3
Entering name and email when the user does not have an Office 365 account
Here is the code for the UsersGallery Visible property:
In words, this says to show the Gallery when the I don’t have an Office 365 Account box is unchecked AND when search text is not an empty string AND is not blank.
Note how there is a label showing that the email format is invalid. We make this work by setting its Visible value to be:
If(
NoO365Account.Value,
If(
IsMatch(
EmailInput.Text,
Match.Email
) || IsBlank(EmailInput.Text),
false,
true
),
False
)
This demonstrates a “nested” If condition. If the NoO365Account box is checked, then we see if we have a valid email format (the second If). If not, the Visible is false. To check the valid format, we use the IsMatch method, passing it what the user entered and telling it to match it to an Email format. Since email is not required, we also allow it to be blank. In those cases, Visible is false. Otherwise (not blank and invalid), it is true. We use similar logic to set the BorderColor to maroon5 when the format is invalid:
If(
NoO365Account.Value,
If(
IsMatch(
EmailInput.Text,
Match.Email
) || IsBlank(EmailInput.Text),
RGBA(0, 18, 107, 1),
RGBA(184, 0, 0, 1)
),
RGBA(0, 18, 107, 1)
)
Let’s look at the right side of Figure 8-1. We set the HintTextproperty for the Phone and Issue to give users more information on what to enter. For Department, we could have linked it to the choices within SharePoint (see later chapters for examples), but we don’t want there to be a default that the user just accepts in this case. Instead, we set the values explicitly with the initial choice being blank. We then make sure there is a selection before allowing the form to be submitted. To do this, we set the Items property to be:
Issue is the single-most important field, so we give it a maroon highlight until the user enters information. We do that by setting the BorderColorproperty to be:
If(
IssueInput.Text = "",
RGBA(184, 0, 0, 1),
RGBA(0, 18, 107, 1)
)
The last task before we write the ticket data to SharePoint is to ensure we have all the required information. For that, we make the RequiredValuesLabel and give it the text of “Please fill out all mandatory data before submitting ticket.” We set its Visibleproperty to be:
The || represents an OR condition. So if there is no department selected, no issue entered, or if the invalid email format warning is showing (see preceding discussion), we show this label.
Writing Data Back to SharePoint
We are now ready to write the ticket back to our SharePoint data source.6 We handle the OnSelectevent, as shown in Figure 8-4. Notice how you can drag down the entry space in the browser to give more space for typing.
A screenshot of a window. On the left is a list titled tree view. Under it, the file submit b t n is selected. The ticket information code is visible on the right.
Figure 8-4
Handling the OnSelect event and writing ticket data to the SharePoint data source
Listing 8-1 shows the code after clicking the Format text link shown in Figure 8-4. We will go through each section in turn.
OnSelect code for writing ticket information to a SharePoint list
We first check to see if our RequiredValuesLabel is visible. If so, we know we have an error (and don’t have to repeat all our earlier error-checking logic) and return false (which means nothing else will happen). Next, we check to see if the user has an Office 365 account. If so, we send the User (Office 365) info. Otherwise, we send the User (Not Office 365) information. Note that we added the non-365 email as a separate SharePoint column so we could use that to send an email when the technician resolves the ticket. To write the information to SharePoint, we call the Patch method.7 The first parameter is the data source (Help Tickets list). The second parameter is the record we want to modify. We call the Defaultsmethod to create a record.8 This also puts any default values into the columns. For example, the Status column defaults to New and thus we don’t have to write it here. Finally, for complex columns like Person or Lookups, we pass in an object (defined with {}) with properties corresponding to the column name in the SharePoint list. Note how column names with spaces need to be in single quotes. The “no Office 365” case is simpler because we just pass in the first name, last name, and email. When we have a 365 user, however, we need to pass in an object. Here it is again:
We start with an OData (Open Data Protocol) type to tell Power Apps that this is a SharePoint person. We take advantage of the fact that the Claims (credentials) are in the format i:0#.f|membership|<email address>. We need that, the DisplayName and the Email in order to present the proper user to SharePoint.
Returning to Listing 8-1, we use the Office365Outlook data connection and call its SendEmailV2function,9 passing in the address, the subject (note how we put the issue and the user in there), and the body (showing the user, issue, and description). Notice how to use the HTML break (<br />) tag to put in hard returns. That goes along with setting the IsHtml property to true. Power Apps defaults to a low importance email, so we set it to Normal. From there, we now want to Reset all the entries so this kiosk form is ready for the next user. Notice how we can separate commands with semicolons (;). That’s it except for the UpdateContext line, which we will cover next.
In testing this form, users weren’t sure if their ticket got submitted correctly. So I decided to add a popup confirmation. For that, we use a local variable we will call popupVisible. It defaults to false, but we use the UpdateContext command to set it to true. Figure 8-5shows how we implement this as a group of a button, icon (the X at the upper right), and a label. We set the Visible property of the group to be popupVisible. Clicking either the button or the X sets the popupVisible variable back to false, which closes the popup and makes the form ready for the next user.
A screenshot of a window. On the left is a list titled tree view. Under it, the file button 1 is selected. A pop up on the right confirms the ticket submission. The ticket information code is visible on the right.
Figure 8-5
Popup confirmation group with its Visible property controlled by the popupVisible local variable
By design, this initial form is fairly simple. Users identify themselves, say what their problem is, and provide some optional identity details. The app updates our data source (SharePoint list) and sends out an email notification of the new ticket. We will pick up this story in later chapters.
Summary
In this chapter, we continued our extended example of building a help ticketing system in the Power Platform. We created a kiosk form for adding a ticket. Since not all users had Microsoft 365 accounts at that time, the form has some logic to account for that. It saves the ticket data back to a SharePoint list and notifies the technicians there is a new ticket to be handled.
