If you are looking to learn how to build your own app for tracking 'stuff' with PowerApps, then please read on and together we'll make an app that looks and functions like what I show in the clip below!
I've decided to chunk this tutorial post into a few sections - so for today we'll start with an overview of the app. I'll step through the data source, and the two main collections I use within the app.
In subsequent posts I'll cover the patching and pop up actions you'll see in the clip below.
The app I've created is a bit of a show case for the different functionality you can easily add via the PowerApps maker portal. Some of the expressions or functions I write may seem fiddly at first, but stick with it as it does get easier.
If you can write basic excel like formulas - you can build your own PowerApps
Data Source
In my app the data source is a SharePoint online list. I've built a few different types of columns to show how to get the data from them and how to pass data into them from you App.
This app is a great starter kit app - but if you are thinking you want to track thousands of items - you should consider adjusting your data source to the Common Data Service or to Azure SQL.
Note : I still have a habit of creating SharePoint column names with no spaces in them!
Once you have your data source in place - add some dummy content as needed. Now its time to start work on our app!
The PowerApp - Overview
I've created a blank PowerApp in the tablet layout and switched the theme of it to 'Orange' as I'm working at Brighter Days and we love Orange!
The app for this tutorial is a single screen application that allows for the following showcase actions:
- Uses local Collections
- Updates Data Source via Patch and SubmitForm actions
- Makes use of Collections for Navigation/Filtering
- Presents items in a tracking gallery that can be dynamically filtered
- Makes use of conditions to show different colours / icons / options
- Makes use of Sum calculations to present values per category you are tracking
- Uses a Pop Up form for adding new items
Step 1 Connect Data Sources
I have one active data connection - SharePoint. I connect to my SharePoint list which is called BDPipeline.
Step 2 Set the OnStart Actions
All PowerApps can do magic as they open - I would suggest entry level magic, if you try to be "Pug" or "Gandalf" the longer the App will take to open. Less is more etc..
OnStart, I create a local collection of this in my App called myPipeline - which is linked directly to my main data source.
I do this to make the dashboard respond quickly to my actions, but it isnt a requirement - you can do this all via calling your data sources remotely, but expect a little bit more latency if you do.
I also create a second collection called myStatus which I use for navigation/filtering.
ClearCollect(
myPipeline,
BDPipeline
);
ClearCollect(
myStatus,
{theStatus: "Pipeline"},
{theStatus: "Verbal"},
{theStatus: "Not Started"},
{theStatus: "Started"},
{theStatus: "Show All"}
)
Step 3 OnVisible Actions
I set two context variables the onVisible property of my screen to ensure that the items I want to show in the main tracking gallery will always work.
UpdateContext({con_Category : "Show All"});
UpdateContext({con_JobStatus: ""})
Step 4 Add Galleries - Filter/Navigation
The tracking app contains 2 galleries - I've inserted a blank horizontal and a blank vertical to help showcase both. We'll use a horizontal gallery for navigation/filtering of the main items gallery.
You'll see in the image below that I use a button in the main cell of the gallery to give a good 'hey you can click me!' type indication to the end users of the app.
The data source of the gallery or 'Items' property is the collection we created onStart - called myStatus.
To make the button different colors per gallery row we have to make some edits to the Fill property. For me I used an if/else type condition, however, for extra kudos you could use your data source to automatically plug in the RGBA numbers if you wanted to.
In the condition shown above I set 4 different colors, then a final catch all color in case my column called theStatus doesnt match and of my conditions.
To get the dollar values I adjust the text of the button with the value shown below - this then presents the name of the status then a filtered sum of my tracked items matching that status in a currency format.
Note - there is no NZ currency formatting - so no '$-en-NZ'. I've used the US one as this shows the same dollar type symbols.
ThisItem.theStatus &" "& If(ThisItem.theStatus = "Pipeline",Text(Sum(Filter(myPipeline, Category.Value = "Pipeline"),Amount),"[$-en-US]$###,###"), ThisItem.theStatus = "Verbal",Text(Sum(Filter(myPipeline, Category.Value = "Verbal"),Amount),"[$-en-US]$###,###"), ThisItem.theStatus = "Not Started",Text(Sum(Filter(myPipeline, Category.Value = "Not Started"),Amount),"[$-en-US]$###,###"), ThisItem.theStatus = "Started",Text(Sum(Filter(myPipeline, Category.Value = "Started"),Amount),"[$-en-US]$###,###"), Text(Sum(myPipeline,Amount),"[$-en-US]$###,###"))
Some additional button magic...
I've worked with buttons a lot, and sometimes they can be tricky to get specific text to run over two lines, or include images etc - the above snippet uses a new line to do this, but the other technique I use at times is to create a 2nd button that sits above all the elements in the cell and has a visibility fill set to zero - so a secret hidden button on top - this is a useful option as you don't then have to add onSelect actions for all the elements you may have sitting under this button etc..
Button onSelect - I add a context variable to the button so that when clicked it will populate a variable I want to re-use for filtering my main tracking gallery.
The onSelect action for the button updates the context with the Status values of the button I've clicked - but also allows that if I click the gray 'Show All' button it clears a secondary context variable used for filtering on a different status type.
UpdateContext({con_Category: ThisItem.theStatus});
If(
ThisItem.theStatus = "Show All",
UpdateContext({con_JobStatus: ""})
)
Step 5 Add Galleries - Main Tracker
My main tracking gallery has a data source of the myPipeline collection, however I want to be able to filter it based on button pushes in the horizontal navigation/filtering gallery.
To do this I need to add some conditions into my Items property for the gallery so I can ensure it filters correctly. The Items gallery will be able to be filtered by Status - eg Verbal AND JobStatus etc Active
This means we need a reasonably complex Items statement like this!
If( con_Category = "Show All" && IsBlank(con_JobStatus), SortByColumns( myPipeline, "CategoryID", Ascending, "Title", Ascending ), SortByColumns( Filter( myPipeline, If(con_Category = "Show All", true, Category.Value in Split( con_Category, "," ).Result, true) && If(con_JobStatus <> "", JobStatus.Value in Split( con_JobStatus, "," ).Result, true) ), "CategoryID", Ascending, "Title", Ascending ) )
To step through this conditional filtering - initially I am looking to see if the value of con_Category is "Show All" AND to check if my other context variable 'con_JobStatus' is blank. If so - show all items in my data source in an a-z order based on Category, then Title.
If we have a different in either context variable value we then need to check what it is. I've allowed filtering on 2 variables that are unrelated eg Show All + Blocked or Verbal + Active so I have to both of my context variables as part of filtering.
If you look at the filter statement above, you'll see that to allow for multiple filters in the same category I'm using an 'In' and a 'Split' - but my app isnt allowing that yet as its just reacting to various button clicks - however, I like to use this syntax in case a client suddenly asks me for the option of select a couple of combinations to filter eg show all Pipeline and Verbal items.
Once the gallery is added I can add my controls and then adjust how they look to suit what I need. For example, I can chose if they are edit or view controls based on any status I want. For the Status dropdown control (pipeline,verbal etc) I want to make this read only if the status value for that item is 'Started'.
To do this I adjust the DisplayMode property of the Status dropdown control to the following.
If(ThisItem.Category.Value = "Started", DisplayMode.View,DisplayMode.Edit)
I can then follow the same technique for status indicators and button colors. Check a value, then do something in the Fill or Text property of the control.
Comments