2018-09-07

This tutorial will show you how to change the look of individual rows (in a PowerApps Gallery) based on logic you specify. The data is based on the theme of Queen Boadicea and managing her expenses within a SharePoint list.

BTW, before we continue, you should also check out how to do conditional formatting using a lookup from a value stored in a collection.

The list below is a modern SharePoint custom list with a few text columns, a date column and a currency column.

Create an app from the SharePoint List

To create the example used in this tutorial, for speed, let’s create a standard PowerApps app by navigating to the list in SharePoint and selecting “Create an app”.

Create App process

Before I begin I make a few quick tweaks to change the gallery layout to Image, title, subtitle and body and change the date to “yyyy-mm-dd” format using the Text function for the Text property of the expense date (date formatting probably needs a post all of it’s own).

To keep in with our ‘Celts’ theme let’s tweak the value to show Amount field as gp and sort by the Expense Date.

Colouring rows based on a condition…

In this section, let’s set a colour for each row in our Gallery. We want the row to be displayed in green if they have been paid and red if not. Colours in PowerApps are specified using RGBA functions that take 4 parameters (red, green, blue and an alpha channel, which specifies the opacity of the colour between 0 and 1). The colour numbers are 0 to 255.

Step 1: Select the gallery object and in the property drop down select TemplateFill.

Step 2: In the formula bar tweak the colour to be bright Red and change the opacity to 1. This changes every row to be bright red. Tweaking the red value or changing the opacity will change the colour. Bright red would be “RGBA(255,0,0,1)

Step 3: Now we will add an If function into the formula to change this. The If function is just the same as Excel’s if, so a test then true part and a false part. In our test if the Paid column, which is a yes / no column is “true”, i.e. yes. In other words the list item will display green if it has been paid and red if it hasn’t been paid.

Step 4: Now only unpaid rows are coloured red. If we want to add another colour to represent a different status, e.g. amber to show if an expense is recent, then we need to nest another “if” statement inside the first one. If the expense date plus 30 is greater than today, i.e. the date was in the last 30 days then it will be coloured amber for an upcoming payment.

If you prefer the functions laid out then click  Format text.

Changing the picture based on a condition …

The list of expenses includes a category which is either Personal, Weapon or Travel. I want these to be shown using small pictures. The first step is to load the media.

Step 1: On the View ribbon, click Media and make sure you have Images selected on the left.

Step 2: In the top right click Browse and select your images. You can use the Ctrl key to select multiple images from one folder.

Step 3: Click Open to load the images into your app. Note you are limited to 200 MB of media storage.

Step 4: Click on the image in the top gallery section and select the Image property. It currently is set to SampleImage.

Step 5: To replace the images we’ll use the Switch function. It takes a single value and then pairs of matching values and the result.

The list should now show images based on the category as below:

Hiding a value based on a condition …

The final section in this tutorial is perhaps the easiest idea. Hide or show an icon based on a simple logic test. In this section, we will show an icon if the value is over 30.

Step 1: Select the “BrowseGallery1” and click the edit icon to select one row of the gallery.

Step 2: From the Insert ribbon, select an icon. The position where you want it to be in the row. One will be shown on every row.

Step 3: Select the “visible” property, it will be set to “true”.

Step 4: As the visibility is just a true or false it can just be a test and doesn’t need an if or switch function.

By applying the formula above you will now notice that there are only rows visible where the value is greater than 30 gp.

Conclusion

This is the first half of my talk at SharePoint Saturday Cambridge 2018. It covers three simple examples of conditional formatting. I’d prefer the values of the conditional formatting such as the colours, days when an expense becomes late and the value at which the expensive icon shows to be stored on a separate screen and not hardcoded but that is probably another post.

Let me know if you’ve any more conditional formatting tips!

(Visited 3,601 times, 1 visits today)

About the author 

Laura Graham-Brown

SharePoint Trainer, Consultant and Agony Aunt

Leave a Reply

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

2 Free Ebooks: How to govern Microsoft teams 

Download your 2 free Microsoft Teams governance Ebooks and learn the steps necessary to create a bullet-proof governance strategy.