Summary Columns

When two tables are related, you can create summary columns. Summary columns are columns in the master table used to represent calculation of specific data from the 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 total items amount will be added to the Invoices table:

When you create a new summary column or edit an existing one, you have to fill in the displayed Summary column form.

Summary Column

General Properties

Name

This property represents a column name. Please note that this field is required.

Description

This property represents a detailed description of information stored in a column. The description is displayed as a tooltip when a user puts mouse over the question-mark icon next to a corresponding field, while adding or editing table records.

Tip: To increase the height of the Description field (add more lines), press Ctrl+Up Arrow on your keyboard. Use Ctrl+Down Arrow to decrease the field height (for the Opera browser use the following key combination: Ctrl+Shift+Up Arrow / Ctrl+Shift+Down Arrow).

Display

In the Display field select necessary properties of column displaying.

Access

Select the Restrict access right by role check box and define specific access level to the column for various user groups if needed.

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 Value Column field select the name of a column in the detailed table. The values of this column will be aggregated in the master table summary column.

In the Function field select a function being used for calculation of the summary column values. The following functions are possible:

This function counts the number of related records. For example, you want to count family members using a sport club card.

This function sums up the related values. For example, total costs of Invoice items.

Using this function you can calculate average sales, wage costs or other values.

This function chooses Minimum value

This function chooses Maximum value

It concatenates related values but only of the Text or Formula-Text type. For example, you can concatenate family members’ names using an insurance policy.

The purpose of the function is to extract the value from the specific record in the detail record set. For example, it is helpful for extraction of the last Exchange Rate value.

This function counts the number of related records with unique values. For example, there is a project and teammembers related to this project. Each teammember works in a certain department, so you can count the number of departments involved into the project.

  • Statistical Metrics

Standard Deviation, Population Standard Deviation, Variance, Population Variance.

Filter

In the Filter field select necessary filter criteria. . It means that only records comprising certain values will be counted.


# of records

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

Function

In the Function field select the # of records function.

Filter

In the Filter field 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, total costs of Invoice items.

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

Function

In the Function field select the function: Total, Average, Minimum or Maximum.

Value Column

In the Value Column field select a column from the related table. Such selected column comprises values that will be used for calculation of summary column values.

Filter

In the Filter field select necessary filter criteria. It means that only records comprising certain values will be counted.


Concatenate

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

Function

In the Function field select the function: Total, Average, Minimum, Maximum or Concatenate.

Value Column

In the Value Column field select a column from the related table. Such selected column comprises values that will be used for calculation of summary column values.

Separator

The Separator option allows to choose a delimiter for the concatenated values.

Filter

In the Filter field select 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

In the Function field select the Index function. It assigns an index to each related record.

Sort by

In the Sort by field specify a column comprising values that will be used for sorting of the related records.

order by

In the order by field choose the Ascending or Descending order of sorting.

Index

In the Index field specify an index of the related record. Only values of the related record with such index can be chosen as a summary column value. To specify the index, select by number or by column option

  • by number

If you choose this option, you should indicate a required number that will be used as an index for choosing a related record. For example the number “1” is specified. It means that only values of the related record with index “1” can be chosen as a summary column value.

  • by column

If you choose this option, you should select a column from the same table where the summary column is created. The values of such selected column will be used as indexes for choosing a related record. Only the column of the Numeric type can be selected. For example the Product table comprises the Client level column. This column includes the following values: 1, 2 or 3. You can select this column in the Index field, so its values will be used as indexes. It means that if Client level column value is “2” only values of the related record with index “2” can be chosen from the Exchange rate related table as a summary column value.

Value Column

In the Value Column field select a column from the related table. Such selected column comprises values that will be used for summary column values extraction.

Filter

In the Filter field select necessary filter criteria. It means that only records comprising certain values will be counted.

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

Say you want to display the current exchange rate and 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 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 Sort By property (sorted by Descending Date). Each record 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 a project and teammembers related to this project. Each teammember works in a certain department. Sometimes two or three persons are invited from a department. If you need to count the number of departments involved into the project and it doesn’t matter how many persons are involved, use the # of unique values function.

Function

In the Function field select the # of unique values function.

Value Column

In the Value Column field select a column from the related table. The system will count only unique values stored in this column.

Filter

In the Filter field select necessary filter criteria. It means that only records comprising certain values will be counted.

Next: System Columns