7 Working with ADF Desktop Integration Table-Type Components

This chapter describes the table-type components that ADF Desktop Integration provides, how to configure and use them, how to download data from Fusion web application, how to insert, update, and delete data rows from the table-type components in the integrated Excel workbook, how to track the changes, how to configure special columns in the table-type components, and other tasks that you can do with table-type components.

This chapter includes the following sections:

7.1 About ADF Desktop Integration Table-Type Components

ADF Desktop Integration provides the following table-type components to display structured data:

  • ADF Table component

  • ADF Read-only Table component

The ADF Table and ADF Read-only Table components provide end users with the functionality to download and upload rows of data. The ADF Table component also enables end users to edit or delete downloaded data, or to insert new rows of data. Figure 7-1 shows the ADF Table and the ADF Read-only Table components.

Figure 7-1 ADF Desktop Integration Table-Type Components

This image is described in the surrounding text

Each ADF Table component contains a Key column. Do not remove the Key column, as it contains important information that is used by ADF Desktop Integration for the proper functioning of the table. Removal of the Key column, or any modification in the Key column cell, results in errors and data corruption. For more information about the Key column, see Section 7.13, "Configuring ADF Table Component Key Column."

The other ADF Desktop Integration components that you can use with these table-type components are described in Chapter 6, "Working with ADF Desktop Integration Form-Type Components."

7.1.1 ADF Desktop Integration Table-Type Components Use Cases and Examples

Tables are used to display the structured information. For example, Figure 7-2 shows an ADF Table component of Summit sample application for ADF Desktop Integration with data downloaded from the respective Fusion web application.

Figure 7-2 ADF Table Component with Downloaded Data

This image is described in the surrounding text

7.1.2 Additional Functionality of Table-Type Components

After you have added a table component to your integrated Excel workbook, you may find that you need to add additional functionality to configure your table. Following are links to other functionalities that table components can use.

7.2 Page Definition Requirements for an ADF Table Component

The ADF Table component is one of the Oracle ADF components that ADF Desktop Integration exposes. It appears in the components palette of the ADF Desktop Integration Designer task pane and, after inserted into an Excel worksheet, allows the following operations:

  • Read-only

  • Insert-only

  • Update-only

  • Insert and update

Review the following sections for information about page definition file requirements specific to an ADF Table component.

Before you can configure an ADF Table component to provide data-entry functionality to your end users, you must configure the underlying page definition file for the Excel worksheet with ADF bindings. For general information about the page definition file requirements for an integrated Excel workbook, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook."

Expose the following control bindings when you create a page definition file for authoring an ADF Table component:

Note:

As Excel displays a flat list of bindings and iterators are not displayed, use descriptive names for the attributes of different iterators.

Figure 7-3 shows the bindings that the ExcelCustomers.xml page definition file includes. This page definition file can support the use of an ADF Table component in the Excel worksheet that it is associated with.

Figure 7-3 ADF Bindings Supporting Use of an ADF Table Component

This image is described in the surrounding text

7.3 Inserting ADF Table Component into Excel Worksheet

After you have configured a page definition file correctly, you can insert the ADF Table component into the worksheet and configure its properties to achieve the functionality you want. The ADF Table component enables you to download, edit, and upload rows of data.

7.3.1 How to Insert ADF Table Component

You can insert the ADF Table component from the ADF Desktop Integration Designer task pane.

Before you begin:

It may be helpful to have an understanding of ADF Table component. For more information, see Section 7.3, "Inserting ADF Table Component into Excel Worksheet."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To insert ADF Table component into Excel worksheet:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet into which you want to insert the ADF Table component.

    When inserting an ADF Table component, you must ensure that the data of two tables does not overlap at runtime, and the selected cell is not a merged cell.

  3. In the bindings palette of the ADF Desktop Integration Designer task pane, select the tree binding to use and click Insert Binding.

    Based on your selection, the Select Component dialog or the Insert Component dialog appears.

  4. In the dialog that appears, select ADF Table and click OK.

    Notes:

    • You can also insert an ADF Table component by using the components palette or the Oracle ADF tab. Select ADF Table and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF Table from the Insert Component dropdown list. If you use either the components palette or the Oracle ADF tab to create the table component, you would have to add each column manually to appear in the component at runtime.

    • You cannot insert an ADF Table component in a merged cell.

    • When you insert an ADF Table component using Insert Binding, then by default, InputText is defined as the subcomponent type for all columns.

      If you want a column to have a different subcomponent type, open the ADF Table property inspector (select any cell of the ADF Table component and click the Edit Properties button in the Oracle ADF tab), click the browse (...) icon of the Columns property. In the Edit Columns dialog, select the column, and click the browse (...) icon of the UpdateComponent property. In the Select Component dialog, select the desired subcomponent type, verify the binding and other properties, and click OK.

  5. Configure properties for the ADF Table component using the property inspector shown in Figure 7-4.

    Figure 7-4 ADF Table Property Inspector

    This image is described in the surrounding text

  6. Specify a binding expression for the attribute that uniquely identifies each row in the iterator associated with the tree binding. The UniqueAttribute property may be left blank if the binding's iterator supports row keys.

  7. Configure the BatchOptions properties of the ADF Table component as described in Table 7-1.

    Table 7-1 BatchOptions Properties of the ADF Table Component

    Set this property to... This value...

    CommitBatchActionID

    The Commit action binding that the page definition file exposes.

  8. Optionally, configure the RowLimit group of properties to determine what number of rows the ADF Table component can download.

    For more information, see Section 7.20, "Limiting the Number of Rows Your Table-Type Component Downloads."

  9. Click OK.

Figure 7-5 shows the ADF Table component in EditCustomers-DT.xlsx in design mode.

Figure 7-5 ADF Table Component in Design Mode

This image is described in the surrounding text

Figure 7-6 shows the ADF Table component in EditCustomers-DT.xlsx at runtime.

Figure 7-6 ADF Table Component at Runtime

This image is described in the surrounding text

For more information about the properties that you can set for the ADF Table component, see Section A.12, "ADF Table Component Properties and Actions."

To remove the table component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."

7.3.2 How to Add a Column in an ADF Table Component

After inserting an ADF Table component in the worksheet of your integrated Excel workbook, you may want to add a column that is not available in the tree binding. For example, you may want to add a column that displays values calculated by an Excel formula.

Before you begin:

It may be helpful to have an understanding of ADF Table component. For more information, see Section 7.3, "Inserting ADF Table Component into Excel Worksheet."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To add a column in an ADF Table component:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that references the ADF Table component and click the Edit Properties button in the Oracle ADF tab.

  3. In the Edit Component: ADF Table dialog, click the browse (...) icon of the Columns property.

    The Edit Columns dialog appears, listing all the columns of the selected ADF Table component.

  4. Click Add to add a new column. The new column is inserted at the end of the Members list. To move the column to a specific position, select the column and use the Up and Down arrow keys.

  5. Configure the new column's properties in the right pane of the dialog.

  6. Click OK.

Note:

If you have not moved the new column as described in Step 4, it appears as the last column of the table.

ADF Desktop Integration does not limit the number of columns you can add in an ADF Table component you can add as many columns as your version of Excel supports. However, it has been observed that a very wide table gives slow performance and poor user experience. If you experience the same, you must try reducing the number of columns of the table before diagnosing other reasons for slow performance.

7.4 Configuring Oracle ADF Component to Download Data to an ADF Table Component

After you add an ADF Table component to a worksheet, you configure it and the worksheet that hosts it, so that the ADF Table component downloads data from the Fusion web application. To achieve this, you configure an Oracle ADF component, such as a worksheet ribbon command, to invoke an action set. The action set that is invoked must include the ADF Table component Download action among the actions that it invokes.

The number of rows that an ADF Table or an ADF Read-only Table component contains expands or contracts based on the number of rows to download from a Fusion web application. You should not place anything to the left or right of a table-type component unless you want to replicate it when Excel inserts rows to accommodate the data that one of the table-type components downloads. You can place other components above or below a table-type component as they maintain their position relative to the table-type component at runtime. End users who want to insert new rows of data into an ADF Table component at runtime must insert full rows into the Excel worksheet that hosts the ADF Table component.

7.4.1 How to Configure an ADF Component to Download Data to an ADF Table Component

Configure an Oracle ADF component, a worksheet ribbon button, or a worksheet event to invoke an action set that, in turn, invokes the ADF Table component Download action.

Before you begin:

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

It may be helpful to have an understanding of how to configure ADF component to download data to an ADF Table data component. For more information, see Section 7.4, "Configuring Oracle ADF Component to Download Data to an ADF Table Component."

To configure an ADF component to download data to an ADF Table component:

  1. Open the integrated Excel workbook.

  2. Click the Worksheet Properties button in the Oracle ADF tab, and add a ribbon command. For more information about adding a ribbon command in a worksheet, see Section 8.3.1, "How to Define a Workbook Command Button for the Runtime Ribbon Tab."

    Note:

    Instead of adding a ribbon command, you can configure a worksheet event or an Oracle ADF component (a button, for example) to invoke the action set at runtime.

  3. Open the Edit Action dialog to configure an action set. For more information about invoking action sets, see Section 8.2, "Using Action Sets."

  4. Add the ADF Table component Download action to the list of actions that the action set invokes at runtime. Note that Download is a component action.

    The ADF Table component Download action downloads the current state of the binding referenced by the ADF Table component TreeID property. To ensure that the state of this binding is up to date before download, add a query action that refreshes the binding before the action set invokes the ADF Table component Download action.

    Figure 7-7 shows the Edit Action dialog in the EditCustomers-DT.xlsx workbook where the action set invoked by the worksheet event Startup is configured.

    Figure 7-7 Action Set Downloading Data to an ADF Table Component

    This image is described in the surrounding text

  5. Click OK.

7.4.2 What Happens at Runtime: How the ADF Table Component Downloads Data

The end user invokes the action set that you configured. The action set invokes the list of actions specified in order. These include an action that invokes the Download action of the ADF Table component. When invoked, the Download action downloads all rows from the tree binding referenced by the ADF Table component TreeID property.

