Progress
Results User’s Guide
for Windows
Creating Calculated Fields
You can create calculated fields from within any query view except the Labels view. See Table 6–1 for a list of the views in which individual calculated fields are available.
Follow these steps to create a calculated field:
- Choose Field
Add Calculated Field, then select the field you want to create.
Results displays the Add field dialog box for the type of calculated field you selected. The following sections describe these dialog boxes.
- Use the Add Field dialog box to specify the parameters for the field, then choose OK. The Properties dialog box appears.
For example, you can choose the Assistant button to modify how the data in the field is displayed. You can also change the name of a calculated field by selecting the Rename button. See the "Renaming Calculated Fields" section later in this chapter.
- Set the properties for the field you created. See the "Changing Display Field Properties" section in Modifying Display Fields."
Adding a Percent of Total Field
The Add Field - Percent of Total dialog box lets you specify the field for which you want to calculate the percent of total. To specify a field, select the field and choose OK:
![]()
The following example uses Percent of Total to calculate each sales representative’s percent of total for the month of January:
![]()
Adding a Running Total Field
The Add Field - Running Total dialog box lets you select the field for which you want to calculate a running total. To specify a field, select the field and choose OK:
![]()
The following example lists the running total for the Total-Paid field:
![]()
Adding a Counter
The Add Field - Counter dialog box lets you specify the parameters for the counter field:
![]()
To create the Counter field, set the following parameters:
- Starting Number—Specifies the number from which to begin counting.
- Number to Add—Specifies the increment between the numbers.
- Sections Used In—Select the sections you want to count: Master, Detail, or both. For more information about master and detail sections, see the "Using Master and Detail Sections to Display Records" section in this chapter.
The following example uses the Counter field to count the total number of orders in the Master section:
![]()
Adding a Stacked Array Field
When a field consists of several elements, each of which contains its own value, the field is called an array. By default, the values in the array are listed horizontally. To list them in a vertical column, use the Stacked Array calculated field.
The Add Field - Stacked Array dialog box lets you specify the array you want to stack vertically. To specify the array, select the array and choose OK:
![]()
In the following example, the Stacked Array field displays the contents for Month-Quota in a vertical column:
![]()
Adding a Lookup Field
The Add Field - Lookup Source dialog box allows you to display the value of a field in a table not currently in the query.
To use this option, both the table that contains the field you want to display and one of the tables in your query must contain a field that stores the same kind of information. This similarity between the two fields lets you establish a relationship between the two tables. For example, you might use a field in the Customer table called Cust-Num and a field in the Invoice table called ID-Num to identify your customers. In this case, they both store a set of numbers that identify your customers:
![]()
For more information about table relationships, see the "Record Displays in Multi-table Queries" section in "Query Basics," and the "Changing Table Relationships in Multi-table Queries" section in Changing the Query Selection Criteria."
Follow these steps to look up a field in a table not included in the query:
- Select a field from one of the tables in your query that contains the same type of information as a field in the table with which you are establishing a relationship.
- Choose OK. The Add Field - Lookup Matching Field dialog box appears:
![]()
- Select the table that contains the field you want to look up.
- Select a field that contains the same type of information as the field you selected in Step 1. This establishes a relationship between the two tables.
- Choose OK. The Add Field - Lookup Display Field dialog box appears:
![]()
- Select the field that contains the information you want to display.
- Type a value to display in place of the lookup field when Results cannot find a matching field value in one or more records. By default, Results displays a question mark (?) when it cannot find a value.
- Choose OK.
The following example displays a lookup field called Region from a table called State that is not included in the query:
![]()
Adding a Math Expression
The Add Field - Math dialog box allows you to create a math expression based on the value of two fields. When you select the Math option, Results displays the Add Field - Math dialog box:
![]()
Follow these steps to create a math expression:
- Select the mathematical type of function to perform. Results lists the available display fields in the Function/Field selection list and displays an empty expression based on the function you choose:
![]()
- Double-click one of the following to create the first part of the expression:
- A field—Uses the value of the field in the first part of the expression.
- Constant Value—Lets you enter a value to use in the first part of the expression. For example, you can use constant value field to insert a number to calculate within the expression.
- Subexpression—Lets you enter a subexpression as the first value in the expression. A subexpression is an expression within another expression.
Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.
- If the expression contains a second value, double-click a field, constant value, or subexpression to create the last part of the expression.
Results creates the remaining part of the expression and displays the entire expression in the Expression selection list:
![]()
- Choose OK to save the expression.
The following example contains a calculated field named Total Price that multiplies the value of the Price and Qty fields for each record in the query:
![]()
Adding a String Function
The Add Field - String Function dialog box allows you to create a string expression:
![]()
Follow these steps to create a string expression:
- In the Function/Field selection list, double-click the function you want to perform. Table 6–2 describes these functions.
Results does the following:
- Double-click one of the following to create the first part of the expression:
- A field—Uses the value of the field in the first part of the expression.
- Constant Value—Lets you enter a value to use in the first part of the expression. For example, you could use constant value to insert a comma or a space within a string.
- Sub-expression—Lets you enter a subexpression as the first value in the expression. A subexpression is simply an expression within another expression.
Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.
- If your expression contains a second value, double-click a field, constant value, or subexpression to create the last part of the expression.
Results creates the remaining part of the expression and displays the entire expression in the Expression field:
![]()
- Choose OK to save the expression.
The following example contains a calculated field named City/State that uses the Combine String function to combine the values of the City and State fields into one field:
![]()
Note that the City/State field uses a constant value that contains a comma and space to separate the city and state. This constant value is added to the expression through a subexpression. The following figure illustrates the expression used in this example:
![]()
Adding a Numeric Function
Use the Numeric function to create a numeric expression. When you select the Numeric Function option, Results displays the Add Field - Numeric Function dialog box:
![]()
Follow these steps to create a numeric expression:
- In the Function/Field selection list, double-click the function you want to perform. Table 6–3 describes these functions.
Results does the following:
- Double-click one of the following to create the first part of the expression:
- A field—Uses the value of the field in the first part of the expression.
- Constant Value—Lets you enter a value to use in the first part of the expression. For example, you can enter a number to calculate within the expression.
- Subexpression—Lets you enter a subexpression as the first value in the expression. A subexpression is simply an expression within an expression.
Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.
- If the expression contains a second value, double-click a field, constant value, or subexpression to create the last part of the expression.
Results creates the remaining part of the expression and displays the entire expression in the Expression field:
![]()
- Choose OK to save your expression.
The following example contains a calculated field named Round Amount that rounds the amount of each customer’s invoice to the nearest whole dollar:
![]()
Adding a Date Function
The Add Field - Date Function dialog box allows you to define a date calculation:
![]()
Follow these steps to add a date function:
- In the Function/Field selection list, double-click the function you want to perform. Table 6–4 describes these functions.
Results does the following:
- Double-click one of the following to create the first part of the expression:
- A field—Uses the value of the field in the first part of the expression.
- Constant Value—Lets you enter a date to use in the first part of the expression.
- Current Date—Uses the current date each time you run the query.
- Subexpression—Lets you enter subexpression as the first value in the expression.
Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.
- Double-click a field, constant value, current date, or subexpression to create the last part of the expression.
Results creates the remaining part of the expression and displays the entire expression in the Expression field:
![]()
- Choose OK to save your expression.
The following example contains a calculated field named Delivery Time that uses the Differences Between Two Dates function to calculate the difference between the Ordered and Shipped dates. Note that a question mark displays in the Delivery Time field for those records that Results cannot calculate because the order has not shipped:
![]()
Adding a Logical Function
The Add Field - Logical Function dialog box allows you to create a logical expression:
![]()
Follow these steps to create a logical expression:
NOTE: Many of the functions in Table 6–5 contain comparison operators. See "Comparison Operators."- In the Function/Field selection list, double-click the function you want to perform. Table 6–5 describes these functions.
- Double-click one of the following to create the first part of the expression:
- A field—Uses the value of the field in the first part of the expression.
- Constant Value—Lets you enter a date to use in the first part of the expression.
- Current Date—Uses the current date each time you run the query.
- Subexpression—Lets you enter subexpression as the first value in the expression. A subexpression is an expression within an expression.
Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.
- If your expression contains a second value, double-click a field, constant value, current date, or subexpression to create the last part of the expression.
Results creates the remaining part of the expression and displays the entire expression in the Expression field:
![]()
- Choose OK to save your expression.
- By default, Results uses the display values “yes” and “no” to represent true and false. To substitute a different set of display values for true and false, choose the Assistant button in the Properties dialog box and modify the display values. See the "Changing Display Field Properties" section in Modifying Display Fields."
The following example contains a calculated field named On Time that uses the Dates Less or Equal function to determine whether the date an order is shipped is equal to or less than the promise date for the order:
![]()
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |