1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Use Lists of Values in an Excel Workbook
  3. List of Values for Descriptive Flexfields

List of Values for Descriptive Flexfields

Oracle Visual Builder Add-in for Excel supports lists of values (LOV) for descriptive flexfields (DFF) in ADF REST services in some scenarios.

About DFFs

DFFs are a feature of ADF REST polymorphic business objects. A DFF is a set of fields for a particular record that differs based on the value of a discriminator field (also known as a context segment). There are three types of fields in a DFF field set:

  • Global fields. Global fields are available for all values of the discriminator field.
  • The discriminator field or "context segment". The discriminator field determines which context-sensitive fields are displayed in the layout.
  • Context-sensitive fields. Context-sensitive fields are dynamic based on the value of the discriminator field.

Let's suppose you have an Employee polymorphic business object that you are using in a layout and that this business object includes a DFF for the employee's location. This DFF may include a couple of global fields ("Site" and "Time Zone"), a discriminator field ("Region"), and context-sensitive fields ("Zip Code", "Postal Code", "State", and "Province").

The global fields are available for all values of the discriminator field but the context-sensitive fields are either available or unavailable depending on this value. If the business user selects "United States" from the Region field, only "Zip Code" and "State" are available.

LOVs and DFFs

LOVs are supported on all three field types—global, discriminator, and context-sensitive fields—and for both LOV types—independent and dependent—with some restrictions. Dependent LOVs are based on a dynamic filter so that the values in the LOV depend on the value of another field. For example, a dependent LOV may display a list of cities based on the U.S. state selected in another field. Independent LOVs do not have a dynamic filter.

Independent LOVs are supported for all three types of fields and are automatically configured based on the service's OpenAPI service metadata document. However, only LOVs for the discriminator field are visible in the designer and configurable by the workbook developer.

Dependent LOVs are supported in these scenarios:

  • On a global field based on the value in another global field. These LOVs are automatically configured based on the OpenAPI service metadata document.
  • On a context-sensitive field based on another context-sensitive field. These LOVs are automatically configured based on the OpenAPI service metadata document.

    Typically, these LOVs use segment-type client binds.

Client Binds

There are two types of client bind: segment-type and parameter-type.

Segment-type client binds are supported automatically as long as the service's OpenAPI service metadata document provides all the proper metadata. If the metadata is present, the add-in configures the list of values at runtime.

Parameter-type client binds must be configured from the Polymorphic Information tab of the Business Object Field editor before they become available. See Configure the Bind Parameters for a Descriptive Flexfield's List of Values.

Keep in mind these limitations:

  • Client binds are only supported for context-sensitive fields and not global fields.
  • Only binds of data type string and number are supported. Binds of other date types are not supported.
  • The add-in uses client binds if and only if the OpenAPI service metadata document provides proper metadata.

Configure the Bind Parameters for a Descriptive Flexfield's List of Values

Configure parameter-type client binds used by fields in a descriptive flexfield (DFF) using the Business Object Field Editor.

Oracle Visual Builder Add-in for Excel currently only supports configuring the polymorphic bind parameters (also known as "flex bind variables"). Other details of the list of values for DFFs must come from the metadata and are not configurable.

To configure a bind parameter:

  1. From the Layout Designer, click the Columns tab and then select the polymorphic field set (the Context Value column in this example).
  2. Click the Edit icon (Edit) to open the Business Object Field Editor.
  3. From the Polymorphic Information tab, configure the bind parameters, as shown in the following image:
    The Polymorphic Information page of the Business Object Field Editor showing the bind parameters for a list of values on a DFF.

    This example uses a bind parameter, BusinessUnit, for the list of values based on the value of the RequisitioningBUId field.

The value of a bind parameter can be a literal value like 100001, Requisition001, or a string that contains one or more expressions surrounded by curly braces ({ }).

An expression can refer to the value of a field in any business object in the current business object hierarchy. The field referred to must be exposed in an existing layout.

In this example, the value is a single expression, where

  • this is the polymorphic field set
  • this.BusinessObject is the business object that owns the polymorphic field set (the DFF business object)
  • Parent gets the parent business object in the hierarchy (bottom-up: RequisitionDistributions/projectDFF, RequisitionDistributions, RequisitionLines, PurchaseRequisitions)
  • this.BusinessObject.Parent.Parent.Parent is the PurchaseRequisitions business object that owns the field. RequisitioningBUId
  • Fields['<field ID>'].Value gets the current field value of the field with the given field ID. Note the single quotes (required) around the ID.

Note:

Expression syntax is limited to what's shown in the example (key parts: this, BusinessObject, Parent, Fields[<field ID>].Value). See About Expressions for more information.