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:

  1. Choose Field Add Calculated Field, then select the field you want to create.
  2. Results displays the Add field dialog box for the type of calculated field you selected. The following sections describe these dialog boxes.

  3. Use the Add Field dialog box to specify the parameters for the field, then choose OK. The Properties dialog box appears.
  4. 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.

  5. 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:

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:

  1. 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.
  2. Choose OK. The Add Field - Lookup Matching Field dialog box appears:
  3. Select the table that contains the field you want to look up.
  4. 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.
  5. Choose OK. The Add Field - Lookup Display Field dialog box appears:
  6. Select the field that contains the information you want to display.
  7. 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.
  8. Choose OK.
  9. 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:

  1. 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:
  2. 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.

  3. If the expression contains a second value, double-click a field, constant value, or subexpression to create the last part of the expression.
  4. Results creates the remaining part of the expression and displays the entire expression in the Expression selection list:

  5. Choose OK to save the expression.
  6. 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:

  1. In the Function/Field selection list, double-click the function you want to perform. Table 6–2 describes these functions.
  2. Table 6–2: String Functions
    Function
    Description
    Combine Strings
    Combines two or more strings into one string.
    Current Time
    Returns the current time.
    Display as Time
    Displays a string in time format.
    Greater of Two Strings
    Returns the greater (larger) of two strings.
    If first expression is true return second else third string
    Defines an expression that makes a decision based on string values. If the first string expression is true, the second expression is returned. If the first string expression is false, the third expression is returned.
    Length of String
    Returns the number of characters in a string. For example, the length of the string “Progress” is 8.
    Lesser of Two Strings
    Returns the lesser (smaller) of two strings.
    Name of Month
    Returns the current month.
    Name of Weekday
    Returns the current day.
    String Constant or Field
    Lets you to build an expression with either a string constant or the value of a character field.
    Substring
    Returns a substring. A substring is part of a string. For example, “hold” is a substring of “credit hold.”
    User ID
    Returns the user ID.

    Results does the following:

    • Lists the available display fields in the Function/Field selection list.
    • Displays an empty expression based on the function you chose in Step 1.
  3. 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.

  4. If your expression contains a second value, double-click a field, constant value, or subexpression to create the last part of the expression.
  5. Results creates the remaining part of the expression and displays the entire expression in the Expression field:

  6. Choose OK to save the expression.
  7. 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:

  1. In the Function/Field selection list, double-click the function you want to perform. Table 6–3 describes these functions.
  2. Table 6–3: Numeric Functions
    Function
    Description
    Absolute Value
    Returns the absolute value of a number. The absolute value is the positive value of a given number. For example, the absolute value of -3 is 3; the absolute value of 5 is 5.
    Cube Root
    Returns the cube root of a number. The cube root of a number is a number that when multiplied by itself three times, equals the number. For example, the cube root of 8 is 2 (2*2*2).
    Greater of Two Numbers
    Returns the largest number from a list of numbers. For example, if a list consists of two numbers, 24 and 55, the function returns 55.
    If first expression is true return second else third number
    Defines an expression that makes a decision. If the first expression is true, the second expression is returned. If the first expression is false, the third expression is returned. For example, you can define an expression that calculates a discount based on the credit limit of a customer.
    Log Base e
    Returns the logarithm based on the value e. The value of e is 2.7182818.
    Log Base n
    Returns the natural logarithm for a number for a given base. For example, 25 is 32. The natural logarithm for 32 with base 2 is 5.
    Numeric Constant or Field
    Defines a field’s value
    Remainder
    Returns the remainder of a division operation.
    Round
    Rounds a value to the nearest whole number. For example, rounding 5.2 returns 5 and rounding 5.7
    returns 6.
    Smaller of Two Numbers
    Returns the smaller of two numbers. For example, if the function compares the numbers 18 and 29, it returns the number 18.
    Square Root
    Returns the square root of a number. For example, the square root of 9 is 3.
    Truncate
    Truncates a number.

    Results does the following:

    • Lists the available display fields in the Function/Field selection list.
    • Displays an empty expression based on the function you chose in Step 1 within the Expression scroll box.
  3. 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.

  4. If the expression contains a second value, double-click a field, constant value, or subexpression to create the last part of the expression.
  5. Results creates the remaining part of the expression and displays the entire expression in the Expression field:

  6. 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:

  1. In the Function/Field selection list, double-click the function you want to perform. Table 6–4 describes these functions.
  2. Table 6–4: Date Functions
    Function
    Description
    Add Days to Date Value
    Adds the number of days you specify to a date and returns the value.
    Current Date
    Returns the current date.
    Day of Month
    Returns the day of the month for the date you specify.
    Day of Week
    Returns the day of the week for the date you specify in numeric format, where the value “1" begins with Sunday.
    Difference between Two Dates
    Returns the difference between two dates.
    Earlier of Two Dates
    Returns the earlier of two dates. For example, if the function compares the dates 01/05/95 and 01/09/99, it returns 01/05/95.
    If first expression is true return second else third date
    Defines an expression that makes a decision. If the first part of the expression is true, the function returns the second date; if the first part of the expression is not true, the function returns the third date.
    Later of Two Dates
    Returns the later of two dates. For example, if the function compares the dates 01/05/95 and 01/09/99, it returns 01/09/99.
    Month of Year
    Returns the number of the month for the date you specify.
    Subtract Days from Date Value
    Subtracts the number of days you specify from a date and returns the value.
    Year Value
    Returns the year for the date you specify. For example, the date 04/05/99 is returned as 1999.

    Results does the following:

    • Lists the available display fields in the Function/Field selection list.
    • Displays an empty expression based on the function you chose in Step 1 within the Expression editor box:
  3. 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.

  4. Double-click a field, constant value, current date, or subexpression to create the last part of the expression.
  5. Results creates the remaining part of the expression and displays the entire expression in the Expression field:

  6. Choose OK to save your expression.
  7. 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:

  1. In the Function/Field selection list, double-click the function you want to perform. Table 6–5 describes these functions.
  2. NOTE: Many of the functions in Table 6–5 contain comparison operators. See "Comparison Operators."

    Table 6–5: Logical Functions
    Function
    Description
    Begins
    Returns the logical value “yes” for strings that begin with the letter you specify or the logical value “no” for strings that do not begin with the letter you specify.
    Dates Equal
    Returns the logical value “yes” for dates equal to the value you specify or the logical value “no” for dates not equal to the value you specify.
    Dates Greater or Equal
    Returns the logical value “yes” for dates larger or equal to the value you specify or the logical value “no” for dates smaller than the value you specify.
    Dates Greater Than
    Returns the logical value “yes” for dates larger than the value you specify or the logical value “no” for dates smaller or equal to the value you specify.
    Dates Less or Equal
    Returns the logical value “yes” for dates smaller or equal to the value you specify or the logical value “no” for dates larger than the value you specify.
    Dates Less Than
    Returns the logical value “yes” for dates smaller than the value you specify or the logical value “no” for dates larger or equal to the value you specify.
    Dates Not Equal
    Returns the logical value “yes” for dates not equal to the value you specify or the logical value “no” for dates equal to the value you specify.
    Logical AND
    Lets you join two logical expressions together. The function returns the value “yes” when both expressions are true and the value “no” when either one or both expressions are false.
    Logical OR
    Lets you join two logical expressions together. The function returns the value “yes” when either expression is true and the value “no” when neither expression is true.
    Matches
    Returns the logical value “yes” for strings that match the characters you specify or the value “no” for strings that do not match the characters you specify.
    Negate logical expression (NOT)
    Returns the logical value “no” for logical expressions that are true and the logical value “yes” for logical expressions that are false.
    Numbers Equal
    Returns the logical value “yes” for numbers equal to the value you specify or the logical value “no” for numbers not equal to the value you specify.
    Numbers Greater or Equal
    Returns the logical value “yes” for numbers larger or equal to the value you specify or the logical value “no” for numbers smaller than the value you specify.
    Numbers Greater Than
    Returns the logical value “yes” for numbers larger than the value you specify or the logical value “no” for numbers smaller or equal to the value you specify.
    Numbers Less or Equal
    Returns the logical value “yes” for numbers smaller or equal to the value you specify or the logical value “no” for numbers larger than the value you specify.
    Numbers Less Than
    Returns the logical value “yes” for numbers smaller than the value you specify or the logical value “no” for numbers larger or equal to the value you specify.
    Numbers Not Equal
    Returns the logical value “yes” for numbers not equal to the value you specify or the logical value “no” for numbers equal to the value you specify.
    Strings Equal
    Returns the logical value “yes” for strings equal to the value you specify or the logical value “no” for strings not equal to the value you specify.
    Strings Greater or Equal
    Returns the logical value “yes” for strings larger or equal to the value you specify or the logical value “no” for strings smaller than the value you specify.
    Strings Greater Than
    Returns the logical value “yes” for strings larger than the value you specify or the logical value “no” for strings smaller or equal to the value you specify.
    Strings Less or Equal
    Returns the logical value “yes” for strings smaller or equal to the value you specify or the logical value “no” for strings larger than the value you specify.
    Strings Less Than
    Returns the logical value “yes” for strings smaller than the value you specify or the logical value “no” for strings larger or equal to the value you specify.
    Strings Not Equal
    Returns the logical value “yes” for strings not equal to the value you specify or the logical value “no” for strings equal to the value you specify.

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

  4. 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.
  5. Results creates the remaining part of the expression and displays the entire expression in the Expression field:

  6. Choose OK to save your expression.
  7. 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."
  8. 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