2018-12-30

This post is going to show an alternative for conditional formatting using lookup. If you want to see another approach please read ‘How to do conditional formatting in PowerApps’. I often get asked how to do conditional formatting or asked to help change existing conditional formatting that is not based on multiple conditions in an IF or SWITCH statement or nested IFs if you haven’t noticed PowerApp IF allows multiple tests.

This method is going to be based on a collection being a lookup table. This could be a data source, especially if multiple apps are going to use the same lookup for formatting. The example used for this post is a list of tasks. Each task has a status, which can be “Not Started”, “In Progress”, “Complete” or “Delayed”. The conditional formatting will be based on the status.

Creating the Lookup Collection

Collections can easily be created by using a CLEARCOLLECT function. It takes the name of the collection followed by JSON objects for each row. This code needs to be in OnStart code of the app and for preview testing in a button that can be removed before publishing.

ClearCollect(ccStatusColours,
    {Status:"Not Started",Colour:"#cccccc"},
    {Status:"In Progress",Colour:"#f4b642"},
    {Status:"Completed",Colour:"#008a00"},
    {Status:"Delayed",Colour:"#f40000"}
)

For each status value, a colour is given that could be used using COLORVALUE function to colour or fill any object.

Using LOOKUP to format

The app contains a gallery of the tasks and we are going to apply conditional formatting to the text of the status.

Step 1: Select the label that contains the status.

Step 2: Select the color property of the label and enter the following:

ColorValue(
    // Lookup the colour
    LookUp(
        ccStatusColours,
        Status = ThisItem.Status,
        Colour
    )
)

The LOOKUP function needs three parameters, firstly the collection of the data source to lookup in, secondly the filter to use and lastly the field to return. In this case, it returns the colour in a hex format which can be used inside a COLORVALUE function to return a colour for formatting.

formatted gallery

Conclusion

Although this is probably one of my shortest posts it has been a real eye opener for some. Although I could use an IF or a SWITCH to achieve the same if I had 10 different statuses that would be one long statement on anything I wanted to format.

If your app has pictures in the media the original collection could also contain a column for a picture. A collection and a few lookups are easier to maintain than IF and SWITCH calculations.

(Visited 430 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.