skip to Main Content

By now, we’ve all seen Microsoft’s rebranding of Power BI, PowerApps, and Flow into the Power Platform, a set of tools for the citizen developer to analyze data, take action from that data, and, ultimately, solve problems. No doubt, you’ve seen tons of blog posts on Power BI and any number of posts on PowerApps and Flow, but have you ever put it all together. In this post, that’s what we’re going to do, and we’re going to do it with the mission of solving a single problem with a simple, minimalist approach.

What’s that problem we’re going to solve? Well, I’m glad you asked! Have you ever worked at a company that used Action Logs or been in a meeting that used them? Sure ya have. We all have! And, typically, they all stink. I mean, have you ever seen an action log and thought, “Oh, great, this’ll make things simpler!”?

Don’t get me wrong: Action logs have their place. They are useful tools that solve a significant problem. That is, how do we get things done and keep track of what is behind schedule and what needs greater attention. I have used, even built, some pretty good ones in Excel. They used conditional formatting to show what was due or behind schedule. You could filter the log by your name to find your actions or by a meeting for a group-wide review.

But, what if multiple people want to edit actions at the same time? What if someone checks out the file to edit it and forgets to check it back in? What if someone uploads an older version with his changes? And, typically, you wind up with several action logs spread across several locations. What a mess?! Power Platform (and SharePoint) to the rescue!

An Overview of the Solution (Or, if you’re Rob Collie, here’s the movie trailer)

We need somewhere to store the data. In its simplest form, an action log is a single table. Yes, you can have lots of support tables and make it super complicated, but for this demo, we’re going to keep it simple. You can, of course, store that table in SQL Server or an Azure SQL database. If I were to productionalize this solution, that’s what I would do, but SharePoint lists are a fine, simple way to store online a single table of data. Your organization may not allow you to spin up a SQL database, or maybe that’s not in your bailiwick. No worries! SharePoint lists will suffice.

From there, we’ll create a PowerApp to pull data from and push data to that list. We’ll then embed that app in a Power BI report so that management can see, at a high level, how teams or individuals are doing at accomplishing their tasks. Finally, we’ll add a button attached to a Flow to send email reminders for past-due actions.

You see, no more version control problems! Everyone can edit at the same time, and you have all your actions in one place.

First, the Data (Or, to continue the movie theme, Act I: The Setup)

As I have mentioned, the action log is a single table of data. Mine has eight columns: Meeting (in which the action was taken), Recorded Date (when the action was written down), Issue (high level), Action (more detailed), Status, Responsible Email (email address of the responsible person), Due Date (when the action is due to be completed), and Complete (a Boolean). Since we will be using Due Date for some conditional formatting, I would encourage you to use your data for this project. If you read this post a month or a year from now, that logic would not give you the results you might expect. Furthermore, an action log is supposed to be a dynamic, living document. So, use your data, and you’ll already be on your way to solving the problems that you face.

Now, On to the PowerApp (Or, Let’s Meet Our Protagonist)

The first thing we’re going to do is build our PowerApp. If you’re using SharePoint 365, it’s as simple as clicking the PowerApps dropdown and selecting Create an app.

Create an App

SharePoint communicates with the PowerApps service, the logo spins for a few minutes, and you get an out-of-the-box app. It follows the three-screen setup: one screen shows the list with some of the fields…

BrowseScreen1

A second screen shows the details of a single entry in that list…

DetailScreen1

A third screen allows you to edit those details or create a new list entry.

EditScreen1

(If you do not have access to this “create-an-app” functionality, you can still build the app manually. Because this post is long enough already and since there is plenty of material about that, I am assuming you do have that functionality and am jumping off from that starting point.)

By now, we’re off to a great start, but there are a few things I want to customize. Here’s what the app looks like so far.

ActionLog

First, I want to modify the Items property of the Browse Gallery (called BrowseGallery1 in my setup) on BrowseScreen1. We’ll use the following code:

SortByColumns(Filter(‘Action Log’, TextSearchBox1.Text in Action, !Complete), “Due_x0020_Date”, If(SortDescending1, Descending, Ascending))

SortByColumns

