Configuring the SharePoint List

As with other examples, we store our data in a SharePoint list . It is not strictly necessary in this case, as the core functionality is an email, post, and/or chat to the recipient. But storing the data allows us to visualize what values are being displayed over time, which organizations are displaying them, and top recipients. It also gives documentation of what messages were sent in case there were any harassment or similar complaints.

For our example, I used the Air Force core values of Integrity First, Service Before Self, and Excellence in All We Do. ² It can easily be adjusted to more or fewer values. As shown in Figure 21-1, we store whether the recipient demonstrated these values in Yes/No columns.

A screenshot of a tab titled columns. It has 2 columns titled column and type. Person or group under type is circled.

We also store the user’s Office, any optional Details the submitter entered, and the Recipient. Notice that this is a Person column and thus we don’t have any issues with similar names and don’t need to separately store an email address. We have another Yes/No column to store the Share Submitter Name with Recipient choice. Finally, we store whether the submitter wanted to Share to Teams, Share via Chat, and/or Share via Email. Note that the Created By column will store the submitter, so we don’t need an extra column for that purpose.

We are now ready to move to Power Apps.

Creating Our Application in Power Apps

Figure 21-2 shows our completed application.

A screenshot of a tab titled employee recognition. The left pane has a search bar and checkboxes under recipient. The right pane has a text box and checkboxes under details.

As with past applications, we create a Blank canvas app with a Tablet format. We connect to Office365Users in order for us to look up recipients and to our SharePoint list we created in the last section. The Power Automate flow we create in the next section shows up as well once we connect to it. These connections are shown in Figure 21-3.

A cropped screenshot of the data menu. The data icon is selected on the left pane. The right pane has a listicle of tabs titled Office 365 users, M 365 Book, and Recognition.

Our first task is to pick a recipient. For that, we have a Search Text input control and a Gallery (Figure 21-4).

A screenshot of an Office 365 User window. The cursor points to the user gallery under the tree view on the left pane. James Rhodes is highlighted in the recipient list on the right pane.

Skip forward to Figure 21-5 to see how this looks once the submitter selects a user.

A screenshot of a tab titled employee recognition for Jeffrey Rhodes. Jeffrey Rhodes is highlighted under recipient on the left with various data under details on the right.

We add a Rich text editor for the Details so we can enter in formatted information. We then have a Check box control for each of the values, for whether the submitter wants to Tell Recipient Who Nominated Them, and for the choices to send via Teams chat, Teams post, and/or email.

Since the logic is fairly complicated, we use the If() function. We make sure that the varRecipient is not blank, at least one value is checked, and at least one output (chat, post, or email) is checked in order to hide the label.

This used the Concatenate() method, to build an unordered list (ul) of list items (li), which is displayed as bullets. If a value is checked, its Text is wrapped up in a list item as in <li>Service Before Self</li>. We normally hide the control as it is only used programmatically in the Submit button, but Figure 21-5 shows how it looks when we show it for testing.

We start by creating a varBody variable that puts a congratulations message and combines it with the selected values (ValuesHtml.HtmlText). Notice how we use the HTML line break (<br />) to get hard returns. Here is how it looks in a later chat, post, or email:

Congratulations!

If the submitter typed in Details, we add that below the values. We then check to see if the Tell Recipient Who Nominated Them box is checked and, if so, add on the name using User().FullName to get the name of the person using the format. We also set to varSubmitter variable either to this name or to blank .

We’ve seen this pattern before. We check if our ErrorLabel is visible and, if so, just return false. This avoids lots of error checking for a valid recipient, values, and output. We’ve covered how to write the data for a Person column in earlier chapters. Note how we use the varRecipient variable heavily to get the email address, display name, department, and job title. From there, we set the values to be written to each column (you might want to refer to Figure 21-1). We use the Department value of the user for the Office column and the Details Rich text control for the Details column. We then set the various Yes/No columns with the corresponding check boxes on the form .

Before we can finish this code, we need to go to Power Automate and create our flow.

Setting Up the Flow in Power Automate

Within Power Automate, we set up an automated cloud flow and trigger it from Power Apps (Figure 21-6).

A screenshot of a tab titled PowerApps V 2. It has empty fields for the recipient display name, recipient email, values h t m l, and submitter display name.