Make a note of following key points when the Download action is invoked at runtime:

  • If there are any rows marked as changed when the Download action is invoked, the end user is prompted to confirm the action and to continue (see Figure 7-8). If the end user chooses No, the action and the action set are cancelled without error.

  • All existing Excel rows are removed from the table in Excel.

  • The status column is cleared of all messages.

Figure 7-8 Confirmation Prompt Before Downloading Data in ADF Table

This image is described in the surrounding text

The number of rows that the action downloads depends on the values set for the RowLimit group of properties in the ADF Table component. For more information, see Section 7.20, "Limiting the Number of Rows Your Table-Type Component Downloads."

Note:

Any filter criteria that has been applied to the worksheet automatically gets cleared prior to the Download action.

7.5 Configuring a Worksheet to Download Data as Pending Insert Rows in an ADF Table component

A Pending Insert row is a worksheet table row with data that, on upload, is inserted as a new data row in the iterator. For example, if the end user creates a new row in the table by using the Insert option in the right click context menu, the new row is treated as a pending insert row and is inserted to the iterator when being uploaded.

A Pending Update row is a worksheet table row with data that, on upload, updates an existing data row in the iterator. For example, if the iterator of the tree binding contains some rows retrieved from the database and when these rows are downloaded to the ADF table, they are treated as pending update rows. If the end user makes changes to these rows and uploads them, the existing rows in the iterator are updated with new values from the ADF Table row.

In most cases, rows in the iterator of the tree binding are downloaded as pending update rows into the ADF Table. If you want some rows to be downloaded as pending inserts, you need to set the state of these rows to STATUS_INITIALIZED. For more information about how to set a row's state as STATUS_INITIALIZED, see the setNewRowState method in Oracle Fusion Middleware Java API Reference for Oracle ADF Model.

Note the following differences between pending insert rows and pending update rows:

  • Pending insert rows are populated with the value of the EL expression for the insert component that is associated with each column in the ADF Table component, while pending update rows are populated with the value of the EL expression for the update component that is associated with each column in the ADF Table component.

  • When evaluated for pending insert rows, the EL expression #{components.componentID.currentRowMode} returns Insert. In contrast, the same EL expression returns Update for pending update rows.

    Note that the componentID part of the EL expression #{components.componentID.currentRowMode} references the ID of the ADF Table component.

7.5.1 What Happens at Runtime: Download Action is Invoked

When the Download action is invoked, it examines the states of all rows in the iterator. Rows of state STATUS_INITIALIZED are downloaded as pending insert rows in the table, while rows of other states are downloaded as pending update rows.

7.5.2 Using STATUS_INITIALIZED Rows for Pending Inserts

You can use the STATUS_INITIALIZED rows to pre-populate values for some, or all, attributes of the pending insert rows. As a STATUS_INITIALIZED row is not validated, you can configure an action to populate the STATUS_INITIALIZED row partially and insert it into the iterator before the Download action is invoked. The Download action then treats this row as a pending insert row so that a new row, based on the pre-populated row, can be inserted.

Note that after uploading the pending insert, a STATUS_NEW row is inserted into the iterator, but the original STATUS_INITIALIZED row is not removed. If you want, you can configure another action to remove STATUS_INITIALIZED rows. For example, you can configure an action set with the following actions:

1. Create STATUS_INITIALIZED rows

2. Run Download action

3. Clean up STATUS_INITIALIZED rows

Notes:

  • If InsertRowEnabled is set to False, any changes to the downloaded STATUS_INITIALIZED rows are ignored when upload is performed.

  • To avoid empty STATUS_INITIALIZED rows if you are using an UpdateOnly table, remove the custom method that inserts STATUS_INITILIAZED rows from the Download action.

7.5.3 What You May Need to Know About DownloadForInsert Action

ADF Desktop Integration also supports a table action called DownloadForInsert. DownloadForInsert is an obsolete action and can be replaced with the Download action. DownloadForInsert continues to work as it always has worked in previous releases.The key difference, with respect to Download, is that DownloadForInsert only considers rows in the iterator that are in the STATUS_INITIALIZED state.

7.6 Configuring an ADF Table Component to Update Existing Data

When you add the ADF Table component, by default, it allows end users to edit the existing data, but it does not allow them to add new data rows or to delete existing data rows.

7.6.1 How to Configure an ADF Table Component to Update Data

If you want the end user to be able to edit existing data, but would like to restrict the addition or deletion of data rows, no additional configuration is required. Ensure that the ADF Table component RowAction properties are set, as described in Table 7-2. and shown in Figure 7-9.

Table 7-2 RowAction Properties of ADF Table Component

Property Value

InsertRowEnabled

False

DeleteRowEnabled

False

UpdateRowEnabled

True

Figure 7-9 ADF Table RowActions Properties to Update Data

This image is described in the surrounding text

7.6.2 What Happens at Runtime: How the ADF Table Component Updates Data

When the end user changes data in a row, ADF Desktop Integration marks the row and an upward pointing triangle appears in a row of the _ADF_ChangedColumn column. After updating the existing data, the end user initiates the upload process to save the changes. For more information about the ADF Table component's upload process, see Section 7.8, "Configuring an ADF Component to Upload Changes from an ADF Table Component."

Excel uploads modified rows from the integrated workbook in batches rather than row by row. You can configure the size of batches and the actions an ADF Table component invokes when it uploads a batch. For more information about batch processing, see Section 7.11, "Batch Processing in an ADF Table Component."

For more information about the properties that you can set for the ADF Table component, see Section A.12, "ADF Table Component Properties and Actions."

Note:

Any filter criteria that has been applied to the worksheet is automatically cleared prior to the upload action.

7.7 Configuring an ADF Table Component to Insert Data

The primary purpose of an ADF Table component is to provide end users with an interface where they can input or edit data which can then be uploaded to the database that serves your Fusion web application. For this to happen, you must expose methods on data controls, create action bindings in your page definition file, and set properties for the ADF Table component that an Excel worksheet hosts. Note that a full Excel row must be inserted for this functionality to work correctly.

7.7.1 How to Configure an ADF Table Component to Insert Data Using a View Object's Operations

If you want the changes that the end user makes in an ADF Table component to be committed invoking the ADF Table component's Upload action, you must configure some of the ADF Table component's properties.

Before you begin:

It may be helpful to have an understanding of how to configure ADF Table component to insert data. For more information, see Section 7.7, "Configuring an ADF Table Component to Insert Data."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure an ADF Table component to insert data using a view object's operations:

  1. Open the project in JDeveloper.

  2. If not present, add a CreateInsert and a Commit action binding to the page definition file that is associated with the Excel worksheet that hosts the ADF Table component.

    For more information, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook" and Section 7.2, "Page Definition Requirements for an ADF Table Component."

  3. Open the integrated Excel workbook.

  4. Select the cell in the Excel worksheet that references the ADF Table component and click the Edit Properties button in the Oracle ADF tab.

  5. In the Edit Component: ADF Table dialog, configure the RowActions properties of the ADF Table component as described in the Table 7-3:

    Table 7-3 RowActions properties of ADF Table component

    Set this property to... This value...

    InsertRowEnabled

    True

    InsertBeforeRowActionID

    The CreateInsert action binding that the page definition file exposes.

    InsertRowsAfterUploadEnabled

    True, to upload the inserted rows again regardless of whether they have been previously uploaded. By default, this property is set to False.

    The property is ignored if InsertRowEnabled is set to False.

  6. Configure the BatchOptions properties of the ADF Table component as described in the Table 7-4.

    Table 7-4 BatchOptions Properties of the ADF Table Component

    Set this property to... This value...

    CommitBatchActionID

    The Commit action binding that the page definition file exposes.

  7. Configure the Columns property of the ADF Table component as described in the Table 7-5.

    Table 7-5 Columns property of ADF Table component

    Set this property to... This value...

    InsertUsesUpdate

    True

    UpdateComponent

    • Set the Value field of the UpdateComponent property to the update attribute from the page definition file. For example, #{row.bindings.ProductId.inputValue}.

    • Verify that ReadOnly property of UpdateComponent is set appropriately.

      This property only appears if you selected InputText or TreeNodeList as the subcomponent to associate with the column. Set ReadOnly to False if you do want users to edit the values in the column, set to True otherwise.

      For more information about the components that you can use as a subcomponent, see Chapter 6, "Working with ADF Desktop Integration Form-Type Components."

    ID

    Set a value in this field that uniquely identifies the column in the ADF Table component's list of columns. A value for this property is required. The ADF Table component generates an initial value that you need not modify.

    CellStyleName

    Set this property to a style defined in the workbook or to an EL expression that applies a style to the cells in the column at runtime. For more information about styles, see Chapter 9, "Configuring the Appearance of an Integrated Excel Workbook."

    HeaderLabel

    Set this property to a label or to an EL expression that evaluates to a label which is rendered in the column header at runtime. For more information about labels, see Section 9.4, "Using Labels in an Integrated Excel Workbook."

    HeaderStyleName

    Set this property to a style defined in the workbook or to an EL expression that applies a style to the column's header cell at runtime. For more information about styles, see Chapter 9, "Configuring the Appearance of an Integrated Excel Workbook."

  8. Repeat Step 7 for each column that contains data to commit during invocation of the Upload action.

    For information about ADF Table component properties, see Section A.12, "ADF Table Component Properties and Actions."

Notes:

  • If you are using a polymorphic view object and want to insert a new row, the default CreateInsert action binding is not sufficient. You must create a custom method that also sets the discriminator value in the newly created row.

    While creating the custom method, you must expose the custom method as an action binding in the page definition file. The action binding must be specified as the InsertBeforeActionId rather than CreateInsert.

  • If the InsertRowsAfterUploadEnabled property is set to False and the end user tries to upload the inserted rows again, an error message in the status column is displayed indicating that the row cannot be inserted more than once.

7.8 Configuring an ADF Component to Upload Changes from an ADF Table Component

You configure the ADF Table component and the worksheet that hosts it so that end user can upload changes they make to data in the ADF Table component to the Fusion web application. To configure this functionality, you decide what user gesture or worksheet event invokes the action set that invokes the ADF Table component's Upload action.

