13 Adding Validation to Your Integrated Excel Workbook
This chapter includes the following sections:
13.1 About Adding Validation to an Integrated Excel Workbook
You configure server-side and data entry validation for the Fusion web application and the integrated Excel workbook to make use of the validation options offered by the ADF Model layer, ADF Desktop Integration, 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 modification in an integrated Excel workbook conflicts 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.
13.1.1 Integrated Excel Workbook Validation Use Cases and Examples
Validation rules protect the server by preventing the upload of invalid data. ADF Desktop Integration provides both data entry validation and server-side validation capabilities. Figure 13-1 shows an example of server-side validation from the Summit sample application's EditCustomers-DT.xlsx workbook where an invalid zip code (12345x) fails an entity validation rule. This failure appears in the Status Viewer entry for the row that contains the invalid zip code.
Figure 13-1 Status Viewer Displaying Entity Validation Rule Failure
Description of "Figure 13-1 Status Viewer Displaying Entity Validation Rule Failure"
Figure 13-2 shows an example of a data entry validation failure from the same workbook where no value appears in a cell that requires a value.
Figure 13-2 Data Entry Validation Message
Description of "Figure 13-2 Data Entry Validation Message "
13.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. The following sections describe other functionality that you can use:
-
Testing integrated Excel workbook: Before publishing and deploying your integrated Excel workbook, you must test it. See 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. See Deploying Your Integrated Excel Workbook .
-
Trigger Server-side Validation in an ADF Table Component: ADF Table components can be configured to automatically refresh a row where an end user edits a value. Enabling this functionality is one way of triggering server-side validation. See Configuring Automatic Row Refresh in an ADF Table Component.
13.2 Using the Status Viewer to Report Error Messages to End Users
The Status Viewer displays information to end users in Excel's task pane. End users can use the information that appears to review and correct errors at the same time.
Information that the Status Viewer always displays includes the worksheet-level status of the current integrated worksheet. In addition, if the worksheet includes an ADF Table component and the currently selected cell is a row in the ADF Table component, the Status Viewer displays the status of the row.
Information about the result of action set invocation also appears in the Status Viewer. For example, an end user enters a value that violates a declarative validation rule in the Fusion web application's ADF Model layer. When the end user attempts to upload the change, a failure is reported for the failed row. In this scenario, the Status Viewer appears and displays a message about the validation failure.
Figure 13-3 shows the Status Viewer that appears in the EditCustomers-DT.xlsx workbook when an end user enters a zip code (12345x) that fails an entity validation rule defined in the Fusion web application's ADF Model layer. Selecting a cell anywhere in the table row that contains the validation failure causes the validation failure message to appear in the Status Viewer. The worksheet-level status that appears in the Status Viewer in Figure 13-3 indicates that the most recent action set from this worksheet completed successfully.
Integrated Excel workbooks that you create using this release of ADF Desktop Integration display the Status Viewer ribbon command in the Excel ribbon by default, as shown in Figure 13-4.
Figure 13-4 Status Viewer Ribbon Command in Excel Ribbon
End users click the Status Viewer ribbon command to display or hide the Status Viewer in Excel's task pane. By default, the Status Viewer appears automatically when integrated Excel workbooks encounter errors at runtime. You can configure this behavior for integrated Excel workbooks created using earlier releases so that they automatically display the Status Viewer when errors occur. See How to Manage the Automatic Display of the Status Viewer.
You add the Status Viewer ribbon command to the Excel ribbon by adding the ToggleStatusViewer workbook action, as described in How to Define a Workbook Ribbon Command for the Runtime Ribbon Tab. For information about workbook actions, see Workbook Actions and Properties.
Although you add the Status Viewer ribbon command the Excel ribbon as a workbook command, the Status Viewer is worksheet specific and displays information for the integrated Excel worksheet that is in focus. If your end users navigate to a non-integrated worksheet and click the Status Viewer ribbon command, a message appears that tells the end user the Status Viewer cannot be used in that worksheet.
13.2.1 How to Manage the Automatic Display of the Status Viewer
You set the value of the AutoDisplayStatusViewerEnabled workbook property to True or False to manage the automatic display of the Status Viewer in Excel's task pane.
Before you begin:
It may be helpful to have an understanding of the Status Viewer provided by ADF Desktop Integration. See Using the Status Viewer to Report Error Messages to End Users.
You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Adding Validation to an Integrated Excel Workbook.
To manage the automatic display of the Status Viewer:
13.3 Providing Data Entry Validation for an Integrated Excel Workbook
ADF Desktop Integration automatically performs basic data entry validation after end users modify cells bound to ADF components.
Basic data entry validation includes verifying the expected data type (for example, user entered a number for a numerical attribute) and that required fields are not empty. ADF Desktop Integration performs this validation as soon as end users leave the cell.
Metadata from the ADF Model layer is used to perform basic data entry validation. No additional workbook configuration is needed. You can disable this validation using the Compatibility.DataEntryValidationEnabled workbook properties described in How to Enable or Disable ADF Desktop Integration Data Entry Validation. ADF Desktop Integration enables basic data validation by default.
ADF Desktop Integration performs additional validation during upload. See Providing Server-Side Validation for an Integrated Excel Workbook.
One other validation that ADF Desktop Integration performs is to reject Excel cell error values that the integrated Excel workbook sends to the Fusion web application. An example of an Excel cell error is the #DIV/0! error that occurs when a number is divided either by zero (0) or by a cell that contains no value. Excel cell error values return large negative numbers. The #DIV/0! error, for example, returns -2146826281. ADF Desktop Integration rejects these values because they are unlikely to be appropriate for upload and, in the absence of ADF Model layer data validation, can be committed to the Fusion web application’s database.
An ADF Table component displays an Update failed message in its Status column when an ADF Table component performs an Upload, RowUpSync, or RowUpSyncNoFail action on a row with a cell containing an Excel cell error value. Similarly, a worksheet’s UpSync action fails to synchronize pending changes from cells that contains Excel cell error values.
Apart from the #DIV/0! error, other error cell values that ADF Desktop Integration rejects include #NULL!, #REF!, #N/A, #NAME?, #NUM!, and #VALUE! For information about Excel cell error values, see Microsoft's documentation.
You can enable an integrated Excel workbook to upload Excel cell error values to the Fusion web application by having ADF Desktop Integration accept Excel cell error values. You do this by setting the Workbook Compatibility.RejectExcelErrorsEnabled property to False. The default value is True for workbooks created using this release of ADF Desktop Integration. See How to Upload Excel Cell Errors to the Fusion Web Application.
13.3.1 Providing Data Entry Validation Using ADF Desktop Integration
ADF Desktop Integration performs data entry validation to verify that:
-
Mandatory fields contain a value. ADF Desktop Integration reports a validation failure if an Excel cell that contains an ADF component which requires a mandatory value (for example, ADF Input Text component) is blank.
-
The correct data type is entered. If, for example, you enter a string (
"Bob") in an input field where the required data type is a date or a number, ADF Desktop Integration reports a validation failure.
ADF Desktop Integration performs the above types of validation without making a request to the Fusion web application.
ADF Desktop Integration performs data entry validation on the ADF Input Text, ADF Input Date and ADF Table components. It does not perform data entry validation on read-only cells, label or headers cells, locked cells or cells in the columns described in Special Columns in the ADF Table Component . It also does not perform data entry validation on cells in the ADF Read-only Table or ADF List of Values components.
Data entry validation performed by ADF Desktop Integration identifies failures upon editing a single cell or multiple cells (simultaneously). Examples of edits that span multiple cells include a selection of a column in an ADF Table component or an end user pasting one or more rows of data into in an ADF Table component. ADF Desktop Integration performs data entry validation only after an end user edits a single cell or multiple cells and leaves edit mode for the cell(s). If a validation failure occurs, ADF Desktop Integration applies a red border to the cell that failed validation after the end user exits edit mode by pressing Enter, tabbing away, or selecting a different cell.
Once ADF Desktop Integration reports a validation failure, the end user can view a non-modal popup message by selecting the cell without entering edit mode. This non-modal popup message describes the validation failure and may suggest an action to resolve the validation failure. It remains visible as long as the end user selects the cell and the validation failure is present. No non-modal popup message appears if end users select multiple cells with validation failures. In ADF Table component cells, a message also appears in the Status column to indicate that a row contains at least one cell with a validation failure. ADF Table component actions such as Download and Upload clear this latter message. The ADF Table component's Download action also clears the red border around cells that contain validation failures. For information about the Status column, see Special Columns in the ADF Table Component.
The Status Viewer displays a message for a row with cells that contain validation failures. It displays this message ("Row contains invalid pending changes") until the end user resolves the validation failures. In addition to data entry validation errors, the Status Viewer might display other messages, such as failure messages from the last Upload operation. See Using the Status Viewer to Report Error Messages to End Users.
Figure 13-1 shows a cell with a data entry validation failure in the Summit sample application's EditCustomers-DT.xlsx workbook. The end user has not entered a value in an ADF Table component cell that requires a value. The non-modal popup message and the Status Viewer both display information about this failure.
Figure 13-5 ADF Desktop Integration Data Entry Validation
Description of "Figure 13-5 ADF Desktop Integration Data Entry Validation"
ADF Desktop Integration applies a red border to a cell that fails validation until the end user resolves the issue that causes the validation failure. If you or an end user set a cell border to red, ADF Desktop Integration does not consider the cell invalid until a validation error occurs (missing mandatory value, for example). To prevent visual confusion for end users, avoid the use of red borders on cells in your integrated Excel workbook so that its use is reserved to ADF Desktop Integration reporting validation failures.
Validation failures do not prevent end users from continuing to edit or enter data in the integrated Excel workbook nor does the presence of data entry validation failures prevent the upload of data from the integrated Excel workbook.
Note:
If an end user modifies a large number of cells at the same time, data validation can take a significant amount of time. In such cases, a progress bar may appear to provide the end user with an indication of progress. If the end user clicks Cancel, the validation stops at that point.
13.3.1.1 How to Enable or Disable ADF Desktop Integration Data Entry Validation
Integrated Excel workbooks enable ADF Desktop Integration data entry validation by default. You enable or disable ADF Desktop Integration data entry validation by configuring the DataEntryValidationEnabled workbook property, described in ADF Desktop Integration Compatibility Properties.
Before you begin:
It may be helpful to have an understanding of ADF Desktop Integration data entry validation. See Providing Data Entry Validation Using ADF Desktop Integration.
You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Adding Validation to an Integrated Excel Workbook.
To enable or disable ADF Desktop Integration Data Entry Validation:
13.3.2 Providing Data Validation Using Excel
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 ZipCode field in the EditWarehouseInventory-DT.xlsx workbook so that users can enter only whole numbers in the cells of this field.
If you apply custom validation to cells that render lists of values, the validation is propagated when ADF Desktop Integration populates cells with lists of values at runtime. Note, however, that ADF Desktop Integration overwrites at runtime any custom validation applied for components with lists of values. This is because ADF Desktop Integration applies its own list-constraint validation, which is invoked at runtime. For information about lists of values, see Working with Lists of Values .
For information about data validation in Excel, see Excel's documentation.
13.3.3 How to Upload Excel Cell Errors to the Fusion Web Application
By default, ADF Desktop Integration rejects Excel cell error values that integrated Excel workbooks send to the Fusion web application. As a result, you cannot successfully upload these values from your integrated Excel workbook. You change this default behavior by configuring the RejectExcelErrorsEnabled workbook property.
The RejectExcelErrorsEnabled workbook property is one of a number of properties that you can configure in integrated Excel workbooks if you want to use a feature that was not available in ADF Desktop Integration when you originally created your integrated Excel workbook. See ADF Desktop Integration Compatibility Properties.
Before you begin:
It may be helpful to have an understanding of the data entry validation options that you can provide for an integrated Excel workbook. See Providing Data Entry Validation for an Integrated Excel Workbook.
You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Adding Validation to an Integrated Excel Workbook.
To successfully upload Excel cell error values:
13.4 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 end users enter or edit in one of the ADF Desktop Integration components can be validated against set rules and conditions in the ADF Model layer.
For an ADF Table component, server-side validation failures can be reported promptly to end users if automatic row refresh is configured for the component. Enable automatic row refresh in the component and identify the column(s) that will contain the data values you want to validate using the TriggersRowRefresh property. At runtime, the automatic row refresh sends the modified value to the ADF Model layer which triggers validation. As a result, end users do not need to invoke the Upload action to view validation failures.
Row data may become inconsistent in an ADF Table component that is configured to support automatic row refresh if end users enter invalid values. Add entity-level validation rules to help prevent the integrated Excel workbook uploading inconsistent data.
Note:
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 information about automatic row refresh, see Configuring Automatic Row Refresh in an ADF Table Component.
See the following content in the Developing Fusion Web Applications with Oracle Application Development Framework for information about:
-
Defining validation rules in Oracle ADF, see Defining Validation and Business Rules Declaratively.
-
Adding ADF Model layer validation, see Defining Validation Rules in the ADF Model Layer.
-
Entity-level validation rules, see Types of Entity Object Validation Rules.
13.5 Providing a Row-by-Row Status on an ADF Table Component
The Status Viewer appears by default if errors occur during the attempted invocation of a number of ADF Table component actions.
Errors that occur during the invocation of the following actions cause the automatic display of the Status Viewer:
-
DeleteFlaggedRows -
Upload -
UploadAllOrNothing -
DoubleClickActionSetinvoked from an ADF Table component's column
End users can view a status message in the Status Viewer for each row in the ADF Table component by selecting a cell in the ADF Table component row that interests them.
In addition, 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 13-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 ZipCode column, a value 12345x has been entered in one row where 12345 or 12345-6789 is expected.
Figure 13-6 ADF Table Component with Changed Rows Before Upload
Figure 13-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. If a row fails to update, the Status Viewer appears automatically, as shown in Figure 13-7 and displays a message describing why the row failed to update.
Note:
A number of columns have been hidden in order to display the Status Viewer in Figure 13-7.
Figure 13-7 ADF Table Component with Changed Rows After Upload
Description of "Figure 13-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 13-8 shows the dialog that appears if the end user double-clicks the cell in Figure 13-7 that displays Update failed in the Status column.
Figure 13-8 Dialog Displaying Row Error Message
Description of "Figure 13-8 Dialog Displaying Row Error Message"
For information about the _ADF_StatusColumn column, see Special Columns in the ADF Table Component .
13.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.
Note:
For security reasons, the implementation of the customer error handler must ensure that the HTML content returned is properly filtered and escaped so that it can be considered trusted content by the browser.For information about creating a custom error handler, see Customizing Error Handling in Developing Fusion Web Applications with Oracle Application Development Framework.
13.7 Handling Data Conflicts When Uploading Data from a Workbook
Expose a DownloadFlaggedRows action to allow users selectively download rows that may have been modified in another session and specify a row-specific attribute of the tree binding for the ChangeIndicatorAttribute property to determine whether a row has been modified.
If one of your end users (User X) makes changes to a row of data downloaded from a Fusion web application to an Excel workbook, and another end user (User Y) in a different session modifies the same row in the Fusion web application after User X downloads the row, User X may encounter an error while uploading the modified row, as the changes conflict with those that User Y made. Depending on the configuration of your Fusion web application, User X may receive RowInconsistentException type error messages. For information about how to configure your Fusion web application to protect your data, see How to Protect Against Losing Simultaneously Updated Data in Developing Fusion Web Applications with Oracle Application Development Framework.
To resolve this conflict in the integrated Excel workbook, User X 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 User X 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, User X can resolve the conflict issues and upload his modified data.
Using an Integrated Excel Workbook Across Multiple Web Sessions provides information about using an integrated Excel workbook across multiple sessions. For information about flagging rows, see Row Flagging in an ADF Table Component. For information about invoking component actions, see How to Invoke Component Actions in an Action Set. For information about the components that the ADF Table component supports, see ADF Table Component Properties and Actions.
13.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:
- Open the integrated Excel workbook.
- Select any cell of the ADF Table component and click Edit Properties in the Oracle ADF tab.
- In Edit Component: ADF Table dialog, for the
RowData.ChangeIndicatorAttributeproperty, 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. - Click OK.
13.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.