We plan to pass in these parameters from Power Apps: RecipientDisplayName, RecipientEmail, ValuesHtml, SubmitterDisplayName, PostToChannel, SendViaChat, and SendViaEmail. These latter three are Boolean (Yes/No) values, while the rest are Text. You might recall from Listing 21-1 that varSubmitter will be blank if the Tell Recipient Who Nominated Them box is not checked. Since that variable will be passed into the SubmitterDisplayName parameter, we will be able to check if it is blank in order to determine whether to try to send anonymously.

We now just go through each of our selections (post to a channel, send a chat, and send an email) and implement each one if its corresponding value is true. Figure 21-7 shows how we use the Condition action and select the PostToChannel parameter.

A screenshot of 3 overlapped tabs. Post to channel option under the listicle for dynamic content points to post to channel tab under the condition tab.

Actually, comparing this value can be challenging. I’ve found the most reliable way is to use the true dynamic expression, as shown in Figure 21-8.

A screenshot of 4 overlapped tabs. The ok button under the expression tab points to the f x true tab under condition.

Note that you select the Expression tab, type in true, select it from the popup list, and then click the OK button.

Since we will want to @mention the user so that the recognition shows up in their activity feed, our next step is to create the token from the RecipientEmail, as shown in Figure 21-9.

A screenshot of 2 adjacent tabs. Recipient email under dynamic content tab points to the recipient email as a field entry under the get an mention token for a user tab.

Now that we have the token, we need to try to send the recognition anonymously if possible.⁴ As mentioned before, we will use the SubmitterDisplayName parameter to check this. If it is not equal to blank, then send the post from the User. Otherwise, we send it from the Flow bot. Both are shown in Figure 21-10.

A screenshot of 4 adjacent tabs. The cursor points to recipient display name under dynamic content menu. The field entry for post as under if yes and if no are user and flow bot, respectively.

The only differences between our Yes and No conditions are 1) sending as User or Flow bot and 2) whether we get to include a Subject. You might recall from Listing 21-1 that the ValuesHtml parameter will also include the name of the submitter if appropriate. Notice how we select our @mention token, the ValuesHtml parameter, and, for the Yes case, RecipientDisplayName from the Dynamic content. If you want to skip ahead to Figure 21-15, you will see that this is only partially successful. The No case uses the Flow bot picture rather than the user’s picture/icon, but it still says “<User> via Power Automate.” This is likely for security reasons.

For chats, it turns out we can’t do anything other than a Flow bot (unless we want to add to an existing group chat). So we don’t bother checking the SubmitterDisplayName parameter. Figure 21-11 shows the resulting Conditions block.

A screenshot of 3 adjacent tabs titled send chat on top and if yes and if no on the bottom. If yes has field entries for post as, post in, recipient, and message.

We use the same technique as before to check if SendViaChat is true. If so, we chat with the Flow bot to the RecipientEmail with our ValuesHtml as the Message. Skipping ahead again to Figure 21-16, you can see the “<User> via Power Automate” is there again regardless of whether our ValuesHtml has who has done the recognizing.

We try again to send anonymously when we get to email, as shown in Figure 21-12.

A screenshot of 3 adjacent tabs titled send as user or service on top, and if yes and if no on the bottom. The tab titled send an email V 2 is highlighted under if yes.

We again check SubmitterDisplayName to see if we want to list the Submitter. If so, we use the standing Send an email (V2) action from Office 365 Outlook. We use the RecipientEmail, RecipientDisplayName, and ValuesHtml parameters. This sends an email from the submitter to the recipient. See Figure 21-17 for an example. If not, we try the Send an email notification (V3) action.⁵ This sends the email from microsoft@powerapps.​com rather than from the submitter. Other than that, the configuration is the same as the Outlook action. While this has worked for me in the past, this connector is apparently a magnet for spam and is not only throttled but currently showing as unauthorized for me (Figure 21-18).

One thing that bothers me with the default emails from both Power Apps and Power Automate is they show up as “Low Importance.” Figure 21-13 shows how to change that in our Outlook action.

A screenshot of a tab titled send an email V 2. Normal is selected from the dropdown menu as the field entry for importance.

That completes our logic. We save our flow and go back to Power Apps.

