Filter and Highlight Rows

On this page

A list offers two filters and one way to color rows:

  • The conditions on the Filter tab of Customize View are applied by the server. They can test fields that the view does not display. In a tree list, the server also returns the ancestors of the rows that match.
  • Column filters, opened from a column heading, run over the rows already loaded and never contact the server. They only narrow what the Filter tab conditions returned.
  • Highlighting rules, on the Conditional Formatting tab of Customize View, color the whole row or one column of the rows that match their conditions.

A change to the Filter tab conditions or to the highlighting rules reloads the rows. Filter conditions, column filters, and highlighting rules are part of the view: saving a view keeps them, and sharing a view shares its coloring. For opening Customize View and saving or sharing a view, see Customize a View.

In the Web App

Build a filter

The Filter tab holds groups of conditions. Conditions inside a group are joined with And, and groups are joined with Or. A group cannot contain another group, and nothing limits the number of groups or conditions.

  1. Open Customize View and select the Filter tab.
  2. Click + Add filter and pick a field. The field list is not limited to the columns of the view. + Add filter is shown while the tab holds no group.
  3. Choose an operator and enter or pick a value. The operators, the value control, and the default operator depend on the field; see Operators and values by field type.
  4. To add a condition to the same group, click And inside the group. To start a new group, click Or under the groups.
  5. To copy or remove a group, click its Clone or Delete. To remove one condition, click its X button.
  6. In the footer of the pane, click Apply, Save, or Save As. Which of these buttons the footer shows depends on the list.

On a shared view, starting a new Or group and cloning or deleting a group require the local view management right or the shared-views administration right, unless the view had no filter yet. Without those rights, the conditions already saved in the view cannot be changed, while the conditions you add in the current session stay editable.

For the operators and value controls of each field type, comparing a field with another field, and free SQL conditions, see Operators and values by field type, Compare a field with another field, and Advanced Expression.

Use column filters

A column heading shows its funnel button when you point to the heading, and keeps showing it while the column is filtered. The funnel of a filtered column is filled in blue. While the column is unfiltered, the button's title is Filter. A grouping chip in the group panel carries the same button.

  1. Click the funnel of a column heading.
  2. Check the values to keep, and click Apply.
  3. To remove the column's filter, uncheck every value and the Blanks, True, or False choice, and click Apply.

For how the value list is built and searched, see The column filter's value list.

A row must match every filtered column. Within one column, the checked values are alternatives. Setting a column's filter replaces that column's earlier choice and keeps the filters of the other columns.

When a column is filtered, pointing to its funnel lists the active filter of every filtered column, joined by "and": each shows the column, = (or != for an exclusion), and the values, and a single blank, non-blank, true, or false choice shows as its operator. The Clear all button there removes all column filters at once and leaves the Filter tab conditions untouched.

From a row's context menu, the Filter submenu offers Filter by Selected Field and Filter Excluding Selected Field, which set the column filter from the value you clicked. On a date and time column, the submenu is disabled with the tooltip "Cannot filter by date values; use the column header to filter". The value list of a column filtered with Filter Excluding Selected Field shows no value checked, so clicking Apply there removes the exclusion.

Column filters are kept with the view when you save the view.

Add a highlighting rule

  1. In Customize View, select the Conditional Formatting tab and click New. The new rule is named "Rule <n>". It is enabled, applies to the view's link column, and has no conditions, no color, the full-cell shape, and No Style on.
  2. In the rule, set Apply to: Row colors the whole row, and Column colors one column. Choosing Row clears the column and sets the full-cell shape; choosing Column again selects the view's link column and keeps the full-cell shape.
  3. Under Format Conditions, build the conditions with the same group-and-condition editor as the Filter tab. A rule with no conditions matches every row.
  4. Set a text color, a background color, or both. Each color picker offers a palette of 16 colors and a No color button.
  5. Turn on any combination of Bold, Italic, Underlined, and Strikethrough, or turn on No Style. Turning on any of the four clears No Style, and turning on No Style clears all four. The colors are kept either way.
  6. For a column rule, choose the shape: full cell, oval badge, or left border stripe. The shape buttons stay disabled, with the tooltip "No background color selected", until a background color is set. A row rule has no shape choice.
  7. Optionally, pick one of the 28 combinations in the Presets menu: 18 full-cell, 5 border, and 5 oval. A preset overwrites the rule's shape and whole style. On a row rule, the border and oval presets are disabled.
  8. In the footer of the pane, click Apply, Save, or Save As.

