Conditional counts, lookups, and rollups

Lookup, rollup, and count fields in a table use linked record fields to pull in values from one table to another. By default, these fields will pull in all the linked records from the other table, but you can choose to apply conditions to filter on these fields to limit what is included. This is ideal for when you only want to show a subset of related records such as showing only pending tasks for a project or only employees with birthdays this month.

How it works

Applying conditions to an existing lookup, rollup, or count field means that you already have two tables connected by a linked record field. If you do not already have one of these fields created, see the following to first connect the tables and then to create a field that pulls in or calculates additional values from one table to another.

• Connecting tables with linked records
• Fields that require linked record fields (lookup, count, and rollup)

Once you have a lookup, count, or rollup in your table, you can configure it to only show a subset of related information. For example, a “Bugs & Issues” table may be linked to a “Features” table to show all the bugs related to each feature. If you wanted to only see the number of high priority bugs for a feature, you could configure a count field to be filtered to just the “Critical” and “High” priority bugs.

To configure, check the “Only include records which match certain conditions” toggle.

The conditions you can apply here work the same asview filtering where you select a field , an operator , and a comparison value . In our example, you could set the filter to only count bugs with priorities of either “Critical” and “High” from the other table (Bugs & Issues).

The number of high priority bugs here can help determine which features need the most attention.

You can also add multiple conditions for each rollup, lookup, and count field by creating a conditional group. In the example below, a rollup includes a condition to calculate the average days old for all bugs with critical priority and a status of being blocked.

To learn more about creating more complex conditions, and using conditional groups, we recommend seeing this support article.

Invalid conditions

When a rollup, lookup, or count field has an invalid condition, a notification will appear in the field configuration with an error symbol (⚠).

Invalid conditions may be caused by the following:

• A field referenced in the condition was deleted.
• A field referenced in the condition was changed to another field type that does not support the operator and comparison value of the original field type. For example, if a date field was changed to a text field, a condition that checks for all dates within the next month would become invalid.

If the field has an invalid condition while you are in the field configuration dialog, you will not be able to add any new conditions until the issue is resolved and you will not be able to save the configuration.

If the field becomes invalid after you created it (e.g., a referenced field was deleted), all the cell values for that field will appear blank.