Completing the Application in Power Apps

We are now ready to complete our app. Returning to Power Apps, we create a new button (so that we don’t overwrite our existing OnSelect script), select it, go to the Action tab, and select Power Automate. Figure 21-14 shows the result.

A screenshot of a tab titled employee recognition. The cursor points to the file M 365 book in the listicle titled data on the right side.

If our ErrorLabel is visible, we just return false and don’t launch Power Automate. Otherwise, we pass in the parameters in the same order as we defined them in Power Automate (Figure 21-6). We then use the Notify() method with the text we want to display along with the Success parameter to tell the user all is OK. Since we don’t specify a timeout, it will display for the default of 10 seconds. We then Reset() each of the controls so that all our data is cleared out and the user could create a new recognition if desired. To make sure we are starting over, we also set the varRecipient variable to Blank().

Let’s look at each of our outputs in turn. Figure 21-15 shows the results of Post to Recognition Channel .

A screenshot of the posts made by the team members. The user icon for the second post is highlighted and labeled not truly anonymous but doesn't have user picture slash icon.

The top post shows what happens if we check Tell Recipient Who Nominated Them. Notice that it has my picture and says that the post comes from me. Our logic to add “You have been recognized by <user>” from Listing 21-1 means that this is at the end of the post as well. The bottom post shows what happens if we don’t check that box. The picture is for the Flow bot but says Jeffrey Rhodes via Power Automate for who made the post. So it is not anonymous. But it does give some idea that it was not a normal user post.

Figure 21-16 shows the Send via Teams Chat option.

A screenshot of a post made by Jeffrey Rhodes. The user name and the text that reads you have been recognized by Jeffrey Rhodes are circled.

As discussed in the previous section, we send the chat as Flow bot either way. So both chats have that picture and are from Jeffrey Rhodes via Power Automate. The only effect of the Tell Recipient Who Nominated Them box is the recognition line at the end of the top chat.

Figure 21-17 shows the results with Send via Email and with the Tell Recipient Who Nominated Them box checked .

A screenshot of a mail titled employee recognition for Jeffrey Rhodes. 2 bullet points in the mail read service before self and excellence in all we do.

The email comes from me and actually shows up in my Sent Items in Outlook. It shows up as normal importance due to our change in Figure 21-13.

When I don’t check the Tell Recipient Who Nominated Them box, there is no email, however. To troubleshoot, we go back to Power Automate and look at our Run History. We see that runs where we try to send email with that box unchecked all fail. We click on one of the runs and see there is a white exclamation point inside a red circle for the Send Email condition. Figure 21-18 shows what it looks like when we expand the condition.

A screenshot of 4 adjacent tabs titled send email, send as user or service, send an email V 2, and send an email notification V 3. An arrow points to unauthorized under the last tab.

Even though this has worked for me in the past, I consistently get this error now. As I mentioned earlier, this is likely due to spam being sent using this connector.

If I were updating this app, I would remove the Tell Recipient Who Nominated Them box completely as it didn’t do much for the post and chat cases and failed with email. Otherwise, we have a pretty functional application.

Reporting on Employee Recognition in Power BI

The last piece of the puzzle is visualizing the recognition data we shared to our SharePoint list. As we’ve discussed earlier, we can choose either the 1.0 or 2.0 implementation when connecting to SharePoint Online Lists. I tried it both ways in this case since I like retrieving the plain text by expanding FieldValuesAsText, which you only get with 1.0. But the Unpivoting that we need gives errors with 1.0, so we use a different technique for getting plain text and use 2.0. Figure 21-19 shows us selecting the Recognition list.

A screenshot of a window titled navigator. Recognition is selected from the listicle titled display options on the left pane. The right pane has a table with columns for office and details.

Notice how the Details column shows HTML rather than the plain text we want.⁶ We Transform data and do our normal task of renaming columns and ensuring the data types are correct. Since the Recipient column displays as a Record, we expand its title and email columns, as shown in Figure 21-20.

A cropped screenshot of a tab in which the recipient menu is opened. The ticked checkbox for email is highlighted. The cursor points to the O K button on the bottom.

We then rename them to Recipient and Recipient Email. We similarly expand Created By into title and then rename that column to Submitter.

