12 Adding Validation to an Integrated Excel Workbook

This chapter describes how to provide validation for your integrated Excel workbook

This chapter includes the following sections:

• Section 12.1, "About Adding Validation to an Integrated Excel Workbook"

• Section 12.2, "Providing Server-Side Validation for an Integrated Excel Workbook"

• Section 12.3, "Providing Client-Side Validation for an Integrated Excel Workbook"

• Section 12.4, "Error Reporting in an Integrated Excel Workbook"

• Section 12.5, "Providing a Row-by-Row Status on an ADF Table Component"

• Section 12.6, "Adding Detail to Error Messages in an Integrated Excel Workbook"

• Section 12.7, "Handling Data Conflicts When Uploading Data from a Workbook"

12.1 About Adding Validation to an Integrated Excel Workbook

You configure server-side and client-side validation for the Fusion web application and the integrated Excel workbook to make use of the validation options offered by the ADF Model Layer and Microsoft Excel. In addition to these validation options, you can make use of components in ADF Desktop Integration to return error messages from the Fusion web application, to provide status on the results of component actions, and to manage errors that may occur when data is changed in an integrated Excel workbook conflict with data hosted by the Fusion web application.

Adding validation to your integrated Excel workbook gives you several benefits. You can create validation rules in your Fusion web application and in your integrated Excel workbook to validate data entry by the end user.

12.1.1 Integrated Excel Workbook Validation Use Cases and Examples

Validation rules protects the server by stopping invalid data to get uploaded. For example, using worksheet and table validation rules, you can capture invalid data when you upload it to the server, as shown in Figure 12-1.

Figure 12-1 Workbook and Table Validation Errors at Runtime

12.1.2 Additional Functionality for Adding Validation to an Integrated Excel Workbook

After you have applied validation rules in your integrated Excel workbook, you may find that you need to add additional functionality to configure your workbook. Following are links to other functionalities that you can use:

• Testing integrated Excel workbook: Before publishing and deploying your integrated Excel workbook, you must test it. For more information, see Chapter 13, "Testing Your Integrated Excel Workbook."

• Publishing and deploying integrated Excel workbook: The final step after you design and validate your integrated Excel workbook is to publish and deploy it. For more information, see Chapter 14, " Deploying Your Integrated Excel Workbook."

12.2 Providing Server-Side Validation for an Integrated Excel Workbook

ADF Desktop Integration uses the validation rules that the ADF Model Layer sets for a binding's attributes. Data that the end user enters or edits in one of the ADF Desktop Integration components, such as the ADF Table component, can be validated against set rules and conditions in the Fusion web application. For general information about defining validation rules in Oracle ADF, see the "Defining Validation and Business Rules Declaratively" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

For information about adding ADF Model layer validation, see the "Adding ADF Model Layer Validation" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

12.3 Providing Client-Side Validation for an Integrated Excel Workbook

ADF Desktop Integration does not provide client-side validation. However, you can use Excel's data validation features to control the type of data or the values that end users enter into a cell. These features allow you to restrict data entry to a certain range of dates, limit choices by using a list, or ensure that only positive whole numbers are entered in a cell. For example, you could configure the Product Number column in the EditPriceList-DT.xlsx workbook so that users can enter only whole numbers in the cells of this column.

If you apply custom validation to columns within an ADF Table component, the validation is propagated when the ADF Table component's columns are populated at runtime. Note, however, that ADF Desktop Integration overwrites at runtime any custom validation applied to columns that reference the TreeNodeList subcomponent at design time. This is because ADF Desktop Integration applies its own list-constraint validation, which is invoked at runtime.

Note:

• Excel displays error messages when a validation fails; these error messages cannot be localized.

• ADF Desktop Integration does not support server-side validation warnings. Validation warnings, set for rules defined in the Fusion web application, are not displayed by the integrated Excel workbook.

For more information about data validation in Excel, see Excel's documentation.

12.4 Error Reporting in an Integrated Excel Workbook

The server that hosts the Fusion web application you integrate your Excel workbook with can return error messages to end users that provide feedback on the results of operations. The error messages returned can be one many types: validation failures, conflict errors, deleted records, and so on.

12.4.1 Error Reporting Using EL Expressions

To return error message summaries to end users, you must set an EL expression for the Value property of an ADF Output Text component. At runtime, the ADF Output Text component displays the error message summary to the end user if an error occurs.

