87Creating, Viewing and Editing a Calculation Formula

To create, view, or edit a calculation formula:

1 Open the Object Designer (SHIFT+F12) and open the table in question.

2 Select the field for which you want to create, view, or edit the calculation formula.

3 Click View, Properties (SHIFT+F4) to open the Properties window.

4 Find the CalcFormula property:

5 You can either enter the calculation formula directly or click the assist-edit button.

When you click the assist-edit button, C/SIDE displays:

6 Click the drop-down button to select the appropriate calculation method. Click the Reverse Sign option to reverse the sign of the result (only for Sum and Average).

Use the lookup buttons to select the table and column (field) from which to get the information. If necessary, you can add a table filter to specify a limited set of

<TableFilters> A list of filters to be used in the computation of the FlowField.

<TableFilter> A table filter can be one of the following: a constant expression, a filter expression, a value from ordinary fields or a FlowFilter field (FlowFilter fields are discussed in the next section). Notice that a key for the other table must exist and include the fields used in the filters.

<DstFieldNo> Specifies the destination field number.

<SrcFieldNo> Specifies the source field number.

<Filter> A filter expression such as 10|20..30.

Symbol Explanation

Enter the calculation formula here

Chapter 6. Table Fundamentals

88

records. Click the assist-edit button to the right of the Table Filter field and C/SIDE opens the Table Filter window:

7 On each line in this window, you can define a field filter. For each field filter, specify a field, a type, and a value. You can also set the OnlyMaxLimit and the ValueIsFilter options. The following example illustrates where the information in this window comes from.

E^XAMPLE

The Balance at Date field in the G/L Account table is a decimal type FlowField. This field is calculated from values in the Amount column in the G/L Entry table.

Each line defines a field filter. Notice that there are implicit logical ANDs between the lines.

6.6 Special Table Fields

89 Some of the fields in the G/L Account table are FlowFilter fields. By entering filter expressions into these fields, the user can affect the calculation of FlowFields (such as Balance at Date) at run time. The user can enter filter values in a FlowFilter form:

If the user enters a date filter expression in the Date Filter field, it is transferred via the table filter and used in the Date column in the G/L Entry table.

The Field column in the Table Filter window contains references to fields (columns) in the G/L Entry table.

The Value column in the Table Filter window contains references to fields (columns) in the G/L Account table.

The Amount field contains the information to be summed. This field is defined as a SumIndexField in the key for the G/L Entry Table.

Chapter 6. Table Fundamentals

90

You can use the OnlyMaxLimit option to remove the lower limit from a range defined by a filter expression. For example, if the filter expression is defined as a range x..y, setting the

OnlyMaxLimit option transforms the expression into ..y.

The ValueIsFilter option determines how the system interprets the contents of the field referred to in the Value column in the table filter window. For example, if the field contains the value 1000..2000, setting the ValueIsFilter option will cause this value to be interpreted as a filter rather than as a specific value.

Using FlowFilter Fields in the Calculation Formula

Users may want to limit calculations so that they include only those values in a column that have some specific properties. For example, the user may want to sum only the amounts of customer entries that are entered in April. This is possible if the application has been designed using FlowFilter fields in connection with the FlowFields.

.

The figure illustrates the relations between various types of database fields and the calculation formula. The filters defined in the calculation formula can consist of constants, values from ordinary fields and of filters given as parameters in FlowFilter fields. In FlowFilter fields, users can enter a filter value (via the user interface in a C/SIDE application) that will affect the calculation of a FlowField.

Table A

FlowFields FlowFilter fields Ordinary fields

Calculation Formula

Table B

Table C

Table D

Path for information used in the calculation formula Path for data used in computation of FlowFields

Constants

6.7 Dividing the Database into Companies

91

6.7 D

D

C

The DBMS can access only one logical database at a time, but this database can be divided into one or more companies. A company is a "sub-database," and its primary use is to separate and group data in the same database. Fields and tables are identified by a number, but companies are identified by a name. A company "bundles"

one or more data tables together into a logical superstructure that is identified by the company name. Other than the shared company name, the different tables within a company have nothing in common.

Opening a company is your first step after opening the database or connecting to a server. How to do this is described in the Installation and System Management manual for the server that you are using

Consider a database with four tables as shown in this figure:

The four table descriptions on the left apply to each of the data tables, which are logically sorted into three companies. The records in the tables G/L Account, Customer and Vendor, all have the same structure and the same field definitions, even though they belong logically in three different companies. Only the data stored in the fields differ. As the information in a Table Description can be used by tables from more than one company, no redundant information will be stored. This minimizes the size of the database.

Even though you have selected a specific company, you can still access data in any table in any other company. Use the C/AL function <Record>.CHANGECOMPANY to explicitly define which other company you want to access.

More than one application can access the same company and the same table(s) at the same time. How the DBMS controls these multiple accesses is described in the section "What is Table Locking?" on page 530.

Data

Chapter 6. Table Fundamentals

92

Chapter 7