This chapter continues with our example of building a Help Ticketing solution with the Power Platform. We look at what technicians need. Since they need to look up tickets and interact with them over time, there is more functionality than the user submission form we examined in the last chapter.
Now that we have our tickets in the SharePoint list, we can significantly improve the productivity of our help desk technicians by letting them manage tickets on their mobile devices (as well as their computer if desired). We let Power Apps build the basic plumbing of the form and then customize from there.
In Power Apps, we choose to Start from SharePoint, which defaults to a Canvas app with a Phone layout. We then tell it to use our SharePoint list as the data source. Power Apps makes us three screens: Browse, Detail, and Edit. We will go through the edits to each of these screens in turn.
Browse Screen
On the Browsescreen
, we want to add a Status drop-down box so a technician can filter the tickets by status. We default it to New so that new tickets will show up by default. We also want to change the Search from the default of searching by Title (Issue in our case) to search by Technician. Finally, we want to edit what information is displayed in BrowseGallery1. All of these are shown in Figure 9-1.
A screenshot of a window. An arrow under properties points to the label 3 pane in the middle from the fields edit tab on the right. Help tickets are visible on the right.
Figure 9-1
The Browse screen in our Help Technician application
We insert a label and drop-down box for our status. We move the gallery down a bit to make room. We set the Items property of the drop-down box to be Choices('Help Tickets'.Status). This ties it to the Choice column in SharePoint and makes sure the choices on the form are updated when the SharePoint column is edited. For the Search functionality, we change the HintText to give an indication that the search is for the assigned technician. The next step is to change the Items property
for the gallery to be:
We take advantage of the fact that the Filter method can take multiple parameters. So in addition to the default StartsWith parameter, which we adjust by making it applicable to the Assigned Technician column,1 we add the matching of the Status to what we select in the Status drop-down box. The last parameter sets the order of searching. SortDescending1 is a context variable that is toggled by the up and down arrows at the top of the screen. The arrows’ OnSelect events have this value: UpdateContext({SortDescending1: !SortDescending1}). This means to make SortDescending1 true if it was false and vice-versa. We list Descending first since SortDescending1 defaults to false and we want an Ascending sort initially so the oldest tickets are at the top.
One complication that took me hours to debug2 later is the minute we use the Filtercommand
on a “complex” column like Status (since it is a SharePoint choice column), Power Apps limits
us to 500 records by default. This is a big problem since our newest tickets are at the end of the SharePoint list. You can raise this limit to 2000 but needs some significant work to go above that (as we will show in a later chapter). To be fair, the Power Apps editor warned me about the filter limit; I just ignored it:). You can see the warning icon in Figure 9-1. Fixing this turned out to be non-trivial. Before coming up with the more general solution covered later, I made Single Line Text columns called Ticket Status and Ticket Assigned Technician and used a workflow3 to automatically update these columns when either the Status or Assigned Technician values changed. The updated Items
value then looks like this:
Notice how we needed to get rid of the IsBlank()function
as well. The good news is these changes fixed the issue.
Our last task on this screen is the edit the information
shown in the gallery control. Referring again to Figure 9-1, we add two extra labels to the card so we can show the Issue, User, Status, Assigned Technician, and Number of Days Open. Since it not obvious what the data represents, we add a description before the value as in "Status: " & ThisItem.Status.Value. As you might recall, we can have either an Office 365 user or a non-Office 365 user. To avoid having to confuse our screen with two values (one of which would be blank), we use this logic for our user display
:
If(IsBlank(
ThisItem.'User (Office 365)'.DisplayName),
ThisItem.'User (Not Office 365)',
ThisItem.'User (Office 365)'.DisplayName
)
The Number of Days functionality takes advantage of the useful DateDifffunction
to give us the number of days between two dates:
"Number of Days Open: " & If(
Status.Value = "Resolved",
DateDiff(Created,'Resolved Date'),
DateDiff(Created,Now())
)
If the ticket has been resolved, we stop the clock and use the difference between Resolved Date and Created. Otherwise, we use the Now() function to get the difference between the current date and time and Created.
Details Screen
On the Details screen (Figure 9-2), we
click Edit fields to display the desired data and to add or delete fields (columns). We display the ID primary key value from SharePoint, but rename it as the Ticket Number. We also show the Created date so the technician can see when the ticket was first entered. The (custom) identifier means that we have unlocked the data card to change the controls and/or the formulas. We customized User as well to put in the same formula described earlier. The renaming of ID is a customization as was including our Number of Days Open information below the Created value. We also unlocked Description and Notes to use an HTML Text control so the HTML/Rich Text that we input in the form in the last chapter as well as the notes that the technicians add will be displayed correctly.4
A screenshot with the following encircled: description H t m l file under tree view on the left, number of open days under C F A M help ticket in the middle, and description custom tab under add field on the right.
Figure 9-2
The Details screen in our Help Technician application
New/Edit Screen
The Edit form (also used for new records) is similar to the one for users to create a ticket discussed in the previous chapter. However, we need to include all the fields rather than just the issue, user, etc. This is because a technician may use this form to open and close a ticket all in one action, for example, when they help a customer over the phone and then document the ticket afterwards. Figure 9-3shows
the form in the development environment. As with other screens, we use Edit fields to add or delete what fields/columns we want to include. Many of these are customized. The biggest reason is because Power Apps uses combo boxes
rather than drop-down boxes for choices like Department, Issue Category, Assigned Technician, and Status. Changing this5 involves unlocking the data card, adding a Drop-down control and making it the same size and position as the combo box, changing its Items property (to something like Choices('Help Tickets'.Status)), and then deleting the combo box
. This results in a couple of error messages. You click the messages and substitute the name of your drop-down box
for the old combo box name. The most important is for the Update method of the data card. For example, for Status you change it to StatusDropDown.Selected. The other main challenge was that the drop-down boxes did not correctly display the current value when you edited an item. That was because the Default value needed to be set to an expression like ThisItem.Status.Value.
A screenshot of a window. On the left a file, description rich text is highlighted. On the right are tabs for issue, description, category, and notes for help tickets. C F A M help tickets are visible in the middle.
Figure 9-3
The New/Edit screen in our Help Technician application
The other main change was to enable the entry of HTML/Rich Text
into the Description. You might think we would use the HTML Text control again, but that displays HTML but does not allow the user to edit or enter it. Instead, we need to use the Rich text editor control.
A time-saving feature
is to set the Resolved Date value to the current date when the technician changes the Status to Resolved. To do that, we unlock the Resolved Date Data Card and set the DefaultDate to:
If(
StatusDropDown.Selected.Value = "Resolved",
Today(),
Parent.Default
)
We check the value of the StatusDropDown and, if it is Resolved, we set it to the built-in Today()function
. Otherwise, we use the original value, which is Parent.Default.
A disconcerting problem surfaced when using the application to change a ticket status
. Since we are filtering on Status (see Figure 9-1), this meant our ticket disappeared once we saved it. For example, we open the application, change the Status filter to Assigned/In Progress and select the ticket we want. This takes us to the Detailsscreen
. We edit the ticket and change its status to Resolved. We are returned to the Details screen, but now a different ticket is displayed. That’s because our current item now doesn’t meet the filter. To fix that, the most logical solution (to me at least) was to change the filter to our new status (Resolved in this example). To make this work, we need to know our current filter value and, if the new status is different, change the filter.
Another related requirement is to send the user an email when the ticket is resolved. For that logic, we need to know our current ticket status
and then send the email if the status has changed to Resolved.6 For both of these requirements, we need to share values between different screens and thus need global variables. We set them when we create a new ticket (OnSelect event for the IconNewItem1 button):
Set(
CurrentFilterStatus,
TicketStatusBox.Selected.Value
);
Set(
CurrentTicketStatus,
"New"
);
NewForm(EditForm1);
Navigate(
EditScreen1,
ScreenTransition.None
)
Notice the syntax for setting a global variable
: Set(<variable name>: <variable value>). As we have seen before, we separate statements with semicolons. The NewForm and Navigate methods were already in place from the wizard that built the form. We have similar logic when we edit an item:
Set(
CurrentFilterStatus,
TicketStatusBox.Selected.Value
);
Set(
CurrentTicketStatus,
DataCardValue7.Selected.Value
);
EditForm(EditForm1);
Navigate(
EditScreen1,
ScreenTransition.None
)
We read the DataCardValue7 to get the current status of the ticket in this case.
Now that we have what we need to determine if the status is different from the current filter, we need to allow our status filter to be set programmatically (instead of just by the user selecting an option). We need another global variable (since TicketStatusBox is on a different screen). We set its Default value to this variable, which we will name ticketStatusDefault. We don’t need to initialize it, since when it is blank the value will use the first one in the list, which is the New value we want.
We take care of both these requirements (updating the filter and sending a resolution email) by handling the OnSuccessevent
7 for the form, as shown in Listing 9-1.
"Your Ticket Has Been Resolved - " & DataCardValue9.Text,
"Dear " & If(
IsBlank('User (Office 365)_DataCard1'.Default),
DataCardValue11.Text,
First(DataCardValue10.SelectedItems).DisplayName
) & "<br /><br />We are pleased to inform you that your ticket has been resolved. Here are the details: <br /><br />Issue: " & DataCardValue9.Text & "<br /><br />Description: " & DescriptionRichText.HtmlText & "<br /><br />Assigned Technician: " & AssignedTechnicianDropDown.Selected.Value & "<br /><br />Notes: " & NotesRichText.HtmlText & "<br /><br />If you have any questions or would like to address this or another issue, please call us at 555-1212 or email us at <a href='mailTo:[email protected]'> [email protected]</a>. <br /><br /><b><a href='https://forms.office.com/Pages/ResponsePage.aspx?id=hiddenIdentifier'>Please take our survey</a></b>.<br /><br /><br />Thanks, <br /><br />The Help Desk",
{
IsHtml: true,
Importance: Normal
}
),
false
);
;Back()
Listing 9-1
OnSuccess code for updating the filter and send a resolution email
We compare our CurrentFilterStatus global variable to what we just set on the Edit form (StatusDropDown.Selected.Value). If those are different, we set the ticketStatusDefault variable to be the new status. This is enough to change the filter.
We then compare the status from the form to our CurrentTicketStatus global variable. If the status just changed to Resolved, we send the user an email. We do some logic to figure out whether they have an Office 365 account or whether to use the email they entered (DataCardValue16 represents that value on the Edit form). If they have an Office 365 account, we need to take account of the fact that the combo box holding the user information could have multiple items selected. So we use the syntax First (DataCardValue10.SelectedItems) to get our hands on the user. We then read the Emailproperty
. The next parameter is the subject. We append the Issue (DataCardValue.Text) to it. From there, we define the body of the email. We include the Issue, Description, Assigned Technician, and Notes. Since we are sending an HTML email, we use breaks (<br />) for hard returns and read the HtmlTextproperty
for the Description and Notes. We use the mailTo: syntax to allow the user to send a follow-on email and also include a link to a survey as described in earlier chapters. Here is how the email
looks:
To: <User Email>
Subject: Your Ticket Has Been Resolved - <Issue>
Dear <User Display Name>
We are pleased to inform you that your ticket has been resolved. Here are the details:
Issue: <Issue>
Description: <Description>
Assigned Technician: <Technician>
Notes: <Notes>
If you have any questions or would like to address this or another issue,
please call us at <phone number> or email us at <email address>.
Please take our survey (hyperlink)
Thanks,
The Help Desk
Finally, we call the original Backmethod
to get to the Details or Browse screen as appropriate.
My Tickets Application
It didn’t take long after releasing this new help ticket solution
for users to ask to view their own tickets. We needed to limit it to those users who have an Office 365 account,8 but it took very little time to edit the application described in this chapter and rename it to MyHelpTickets. Figure 9-4 shows the result.
A screenshot shows the following: on the right, under the properties tab, an arrow from edit fields points at the drop-down menu of number of days open label. My help ticket window is on the right with user, status, technician details.
Figure 9-4
The My Help Tickets application
The user label has a simple formula for its Text property:
"User: " & User().FullName
Rather than a drop-down box showing the different Status values, we default to only open tickets but give the option to Show Resolved Tickets as well. The Items property for the gallery is a bit different than what we saw earlier:
We limit our tickets to the current user by making sure the Email property of our SharePoint person column matches the email of the current user. If the Show Resolved Tickets box is checked, we take any ticket (make sure the status is not ““). Otherwise, we take all except those with a Resolved status.
As you might have guessed from the warning symbol in Figure 9-4, this initial implementation
also suffered from the 500-record limit. Since there were multiple issues here, we fix it a different way. We first set two variables in the OnStart event for the application:
The first part creates the currentEmail variable and sets it to the current user’s email. We need this because putting the User().Email value into either the ClearCollect function or the Items property of gallery causes the delegation error and limits us to 500 or 2000 records. The ClearCollect function makes a collection variable (col) from the second parameter, which is the Filter of all help tickets with the user’s email address.9 We can then use col in our Items property for our gallery control:
We filter on col and don’t need to limit to the user’s email address since we already took care of that.
For the rest of the application, we delete the Edit screen, hide the “edit” and “new” buttons, and fix the errors (e.g., code that references the missing screens).
Summary
In this chapter, we customized the template that Power Apps creates for the SharePoint list to turn it into a Help Desk Technician’s application. We added important functionality like displaying the number of days that the ticket is open and notification to the customer when the issue has been resolved. We then saw how just minor edits to this solution could turn it into a “My Tickets” application where users could see the status of their own help tickets.
