Skip to main content

How to use color-coding in reports

 

Color-coding is available in three contexts. You can add it to:

  • The rows in a table report
  • The stripe along the edge of a card in a Kanban report
  • The background of an event in a calendar

You can set colors based on any data in the table. For example, you could color-code tasks based on the value in the Priority field of your Tasks table with high-priority tasks red, medium priority tasks yellow, and low priority tasks white.

You can switch between three color-coding methods available in the report builder, using the default color-coding, selecting from a color palette, and writing a formula.

Selecting colors

Color-coding by selecting colors is available for the following field types:

  • Text, including Multiple Choice and Multi-select
  • Email
  • Components of an Address field, like street or state
  • Formula text
  • Formula email
  • Checkbox and Formula - Checkbox

You can assign colors to the choices in the field’s properties of Text - Multiple Choice or Multi-select fields, along with values currently present in that field in your records. The first 200 values are displayed. If you want to assign colors to other values, such as values not yet present in your records, switch to writing a formula.

Note: If a value in your records is invalid, the value will appear in red text. For example, the field’s properties might say the list is High, Medium, and Low, while some records have an obsolete choice called Neutral.

The pop-up color palette offers 20 colors plus an input box. You can enter your own colors in the box, in these formats:

  • Any of the 140 HTML color names, like “orange”
  • HTML hexadecimal format, like “#3399CC”
  • HTML RGB format, like “rgb(0,128,255)”

Any color you enter in the input box is translated and saved in hexadecimal format.

color picker for color-coding reports

To select colors:

    1. Start creating or customizing a table report, Kanban report, or calendar report.

    2. In the Color-Coding section of the report builder, select Assign to choices.

    3. Select the field you want to color-code. Quickbase automatically creates up to 10 unique color assignments.

    4. You can assign colors for any field values without a color, up to a maximum of 10 choices.

    5. Customize color assignments by

      • Choosing a different color from the color palette
      • Selecting a different field value from the drop down
      • Removing the color by selecting the Remove icon
      • Adding a color by selecting a field value, then selecting a color

assigning color to a field value in the report builder

  1. Select Display to check your work or select Save.

Writing a formula

A formula can use conditional logic based on multiple fields. For example, records created today could be assigned one color, while older records could be assigned other colors according to who created them.

A formula can also use variables for colors. For example, the formula might refer to a separate table where each record represents one color.

A formula can be copied and then pasted into the settings for other reports or shared with other Quickbase users.

To write a formula:

  1. Start creating, or customizing, a table report, Kanban report, or calendar report.
  2. In the Color-Coding section of the report builder, select Write a formula.
  3. In the formula editor, enter a formula that defines color assignments.
  4. The formula must tell Quickbase the conditions needed to assign a color. In most cases, formulas use IF or CASE statements to define the conditions.
  5. The formula must return the colors as text, either as an HTML color name like “red,” or an HTML hexadecimal color number like “#FF3333”.
  6. Select Display to check your work or select Save.

Notes:

  • If a record matches more than one condition in your formula, the condition that is listed first in the formula determines the color.

  • You can assign a color to a blank value, like “Use blue when there’s no priority,” by writing a color-coding formula.

Tip For an example of a formula, begin by selecting colors and then switch to writing a formula. The formula editor shows the visual assignments as a formula that you can customize.

Removing color-coding

  1. Customize the report you want to remove color-coding from.

  2. In the Color-Coding section of the report builder, select None.

  3. Select Save.

Setting default color-coding

You can also specify default color-coding for field values for all table, Kanban, and calendar reports in your table. To set default color-coding, go to Table Settings, select Reports and Charts, then choose Default report settings from the Reporting defaults menu in the top right-hand side of the page. More about default color-coding.

Formula examples

You want to

Formula

Explanation

Highlight records modified today

This formula requires a Date field labeled "Start Date."

If(

[Start Date]=Today(),"plum")

If a record was modified today, color-code it plum.

Color-code records based on a combination of fields

This formula requires two Text Multi-choice fields labeled “Priority” and “Status.” On their properties pages, enter the following choices:

  • Priority: High, Medium, Low
  • Status: Not Started, Open, In Progress, Closed

If(

([Status]="Open" or [Status]="In Progress"),

If(

[Priority]="High", "red", [Priority]="Medium", "orange", [Priority]="Low", "yellow"), "whitesmoke")

If the value in the Status field is either “Open” or “In Progress,” color-code the records by Priority as follows:

"High": red

“Medium”: orange

“Low”: yellow

All other records are color-coded pale gray.

Color-code records based on a user field

This formula requires a user field labeled “Assigned To” to your table and a built-in “Last Modified By” field that still has that label.

Case(

[Assigned To],

User(), "red",

ToUser("baker@example.com"), "orange",

[Last Modified By], "yellow",

""))

This formula uses a Case statement, which is more efficient here than an If statement.

Evaluate the Assigned To field in each record. If it’s the current user, color the record red.

Otherwise, if it’s the user whose email address is “baker@example.com”, color the record orange.

Otherwise, if it’s the same user as in the Last Modified By field, color the record yellow.

Color-code records based on a Status field.

This formula requires a Text Multi-choice field labeled “Status” with these choices: Not Started, Open, In Progress, Closed

Case(

[Status], "Not Started", "#FFCC99",

"Open", "#E6FF99",

"In Progress", "#99E6FF",

"Closed", "#B399FF", "")

This formula uses a Case statement, which is more efficient here than an If statement.

Evaluate the Status field in each record. If it’s “Not Started,” use orange.

Otherwise, if it’s “Open” use green.

Otherwise, if it’s “In Progress” use blue.

Otherwise, if it’s “Closed” use purple.

Color-code records based on ranges of numbers

This formula requires that the built-in “Record ID#” field still has that label.

Case(true,

[Record ID#]<5,"gold",

[Record ID#]<10,"goldenrod",

[Record ID#]<15,"peru", "")

Evaluate the Record ID# field.

Less than 5: gold

Less than 10: dark gold

Less than 15: brown

Related topics:

Related articles

Top