The Upload action commits all successful rows even when some rows have failures. Use the UploadAllOrNothing action instead if you want no row changes to get committed if one, or more, row failures occur (see Section 7.9, "Configuring an ADF Table Component to Upload Changes Using UploadAllOrNothing Action"). To provide upload options to end users in a web page from the Fusion web application that differ from the default upload dialog, you must specify a Dialog action in the action set before the action that invokes the ADF Table Component's Upload action. For more information, see Section 7.8.5, "How to Create a Custom Upload Dialog."

Note:

In a master-detail relationship, ADF Desktop Integration does not support editing of the ViewLink source attributes, as the selections in the child view object would change as a result. To prevent any accidental editing, define the ViewLink source attributes to be read-only, or use a model configuration that does not include a view link between master and detail.

7.8.1 How to Configure an ADF Component to Upload Data from an ADF Table Component

Configure an ADF component, such as a worksheet ribbon command, to invoke an action set that, in turn, invokes the ADF Table component Upload action.

Before you begin:

It may be helpful to have an understanding of how to configure ADF component to upload data from an ADF Table data component. For more information, see Section 7.8, "Configuring an ADF Component to Upload Changes from an ADF Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure an ADF component to upload changed data from an ADF Table component:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog to configure the action set that invokes the ADF Table component Upload action.

    For more information about action sets, see Section 8.2, "Using Action Sets."

  3. Add the ADF Table component Upload action to the list of actions that the action set invokes at runtime.

    Figure 7-10 shows the Edit Actions dialog in the EditCustomers-DT.xlsx workbook, where the action set invoked by the ribbon command labeled Upload at runtime is configured.

    Figure 7-10 Action Set Uploading Data from an ADF Table Component

    This image is described in the surrounding text

  4. Click OK.

Note:

The action set does not include a call to a commit-type action as the ADF Table component's batch options already include calls to Commit. For more information, see Section 7.11.1, "How to Configure Batch Options for an ADF Table Component."

7.8.2 What Happens at Runtime: How the ADF Table Component Uploads Data

At runtime, the end user invokes the action set through whatever mechanism you configured (ADF component, worksheet ribbon button, worksheet event). This triggers the following sequence of events:

  1. If the ADF Table component contains dynamic columns, ADF Desktop Integration verifies whether the dynamic columns that were expanded the last time the ADF Table component's Download action was invoked are still present in the Fusion web application. If the columns are not present, ADF Desktop Integration prompts the end user to determine whether to continue upload process. If the end user decides not to continue, ADF Desktop Integration returns an abort code to the executing action set.

  2. If the ADF Table component contains no pending changes to upload, the ADF Table component's Upload action returns a success code to the executing action set.

  3. If you did not configure a custom upload dialog for the action set, as described in Section 7.8.5, "How to Create a Custom Upload Dialog," ADF Desktop Integration presents the default upload dialog shown in Figure 7-11.

    Figure 7-11 Default Upload Dialog

    This image is described in the surrounding text

    If the end user clicks Cancel, ADF Desktop Integration returns an abort code to the executing action set. If the end user clicks OK, the action set continues executing with the options specified in the dialog for the upload operation.

  4. The ADF Table component uploads modified rows in batches, rather than row by row. You can configure the batch options using the BatchOptions group of properties. For more information about batch options for the ADF Table component, see Section 7.11, "Batch Processing in an ADF Table Component."

    Each row of a batch is processed in the following way, and the process continues until all changed rows of each batch are processed:

    1. For inserted rows, invoke the InsertBeforeRowActionID action, if specified.

    2. Set attributes from the worksheet into the model, including any cached row attribute values.

    3. For edited rows, invoke the UpdateRowActionID action; and for inserted rows, invoke the InsertAfterRowActionID action, if specified.

    4. For each uploaded row, displays a status message in the Status column. For more information, see Section 8.2.5, "How to Display a Status Message While an Action Set Runs."

    5. For any row failure, it verifies the value of AbortOnFail. If AbortOnFail is set to False, it continues upload process, otherwise it stops uploading data and invokes the commit action.

  5. While uploading data, the ADF Table component returns a success or failure code to the executing action set based on the following:

    • If the ADF Table component uploads all batches successfully, it returns the success status to the executing action set. If the end user has selected the Download all rows after successful upload option in Step 3, the ADF Table component then downloads all rows from the Fusion web application.

    • If the ADF Table component did not upload all batches successfully, the action set invokes the action specified by the RowActions.FailureActionID property, if an action is specified for this property. ADF Desktop Integration returns a failure code to the action set.

If you selected On failure, continue to upload subsequent rows in the Upload Options dialog of Step 3, the Upload action returns a success code to the action set even if some individual rows encountered validation failures.

Notes:

  • If an ADF Table component column's ReadOnly property evaluates to True, the ADF Table component's Upload action ignores changes in the column's cells.

    For more information about an ADF Table component column's properties, see Table A-13.

  • When the Upload action is invoked on an ADF Table that has an Excel filter applied, Excel filter's criteria is cleared to show any hidden Excel worksheet rows, but the filter is not removed.

7.8.3 What Happens at Runtime: How the ReadOnly EL Expression Is Evaluated During Upload

At runtime, if an ADF Table component column's ReadOnly property evaluates to True, the ADF Table component's Upload action ignores all changes in the column's cells.

For more information about change tracking, see Section 7.23, "Evaluating EL Expression for ReadOnly Properties."

7.8.4 What Happens at Runtime: Upload Failure

When the ADF Table component starts uploading data, ADF Desktop Integration creates a DataControlFrame savepoint before initiating the upload process (once per batch of uploaded rows). In case of any failure, ADF Desktop Integration reverts back to the same savepoint, ensuring the integrity of the server-side state of the Fusion web application.

For each row in a batch of uploaded rows, ADF Desktop Integration does the following:

  1. Invokes configured actions, applies row attribute value changes, and performs data validation.

  2. In case of any error, reverts back to the savepoint state.

Note:

A second iteration is performed, if required, to re-upload any successfully uploaded rows whose changes were reverted due to a subsequent upload error.

For more information about savepoints, see the "Using Trees to Display Master-Detail Objects" section in the Developing Fusion Web Applications with Oracle Application Development Framework.

7.8.5 How to Create a Custom Upload Dialog

You display a page from Fusion web application that offers end users different options to those presented in the default upload dialog. You add a Dialog action before the action that invokes the ADF Table component's Upload action in the action set.

Before you begin:

It may be helpful to have an understanding of how to configure ADF component to upload data from an ADF Table data component. For more information, see Section 7.8, "Configuring an ADF Component to Upload Changes from an ADF Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To create a custom upload dialog:

  1. Create a page in the JDeveloper project where you develop the Fusion web application. For information on how to create this page, see Section 8.4, "Displaying Web Pages from a Fusion Web Application."

  2. In addition to the ADFdi_CloseWindow element (for example, a span element) described in Section 8.4, "Displaying Web Pages from a Fusion Web Application," the page that you create in Step 1 must include the elements described in Table 7-6.

    Table 7-6 Span Elements Required for Custom Upload

    Name Description

    ADFdi_AbortUploadOnFailure

    If you set this element to True, the action set stops uploading if it encounters a failure. If the element references False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload.

    ADFdi_DownLoadAfterUpload

    Set this element to True so the action set downloads data from the Fusion web application to the ADF Table component after the action set uploads modified data.

    Note:

    The page you create must include both elements to prevent ADF Desktop Integration presenting the default upload dialog to end users.

  3. Add a Dialog action to invoke the page you created in Step 1 before the action in the action set that invokes the ADF Table component's Upload action.

    For more information about displaying pages from a Fusion web application, see Section 8.4, "Displaying Web Pages from a Fusion Web Application."

7.8.6 What Happens at Runtime: Custom Upload Dialog

When a custom dialog appears, the page from the Fusion web application that you configure the Dialog action in the action set to display appears instead of the default upload dialog.

Note:

If there is no server connectivity when the end user tries to upload data, the end user gets an error when the Dialog action fails to find the custom upload page. ADF Desktop Integration does not revert to the standard dialog when server connectivity is not available.

For more information about displaying a page from the Fusion web application, see Section 8.4, "Displaying Web Pages from a Fusion Web Application." Otherwise, the runtime behavior of the action set that you configure to upload data is as described in Section 7.8.2, "What Happens at Runtime: How the ADF Table Component Uploads Data."

7.9 Configuring an ADF Table Component to Upload Changes Using UploadAllOrNothing Action

ADF Desktop Integration commits all row changes that are successfully uploaded during a Table.Upload operation, even when one or more rows has failures. For example, if 100 rows are uploaded and only three rows contain failures, 97 rows are still committed to the database. For more information, see Section 7.8, "Configuring an ADF Component to Upload Changes from an ADF Table Component."

Using the UploadAllOrNothing action, you can configure the upload process to commit all changed rows only if all rows are successfully uploaded. For example, if 100 rows are uploaded, and if any row fails, no rows are committed to the database.

7.9.1 How to Configure an ADF Component to use UploadAllOrNothing Action

Configure an ADF component, such as a worksheet ribbon command, to invoke an action set that, in turn, invokes the ADF Table component UploadAllOrNothing action.

Before you begin:

It may be helpful to have an understanding of how to configure ADF component to upload data from an ADF Table data component. For more information, see Section 7.9, "Configuring an ADF Table Component to Upload Changes Using UploadAllOrNothing Action."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure an ADF component to use UploadAllOrNothing action:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog to configure the action set that invokes the ADF Table component actions.

    For more information about action sets, see Section 8.2, "Using Action Sets."

  3. Add the ADF Table component UploadAllOrNothing action to the list of actions that the action set invokes at runtime.

  4. Click OK.

7.9.2 What Happens at Runtime: UploadAllOrNothing Action is Invoked

If you have chosen the UploadAllOrNothing action, ADF Desktop Integration commits row changes only when all rows are uploaded successfully.

Note:

The UploadAllOrNothing action uploads data in the same way as the Upload action. For more information about how data gets uploaded during Upload as well as UploadAllOrNothing, see Section 7.8.2, "What Happens at Runtime: How the ADF Table Component Uploads Data."

During the UploadAllOrNothing action, ADF Desktop Integration uploads all changed worksheet rows prior to invoking specified by CommitBatchActionID. If one, or more, row-level failures occur, the action specified by FailureActionID is invoked and the action specified by CommitBatchActionID is not invoked.