This formula filters the SharePoint list called ‘Action Log’ by two Boolean parameters. First, it looks for the text in the search box toward the top of the screen (called TextSearchBox1) in the Action column of the Action Log table; it returns only those records where it finds the search value in the Action column. Second, it only returns rows where the value in the Complete column is false (or !Complete). The formula then sorts the resultant table by the Due Date column (here expressed as “Due_x0020_Date” since PowerApps does not like spaces) and sorts it either ascending or descending based on what is in the variable SortDescending1. There is a button at the top of the screen to toggle this variable and, thus, the sort behavior.

Quick note: If you click the down arrow on the right side of the formula bar, you will see a button called “Format text”. Be sure to utilize this feature, whenever you have any code of any complexity. Also, you can expand the formula bar by hovering over the bottom of it and dragging it down.

Format Text

When building our PowerApp, the system gave us a vertical gallery with three elements called Title1, Subtitle1, and Body1.

Body, Title, and SubTitle

While the PowerApps software made its best guess at what fields we would want in each of these slots, we can make improvements. Expand BrowseGallery1, select Title1, and access the Text property. Change it to

ThisItem.’Responsible Email’.Value

This.Item."Responsible Email".Value

ThisItem refers to the selected object (in this case, BrowseGallery1) and the data to which it is connected, the result set of the earlier formula. That formula returns a table of data, so ThisItem.’Responsible Email’ is the intersection of the Responsible Email column from that table and whatever record the gallery row is on. That intersection refers to a single cell. “.Value” returns the text of that cell. Notice how, when you hit enter after editing the formula, the selected card changed, in my case, from blank to an email address.

Next, select Subtitle1, access the Text property, and change it to

ThisItem.’Due Date’

ThisItem. " Due Date"

Finally, select Body1 and change the Text property to

ThisItem.Action

ThisItem.Action

Let’s Add Some Conditional Formatting

We want the date value to turn yellow for any incomplete action due today and red for any incomplete action due earlier than today. In Excel, we would call this conditional formatting. Within PowerApps, we are just controlling a fill color property by a script rather than a simple color. Select Subtitle1 (which we set to Due Date) and access the Fill property. Change it to

If(And(ThisItem.’Due Date’ < Today(), ThisItem.Complete = false), Red, If(And(ThisItem.’Due Date’ = Today(), ThisItem.Complete = false), Yellow, White))

Fill

DetailsScreen1 (Or, Act II)

We’re now going to customize the details screen. A quick note on SharePoint lists: when you start a new list, there is a single visible column called Title and several hidden columns. On the display form DetailForm1 on the detail screen, you will find, at the top of the fields listed, a field called Title. Since I did not use this field in my SharePoint list, we will remove this card from the detail form. In the Screens section on the left, expand DetailForm1, right-click on Title_DataCard1, and select Delete.

Delete

Next, select Due Date_DataCard1, which, of course, is the data card in the detail form for the Due Date column in the Action Log SharePoint list. Access the Fill property, and set the formula to

If(And(ThisItem.’Due Date’ < Today(), ThisItem.Complete = false), Red, If(And(ThisItem.’Due Date’ = Today(), ThisItem.Complete = false), Yellow, White))

DueDate

This is the same formula as the one above, used for the fill color for the Due Date field for Subtitle1.

Finally, add Complete to the list of fields displayed by clicking on DetailForm1, “Edit fields” on the Properties blade. Click “Add field”, check the box next to Complete, and hit Add.

AddField

Then, expand the Complete_DataCard1, select DataCardValue1, and change the TrueText property to “Complete” and the FalseText property to “Not Complete.”

Not Complete

EditScreen1

Lastly, on to the final screen, EditScreen1. Again, delete the Title data card and add the Complete data card just as you did before. Expand EditForm1 and the data card for the Complete field, select DataCardValue9 (yours may have a different number), and change the TrueText property to “Complete” and the FalseText property to “Not Complete.”

CardComplete

Intermission

OK.  That was a lot of information.  Stay tuned for Part 2 next week where we will add a flow and finish this PowerApp!

Where It’s At:  The Intersection of Biz, Human, and Tech*

We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”

In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂

* – unless, of course, you have two turntables and a microphone.  We hear a lot of things are also located there.

Michael McKinley

Michael McKinley is the founder of McKinley Consulting. Michael has managed projects and workstreams in consulting for the last five years, in areas including data analytics, change management, operations excellence, and supply chain transformation.

This Post Has 2 Comments

Leave a Comment or Question