The type of EL expression that you set for the Value property of the ADF Output Text component depends on whether you want to return error message summaries generated by action sets invoked on a worksheet, or by actions invoked by other components such as the ADF Table and ADF Read-only Table components. The following EL expression displays error message summaries which are returned during the invocation of an action set on a worksheet:

#{worksheet.errors}

At runtime, the previous error message summary is cleared (if one existed) when the action set starts the invocation. If no errors occur during invocation, error message remains blank. If an error occurs, the ADF Output Text component displays the error message summary.

An alternative approach to returning error message summaries generated by action sets invoked on a worksheet is to set #{worksheet.errors} as the value for an action set's Alert.FailureMessage property. This approach displays the generated error message summary in a dialog.

Components such as the ADF Table and ADF Read-only Table components that have actions which interact with the Fusion web application can also return error message summaries. Set the following EL expression for the Value property of the ADF Output Text component or for an action set's Alert.FailureMessage property:

#{components.componentID.errors} 

where componentID refers to the ID of the component (ADF Table or ADF Read-only Table component) that invokes the action.

The EditPriceList-DT.xlsx file in the Master Price List module of the Fusion Order Demo application demonstrates how to return error message summaries generated by action sets invoked on a worksheet and by the actions of an ADF Table component. Figure 12-2 shows these EL expressions in design mode.

Figure 12-2 EL Expressions to Return Error Messages in an ADF Output Text Component

12.4.2 Error Reporting Using Component Actions

ADF Desktop Integration provides actions that display error details generated by an ADF Table component or an integrated Excel worksheet.

The action set in which you invoke one of these actions must include only one action. In general, action sets clear error labels and message lists when invoked. An action set that invokes one of the following actions returns error labels and message lists to the end user:

• Worksheet's DisplayWorksheetErrors action

  To display a worksheet's error messages, configure the action set of a component on the worksheet or the worksheet ribbon button to invoke this action. For example, Figure 12-3 shows the Edit Actions dialog configuring the DisplayWorksheetErrors action as a DoubleClickActionSet item for an ADF Output Text component on the worksheet.

  Figure 12-3 DisplayWorksheetErrors Action

  
  

  At runtime, double-clicking the ADF OutputText component invokes the DisplayWorksheetErrors action as shown in Figure 12-4.

  Figure 12-4 Runtime View of DisplayWorksheetErrors action

  
  

  For more information about the Worksheet's DisplayWorksheetErrors action, see Section A.13, "Worksheet Actions and Properties."

• ADF Table component's DisplayRowErrors action

  To display row-level failures that occur in an ADF Table component, invoke this action. Row-level failures occur when end user invokes the following actions:

  • Upload

  • DeleteFlaggedRows

  • DoubleClickActionSet invoked from an ADF Table component column

  For more information about using this action, see Section 12.5, "Providing a Row-by-Row Status on an ADF Table Component."

• ADF Table component's DisplayTableErrors action

  To display table-level failures that occur in an ADF Table component, invoke this action. It is not intended that an ADF Table component column's DoubleClickActionSet invoke this action. Instead add this action to an action set that returns error messages to end users when failures occur during invocation of the action binding specified by an ADF Table component's BatchOptions.CommitBatchActionID property.

  At runtime, double-clicking the ADF OutputText component invokes the DisplayTableErrors action as shown in Figure 12-5.

  Figure 12-5 Runtime View of DisplayTableErrors action

  
  

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

12.5 Providing a Row-by-Row Status on an ADF Table Component

The ADF Table component provides a mechanism to indicate to end users whether rows from the ADF Table component have been processed successfully or not after invocation of following ADF Table component actions:

• DeleteFlaggedRows

• Upload

• DoubleClickActionSet invoked from an ADF Table component's column

The ADF Table component populates the _ADF_StatusColumn column with the status for each row following the invocation of the ADF Table component action. For example, it populates the _ADF_StatusColumn column with the upload status for each row following the invocation of the ADF Table component's Upload action.

Figure 12-6 shows rows in an ADF Table component where the values in those rows have been changed, as indicated by the upward pointing arrows in the Changed column. In the CostPrice column, two string values have been entered where a number value is expected.

Figure 12-6 ADF Table Component with Changed Rows Before Upload

