About Row Colorization

Are your team members missing their deadlines? How would you like to color all your time-sensitive tasks red, so staff members sit up and take notice? Or maybe you want to color code tasks by status or category to get the big picture at a glance. QuickBase lets you set the background color of rows in any table report. You can set colors based on any data that exists within the table. For example, you could make the color depend upon the value in the Priority field. Make high priority tasks red, medium priority tasks pink and leave low priority entries white.

You set row color when you're designing or editing a report. To apply color, you enter a formula in the report builder that spells out your conditions and your chosen colors. You can specify color using a hexadecimal value (e.g., #0033CC) or, for simple colors, a name (e.g., "red" or "blue").

Tip: Hexadecimal values tell Web browsers exactly what color to display by indicating alphanumerically how much red, blue and green the color contains. To learn more, read about Web colors on the World Wide Web Consortium (W3C) site.

Formulas that color rows are fairly simple and you can consult the many examples at the end of this topic for guidance. For a soup-to-nuts primer on QuickBase formulas, please read Using Formulas in QuickBase. If you enter your formula, and QuickBase rewards you with an error message, get help by reading Troubleshooting Formula Errors. To view a comprehensive list of formula functions, refer to the Formula Functions Reference (opens in a new window).

To use row colorization in a table report:

  1. Create a table report or edit an existing one.

  2. In the Options section of the Report Builder, turn on the Highlight records based on criteria checkbox.
    QuickBase displays a set of additional controls.

  3. In the Colorization formula box that appears, enter a formula.

  4. You use the formula to tell QuickBase which rows to color. Most of the time you'll use the If() function to set your conditions. (List of HTML color names.)

    For example, say you want to color all high priority items pink. To do so, you'd enter the following formula:

    if ([Priority] = "High", "pink", "")

    This formula says: If the Priority field contains the text "High", then color the background of the row pink, otherwise don't color it. If you'd like to set many conditions on the same field, use the case() function as illustrated in the last two examples at the end of this topic. Find other scenarios in the table of examples below.

  5. To test your formula and see what your report looks like, click Display.

    If you need to make changes to your formula, click the Customize button above the report. QuickBase returns you to the Report Builder where you can edit the formula. You can go back and forth like this as many times as you'd like until the formula creates the effect you want. (Getting an error? See Troubleshooting Formulas.)

    When you're done, click the Save button above the report. Or, click Customize to return to the Report Builder screen and click Save.

Note: You may have noticed that in table reports, QuickBase automatically applies shading to every other row. The program does so to help you read across the screen without confusing rows. When you color rows, this shading still shows through.

Formula Examples

You want to...

Formula

Explanation

Highlight your high earners in green.

if ([Salary] > 100000, "#88FF88", "")

If the Salary field contains a value greater than $100,000, then color the row a shade of green (#88FF88), otherwise, don't color it.

Highlight overdue tasks in red.

if ([Due Date]<Today(), "red")

If the value in the Due Date field is less than (e.g. earlier than) today, then color the row red, otherwise, don't color it.

Highlight sales where the actual amount of the sale is less than the amount forecast.

if ([Forecast Amount] > [Closed Amount], "#FFCC99", "")

If the value in the Forecast Amount field is greater than the value in the Closed Amount field, then color the row a shade of orange (#FFCC99), otherwise don't color it.

Color rows based on a combination of priority and status.

If(([priority]="High" and [status]="Open"),"red",
If(([priority]="Low" and [status]="In-Progress"),"green", "yellow"))

If the value in the Priority field is "high" and the value in the Status field is "open," then color the row red. If the value in the Priority field is "low" and the value in the Status field is "In-Progress" then color the row green. If none of these are true, color the row yellow.

Color rows based on the user assigned to complete a task.

If([Assigned To]=ToUser("baker@example.com"), "pink", "")

Take the email address baker@example.com and convert it to the user value connected with that email account (you can use a user name instead of an email address). If the value in the Assigned To field is that user, then color the row pink, otherwise, don't color it.

Tip: Don't forget to enclose the email address or user name in double quotation marks!

Color each row differently, based on the status of each task.

case([Status], "Not Started", "#FFCC99",
"Started", "#99FFFF",
"In Progress", "#FFFFCC",
"On Hold", "#9999CC",
"Completed", "#99FF99", "")

Note: This formula doesn't use the if() function. Because you want to set multiple conditions on one field, it's more efficient to use the case() function. (Read more.)

If the value in the Status field is "Not Started," then color the row a shade of orange (#FFCC99).
If the value in the Status field is "Started," then color the row a shade of blue (#99FFFF).
If the value in the Status field is "In Progress," then color the row a shade of yellow (#FFFFCC).
If the value in the Status field is "On Hold," then color the row a shade of purple (#9999CC).
If the value in the Status field is "Completed," then color the row a shade of green (#99FF99)

If the value in the Status field is none of these, don't color it.

Color each row differently, based on a range of number values in a specific field.

Case(true,
[duration]<=5,"pink",
[duration]<=8,"red",
[duration]<=12,"green",
[duration]<=16,"blue","")

If it's true that the value in the duration field is:

-Less than or equal to 5, then color the row pink.
-Less than or equal to 8, then color the row red.
-Less than or equal to 12, then color the row green.
-Less than or equal to 16, then color the row blue.

Related Topics

 

This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.

 

Return to top   

© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.