How to Add Aggregate Columns to a Schema View

This guide shows you how to add a column that summarises each Record's related Records, such as the number of items in each release or the total points of its items, to a Schema view or to the Schema's Records table.

Prerequisites

• To save a view, you must be an admin of the Space. Anyone can apply columns to their own table without saving them.
• The Schema has a relationship to the Schema you want to summarise. Only relationships to Schemas you can read are offered.

Add an Aggregate Column

1. Open the Schema's Records and click the view button (the filter icon) next to the search box. Admins can also create or edit a view under Views in the Schema's settings.
2. In Select, click Add aggregate column.
3. Select the Relationship, by the name its column has in this table, for example Items on a Release.

4. Select the Function:

   • Count — counts the related Records.
   • Sum and Avg — add up or average a number or duration property.
   • Min and Max — return the smallest or largest value of a number, duration, or date property.
5. Select the Property of the related Records that the function reads. The list shows only the properties the function can read. Sum, Avg, Min, and Max need a property. Count needs none: leave it on (all records) to count every related Record, or select a property to count only the Records where it is set.
6. Optional: change the Label. It defaults to the relationship, function, and property, for example Items count or Items sum of Points, and must be unique among the view's aggregate columns.
7. Optional: under Only include related records where, add conditions on the related Records, for example Status equals Released.
8. Click Add column. The column is added after the other selected columns.
9. Drag the column to where you want it in the list.
10. Click Apply to show the columns, or Save view to save them with the view.

Result: The table shows the aggregate column next to the Record's fields:

• The value summarises the related Records the viewer can read that match the column's filter. Removed Records and links are not included.
• A count is a number. A sum or average has the type of its property, a number or a duration. A minimum or maximum is a number, duration, or date, like its property.
• With no related Records to summarise, a count is 0 and a sum is 0 (a zero duration for a duration property). An average, minimum, or maximum has no value.
• No access means the viewer cannot read the related Schema at all, so the table cannot summarise its Records. It is not the same as 0.
• A dash means the values could not be computed, for example because the relationship or the property has been removed. Hover over it to see why. The rest of the table still loads.

Sort by an Aggregate Column

1. Click the aggregate column's header to sort by it, or open its sort menu (the filter icon in the header) and choose Lowest or Highest.
2. Optional: in the view editor, add the column under Sort, with or before other sort keys, and save the view.

Result: The table's Records are ordered by the column across every page, for example the Releases with the most open tasks first. Records with equal values are ordered by the next sort key.

Change or Remove an Aggregate Column

1. In the view editor, click the column's edit button in the Select list.
2. Change its settings. If its property has been removed from the related Schema, select another property.
3. Click Update column.

To remove the column, clear its checkbox in the Select list.

Result: The table shows the updated columns after you click Apply or Save view.

You can also reorder or remove aggregate columns with the column button (Select fields) above the table, but you add and edit them in the view editor. In that column list, an aggregate column shows its function (for example Σ Count), and a filtered tag when it has a filter. When you clear an aggregate column there, it moves to Removed aggregate columns at the bottom of the list. Select it again to add it back with its settings. It stays there while you stay on the page, until you select another view.

Limitations

• You can sort the table by an aggregate column, but not filter by one. Records whose column has no value (an average, minimum, or maximum over nothing, or No access) are sorted last, in either direction. The sort is not applied while you search the table, and a message above the table says so. Sorting by an aggregate column on a Schema with many Records takes longer, because the column is computed for every Record that matches the filter, not only the page.
• A view can have at most 10 aggregate columns, over at most 3 different relationships (each side of a relationship counts separately). An aggregate column's filter can cross at most 3 nested relationships. The table shows at most 100 Records per page when it has aggregate columns.
• Values are computed when the page loads. They do not update live when related Records change. Reload the page, or change the page, sort, or filter, to refresh them.
• Aggregate columns are shown on the Schema's Records page. Tables of related Records inside a Record ignore them.
• Space exports include each view's columns, including aggregate columns, and a Space import links them to the imported relationships.
• An export includes only relationships whose Schemas are both exported. If you export only some Schemas, columns that use a relationship outside the export are left out of the view: relationship columns, and aggregate columns whose relationship or filter uses such a relationship. Columns over removed relationships are left out the same way. A view whose columns are all left out is imported without columns. If an export file still contains such columns, for example because it was edited, the import skips them instead of failing.

For how aggregates are computed and what a viewer can see, see The Record Query Shape.