Dealing with our True/False data for our Values is more challenging. We want to be able to count the number of recognitions by value and also slice and drill down by value shown. Another way to look at this is that we care about the True values, but not the False ones. To get the data how we want it, we again unpivot the data, as shown in Figure 21-21.

A screenshot of the transform menu. The option unpivot only selected columns is pointed with an arrow from unpivot columns. The bottom pane has a table.

This copies the column name (Integrity First, Service Before Self, and Excellence in All We Do) to the Attribute and then the True or False to the Value (Figure 21-22).

A screenshot of a table with columns for attribute on the left and value on the right. Various data are displayed under each.

Notice that this expands the number of rows and thus we will need to use Count (Distinct) when we get to our visualization. We then rename Attribute to Value and Value to HasValue. Since we only care about the True values, we create an Exemplified Value conditional column that is True if HasValue is True and is otherwise null. Figure 21-23 show the screen for doing this.

A screenshot of a window titled add a conditional column. The field entry for the new column name is exemplified value, and for A B C 1 2 3 under else is null, respectively.

We now can filter out all the null rows,⁷ as shown in Figure 21-24.

A screenshot of a window titled filter rows. The radio button for basic is highlighted under apply one or more filter conditions to the rows in this table.

As with past similar columns, we change the data type to Whole Number as we don’t care about partial days.

Our last challenge is that our Details column still contains HTML (you might want to refer back to Figure 21-19). We start by renaming the column to Details - HTML. That will allow us to eventually get to a new column we will call Details. Before that, we need to create a custom DetailsTable column, as shown in Figure 21-25.

A screenshot of a window titled custom column. The field entry for the new column name is details table. It has a command window on the left and a list of available columns on the right.

This uses the powerful Html.Table() method and the technique I learned from Dhruvin Shah’s video.⁸ The first parameter is the HTML itself (our Details - HTML column). The second parameter is called columnNameSelectorPairs. Since it is a list, we enclose it in {} with each item also in {}. We call the name of the table we will create “Details,” which is why that is what we will select in Figure 21-26. Since we want all the text in all the HTML, we use “:root” for the second parameter. If we wanted sections of our HTML, we could use specific selectors.⁹ Looking again at Figure 21-26, we see that there is a Table in each row of our custom column.

A cropped screenshot of a tab in which the details table menu is highlighted. The checkboxes for select all columns and details under expand are ticked.

We expand it and select our Details table. This creates a Details column that contains the plain text we want.

We are now ready to visualize our data. Our first challenge stems from unpivoting our Values data (see Figure 21-21). Since there are multiple rows now for each value exemplified by the recipient, a Table control would have excess data. Instead, we want to use a Matrix control, as shown in Figure 21-27.

A cropped screenshot of a window. The left pane has a list of dates and remarks under recipient. The right pane has field entries for rows, columns, values, and drill through.

We include Recipient, Created (Date), Details, and Exemplified Value. The order is important since Exemplified Value is the one that can have multiple values per submission and thus needs to be last. We expand the Matrix to its lowest level so all four columns are displayed. Note that we don’t include the Submitter in this example, but could easily add this to the control and also create a slicer for it and the Recipient if desired.

Figure 21-28 shows why we unpivoted, filtered, and created our Exemplified Value column. We can have a slicer that shows our three possible values as well as both a pie and bar chart that (distinctly) counts number of recognitions for each value.

A screenshot of a tab has pie charts and bar graph with data on submissions through exemplified value and recipient. A panel on the right titled visualizations has various options.

Although we have a Days Since Submitted slicer, we can duplicate this page and then add a page-level filter to create a Previous 7 Days page, as shown in Figure 21-29.

A screenshot of a tab has pie charts and a bar graph with data on submissions through exemplified value and recipient. The previous 7 days option is highlighted on the left pane.

Summary

In this chapter, we put all the pieces together to create an Employee Recognition solution. We stored our information in SharePoint, created the user interface and saved the info back to SharePoint in Power Apps (with some special sleight of hand to format our demonstrated values), called a Power Automate flow to post to a Teams channel, send a Teams chat, or and/or send an email, and then visualized the results in Power BI. In the next chapter, we will some of these same pieces and techniques to create a reservations solution.