Figure 12-7 shows the same rows in the ADF Table component after invocation of the ADF Table component's Upload action. The ADF Table component populates the _ADF_StatusColumn column (labeled Status in this example at runtime) with a message indicating whether the row updated successfully or not.

Figure 12-7 ADF Table Component with Changed Rows After Upload

By default, the _ADF_StatusColumn column's DoubleClickActionSet is configured to invoke the ADF Table component's DisplayRowErrors action. When end users double-click a row in this column at runtime, the ADF Table component invokes the DisplayRowErrors action. This action displays a dialog with a list of errors for that row if errors exist. If no errors exist, the dialog displays a message to indicate that no errors occurred. Figure 12-8 shows the dialog that appears if the end user double-clicks the cell in Figure 12-7 that displays Update failed in the Status column.

Figure 12-8 Dialog Displaying Row Error Message

For more information about the _ADF_StatusColumn column, see Section 7.11, "Special Columns in the ADF Table Component."

12.6 Adding Detail to Error Messages in an Integrated Excel Workbook

You can configure your Fusion web application to report errors using a custom error handler to provide more detail to the error messages displayed to end users in an integrated Excel workbook.

To implement this functionality, the custom error handler must override the getDetailedDisplayMessage method to return a DCErrorMessage object. At runtime, ADF Desktop Integration detects the custom error handler and invokes the getHtmlText method on the DCErrorMessage object. ADF Desktop Integration includes the HTML returned by the getHtmlText method in the error message list as detail.

For more information about creating a custom error handler, see the "Customizing Error Handling" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

12.7 Handling Data Conflicts When Uploading Data from a Workbook

If one of your end users (John) makes changes to a row of data that he downloaded from a Fusion web application to an Excel workbook and another end user (Jane) in a different session modifies the same row in the Fusion web application after John downloads the row, John may encounter an error when he attempts to upload the modified row, as his changes conflict with those that Jane made. Depending on the configuration of your Fusion web application, John may receive RowInconsistentException type error messages. For information about how to configure your Fusion web application to protect your data, see the "How to Protect Against Losing Simultaneously Updated Data" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

To resolve this conflict in the integrated Excel workbook, John needs to download the most recent version of data from the Fusion web application. However, invoking the ADF Table component's Download action causes the component to refresh all data that the component hosts in the Excel workbook. This may overwrite other changes that John made that do not generate conflict error messages. To resolve this scenario, you can expose the ADF Table component's DownloadFlaggedRows action. When invoked, this action downloads data only for the rows that the end user flags for download. Using this action, John can resolve the conflict issues and upload his modified data.

Chapter 15, "Using an Integrated Excel Workbook Across Multiple Web Sessions and in Disconnected Mode" provides information about using an integrated Excel workbook across multiple sessions. For information about flagging rows, see Section 7.10.2, "Row Flagging in an ADF Table Component." For information about invoking component actions, see Section 8.2.2, "How to Invoke Component Actions in an Action Set." For more information about the components that the ADF Table component supports, see Section A.9, "ADF Table Component Properties and Actions."

12.7.1 How to Configure a Workbook to Handle Data Conflicts When Uploading Data

You specify a row-specific attribute of the tree binding for the RowData.ChangeIndicatorAttribute property to determine whether a row has been modified by another user since the row was last downloaded by the ADF Table component.

To configure a workbook to handle data conflicts:

1. Open the integrated Excel workbook.

2. Select the cell in the Excel worksheet that references the ADF Table component and click Edit Properties in the Oracle ADF tab to display the Edit Component: ADF Table dialog.

3. For the RowData.ChangeIndicatorAttribute property, specify the row-specific attribute of the tree binding that you use to determine whether a row has been modified by another user since the row was last downloaded by the ADF Table component in your integrated Excel workbook.

4. Click OK.

12.7.2 What Happens at Runtime: How Data Conflicts Are Handled

The ADF Table component caches the original value of the row-specific attribute of the tree binding that you specified as a value for RowData.ChangeIndicatorAttribute when it invokes the RowDownSync action. When the ADF Table component invokes the RowUpSync action, it checks if the value of the binding hosted by the Fusion web application and the original value cached by the ADF Table component differ. If they differ, it indicates data conflict, as changes have been made to the value of the binding hosted by the Fusion web application since the ADF Table component downloaded the value of the binding.