Summary Columns

When two tables are related, you can create summary columns. Summary columns are columns in a master table used to represent calculation 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 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.

General Properties:

Summary Column General.png

Summary Column General Properties
Name	This property represents a column name. Please note that this field is required.
Notes	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.

Properties defined by the column type:

Summary Column.png

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 Value Column field select a name of a column in the detail 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:

# of records	This function counts the number of related records. For example, you want to count family members using a sport club card.
Total	This function sums up the related values. For example, total costs of Invoice items.
Average	Using this function you can calculate average sales, wage costs or other values.
Minimum	This function chooses Minimum value
Maximum	This function chooses Maximum value
Concatenate	It 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.
Index	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.
# 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, 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.

Help

When the Help text property is filled in, the question mark is displayed near the field name on the View/Edit form. If you point to this question mark, the 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, 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. This 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 Text based columns, Formula-Text based columns or Formula-XHTML columns. For example, you can concatenate family members’ names using an insurance policy.

Concatinate Separator.png

Function	In the Function field select the Concatenate function.
Value Column	In the Value Column field select a column from the related table. This column should comprise values that will be used for concatenation in the summary column.
Separator	The Separator option allows to choose a delimiter for concatenated values.
Filter	In the Filter field select necessary filter criteria. It means that only records comprising certain values will be concatenated.

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 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 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

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. This 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 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 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