Summary Columns

When two tables are related, you can create summary columns. Summary columns are columns in a master table used to represent calculations of specific data from a detail table. For example, the "Invoices" table displays the total amount for all invoice items stored in the "Items" table.

As a result, the Total summary column, calculating the total items amount, will be added to the "Invoices" table.

Summary Column Example

General Properties

Summary Column General

If a column contains data that doesn’t appear or behave as you desire, you can edit the column’s General Properties.

Aggregate Options

Summary column aggregate options

Table
This field comprises the name of the related table. It works as a link referring to the table properties.
Relation
This field keeps the relation name displayed as a link referring to the relation.
Aggregate
In the "Function" column, select how your summary column should be calculated. Depending on the function selected, the respective additional fields will show up. The detailed description for each function can be found below in this document:
Filter
Select the necessary filter criteria. It means that only records comprising certain values will be counted.

If you are using a custom formula, the Related column prefix enables you to reference columns on the other side of the relationship in filter conditions for summary columns and relation-specific record pickers. Refer to this blog article for usage examples.

Help
When the Help text property is filled in, a question mark is displayed near the field name on the View/Edit form. If you point to this question mark, a pop-up with a tip or a help text message will be shown.

# of records

Number of records function

This function allows calculating the count of related records. For example, if you want to count family members using a sports club card.

Function
Select the "# of records" function.
Filter
Select necessary filter criteria. It means that only records comprising certain values will be counted. For example, if you want to count non-blanks in a certain column, you can add "Column is not <blank>" to the filter criteria.

Total, Average, Minimum, Maximum

Total function

The Total function sums up the related values of the Numeric or Formula-Numeric type. For example, the total cost of Invoice items.

The Average, Minimum, or Maximum function can be used for the calculation of the minimum, maximum, or average price, percentage, or volume. Also, the Minimum or Maximum function allows choosing the minimal or maximum date. For example, if a project includes many stages, you can choose the first or last date of a project.

Function
Select the function: "Total", "Average", "Minimum", or "Maximum".
Value Column
Select a column from the related table. This selected column comprises values that will be used for the calculation of summary column values.
Filter
Select the necessary filter criteria. It means that only records comprising certain values will be counted.

Concatenate

Concatenate function

The Concatenate function concatenates related values but only of Text-based columns, Formula-Text-based columns, or Formula-XHTML columns. For example, you can concatenate family members’ names using an insurance policy.

Function
Select the "Concatenate" function.
Value Column
Select a column from the related table. This selected column comprises values that will be used for the calculation of summary column values.
Separator
This option allows you to choose a delimiter for the concatenated values. The following options are available:
Filter
Select the necessary filter criteria. It means that only records comprising certain values will be counted.

Index

Index function

The purpose of the Index function is to extract the value from the specific record in the detail record set.

Function
Select the "Index" function. It assigns an index to each related record.
Sort by
Specify a column comprising values that will be used for sorting of related records.
Order by
Choose the "Ascending" or "Descending" order of sorting.
Index
Specify an index of a related record you need to choose. Only values of this related record with such an index can be chosen as a summary column value. To specify the index, select "by number" or "by column" option.
Value Column
Select a column from the related table. This selected column comprises values that will be used for the calculation of summary column values.
Filter
Select the necessary filter criteria. It means that only records comprising certain values will be counted.

Example: Exchange rate

Index usage example

Let’s consider an example of Exchange Rate extraction. Say you created the Exchange Rates table.

Say you want to display the current exchange rate and to use it for USD Price calculation in the Product table. For these matters, the Index Summary column will be helpful.

The Products table should be related to the Exchange Rate table by Many-to-Many relations, so you can add a Summary Index column. Let’s name it the Current Rate (GBP into USD). This column will help you to convert the GBP Price into the USD Price.

Price USD

How it works: The Exchange Rates records are filtered and then sorted in the order specified in the "Sort By" property (sorted by Descending Date). Each record is assigned an index number starting from 1, so the Exchange Rate of the Last Date has the index number 1. The Index function picks up the Rate (GBP into USD) value specified in the "Value Column" property from the record with an index as specified in the "Index" property, so the Exchange Rate of the Last Date is picked up and displayed in the Current Rate (GBP into USD) summary column of the Products table.

# of unique values

# of unique values function

This function counts the number of related records with unique values. For example, there is an order and product items related to it. Each product item has a category. Several products of the same category can be added to one order. If you need to count the number of different product categories within the order regardless of the number of the products under each category, use the # of unique values function.

Function
Select the function "# of unique values".
Value Column
Select a column from the related table. This selected column comprises values that will be used for the calculation of summary column values.
Filter
Select the necessary filter criteria. It means that only records comprising certain values will be counted.