In the event of a failure, all changed column values remain unchanged. The status column indicates failure for all row-level failures, but remains empty for all rows without errors. When all rows succeed and are successfully committed, the changed column values are cleared and the status column for the uploaded rows reports success.

Notes:

  • The UploadAllOrNothing action is only supported for DataControls that support database transactions.

  • If CommitBatchActionID is not configured and an action set contains the UploadAllOrNothing action, a validation error is reported.

7.9.3 Limiting the Amount of Changed Data That Can Be Uploaded With UploadAllOrNothing Action

Uploading a very large number of changed worksheet rows with the UploadAllOrNothing action could result in significant memory consumption on the application server. To prevent end users from uploading too much data during the UploadAllOrNothing action, set the UploadAllOrNothing.ChangedDataLimit servlet parameter (specified in Kb) to limit the total amount of changed data that can get uploaded. If no parameter value is specified, a default limit of 10,240 Kb is used.

If the total amount of changed data uploaded exceeds the UploadAllOrNothing.ChangedDataLimit value, an error message is reported to the end user, and the UploadAllOrNothing action is aborted. Note that the action specified by Table.RowActions.FailureActionID is invoked when the changed data limit is exceeded.

7.10 Configuring an ADF Table Component to Delete Rows in the Fusion Web Application

The ADF Table component exposes an action (DeleteFlaggedRows) that, when invoked, deletes the rows in the Fusion web application that correspond to the flagged rows in the ADF Table component. A flagged row in an ADF Table component is a row where the end user has double-clicked or typed a character in the cell of the _ADF_FlagColumn column as described in Section 7.11, "Batch Processing in an ADF Table Component." The _ADF_FlagColumn column must be present in the ADF Table component to configure it to delete rows in the Fusion web application.

In addition, the page definition file that you associate with the worksheet that hosts the ADF Table component must expose a Delete action binding.

7.10.1 How to Configure an ADF Table Component to Delete Rows in the Fusion Web Application

To delete rows from an ADF Table component, you must add the Delete action binding to the page definition file, configure RowActions group of properties of the ADF Table component, and configure an action set to invoke the DeleteFlaggedRows action.

Before you begin:

It may be helpful to have an understanding of how to configure ADF Table component to delete data rows in Fusion web application. For more information, see Section 7.10, "Configuring an ADF Table Component to Delete Rows in the Fusion Web Application."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure an ADF Table component to delete rows in a Fusion web application:

  1. Open your Fusion web application in JDeveloper.

  2. If not present, add a Delete action binding to the page definition file that is associated with the Excel worksheet that hosts the ADF Table component.

    For more information, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook."

  3. Open the property inspector for the ADF Table component and set values for the RowActions group of properties as described in Table 7-7.

    Table 7-7 RowActions Properties of ADF Table component

    Set this property... To...

    DeleteRowActionID

    The Delete action binding that the page definition file exposes.

    DeleteRowEnabled

    True to enable the ADF Table component to delete rows in the Fusion web application.

    False is the default value.

    For more information about ADF Table component properties, see Section A.12, "ADF Table Component Properties and Actions."

  4. Click OK.

  5. Open the integrated Excel workbook.

  6. Open the Edit Action dialog to configure an action set for the Oracle ADF component, ribbon control, or worksheet event that the end user uses to invoke the action set at runtime.

  7. Add the ADF Table component's DeleteFlaggedRows action to the list of actions that the action set invokes at runtime.

    For more information about invoking action sets, see Section 8.2, "Using Action Sets."

  8. Click OK.

Note:

Add the DeleteFlaggedRows action in a separate action set and do not include it with other table actions like Download or Upload.

7.10.2 What Happens at Runtime: How the ADF Table Component Deletes Rows in a Fusion Web Application

The end user flags rows to delete, as described in Section 7.11.2, "Row Flagging in an ADF Table Component." The end user then invokes the action set. The following sequence of events occurs:

  1. If specified, the action binding referenced by the BatchOptions.StartBatchActionID property is invoked.

    Failures from this step are treated as errors. An error stops the action set invoking. It also returns the error condition to the action set. If an action binding is specified for the ActionSet.FailureActionID property, the action set invokes the specified action binding.

    For more information about configuring batch options, see Section 7.11, "Batch Processing in an ADF Table Component."

  2. The action set invokes the Delete action binding specified by RowActions.DeleteRowActionID.

    Note:

    Rows inserted since the last invocation of the ADF Table component's Download action but not uploaded to the Fusion web application are ignored even if flagged for deletion.

  3. If no errors occur during the invocation of the Delete action binding, a success message entry appears in the _ADF_StatusColumn column. If a failure occurs, the ADF Table component stops invocation of the Delete action binding and continues to Step 4.

  4. If an action binding is specified for the BatchOptions.CommitBatchActionID property, the action set invokes it. If this step fails, the action set stops processing batches. If no failures occur, the action set processes the next batch by invoking the action binding specified by the BatchOptions.StartBatchActionID property, and so on until the action set processes all batches.

  5. If the action set processes all batches successfully, it invokes the action binding specified by its ActionOptions.SuccessActionID property if an action binding is specified for this property. It then removes the rows deleted in the Fusion web application by invocation of the Delete action binding specified by RowActions.DeleteRowActionID from the worksheet and returns a success code to the action set.

    If failures occur while the action set processes the batches, the action set invokes the action binding specified by its ActionOptions.FailureActionID property if an action binding is specified for this property. This action binding returns a failure code to the action set.

  6. If an unexpected exception occurs while the action set invokes its actions, an error code is returned to the action set. All row-level errors are displayed in the Status column, and all batch-level errors can be tracked through Table.errors. For more information about error handling, see Section 12.4, "Error Reporting in an Integrated Excel Workbook."

Note:

When the DeleteFlaggedRows action is invoked on an ADF Table that has an Excel filter applied, Excel filter's criteria is cleared to show any hidden Excel worksheet rows, but the filter is not removed.

7.11 Batch Processing in an ADF Table Component

The ADF Table component uploads modified rows from the Excel workbook in batches rather than row-by-row. You can configure batch option properties that determine the size of batches and what actions the ADF Table component invokes when it uploads a batch.

7.11.1 How to Configure Batch Options for an ADF Table Component

The ADF Table component has a group of properties (BatchOptions) that allow you to configure how the ADF Table component manages batches of rows. Information about these properties can be found in Section A.12, "ADF Table Component Properties and Actions."

Before you begin:

It may be helpful to have an understanding of batch processing in the ADF Table component. For more information, see Section 7.11, "Batch Processing in an ADF Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure batch options for an ADF Table component:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that references the ADF Table component, and then click the Edit Properties button in the Oracle ADF tab.

  3. Set values for the BatchOptions group of properties in the property inspector that appears.

    Table 7-8 RowData.BatchOptions Properties

    Set this property... To...

    BatchSize

    Specify how many rows to process before an ADF Table component action (Upload or DeleteFlaggedRows) invokes the action binding specified by CommitBatchActionID. Any value other than a positive integer results in all rows being processed in a single batch. The default value is 100 rows.

    CommitBatchActionID

    The action binding to invoke after the ADF Table component processes each batch. Typically, this is the Commit action binding.

    LimitBatchSize

    True

    When True, the ADF Table component processes rows in batches determined by the value of BatchSize. When False, the ADF Table component uploads all modified rows in a single batch.

    True is the default value.

    StartBatchActionID

    Specify the action binding to invoke at the beginning of each batch.

  4. Click OK.

Note that a failure at the entity-level is not considered a batch failure. A failure at the commit level (for example, a wrong value for a foreign key attribute) is considered a batch failure.

7.11.2 Row Flagging in an ADF Table Component

By default, the ADF Table component includes a column, _ADF_FlagColumn, that facilitates the selection of rows for flagged-row processing. Double-clicking a cell of the _ADF_FlagColumn column flags the corresponding row for processing by actions invoked by a component action.

When the end user double clicks a cell of the _ADF_FlagColumn column, a solid circle appears, or disappears, in the cell to indicate that the row is flagged, or not. Figure 7-12 shows an example of a flagged column.

Figure 7-12 Flagged Column in ADF Table Component

This image is described in the surrounding text

Note:

By default, the solid circle character indicates a row flagged for flagged-row processing. However, any non-empty cell in a _ADF_FlagColumn column flags the corresponding row for flagged-row processing.

The following component actions can be invoked on flagged rows:

  • DeleteFlaggedRows

  • DownloadFlaggedRows

You can use the FlagAllRows component action to flag all rows, and the UnflagAllRows component action to unflag all rows of the ADF Table component.

Notes:

  • The ADF Table component's DownloadFlaggedRows action does not support changes in table column structure after the last invocation of the Download or DownloadForInsert action. The table column structure usually changes if you are using dynamic columns, or if the table contains columns with complex expressions in the Visible property.

  • The DownloadFlaggedRows action is not applicable to inserted rows.

Use of these component actions is dependent on the appearance of the _ADF_FlagColumn column in the ADF Table component. If you remove the _ADF_FlagColumn column from the ADF Table component, you cannot invoke any of these component actions. For more information about these component actions, see Section A.12.3, "ADF Table Component Actions."

At runtime, the end user can invoke any of the previously listed component actions from an action set. The invoked component action processes all flagged rows. For example, it downloads or deletes all flagged rows. For more information about configuring an action set to invoke a component action, see Section 8.2.2, "How to Invoke Component Actions in an Action Set."

7.11.3 Troubleshooting Errors While Uploading Data

End users might encounter unexpected reports of errors under certain circumstances while uploading data from ADF Table components. After posting changes from a batch, ADF Desktop Integration runs the action specified by the CommitBatchActionID. Errors that occur during the commit action might continue to be reported on subsequent batch commit actions, even though those batches of records do not contain the error. This can happen when any pending model updates are not automatically reverted when the CommitBatchActionID action fails.

To avoid any such error, you must explicitly revert pending model updates that exist after a commit failure. For example, you could create a custom action for the CommitBatchActionID that first attempts to commit the pending model changes. However, if an exception occurs during commit, the custom method should first roll back the pending model changes, so that any subsequent batch commit attempts can succeed.

