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:
In the Additional Options section
of the Report Builder, select the Custom Options
radio button.
QuickBase displays a set of additional controls.
Turn on the Row Colorization check box.
In the Row Colorization Formula box that appears, enter a formula.
You use the formula to tell QuickBase which rows to color. Most of the time you'll use the If() function to set your condition.
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 and formula examples in the table below.
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 this Report link on the upper right of the screen. 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 link above the report. Or, click Customize this Report 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.
| 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 screen 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 screen 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 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. |