Tip: Advanced field validation with colors

How do you convey that a field is 62 percent invalid? By assigning a background color to the field that gets more intense the less valid the field is! Read this tip to learn how to use the new BRIGHTEN function to achieve this.

We recently added support for assigning colors to various elements of your app using formulas. In addition to the formula functions COLOR and DEFAULTCOLOR, there are 22 new color functions which fall into three categories:

  • Functions which allow you to create new colors using the color components supported by the various color models (RGB, RGBA, HSB, HSBA, HSL and HSLA).
  • Functions which extract these color components from colors (RED, GREEN, BLUE, ALPHA, HUE, HSBSATURATION, HSLSATURATION, BRIGHTNESS and LIGHTNESS).
  • Functions which serve as easy-to-use, but less versatile, alternatives to the other functions (BRIGHTEN, DIM, LIGHTEN, DARKEN, SATURATE, DESATURATE and COLORSPIN).

How can these functions be used to create more useful apps? Let’s return to the subject of validating fields. So far, we have treated field validity as an either-or proposition: either a field is valid, or it isn’t, with nothing in-between. That is not always enough — sometimes you need to convey that a field value is mostly acceptable or is close to being invalid, but is not quite invalid. There are obviously many ways to convey this information, but using different shades of a color is effective, elegant and takes up very little space. Here, we’ll change the background color of a field, but you could also change the color of the field value or field label.

Our goal is that the background color of the field should be white if the field is fully valid, deep orange if it is invalid and then use different shades of orange to convey that the field is somewhere in between being fully valid and fully invalid.

Realizing fine-grained validation with the IF and COLOR functions

The most obvious way to realize this more fine-grained validation feature is to use the IF formula function in conjunction with the COLOR function:

IF(BoilerPressure < 500, COLOR("white"), IF(BoilerPressure < 1000, COLOR("deep orange 300"), IF(BoilerPressure < 2000, COLOR("deep orange 600"), COLOR("deep orange 900"))))

This formula, when associated with the BackgroundColor property of a field, uses four different colors to convey the severity of a fictitious boiler pressure level. If the value of the BoilerPressure field is less than 500, the background is set to white. If it is less than 1000, it’s set to a desaturated orange color. (“Deep Orange 300” is a color from the Material Design palette.) If it is less than 2000, a somewhat more saturated color is used. Otherwise, the background is made deep orange.

Realizing fine-grained validation with the BRIGHTER function

The problem with the formula using the IF and COLOR functions is that it only supports assigning one of four colors to the background. As it can only return four specific values, we have created a discrete formula. Ideally, we’d like the formula to be able to return an infinite number of colors. If the boiler pressure is zero, we’d like it to return white. If it is slighty more than zero, we’d like the formula to return a color which is mostly white, but has a hint of orange in it. Also, we don’t want to create a huge formula returning, say, a hundred different pre-calculated colors — we’d like to use a simple, elegant formula capable of returning an arbitrary number of colors (a continuous formula). To do so, we will use the BRIGHTER function.

First, we need a measure of the degree to which a boiler pressure should be considered “valid.” For that purpose, we’ll define a hidden field we name Validity, where the value 0 means that the field should be considered fully invalid and the value 1 means that it should be considered fully valid. The value 0.5 should thus be taken to mean that the boiler pressure is halfway between being fully valid and fully invalid.

Let’s say that a boiler pressure of 0 should be considered fully valid and a pressure of 3000 should be considered fully invalid. We then use this formula to determine the validity:

1 - MAX(0, MIN(3000, BoilerPressure)) / 3000

The MAX and MIN functions are used to ensure that the BoilerPressure value is between 0 and 3000 (inclusive). We then divide the number by 3000 to arrive at the validity — almost. As we want a fully valid number to equate 1, not 0, we need to subtract the number we arrived at from 1.

We now have all the building blocks we need to brighten the color. The BRIGHTEN function returns a brightened version of the color given as its first parameter. The second parameter should be a number between 0 and 100 which specifies the degree to which the given color should be brightened. 0 means that the color shouldn’t be brightened at all and 100 means that white should be returned.

To achieve our objective, we need to make use of the Validity field we calculated, which is between 0 and 1. As the BRIGHTEN function expects a number between 0 and 100, we simply multiply the validity by 100 before passing it to the BRIGHTEN function:

BRIGHTEN(COLOR("deep orange 900"), Validity * 100)

That’s it! We just need to associate this formula with the BackgroundColor property of the boiler pressure field to effectively convey the validity to the user.

Fun fact: The first draft of this blog post was more than twice as long as the finished version. At that time, Calcapp didn’t support the convenience functions BRIGHTEN, DIM, LIGHTEN, DARKEN, SATURATE, DESATURATE and COLORSPIN, so we brightened the background color using the HSL, HUE, HSLSATURATION and LIGHTNESS functions instead. The resulting formula was overly long and the blog post explaining how it worked was hard to understand. Dissatisfied, we added the convenience functions and rewrote the blog post. If you don’t feel that the convenience functions give you the control you need, spend some time learning about the RGB, HSB and HSL functions in the reference sidebar!

Do you want to share a tip with other Calcapp users through this blog? Let us know!

« Feature: Select colors using formulas Release: Our August, 2017 update is here »