Note:

It is important that the commit exception gets thrown again after rollback so that the commit errors are reported as expected on the client.

7.12 Special Columns in the ADF Table Component

By default, the ADF Table component includes some columns when you insert an ADF Table component in a worksheet. You can retain or remove these columns, if required. The following list describes the columns and the purpose they serve:

  • _ADF_ChangedColumn

    The cells in this column track changes to the rows in the ADF Table component. If a change has been made to data in a row of the ADF Table component since download or the last successful upload, a character that resembles an upward pointing arrow appears in the corresponding cell of the _ADF_ChangedColumn column. This character toggles (appears or disappears) when a user double-clicks a cell in this column. Figure 7-13 shows an example.

    Figure 7-13 Changed Column in an ADF Table Component

    This image is described in the surrounding text

    Note:

    If the end user does not want the ADF Table component's Upload action to upload changes in the rows flagged by this column, the user must clear the entry that appears in the corresponding cell.

    A confirmation dialog appears to end users when the ADF Table component's Download action is invoked, and one or more rows in this column are flagged as changed. The end user clicks OK to allow the Download action to run, or Cancel to stop the execution of the Download action.

  • _ADF_FlagColumn

    When the end user double-clicks a cell in this column, the corresponding row is flagged for flagged-row processing. A solid circle character appears to indicate that the row is flagged for flagged-row processing. For more information about the use of this column, see Section 7.11.2, "Row Flagging in an ADF Table Component."

    A confirmation dialog appears to end users when the ADF Table component's DownloadFlaggedRows action is invoked, and one or more rows in _ADFChangedColumn and _ADF_FlagColumn are flagged. The end user clicks OK to allow the action to run or Cancel to stop the execution of the action.

    Note:

    By default, the solid circle character indicates a row flagged for flagged-row processing. However, any non-empty cell in a _ADF_FlagColumn flags the corresponding row for flagged-row processing.

  • _ADF_StatusColumn

    This column reports the results of invocation of the following ADF Table component actions:

    • DeleteFlaggedRows

    • Upload

    A message appears in the cell of the _ADF_StatusColumn to indicate the result of the invocation for the corresponding row. If the end user invokes a DoubleClickActionSet defined in an ADF Table column and an error occurs, the errors are also reported in the status column of the corresponding row. Figure 7-14 shows an example of Status column message.

    Figure 7-14 Status Column in an ADF Table Component

    This image is described in the surrounding text

  • _ADF_RowKeyColumn

    This column, also referred to as the Key column, contains important information about the ADF Table component used by ADF Desktop Integration at runtime. The column appears both at runtime and design time. You can remove the column from the table at design time, but note that it automatically appears at runtime as the last column of the table.

    For more information about the _ADF_RowKeyColumn, see Section 7.13, "Configuring ADF Table Component Key Column."

The ADF Table component treats the properties of the _ADF_ChangedColumn, _ADF_FlagColumn, _ADF_RowKeyColumn, and _ADF_StatusColumn columns differently from the properties of other columns that it references. It ignores the values set for properties such as InsertComponent, InsertUsesUpdate, and UpdateComponent unless it invokes the DisplayRowErrors action described in Table A-14. It reads the values for properties related to style and appearance, for example CellStyleName and HeaderStyleName.

7.13 Configuring ADF Table Component Key Column

When you add ADF Table to your integrated Excel workbook, the Key column (column ID: _ADF_RowKeyColumn) appears automatically at design time. The Key column contains important information that is used by ADF Desktop Integration for proper functioning of the table. Note that you must not remove the Key column at runtime.

7.13.1 How to Configure the Key Column

You can configure the Key column's position, style properties, and header label. By default, the _ADFDI_TableKeyCellStyle style is applied to it.

Before you begin:

It may be helpful to have an understanding of the Key column in the ADF Table component. For more information, see Section 7.13, "Configuring ADF Table Component Key Column."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure the Key column:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that references the ADF Table component and click the Edit Properties button in the Oracle ADF tab.

  3. In the Edit Component: ADF Table dialog, click the browse (...) icon beside the input field for Columns.

    The Edit Columns dialog appears, listing all the columns of the selected ADF Table component.

  4. Select the column with ID as _ADF_RowKeyColumn.

  5. Change the column properties as desired, but do not change the following properties:

    • DynamicColumn

    • InsertComponent

    • InsertUsesUpdate

    • UpdateComponent

    • ID

    • Visible

  6. If desired, change the position of the column using the Up and Down arrow keys.

  7. Click OK to close Edit Columns dialog.

  8. Click OK to close the Edit Component: ADF Table dialog.

7.13.2 How to Manually Add the Key Column At Design Time

If you are using the integrated Excel workbook prepared and configured using an earlier version of ADF Desktop Integration, the Key column will not be available at design time. It will appear only at runtime. To configure the Key column properties, you can add it in the workbook at design time.

Before you begin:

It may be helpful to have an understanding of the Key column in the ADF Table component. For more information, see Section 7.13, "Configuring ADF Table Component Key Column."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To manually add the Key column at design time:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that references the ADF Table component, and then click the Edit Properties button in the Oracle ADF tab.

  3. Add a new column in the ADF Table, and specify the properties as described in Table 7-9. For more information about adding a column, see Section 7.3.2, "How to Add a Column in an ADF Table Component."

    Table 7-9 Key Column Properties

    Set this property... To ...

    CellStyleName

    _ADFDI_TableKeyCellStyle

    HeaderStyleName

    _ADFDI_HeaderStyle

    DynamicColumn

    False

    HeaderLabel

    #{_ADFDIres[COMPONENTS_TABLE_ROWKEY_COL_LABEL]}

    ID

    _ADF_RowKeyColumn

    InsertUsesUpdate

    True

    UpdateComponent

    OutputText

    The Value property must be empty.

    Visible

    True

    If desired, you may change the position of the Key column using the Up and Down arrow keys.

  4. Click OK.

Note:

You must specify the ID property of the new column as _ADF_RowKeyColumn; otherwise, the column will not be considered to be a Key column, and another Key column will automatically appear at runtime.

7.14 Creating a List of Values in an ADF Table Component Column

Use the TreeNodeList subcomponent when you want to render a dropdown list of values in an ADF Table component column. The list of values can display a maximum of two hundred and fifty values at runtime. Unlike other ADF Desktop Integration components, the TreeNodeList subcomponent does not appear in the components palette described in Section 5.5, "Using the Components Palette." Instead, you invoke it as a subcomponent when you specify values for the InsertComponent or UpdateComponent properties of an ADF Table component column. For information about the properties of an ADF Table component column, see Section A.12.2, "ADF Table Component Column Properties."

After you invoke the TreeNodeList subcomponent, you must specify a tree binding attribute associated with a model-driven list as a value for the TreeNodeList subcomponent's List property. The tree binding attribute associated with a model-driven list populates the dropdown menu in the Table component's column with a list of values after invocation of the Table component's Download action.

Note:

You can create a model-driven list of values in your ADF Table component by choosing ModelDrivenColumnComponent as the subcomponent type. For more information about creating a model-driven list, see Section 7.15, "Adding a ModelDrivenColumnComponent Subcomponent to Your ADF Table Component."

For information about the properties of a TreeNodeList subcomponent, see Section A.9, "TreeNodeList Subcomponent Properties."

7.14.1 How to Create a List of Values in an ADF Table Component Column

You add a column to the ADF Table component column and select TreeNodeList as the subcomponent. You then specify a tree binding attribute as the value for the TreeNodeList subcomponent's List property. A model-driven list must be associated with the tree binding attribute that you specify.

Notes:

  • The TreeNodeList subcomponent does not support a model-driven list whose control type is input_text_lov or combo_lov.

  • ADF List of Values components using date values are not supported.

  • The TreeNodeList subcomponent may not support model-driven lists for EJB-based data controls in all cases.

Before you begin:

It may be helpful to have an understanding of how to create a list of values in ADF Table component. For more information, see Section 7.14, "Creating a List of Values in an ADF Table Component Column."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To create a list of values in an ADF Table component column:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that references the ADF Table component and click the Edit Properties button in the Oracle ADF tab.

  3. In the Edit Component: ADF Table dialog, click the browse (...) icon beside the input field for Columns.

    The Edit Columns dialog appears, listing all the columns of the selected ADF Table component.

  4. Click Add to add a new column.

  5. Choose the appropriate option for the newly created column:

    • Click the browse (...) icon beside the input field for InsertComponent to configure the runtime list of values for insert operations.

    • Click the browse (...) icon beside the input field for UpdateComponent to configure the runtime list of values for update and download operations.

    In both options, the Select subcomponent to create dialog appears.

  6. Select TreeNodeList and click OK.

  7. Expand the property that you selected in Step 5 and configure values as follows:

    Figure 7-15 shows the property inspector for an ADF Table component column in EditCustomers-DT.xlsx after TreeNodeList is selected as the subcomponent for the column's UpdateComponent property.

    Figure 7-15 ADF Table Component Column Configured to Display a List of Values

    This image is described in the surrounding text

  8. Click OK.

7.14.2 What Happens at Runtime: How the ADF Table Column Renders a List of Values

At runtime, the ADF Table component invokes the Download action and populates each column. This action also populates the list of values in the column that you configure to render a list of values. Figure 7-16 shows an example from EditCustomers-DT.xlsx of the Summit sample application for ADF Desktop Integration, where Country is the column configured to display a list of values.

Figure 7-16 Runtime View of an ADF Table Component Column Displaying a List of Values

This image is described in the surrounding text

7.15 Adding a ModelDrivenColumnComponent Subcomponent to Your ADF Table Component

You can add a ModelDrivenColumnComponent subcomponent to an ADF Table component. The value of ModelDrivenColumnComponent is determined by the Control Type hint specified for each attribute on the server.

At design time, for a column, specify the subcomponent type as ModelDrivenColumnComponent for the UpdateComponent or InsertComponent properties. At runtime, if there is a model-driven list associated with the attribute, then the column uses a dropdown list using the TreeNodeList subcomponent.

Note:

If there is no model-driven list associated with the attribute, or if any non-list-based control type is specified, then the column uses an Input Text subcomponent. If there is a model-driven list whose control type is input_text_lov or combo_lov, then the column uses an Input Text subcomponent.

