Conditional Formats: /Documentation/Archive/24.3

Conditional Formats

Conditional formats change how data is displayed depending on the value of the data. For example, if temperature goes above a certain value, you can show those values in red (or bold or italic, etc.). If the value is below a certain level those could be blue.

Conditional formats are defined as properties of fields using the Field Editor.

Notes:

For dates, strings must be compatible with the Java Class SimpleDateFormat
For decimals, integers, users, strings must be compatible with the Java class DecimalFormat

Specify a Conditional Format

To specify a conditional format, open the field editor, and click to expand the field of interest. Under Create Conditional Format Criteria, click Add Format.

In the popup, identify the condition(s) under which you want the conditional format applied. Specifying a condition is similar to specifying a filter. You need to include a First Condition. If you specify a second one, both will be AND-ed together to determine whether a single conditional format is displayed.

Only the value that is being formatted is available for the checks. That is, you cannot use the value in column A to apply a conditional format to column B.

Next, you can specify Display Options, meaning how the field should be formatted when that condition is met.

Display options are:

Bold
Italic
Strikethrough
Colors: use dropdowns to select text (foreground) and fill (background) colors. Click a block to choose it, or type to enter a hex value or RGB values. You'll see a box of preview text on the right.

Click Apply to close the popup, then Save to save the datastructure. For a dataset, click View Data to see the dataset with your formatting applied.

Multiple Conditional Formats

Multiple conditional formats are supported in a single column. Before applying the format, you can click Add Formatting to add another. Once you have saved an active format, use Edit Formats to reopen the popup and click Add Formatting to specify another conditional format. This additional condition can have a different type of display applied.

Each format you define will be in a panel within the popup and can be edited separately.

If a data cell fulfills multiple conditions, then the first condition satisfied is applied, and conditions lower on the list are ignored.

For example, suppose you have specified two conditional formats on one field:

If the value is 40 degrees or greater, then display in bold text.
If the value is 38 degrees or greater, then display in italic text.

Although the value 40 fulfills both conditions, only the first condition to apply is considered, resulting in bold display. A sorted list of values would be displayed as shown below:

41
40
39
38
37

Specify Conditional Formats as Metadata XML

Conditional formats can be specified (1) as part of a table definition and/or (2) as part of a table's metadata XML. When conditional formats are specified in both places, the metadata XML takes precedence over the table definition.

You can edit conditional formats as metadata XML source. It is important that the <filters> element is placed first within the <conditionalFormat> element. Modifiers to apply to that filter result, such as bold, italics, and colors must follow the <filters> element.

In the metadata editor, click Edit Source. The sample below shows XML that specifies that values greater than 37 in the Temp_C column should be displayed in bold.

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="Physical Exam" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="Temp_C">
        <conditionalFormats>
          <conditionalFormat>
            <filters>
              <filter operator="gt" value="37"/>
            </filters>
            <bold>true</bold>
          </conditionalFormat>
        </conditionalFormats>
      </column>
    </columns>
  </table>
</tables>

Example: Conditional Formats for Human Body Temperature

In the following example, values out of the normal human body temperature range are highlighted with color if too high and shown in italics if too low. In this example, we use the Physical Exam dataset that is included with the importable example study.

In a grid view of the Physical Exam dataset, click Manage.
Click Edit Definition.
Select a field, expand it, and click Add Format under "Create Conditional Format Criteria".

For First Condition, choose "Is Greater Than", enter 37.8.
From the Text Color drop down, choose red text.
This format option is shown above.

Click Add Formatting in the popup before clicking Apply.
For First Condition, choose "Is Less Than", enter 36.1.
Check the box for Italics.

Click Apply, then Save.
Click View Data to return to the data grid.

Now temperature values above 37.8 degrees are in red and those below 36.1 are displayed in italics.

When you hover over a formatted value, a pop up dialog will appear explaining the rule behind the format.

LabKey Support

LabKey Support