Computed Fields

From AgileApps Support Wiki
Revision as of 16:49, 28 April 2011 by imported>Alice (Removed HTML Text type per case 18635)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Computed fields are user-defined fields that expand analytic capabilities in Views and Reports. Without modifying the object model, users can build ad hoc fields that support complex data modeling and analysis. These fields do not reside in the database, but are created when needed for presentation in Views or Reports.

When included in Views or Reports, Computed Fields provide powerful building blocks for analytical models and metrics to monitor business operations in easy-to-read dashboards, tables or charts.

Eligible Field Types

These field types can be used in Computed Fields formulas:

  • Text string
  • Numbers (integers and numbers with decimals)
  • Rollup Summary Fields
  • Boolean
  • Percent (%)
  • Currency

How it Works

Let's consider a real-life example: an Inventory system where simple details about a product are specified and Computed Fields are used to analyze complex Pricing and Packaging schemes.

Pricing Information

Assume an inventory item, a 6-pack of soda, which is defined as follows:

 Field Name:                Value:
   Part Number                Soda-20457
   Description                6 cans, shrink-wrapped, 12 oz each
   Price                      $4.50
   Number of Units per Pack   6
   Unit Price                 Price / Number of Units per Pack  (this is a [[Formula Fields|Formula Field]])
In this record, the unit price is calculated as $.75 per can of soda ($4.50/6 cans of soda = $.75)

Analysis

To setup the pricing analysis, create the following Computed Fields, one for each of the available packaging options. Each package option calculates the Unit Price of the 6-pack of soda, based on packaging.

This Expression is used to calculate the Unit Price for each package option: Unit Price = (Package Price / (Quantity * 6 cans each))

 Computed Fields
   Package Option:     Quantity:   Package Price:    Unit Price:
      Case                   4         $15.              $.625
      Carton                 8         $20.              $.416
      Bulk                  12         $35.              $.486
With this Analysis, it's easy to compare the unit price of Package Option to the Unit Price of a single 6-pack ($.75).

Add a Computed Field

In this example, the Revenue field in the Accounts object is used to create the computed field. The ranges are defined as follows:

  1. From the Reports tab, add a new report, or choose a report to modify
  2. From the Fields tab, confirm that an appropriate field is in the list of Selected Fields (in this case, Revenue)
  3. Click the New Computed Field link
  4. Add the following code to create a computed field
    Column Title
    Name of the computed field
    Return Type
    Select Text
    Formula
    Add the following code:
    IF((REVENUE <= 10000),'<$10K',
    IF((REVENUE >= 10000 && REVENUE < 100000),'$10-100K',
    IF((REVENUE >=100000 && REVENUE < 500000),'$100K-500K',
    IF((REVENUE >=500000 && REVENUE <1000000),'$500K - 1M',
    '>= $1M'))))
  5. From the Group tab, choose the computed field in the Group Information by field
  6. From the Charts tab, choose Pie
  7. Click the [Preview] button to display this chart
    File:Revenuepie.gif
    In this example, the computed field allows records to be grouped by range, and displayed in a pie chart

Using Computed Fields

Computed fields offer different functionality, depending on whether the computed fields are used in a View or a Report.

Computed fields must be created separately for Views and Reports. A computed field created in a View is not available in a Report, just as a computed field created in a Report is not available in a View.

In Reports, computed fields can be used to:

In Views, computed fields can be used to:

Formula Functions used in Computed Fields

The following Formula Functions can be used in Computed Fields:

Date Functions Logical Functions
  • AND
  • IF
  • NOT
  • OR
  • Not included: ISNEW, ISCHANGED, ISNULL
Math Functions Text Functions
  • Not included: PRIORVALUE

For details on how to use these functions, see Formula Functions.