For more information about creating a model-driven list, see the "How to Create a Model-Driven List" section of the Developing Fusion Web Applications with Oracle Application Development Framework.

Support for Dependent List of Values

When multiple ModelDrivenColumnComponent list subcomponents are exposed in an ADF Table component, then for each list ADF Desktop Integration determines whether it depends on another model-driven list. It verifies that the bind variable specified for a list references an attribute bound to another list.

If the list depends on another model-driven list, the subcomponent's DependsOnList value is set automatically at runtime.

As server-side list binding dependencies are determined only for lists in the same tree node, the following tree node list bindings are not supported:

  • A binding that depends on a list binding in a different tree or tree node

  • A binding that depends on a list binding in the page definition file

7.16 Adding a Dynamic Column to Your ADF Table Component

You can add dynamic columns to an ADF Table component so that the ADF Table component expands or contracts at runtime depending on the available attributes returned by the view object. The DynamicColumn property of the Columns group in the TableColumn array controls this behavior. To make a column dynamic, set the DynamicColumn property to True. A dynamic column in the TableColumn array is a column that is bound to a tree binding or a tree node binding whose attribute names are not known at design time. A dynamic column can expand to more than a single worksheet column at runtime.

The ADF Table component's dynamic column supports the following subcomponent types:

  • Input Text

  • Output Text

  • ModelDrivenColumnComponent

Note:

ADF Desktop Integration does not support the subcomponent type TreeNodeList in a dynamic column.

Support for Model-Driven List of Values

You can also configure a dynamic column to support the List of Values subcomponent where the subcomponent type is determined from model configuration at runtime. At design time, specify the subcomponent type as ModelDrivenColumnComponent for the UpdateComponent or InsertComponent properties. At runtime, during dynamic column expansion, the model-driven runtime component is determined before caching the list of values. The remote servlet allows the client to retrieve Model configuration, allowing the client to choose the desired column subcomponent type. For more information about ModelDrivenColumnComponent, see Section 7.15, "Adding a ModelDrivenColumnComponent Subcomponent to Your ADF Table Component."

7.16.1 How to Configure a Dynamic Column

You configure a dynamic column by specifying an EL expression with the following format for the Value property of the component specified by the ADF Table component column's InsertComponent property as a subcomponent:

#{bindings.TreeID.[TreeNodeID].AttributeNamePrefix*.inputValue}

or:

#{bindings.TreeID.AttributeNamePrefix*.inputValue}

where:

  • TreeID is the ID of the tree binding used by the ADF Table component

  • TreeNodeID is an optional value that specifies the tree node binding ID. If you omit this value, all matching attributes from the tree binding display regardless of which tree node binding the attribute belongs to.

  • AttributeNamePrefix identifies a subset of attributes that exist within the tree binding's underlying iterator. If you do not specify a value for AttributeNamePrefix, all attributes for the tree binding or tree binding node are returned. Always use the * character.

Note:

While adding a dynamic column, ensure that tree node attribute names are not specified in the page definition file. At runtime, the tree node object returns all attribute names from the underlying iterator. If there are attribute names specified in the page definition file, the tree node object limits the list of available attribute names based on that list.

The following example returns all attributes that begin with the name "period" in the model.EmpView node of the EmpTree binding:

#{bindings.EmpTree.[model.EmpView].period*.inputValue}

7.16.2 What Happens at Runtime: How Data Is Downloaded or Uploaded In a Dynamic Column

When the ADF Table component's Download or DownloadForInsert action is invoked, the ADF Table component automatically updates the dynamic columns so that they contain an up-to-date set of matching attributes. For each invocation of Download, ADF Desktop Integration requires that all rows must have the same set of attributes for the dynamic column. It may generate errors if the set of attributes changes from row to row during Download.

If a dynamic column supports both Insert and Update operations, you should specify the same EL expression for the Value properties of the dynamic column's InsertComponent and UpdateComponent subcomponents. At runtime, the ADF Table component expands to include a dynamic column that displays the value of the attribute binding returned by the EL expression.

When the ADF Table component's Upload action is invoked, the workbook prompts the end user to determine if the end user wants to continue to upload data when the previously downloaded attributes no longer exist in the tree binding.

Note:

If an Excel AutoFilter is applied on a table with dynamic columns, the AutoFilter is automatically removed every time the dynamic columns are expanded, collapsed, or adjusted.

Support for View Objects with Declarative SQL Mode

To support view objects that are configured with declarative SQL mode and customized at runtime, expose a tree binding in the page definition file that has no attributes defined. For example:

<tree IterBinding="DeclSQLModeIterator" id="DeclSQLModeTree">
    <nodeDefinition Name="DeclSQLModeTreeNode"/>
</tree>

At runtime, the tree binding will return the selected attributes from the underlying declarative SQL mode view object to the integrated Excel worksheet.

7.16.3 How to Specify Header Labels for Dynamic Columns

Use the following syntax to write EL expressions for the HeaderLabel property of a dynamic column:

#{bindings.TreeID.[TreeNodeID].hints.AttributeNamePrefix*.label}

or:

#{bindings.TreeID.hints.AttributeNamePrefix*.label}

Specify the same tree binding ID, tree node binding ID, and attribute name prefix values in the HeaderLabel property of the dynamic column as the values you specify for the Value properties of the dynamic column's InsertComponent and UpdateComponent if the dynamic column supports Insert and Update operations.

Note:

The ADF Table component ignores the value of a column's Visible property when you configure a column to be dynamic. For more information about ADF Table component column properties, see Table A-13.

If you want the mandatory columns, where the end user must enter a value, to be marked with a character or a string, you must configure the HeaderLabel property. Use the following syntax to write EL expression to add a character or string to all mandatory columns:

