Color-Coding Tasks by Status with Efficient Rich-Text Formulas in Quickbase

Visuals can greatly enhance the speed and efficiency of our comprehension. Traffic lights are an obvious example. Can you imagine the potential delays and accidents if instead of red, yellow, and green, traffic signals just utilized words like “stop”, “caution”, and “go”? Just like traffic lights, there’s no better way to get quick insight into the condition of tasks and projects than leveraging the power of color. Quickbase allows builders to do this through the use of a formula rich-text field.

In this installment of Synctivate’s “Helpful Hint” series, we will develop two useful skills. After reading this post, you will be able to:

-Insert a color-coded status icon into any kind of report where you are tracking due dates.

-Write cleaner, less error-prone formulas where multiple conditions exist.

So without further ado…

Happy New Year! It’s January 1st and we have 5 tasks on our report. Each task has a status that can be one of several options: Not Started, Started, Review, Completed, or Cancelled. We also have a due date for each project. Wouldn’t it be nice to have a quicker way to identify priorities than to read through due dates?

Let’s make one! We are going to create a new field called “Task Condition” which is going to provide us with a quick color-coded icon to let our managers get a rapid-fire reading of their tasks and prioritize accordingly.

Go ahead and create a new field. For field type select “Formula – Rich Text”.

Click “Add”.

On the next page, under the section “Formula Rich Text – field options” in the “formula” field, copy and paste the following code into it:

If([Status] <> "4 - Completed" and [Status] <> "C - Cancelled" and ToDays([Due Date] - Today()) >= 7,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/228-rect_green.png\"></div>",

If([Status] <> "4 - Completed" and [Status] <> "C - Cancelled" and ToDays([Due Date] - Today()) < 7 and ToDays([Due Date] - Today()) >= 3,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/229-rect_yellow.png\"></div>",

If([Status] <> "4 - Completed" and [Status] <> "C - Cancelled" and ToDays([Due Date] - Today()) < 3,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/227-rect_red.png\"></div>",

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"></div>")))

Once you’ve done that it should look like this in the formula box.

This formula is made up of three “If” statements. The truth of the condition is checked for each line and a result is returned for the statement that matches the particular condition of the record. Let’s translate each “If” statement into plain old English one by one.

The first “If” statement means:

If the status is not ‘Completed’ or ‘Cancelled’ and the number of days until the due date is greater than or equal to ‘7’, display a green rectangle.

The second:

If the status is not ‘Completed’ or ‘Cancelled’ and the number of days until the due date is less than 7 and greater than or equal to 3, display a yellow rectangle.

And the third:

If the status is not ‘Completed’ or ‘Cancelled’ and the number of days until the due date is less than 3, display a red rectangle.

After the third “If” statement, there is a comma and the following line returns another command: display a gray rectangle. This means that if none of the three conditions is met, a gray rectangle will be displayed.

Click “Save”. Let’s go look at our default report.

Now we have that fantastic color-coded task condition icon. Our HTML Template has been completed and our Calendar Cleanup is cancelled, so their icons are both gray. The server set up has a due date that’s over 7 days from today, so the icon is green. The Monthly Report is under a week away, so it’s good that this yellow icon is bringing that to our attention. Finally, the Flight Booking needs to happen tomorrow! Thanks, red icon. Now we know what to prioritize.

But we can make an improvement. The following formula example is a much more efficient way to get the same results. Let’s access the properties for the task condition field and replace our formula with the following:

var bool active = [Status] <> "4 - Completed" and [Status] <> "C - Cancelled";

var number dr = ToDays([Due Date] - Today());

If($active = false,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"></div>",

$dr >= 7,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/228-rect_green.png\"></div>",

$dr < 7 and $dr >= 3,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/229-rect_yellow.png\"></div>",

$dr < 3,

"<div align=\"center\"><img src=\"https://images.quickbase.com/si/16/227-rect_red.png\"></div>")

It should look like this.

First we define two variables: “active” which is true if the status is not completed and not cancelled, and “dr” which stands for days remaining and works the same way as our previous formula “(Todays([Due Date] - Today())” returning the number of days until the due date.

Now we can create a single “If” statement with all of our possible conditions nested within it. If the status is not active, display a gray rectangle. If that’s not true, move to the next condition and check. Is days remaining greater than or equal to 7? If so, display a green rectangle. If that’s not true move on to the next condition. Is days remaining greater than or equal to 3? If so, display a yellow rectangle. If not, move on to the last possibility. If days remaining is less than three, display a red rectangle.

Click “Save”.

This new formula will not create a different result from our previous formula. However, it will reduce repeated phrasing and unnecessary keystrokes. Any time you can find a way to get more functionality with fewer commands, you are improving your chances of developing an error-free, efficient solution. And that’s what Quickbase is all about.