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.
General Properties
If a column contains data that doesn’t appear or behave as you desire, you can edit the column’s General Properties.
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.
- 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
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
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
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:
- Comma and space;
- Space;
- Semicolon;
- Dash;
- Ampersand;
- Pipe;
- New line;
- Tab.
- Filter
- Select the necessary filter criteria. It means that only records comprising certain values will be counted.
Index
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
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.
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
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.