=IF(#{bindings.TreeID.[TreeNodeID].hints.*.mandatory}, "<prefix_for_mandatory_cols>", "") & "#{bindings.TreeID.[TreeNodeID].hints.*.label}"

For example, the following EL expression adds an asterisk (*) character to the mandatory columns label:

=IF(#{bindings.MyTree.[myapp.model.MyChildNode].hints.*.mandatory}, "* ", "") & "#{bindings.MyTree.[myapp.model.MyChildNode].hints.*.label}"

7.16.4 How to Specify Styles for Dynamic Columns According to Attribute Data Type

You can specify different styles for each data type according to the data type of the column. Use the following syntax to write EL expressions for the CellStyleName property of a dynamic column:

=IF("#{bindings.TreeID.[TreeNodeID].hints.*.dataType}"="<data_type>", <custom_style_expression1>, <custom_style_expression2>)

In the following example, the MyDateStyle style is applied to all date columns, and MyDefaultStyle is applied to other data type columns:

=IF("#{bindings.MyTree.[myapp.model.MyChildNode].hints.*.dataType}"="date", "MyDateStyle", "MyDefaultStyle")

The following example shows another scenario where the MyDateStyle style is applied to all date data type columns, MyNumberStyle is applied to all number data type columns, and MyDefaultStyle is applied to other data type columns:

=IF("#{bindings.MyTree.[myapp.model.MyChildNode].hints.*.dataType}"="date", "MyDateStyle", IF("#{bindings.MyTree.[myapp.model.MyChildNode].hints.*.dataType}"="number", "MyNumberStyle", "MyDefaultStyle"))

For more information about EL expressions, see Appendix B, "ADF Desktop Integration EL Expressions."

7.17 Configuring Column Widths in an ADF Table Component

You can configure column widths of an ADF Table component so that they are automatically resized at runtime. The columns can be resized using Excel's AutoFit column width feature, which determines the width based on the data values in the column. ADF Desktop Integration can also resize the columns using explicit width values derived from EL expressions.

The resizing behavior of ADF Table columns is configured at the table level. You can then override them at the column level.

7.17.1 How to Configure an ADF Table Component to Resize Columns at Runtime

You can use the design-time ResizeColumnsMode property to specify the common resizing behavior for all columns of the ADF Table component. Use the ResizeColumns table component method to control when the resizing occurs. To override resizing behavior of a particular column, use the column's ResizeMode property.

Resizing a column's width at runtime is a two-step process. First, you need to configure the table column with the desired width-related property values. Then, add ResizeColumns to the desired action sets.

Before you begin:

It may be helpful to have an understanding of configuring resizing behavior of ADF Table columns. For more information, see Section 7.17, "Configuring Column Widths in an ADF Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure resizing behavior of ADF Table columns:

  1. Open the integrated Excel workbook.

  2. Select any cell in the ADF Table component and click the Edit Properties button in the Oracle ADF tab.

  3. In the Edit Component: ADF Table dialog, configure and set the ResizeColumnsMode property as described in the Table 7-10:

    Table 7-10 ResizeColumnsMode Values of the ADF Table Component

    Value Description

    UseColumnValue

    Default. The table is not automatically resized at runtime, unless individual columns have Column.ResizeMode properties set to a value other than InheritFromTable.

    AutoFitAllWithHeader

    All columns within the table boundaries are resized to best fit using Excel's AutoFit support. Data values in the columns' cells, including the header cells, are used to determine the best fit.

    Individual columns that have Column.ResizeMode properties set to a value other than InheritFromTable are resized accordingly.

    Note that values in the column's cells above or below the table are not considered when finding the best fit.

    AutoFitAllWithoutHeader

    All columns within the table boundaries are resized to best fit using Excel's AutoFit support. Data values in the columns' cells, excluding the header cells, are used to determine the best fit.

    Individual columns that have Column.ResizeMode properties set to a value other than InheritFromTable are resized accordingly.

    Note that values in the column's cells above or below the table are not considered when finding the best fit.

    Figure 7-17 shows the ResizeColumnsMode property at design-time.

    Figure 7-17 ResizeColumnsMode Property in design-time

    This image is described in the surrounding text

  4. To configure the resizing behavior of a column and override the table-level resizing behavior, set the ResizeMode property.

    In the Edit Component: ADF Table dialog, expand the Columns property and set the ResizeMode property as described in the Table 7-11:

    Table 7-11 ResizeMode Values of the ADF Table Column Property

    Value Description

    Manual

    The column is not resized; column width is left at the current setting.

    InheritFromTable

    Default. The column is resized based on the table's ResizeColumnsMode setting.

    If ResizeColumnsMode is set to UseColumnValue, then no resizing occurs.

    AutoFitWithHeader

    Including the header cell, the column is resized to best fit using the Excel's AutoFit support.

    AutoFitWithoutHeader

    Excluding the header cell, the column is resized to best fit using the Excel's AutoFit support.

    SpecifiedWidth

    ADF Desktop Integration uses the Width property to determine the desired width of the column. You can specify a numerical value, or an EL expression.

    Figure 7-18 shows the ResizeMode property of four different columns of the ADF Table component of EditCustomers-DT.xlsx at design-time.

    Figure 7-18 ResizeMode Property in design-time

    This image is described in the surrounding text

  5. If ResizeColumnsMode is set to UseColumnValue, and ResizeMode is set to SpecifiedWidth, set Column.Width to the number of characters you want to display in the column.

    A given column's Width property may be set to a literal numerical value or an EL expression that evaluates to a number between 1 and 255, inclusive. If the expression cannot be evaluated, or if the expression evaluates to less than 1 or greater than 255, the ResizeMode is considered to be Manual and the column is not resized. An example EL expression for Width that makes use of the UI Hint displayWidth for an attribute is:

    #{bindings.Customers.hints.Name.displayWidth}

  6. Click OK.

7.17.2 How to Configure an Action Set to Resize Columns of an ADF Table Component at Runtime

You can configure the action set in a worksheet ribbon command, ADF Button component, or a worksheet event to invoke the ADF Table component ResizeColumns action.

Note that resizing a table with many columns and many rows might take a noticeable amount of time.

Before you begin:

It may be helpful to have an understanding of configuring resizing behavior of ADF Table columns. For more information, see Section 7.17, "Configuring Column Widths in an ADF Table Component" and Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To configure an action set to resize Columns of an ADF Table component:

  1. Open the integrated Excel workbook.

  2. Click the Worksheet Properties button in the Oracle ADF tab, and add a ribbon command. For more information about adding a ribbon command in a worksheet, see Section 8.3.1, "How to Define a Workbook Command Button for the Runtime Ribbon Tab."

    Note:

    Instead of adding a ribbon command, you can configure a worksheet event or an Oracle ADF component (a button, for example) to invoke the action set at runtime.

  3. Open the Edit Action dialog to configure an action set. For more information about invoking action sets, see Section 8.2, "Using Action Sets."

  4. Add the ADF Table component ResizeColumns action to the list of actions that the action set invokes at runtime. Note that ResizeColumns is a component action.

  5. Click OK.

Figure 7-19 shows the ResizeColumns action in EditCustomers-DT.xlsx at design-time.

Figure 7-19 ResizeColumns Action

This image is described in the surrounding text

7.17.3 What Happens at Runtime: How the ADF Table Columns are Resized

The ADF Table columns are resized as the result of running of Table.ResizeColumns component action in an action set (see Section 7.17.2, "How to Configure an Action Set to Resize Columns of an ADF Table Component at Runtime").

7.17.4 What You May Need to Know About Resizing Columns of an ADF Table Component at Runtime

The entire worksheet columns containing the ADF Table component columns are resized depending on the values in the Table.ResizeColumnsMode and Column.ResizeMode properties. Resizing the table columns affects the contents of the cells or any other components (such as form components) located in the same Excel worksheet column outside of the table's boundaries.

If a worksheet contains two or more ADF Table components configured with action sets to be resized at runtime, each table attempts to resize the Excel worksheet columns independently, but the table's ResizeColumns action that runs last sets the column width.

Tip:

For worksheets that contain more than one ADF Table component, call the ResizeColumns action only on the primary table

Notes:

  • The Column.Width property does not support row-specific bindings.

  • A common strategy is to call ResizeColumns after one of the Download actions. See Figure 7-19 for an example.

  • Excel internally rounds the specified Width values to the nearest whole pixel value. For example, a value of 8.5 characters rounds to 8.43, which equates to 64 pixels.

  • Using one of the AutoFit resizing modes on cells that have Wrap Text selected in their style definition may not resize as expected. Using SpecifiedWidth mode, or removing the Wrap Text setting from the style, may produce better results.

7.18 Displaying Tooltips in ADF Desktop Integration Table-Type Components

You can add tooltips to table column headers of ADF Table and ADF Read-only Table components. Note that the tooltip is always initially empty for headers of special columns of the ADF Table component.

To add a tooltip to an table column header:

  1. Open the integrated Excel workbook.

  2. If the Table-type component has already been inserted in the Excel worksheet, click any cell of the table, and click Edit Properties in the Oracle ADF tab.

    To insert a Table-type to the worksheet, select the cell where you want to anchor the component. In the components palette or the bindings palette, select the Table-type component or the binding, and click Insert Component or Insert Binding.

  3. In the Edit Component: ADF Table dialog, expand the Columns property. Click the browse (...) icon of the Tooltip property of the desired column, and enter the tooltip message.

    Figure 7-20 shows the tooltip EL expression for an ADF Table column header in EditCustomers-DT.xlsx.

    Figure 7-20 Tooltip for ADF Table Column Header at Design-time

    This image is described in the surrounding text

  4. Click OK.

Figure 7-21 shows the tooltip message at runtime. Notice the small red arrow at the top-right of the header cell. It indicates the header cell has a comment. Hover mouse pointer over the cell to see the tooltip message.

Figure 7-21 Tooltip for ADF Table Column Header at Runtime

This image is described in the surrounding text

7.18.1 What You May Need to Know About Tooltips for Table Columns

The tooltips for column headers are evaluated and rendered when the table column headers are rendered including first time table initialization, Table.Initialize, and Table.Download actions.

By default, the tooltips for special columns are empty. If the Tooltip property of a special column is set to a non-empty expression, it is trimmed of whitespace, and inserted into the target cell as an Excel comment.

To get a unique tooltip for each expanded dynamic column at runtime, enter the expression in the following syntax in the ToolTip property:

#{bindings.<TreeID>.hints.*.tooltip}

At runtime, the dynamic column expands to the available set of attributes in the specified tree or the node. ADF Desktop Integration also retrieves the corresponding tooltip values and applies each one to the appropriate column using the rules described above.

For more information about tooltips, see Section 6.9.1, "What You May Need to Know About Tooltips." You can also add tooltips to the headers of special columns of the table components (see Section 7.12, "Special Columns in the ADF Table Component") and the dynamic columns (see Section 7.16, "Adding a Dynamic Column to Your ADF Table Component")

7.19 Creating an ADF Read-Only Table Component

At runtime, the ADF Read-only Table component renders a table across a continuous range of cells that displays data from the tree binding that the ADF Read-only Table component references. Use this component to display data that you do not want the end user to edit.

This component supports several properties, such as RowLimit, that determine how many rows the component downloads when it invokes its Download action. It also includes a group of properties (Columns) that determine what columns from the tree binding appear at runtime in the Excel worksheet. The TreeID property specifies the tree binding that the component references. More information about these properties and others that the ADF Read-only Table component supports can be found in Section A.13, "ADF Read-only Table Component Properties and Actions."

ADF Desktop Integration uses the first column of a worksheet (column A) to store some special information needed for the proper operation of Read-only Table components. Therefore, at runtime, column A is hidden for any worksheet that contains at least one instance of ADF Read-only Table component. The content of the worksheet are shifted by one column in the right direction to compensate for the hiding of column A, and maintain the visual layout from design time. Upon returning to design time, column A is unhidden and the worksheet contents are shifted back one column in the left direction.

Figure 7-22 shows the ADF Read-only Table component at design time with the property inspector in the foreground.

Figure 7-22 ADF Read-only Table Component at Design Time

This image is described in the surrounding text

Figure 7-23 shows the columns that an ADF Read-only Table component which references the Customers tree binding at runtime.

Figure 7-23 Columns in an ADF Read-only Table Component at Runtime

This image is described in the surrounding text

Notes:

  • At runtime, inserting a row into the ADF Read-only Table component results in a new Excel row that behaves as if it is part of the downloaded data set, but the new row exists only in Excel. The data from the new row is not uploaded to the server, and does not affect the Fusion web application data.

  • Read-only columns include double-click action sets. However, these actions cannot reliably position on the current row. So, the results of using row-level action sets with the ADF Read-only Table component is not consistent.
    If you need to use row-level action sets with reliable row positioning, use the ADF Table component instead of the ADF Read-only Table component.

7.19.1 How to Insert an ADF Read-only Table Component

You use the ADF Desktop Integration Designer task pane to insert an ADF Read-only Table component into a worksheet.

Before you begin:

It may be helpful to have an understanding of ADF Read-only Table component. For more information, see Section 7.19, "Creating an ADF Read-Only Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To insert an ADF Read-only Table component:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet where you want to anchor the component.

    When inserting a table component, you must ensure that the data of two tables does not overlap at runtime, and the selected cell is not a merged cell

  3. In the bindings palette, select the binding to create the ADF Read-only Table component, and then click Insert Binding.

  4. In the dialog that appears, select ADF Read-only Table.

    Note:

    You can also insert an ADF Read-only Table component by using the components palette or Oracle ADF tab. Select ADF Read-only Table and click Insert Component. If you use the components palette to create the component, you would have to add each column to appear in the component at runtime.

  5. Configure properties in the property inspector that appears to determine the columns to appear and the actions the component invokes at runtime.

  6. Click OK.

    Note:

    You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector.

    To remove the table component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."

7.19.2 How to Manually Add a Column to the ADF Read-only Table Component

You can manually add additional columns to an ADF Read-only Table component or re-add columns that you previously removed.

Before you begin:

It may be helpful to have an understanding of ADF Read-only Table component. For more information, see Section 7.19, "Creating an ADF Read-Only Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To manually add a column to the ADF Read-only Table component:

  1. Open the integrated Excel workbook.

  2. Select the cell in the worksheet that hosts the ADF Read-only Table component and click the Edit Properties button in the Oracle ADF tab.

  3. In the Edit Component: ADF Table dialog, click the browse (...) icon beside the input field for Columns.

    The Edit Columns dialog appears, listing all the columns of the selected ADF Table component

  4. Click Add to add a new column to the ADF Read-only Table component.

  5. Set values for the properties of the new column.

    For information about the properties of an ADF Read-only Table component column, see Table A-16.

  6. Click OK.

7.20 Limiting the Number of Rows Your Table-Type Component Downloads

You can configure the number of rows that an ADF Table or ADF Read-only Table component downloads by setting values for the component's RowLimit group of properties. You can also display a warning message, if desired, that alerts the end user when the number of rows available to download exceeds the number of rows specified for download.

7.20.1 How to Limit the Number of Rows a Component Downloads

Specify the number of rows that the component downloads when it invokes its Download action as a value for the RowLimit.MaxRows property. Optionally, write an EL expression for the RowLimit.WarningMessage property so that the end user receives a message if the number of rows available to download exceeds the number specified by RowLimit.MaxRows.

Before you begin:

It may be helpful to have an understanding of how to limit the number of rows while downloading data in your ADF Table component. For more information, see Section 7.20, "Limiting the Number of Rows Your Table-Type Component Downloads."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To limit the number of rows a table-type component downloads:

  1. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that references the table-type component and click the Edit Properties button in the Oracle ADF tab.

    For more information, see Section 8.2, "Using Action Sets."

  3. Configure properties for the RowLimit group of properties, as described inTable 7-12. For more information about these properties, see Section A.1, "Frequently Used Properties in the ADF Desktop Integration."

    Table 7-12 RowLimit Group of Properties

    Set this property to... This value...

    RowLimit.Enabled

    Set to True to limit the number of rows downloaded to the value specified by RowLimit.MaxRows.

    RowLimit.MaxRows

    Specify an EL expression that evaluates to the maximum number of rows to download.

    RowLimit.WarningMessage

    Write an EL expression for this property to generate a message for the end user if the number of rows available to download exceeds the number specified by RowLimit.MaxRows.

    If the value for this property is null, the Download action downloads the number of rows specified by RowLimit.MaxRows displaying the default warning message to the end user.

  4. Click OK.

Figure 7-24 shows the Edit Component dialog in the EditCustomers-DT.xlsx workbook where the row limit of an ADF Table component is configured.

Figure 7-24 Limiting Number of Rows of an ADF Table Component

This image is described in the surrounding text

7.20.2 What Happens at Runtime: How the RowLimit Property Works

When invoked, the Table-type component's Download action downloads the number of rows that you specified as the value for RowLimit.MaxRows from the Fusion web application. A message dialog similar to the one in Figure 7-25 appears if you specify an EL expression for RowLimit.MaxRows or do not modify its default value.

Figure 7-25 Row Limit Exceeded Warning Message

This image is described in the surrounding text

7.21 Clearing the Values of Cached Attributes in an ADF Table Component

The RowData group of properties described in Table A-12 allow you to specify data to cache in the ADF Table component. For more information about this functionality, see the following:

The ADF Table component exposes an action (ClearCachedRowAttributes) that, when invoked, clears the values of cached attributes for the current row of the ADF Table component.

Do not configure a component (for example, an ADF Table component's column or an ADF Input Text component) so that an end user can view or edit an attribute binding that you have also specified for an element in the RowData.CachedAttributes array. The RowData.CachedAttributes array caches the values retrieved by the worksheet DownSync action. The worksheet UpSync action sends the values of the RowData.CachedAttributes array to the Fusion web application. This may override edits an end user makes to an attribute binding exposed through a component in the worksheet.

7.21.1 How to Clear the Values of Cached Attributes in an ADF Table Component

Configure a DoubleClickActionSet that includes an action to invoke the ADF Table component's ClearCachedRowAttributes action.

Before you begin:

It may be helpful to have an understanding of how to clear cached attributes in an ADF Table component. For more information, see Section 7.21, "Clearing the Values of Cached Attributes in an ADF Table Component."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To clear the values of cached attributes in an ADF Table component:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog for the Oracle ADF component that is going to invoke the DoubleClickActionSet at runtime.

    For more information about invoking action sets, see Chapter 8, "Using Action Sets."

  3. Add an action to the DoubleClickActionSet that invokes the ADF Table component's ClearCachedRowAttributes action.

  4. Click OK.

7.21.2 What Happens at Runtime: How the ADF Table Component Clears Cached Values

The action set invokes the ADF Table component's ClearCachedRowAttributes action. This action clears the cached values specified by the RowData.CachedAttributes property for the current row of the ADF Table component.

7.22 Tracking Changes in an ADF Table Component

End users can create or modify data in the cells of an integrated Excel workbook that hosts an ADF Table component.

If a column is updatable and not read-only, change tracking is activated. End users can make the following changes to activate change tracking:

  • Edit cell values

  • Insert or delete cell values

  • Paste values to cells in the ADF Table component column that they copied elsewhere

A character that resembles an upward pointing arrow appears in a row of the _ADF_ChangedColumn column if the end user makes a change to data in a corresponding row. Figure 7-26 shows an example.

Figure 7-26 Changed Column in an ADF Table Component

This image is described in the surrounding text

This character appears if the end user makes a change to data hosted by a component where the component's ReadOnly property value is False. The ADF Input Text and TreeNodeList subcomponents both have a ReadOnly property. You can write an EL expression or a static string for this ReadOnly property that evaluates to True or False. If you write a static string or an EL expression that evaluates to True, no character appears in the _ADF_ChangedColumn column. For more information about ReadOnly EL expressions and change tracking, see Section 7.23, "Evaluating EL Expression for ReadOnly Properties."

7.23 Evaluating EL Expression for ReadOnly Properties

If a table column's ReadOnly EL expression contains a binding expression (for example, #{row.bindings.color.inputValue}), the runtime evaluation of that expression will be different depending on when the evaluation occurs. The evaluation happens during the following:

  • downloading data (Download, DownloadFlaggedRows, DownloadForInsert)

  • uploading data (Upload), and change tracking

7.23.1 What Happens at Runtime: Evaluating EL Expression While Downloading Data

During Download, the EL expression is evaluated with the current binding value as expected.

7.23.2 What Happens at Runtime: Evaluating EL Expression While Uploading Data or Tracking Changes

During Upload, or when the end user changes values in the editable table, the EL expression is evaluated differently than Download. Specifically, an empty string is substituted for the binding expression prior to evaluation of the EL expression.

For example, if you have the following EL expression in an editable cell:

=IF("#{row.bindings.color.inputValue}"="RED", True, False)

During Upload, or when the end user changes values in the editable table, the EL expression evaluates to =IF(""="RED", True, False), and always returns False.

7.23.3 What You May Need to Know About Evaluating EL Expression While Uploading Data or Tracking Changes

During Upload and change tracking, an extra round trip to the server would be required to retrieve the binding values, in order to evaluate the EL expression properly. The extra round trip to the server would impact performance negatively, and could even require a new login if the end user did not have a currently valid session.

Note:

The same EL expression evaluation behavior also applies to the CellStyleName EL expression property when inserting new worksheet rows during table change tracking.

Due to the difference in behavior, if possible, you should avoid ReadOnly EL Expressions that contain binding expressions. However, if it is important for a given use case to use an attribute value in the ReadOnly expression, you should consider setting the worksheet protection to Automatic. For more information about worksheet protection, see Section 9.7, "Using Worksheet Protection."

For example, if you have the following EL expression in a cell:

=IF("#{row.bindings.color.inputValue}"="RED", True, False)

During Download, the RED cells in this column will be set to Locked and the end user will not be able to edit those cells.

7.24 Using Explicit Worksheet Setup Action

ADF Desktop Integration provides several features for configuring or customizing a worksheet after the binding container's metadata has been obtained from the server at runtime. However, at times, you might want to customize the data or the binding container before the client retrieves the binding container metadata. For example, at design time, you might want to add a table to the worksheet, but without specifying the View Object that will drive that table, until runtime. This would be desirable if the View Object to be used depends on some parameter values or settings that are not known until runtime. In addition, you might want to customize the View Object based on runtime parameter values (such as add attributes, or indicate which attributes to display). Similarly, you may also want to configure the binding container based on runtime parameter values. Such use cases require performing setup tasks before the binding container metadata is sent from the sever to the worksheet.

Using the Explicit Worksheet Setup Action feature of ADF Desktop Integration, you can specify a setup action that is invoked before the client retrieves the binding container metadata.

7.24.1 How to Configure Explicit Worksheet Setup Action

Using the SetupActionID property of the worksheet, you can specify a method that is invoked before the binding container metadata is sent to the worksheet. In the method, you can implement the logic necessary for any customization on the data and binding container.

Before you begin:

It may be helpful to have an understanding of the Explicit Worksheet Setup Action feature. For more information, see Section 7.24, "Using Explicit Worksheet Setup Action."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 7.1.2, "Additional Functionality of Table-Type Components."

To use SetupActionID property of the worksheet:

  1. Open the worksheet in the integrated Excel workbook.

  2. From the Excel Ribbon, click Workbook Properties.

  3. In the Edit Worksheet Properties dialog, expand Data and click the browse icon (...) beside the input field for the SetupActionID property, as shown in Figure 7-27.

    Figure 7-27 SetupActionID Property in Edit Worksheet Properties Dialog

    This image is described in the surrounding text

  4. In the Select Binding dialog, select the action that you want to invoke before the binding container metadata is sent to the worksheet, and click OK.

    Note:

    The SetupActionID property accepts ADFm actions only. Validation error is reported if an invalid method is set for the property.

  5. Click OK to close the Edit Worksheet Properties dialog.

7.24.2 What You May Need to Know About Explicit Worksheet Setup Action

After the action specified in the SetupActionID property runs, the binding container metadata that is sent to worksheet reflects the customization configured in the method. ADF Desktop Integration ensures that the setup action runs only once for any binding container instance. If, for any reason, a new binding container instance becomes associated with the worksheet, the setup action will be invoked again, to ensure it is configured.

If any kind of failure occurs during the invoking of the setup action, ADF Desktop Integration is automatically disabled in the worksheet. Logging out, and then logging in, will not enable ADF Desktop Integration in the worksheet. Running Clear All Data command from the Excel Ribbon re-enables ADF Desktop Integration in the worksheet, the setup action runs again on subsequent requests.