When you choose Row for a rule whose shape and style exactly match one of the border or oval presets, the style is also reset to no background, no text color, and No Style. Otherwise, the colors and text style are kept.

In the rule list, drag rules to reorder them, and use each rule's switch to turn it on or off. Pointing to a rule shows Duplicate, which adds a copy with the same name, and Delete. Each rule's preview strip in the list is drawn in its own color and style before you save the view.

For how several rules combine on one cell, and what happens to a rule when its field leaves the view, see How several rules combine and When fields leave the view.

Undo unsaved changes

The circular-arrow Reset button in the header of Customize View, beside Close, takes the pane and the list back to the view as it was last saved. It is the same button for all four tabs and can be clicked at any time. Reset discards the Filter tab conditions, columns, grouping, highlighting rules, hidden columns, and sharing choice you changed, whether or not you applied them, and returns the column filters to their saved state. The gear button then no longer shows the view as modified. When the list has no saved view, Reset only restores the pane to the view last applied.

Reference

This section gives the details of filters and highlighting rules in the Web App.

Operators and values by field type

Field type Value control Operators Default operator
Lookup, combo, reference, or array (multiselect) A list of values: single-select for a lookup field, multi-select for the others equals, does not equal, is blank, is not blank, contains, does not contain, like, not like; has one of, does not have one of, has all of, has none of (array fields only); the current-user pair that a text field of the same kind gets equals
Tags A multi-select list of tags equals, does not equal, has one of, does not have one of, has all of, has none of, is blank, is not blank equals
Flag A single flag; Clear is not offered equals, does not equal, is blank, is not blank equals
Picture None is blank, is not blank is blank for an image field; otherwise equals, which the operator list does not offer
Boolean None Is true, Is false Is true
Number (integer, float, currency, duration) A value box >, >=, <, <= (one value); equals, does not equal (several values); is blank, is not blank equals
Text A value box equals, does not equal, contains, does not contain, like, not like (each with several values); is blank, is not blank; plus a current-user pair equals
Date, date and time Depends on the operator Is, Is equal to, Is before, Is after, Is between, Is more than, Is less than, is blank, is not blank Is

The current-user pair is "is current user" and "is not current user" on a person field. Otherwise, a field whose name ends in Organization gets the current-user-organization pair, and a field whose name ends in Location gets the current-user-location pair. A field gets at most one pair.

Several values under one operator make one condition. For contains and like, and for equals on a field other than an array (multiselect) or Tags field, the values are alternatives: "Assignee equals A, B, or C" is one condition that matches any of the three. For does not contain and not like, and for does not equal on a field other than an array or Tags field, a row must match none of the values.

On an array (multiselect) or Tags field, each value is checked against the field's list of values:

  • equals and does not equal compare the row's whole list with exactly the chosen set.
  • has one of matches a row that has any of the values, and has all of a row that has every value.
  • does not have one of matches a row that lacks at least one of the values, and has none of a row that has none of them.

On such a field, a has one of, does not have one of, has all of, or has none of condition whose only value is empty matches the rows where the field is blank.

The value controls work as follows:

  • Multi-select lists. The list shows the hint "Hold Ctrl key to select multiple values". Each value you pick is shown as a chip and added to the condition. The values are fetched while the list is open; when more values match than were sent, the list says "only the first 100 are displayed. Start typing to narrow down the list." The Tags list matches tags by the first letters you type and shows the same hint.
  • Several values in a value box. Typing in the last value row, or switching it to compare with another field, shows + for another row; each earlier row carries an Or delete control.
  • Number values. A new number condition starts with the value 0. Integer and duration values are whole numbers. Float and currency values are decimals with the precision of the regional settings, or 2 decimal places when that is not set.
  • like and not like. A value with no % or _ wildcard matches anywhere in the text. A value with a wildcard is used as typed, so upgrade% matches only text that starts with "upgrade".

When you pick another operator, the values you entered are kept, except in three cases: the new operator takes a different number of values, either operator is Is between, or a condition on a lookup, combo, reference, or array field changes from like or not like to any other operator.

