A computed field is a “virtual” field that uses data derived from a calculation or the result of an Analyzer command. It does not contain physical data. Computed fields allow you to perform calculations based on information in the data file, without altering or changing the original data in any way.
Computed fields consist of at least a name and a default value. Once defined, you can refer to computed fields by name as if they were physical fields. Field names are limited to 127 characters and must start with an alphabetic character. Spaces are replaced with the underscore character.
The value of a computed field is defined by an expression or a specified constant. Computed fields defined by numeric or date expressions can be formatted in the same way as numeric and date data fields. Computed fields can also contain true/false logical values (T or F) or character constants that are enclosed in quotation marks. For information about expressions, see Building Expressions.
There are three main uses for computed fields:
| • | Perform mathematical computations |
| • | Create true/false logical tests |
| • | Word substitutions |
Computed fields can also have multiple values based on conditions. For more information, see Multi-Value Computed Fields.
In its simplest form, a computed field is a field that has a single value defined by a named algebraic expression. For example, you can define a Value field that does not usually exist in an inventory file. This can be calculated as the quantity on hand (QtyOH) multiplied by the unit cost (UnCst).
To add a computed field to a table layout, first display the Edit Table Layout tab, then select Edit from the menu and choose Table Layout.
Analyzer displays the [Edit Fields/Expressions] tab of the Edit Table Layout tab.
To define computed fields, display the Computed Field Definition screen, then click [Add a New Expression] to display the Computed Field Definition screen.
To define a computed field:
| 1. | Enter the name of the computed field in the Name text box. |
| 2. | Specify the default value of the computed field. Click the Fx button to display the Expression Builder or enter the expression in the Default Value text box. |
| • | In this example, the computed field Stock_Value is defined by the expression QtyOH * UnCst in the Default Value text box. |
| 3. | Click [Accept Entry] or press Enter to add the computed field to the table layout. Click [Clear Entry] or press ESC to return to the Fields/Expressions list screen. |
Once the computed field is defined, you can add it to a View or printed report or use it in any command that uses numeric fields.
Multi-value computed fields are computed fields whose values depend on a set of conditions.
Multi-value computed field definitions have two components: a condition statement and a value statement. The condition statement must be a logical expression. The value statement can be computed using an expression or it can be a constant.
Analyzer evaluates conditions in the order in which they appear in the lower part of the Computed Field Definition screen. The value associated with the first true condition becomes the value of the field. If all conditions are false, the field is given the default value. You must specify a default value.
For example, you can define a computed field named Error_Test to display the message “Error!” whenever a field has a negative value. In all other instances, the field would display the message “OK”.
To define a multi-value computed field, follow these steps:
| 1. | Select Edit from the menu and choose Table Layout. In the [Edit Fields/Expressions] tab Analyzer displays the Fields List screen. |
| 2. | Click [Add a New Expression] to display the Computed Field Definition screen. |
| 3. | Enter the name of the computed field in the Name text box. |
| 4. | Specify the default value. Click Fx to display the Expression Builder or enter the expression in the text box. |
| • | In this example, the computed field Error_Test is defined by the value "OK" in the Default Value text box. |
| 5. | Specify the conditional values. Click [Insert a Condition] to display the Add a Condition and Value dialog. |
| 6. | Click [Condition] to display the Expression Builder or enter the expression in the text box. Then click [Value] to display the Expression Builder or enter the expression in the text box. |
| 7. | Click [OK] to accept the condition and display it at the bottom of the Computed Field Definition screen. You can use the buttons beside the condition box to edit and maintain conditions. |
| 8. | When you have finished defining conditions, click [Accept Entry] or press Enter to add the computed field to the table layout. |
| 9. | Click [Clear Entry] or press ESC to return to the Fields/Expressions list screen. |
The computed field is not automatically added to Views based on this table layout. To display the computed field in the View, use the [Add Columns] button.
You can use multi-value fields to substitute descriptive terms for the short numeric codes that are often used in files to speed processing and save space. For example, a two digit numeric code might be used to identify the city in which company branches are located. Such codes can make it difficult to identify the branch for a given transaction.
You can solve this interpretation problem by defining a multi-value computed field named City, with a Default Value of “Unknown”.
The conditions would be based on the Location field (for example, LOC = “01” for London) and their values would be the corresponding city (for example, “London”).
Note: This definition, like all multi-value computed fields, has a default value that is not conditional.
When you add this field to the View, cities are clearly identified.
You can use expressions to define all the values in a multi-value computed field, allowing you to take full advantage of Analyzer’s ability to evaluate complex expressions.
For example, you can use multi-value computed fields to calculate royalties based on sales from different locations. If the royalties payable are 10% on European sales, 20% on American sales and 15% otherwise, you can define a multi-value computed field named Royalty (with a Default Value of Value *.15) to help simplify your calculations.
The conditions would be based on the Location field (for example, LOC="03" OR LOC="04" for American sales) and their values would be the corresponding Market Value percentage (for example, Value *.20 for American sales).
Note that the value of the Royalty field takes on a different value for each record in the file, depending on each transaction’s branch location. To calculate the total worldwide royalties payable, use the Total command to compute the value of the Royalty field.