For a date or date and time field, each operator has its own control:

  • Is shows a searchable list of 28 named periods with descriptions, and starts at Today: Today, Yesterday, Tomorrow, In the future, In the past; this, so far, last, and next for week, month, quarter, and year; and Last 7, 14, 30, 60, 90, 180, and 365 days.
  • Is more than and Is less than take a positive whole number of days and a direction, days ago or days from now, and start at 1 day ago. A value that is not a whole number, or is zero or less, shows an error beside the box.
  • Is between takes two calendar dates. When both are typed dates and the range is reversed, the control flags it but does not prevent it.
  • Is equal to, Is before, and Is after show one date picker.

Compare a field with another field

The Compare with another field toggle makes a condition compare its field with another field instead of a typed value. The toggle sits beside each text or number value row, the date of Is equal to, Is before, and Is after, each date of Is between, and the value list of a lookup, combo, reference, or array field. The Is period list and Is more than and Is less than have no toggle. The flag, Tags, and rating fields have no toggle and are not offered in the list of fields to compare with.

When you switch the toggle on, the value box becomes a searchable list, sorted by header, of the other fields that the field list offers with a compatible type. Integer, float, currency, and duration match one another, date matches date and time, and any other type matches only itself. On a Boolean field, the toggle replaces Is true and Is false with equals and does not equal. A value list in this mode holds one field.

The chosen field is compared only under equals, does not equal, >, >=, <, and <=, and in the dates of Is equal to, Is before, Is after, and Is between. Under any other operator, and under equals and does not equal on an array (multiselect) field, the condition uses the typed value instead. While the toggle is on and no field is picked yet, an equals or does not equal condition counts the value as blank, and a date condition matches no row.

Advanced Expression

The field list also has a pinned Advanced Expression entry. It adds a condition with no operator that holds free SQL text, which the condition takes shortly after you stop typing. In the text, <Current User>, <Current User Organization>, and <Current User Location> are replaced with the current user's values. An empty expression does not restrict the rows.

If a condition of a view's filter or of one of its highlighting rules holds an operator the server does not recognize, the server stops with the message "Unable to display the data because the view definition is not valid. A filter operator appears to be missing. Please try reconfiguring the view using the gear icon."

The column filter's value list

The value list can be widened from its left or right edge and lengthened from its bottom edge, but it cannot be made smaller than its opening size or moved. It starts with True and False for a Boolean field, or with a Blanks choice for any other field, and the values follow:

  • A date or date and time field lists the 28 named periods of the Is operator.
  • Boolean, array, and picture fields list no values.
  • Any other field lists distinct values from the loaded rows, with Tags split into single tags, followed by any value of the column's current filter that is not among them. The list keeps the first 100 entries and sorts them, so when the rows already gave 100 values, a filtered value is left out. When the list holds exactly 100 entries, it shows a warning.
  • A search box appears when the field is not a date, date and time, array, Boolean, or flag field and more than 8 values were offered. It takes up to 250 characters and narrows the list as you type. Shortly after you stop typing, the list is collected again from the loaded rows, up to 100 values that match.

The checked values take effect only when you click Apply. The value list has no item that checks every value at once. The Clear all button of the value list unchecks its values and its Blanks, True, or False choice, and empties its search box. It does not change the column filter, and the value list stays open. Clicking outside the value list closes it and drops what you did not apply.

How several rules combine

Rules are ordered, and switched on or off, in the rule list; see Add a highlighting rule.

For each cell, the list applies, in list order, every enabled row rule that matches and every enabled rule for that cell's column that matches; a rule that is turned off is skipped:

  • A later rule's text color always replaces the earlier one, and a later rule with no text color resets the text to the default color.
  • A later rule's background color replaces the earlier one only when the later rule sets one.
  • A later rule's bold, underline, and strikethrough replace the earlier ones, unless the later rule is set to No Style, which keeps the earlier bold, underline, and strikethrough. A No Style rule does not keep an earlier rule's italic.
  • A border or oval column rule draws its stripe or badge in the combined background color. The cell behind it takes the background of the last matching row rule, or none when that rule sets none.

When fields leave the view

When a saved view is opened, a column rule is removed if its column is no longer among the view's columns with the same type; row rules are kept. Within each rule, a condition is removed if its field is no longer offered by the class with a compatible type. Text, memo, and HTML fields count as one type, and Advanced Expression conditions are kept. Groups left empty are removed too. The view's own filter is cleaned the same way.

  • Customize a View - choose columns, sort and group, and save or share the view that these filters and rules belong to.