14 View and Edit Data in an Excel Workbook

In Microsoft Excel, select the Oracle Visual Builder tab to perform operations and work with data in a workbook.

Description of the illustration excel-download.png

For any given layout, you can:

• View existing rows
• Edit existing rows
• Create new rows
• Delete existing rows
• Perform custom actions on existing rows, for example, an "Approve" or "Reject" action for an Invoice business object. These kind of special actions can be performed on certain business objects depending on the context. For more information, see Perform Custom Actions in a Table or Form-over-Table Layout.

If your action requires access to the REST service and the service host is missing (because it was removed when the workbook was published), a Service Configuration window prompts you to enter the service host value. Actions that require access to the service include the Download Data and Upload Changes commands.

Description of the illustration excel-input-service-host.png

(See REST Service Support for more about REST operations.)

When working with data in the Excel workbook, remember the following guidelines:

• Never edit the Key column, the last column in the table.
• Avoid using the following Excel features with the add-in. The following is a sample list of Excel features that do not work well with the add-in. Other Excel features not listed may also not work well with the add-in.
  • Do not use the Protect Sheet or Workbook features of Excel.
  • Do not attempt to re-arrange the layout of the integrated worksheet.
  • Do not use the Mark as Final command to make the Excel workbook read-only.
  • Do not delete anything from the integrated worksheet using Excel's delete features, including the Delete key.

You can remove all data from the workbook, including any pending changes that have not yet been uploaded to the REST service by clicking the Clear button. This button does not make any calls to the service and does not change data in the service.

Download Data to the Workbook

Download data to the workbook using the Download Data button in the Oracle Visual Builder tab. The workbook prompts you for a user name and password the first time you connect to the service that the workbook is configured to use.

If you configured search options for download, a window or windows appear where users specify the value(s) to search on, as shown in the following example where data for the Sales department will be downloaded. Configure Search Options for Download describes how you configure search options for the workbook.

Description of the illustration excel-basic-filter-lov.png

If the business object supports pagination, the add-in retrieves one page of rows, then prompts the user whether to attempt downloading more rows. You can view if the business object supports pagination, and change the default download limit of 499, in the Download tab of the Business Object Editor.

Description of the illustration excel-download-data-png.png

When download is complete, the add-in updates the table in the worksheet with data retrieved from the web application.

Description of the illustration addin-download.png

Edit Downloaded Data in the Workbook

Update data downloaded to a workbook by editing editable cells that contain the downloaded data.

The following image shows three examples where a user has edited data in the table. The Change column for the first row displays an Update message that indicates the user has updated this row with required and valid values. The Change column for the second row also displays an Update message, but its Status column displays an Invalid message and a red border appears around the Hire Date* column’s cell to indicate that a value is required in that cell’s field. Finally, in the third row, the user attempted to input Software in the field for Department. As Software is not a valid choice for this cell's list of values, a red border appears around this cell.

Description of the illustration edit-data-val.png

When columns have an associated list of values, you can select a value in a search-and-select window. In this case, the Change column displays Update. Alternatively, enter one or more of the starting characters for other values, then click the Search icon to filter the values based on your input. The following composite image illustrates both these scenarios. For the latter scenario, the user entered S in the search box next to the Search icon to find all display values that start with S (Sales Manager, Sales Representative, and so on).

Description of the illustration excel-lov1.png

Note:

You can alter the search behavior of the list of values using the Business Object Field Editor described in Use Lists of Values in an Excel Workbook.

Understanding Data Validation

As you enter values in cells associated with layouts, the add-in validates the cell's content based on the business object field definition.

Description of the illustration data_validation.png

Validation occurs when you complete data entry in a cell; in other words, as soon as you enter or edit a value in a cell and move on to another cell or area. Validation also occurs at other key points:

• When a new row is added to the data table
• When a form create starts
• At the beginning of an upload.

When a row is marked for a custom action, the custom action's payload fields also receive the same validation. See Custom Actions.

Typically, the add-in determines whether a cell's value is consistent with the expected data type. For example, you can't enter a word, say, book, in a field that expects a number like 2,000. It also checks whether a required cell is missing a value and whether the value in a cell associated with a list of values is valid for that list. Required fields are designated by the workbook developer and must include a value for your changes to be uploaded successfully.

Local field properties are used for validation. Validation that is enforced by the REST service is not triggered at the points mentioned here. REST service validation is generally triggered by the requests sent during Upload (for details on Upload failure handling, see Upload Changes to the REST Service).

Understanding Read-Only Behavior

If a particular field is considered read-only, the add-in behaves as follows:

• It applies a "read-only" style to the corresponding cells to indicate that the value cannot be changed
• The cell's value is excluded from any subsequent REST requests
• The data entered by the user is not validated
• If the workbook is published with worksheet protection, any attempt to edit the cell results in a warning from Excel:
  
  
  Description of the illustration worksheet_protection.png
  
  

Create New Rows to Upload to the REST Service

Create new rows in the table using the Insert Rows option in the add-in’s Table Row Changes menu or by using Excel options to insert a full row.

You can create new rows before or after downloading data to your workbook:

• To create new rows in a table with downloaded data, click Table Row Changes and select Insert Rows, or right-click and choose Insert from the Excel context menu (you can choose any Excel option to insert a row). You can create new rows either at the end of the data table or in the middle.

  Tip:

  To insert multiple rows, select cells from multiple rows, then select Table Row Changes > Insert Rows. The add-in will create multiple pending Create rows for you to fill out as needed, as shown here:
  Description of the illustration excel-insert1.png
• To create several new rows without downloading data, if create is enabled for the Table, add values in the empty row that appears below the column headers. This empty row is known as the placeholder row:
  
  
  Description of the illustration excel_placeholder_user.png
  
  

  Once you enter data in the placeholder row (for example, in the cells for First Name), the row below becomes one where data values can be entered, and so on. This method is not available in published workbooks due to worksheet protection. Instead, you can select a cell in the first row after the table and select Insert Rows from the Table Row Changes menu.

  
  Description of the illustration excel-placeval.png

Any time a new row is created, it is validated for data entry. An Invalid message appears in the Status column if the row contains a cell where you are required to enter a value and are yet to do so, or you have entered an incorrect value (such as an unexpected data type). A red border also appears around the cell where a value is required or invalid. See Understanding Data Validation.

Delete Data from the REST Service

Mark rows for deletion from the REST service using the Mark for Delete option in the Table Row Changes menu.

To mark a row for deletion from the service:

1. Select a cell in the table row that you want to delete from the service. If you want to select a range of table rows to mark for deletion, hold down your keyboard’s Shift key and select the first and last row in the range of table rows that you want to delete.
2. Click the Table Row Changes menu, then Mark for Delete.

   A Delete message appears in the Change column and the add-in changes the style applied to the data in the table rows, as shown in the following image where three rows in the table are marked for deletion:

   
   Description of the illustration markdelete.png
3. Click Upload Changes.
4. When prompted to confirm pending deletions, click Yes.

   The add-in sends delete requests for each row that you marked for deletion. For each successful deletion, the corresponding Excel row is removed. If the deletion fails, the Excel row remains with an error message.

   
   Description of the illustration excel-deletedrows.png

Tip:

If you change your mind about deleting one or more rows that you have marked for deletion, select the rows and select Unmark Pending Changes from the Table Row Changes menu. Use Unmark Pending Changes before you upload changes from the Excel workbook; the option does not work after the changes are uploaded.

Upload Changes to the REST Service

Once you complete the changes that you want to make, click the Upload Changes button to upload all the changes to the REST service.

The Excel add-in performs all the requested operations, as seen in the Change column. Review the Status column to see which rows succeeded and failed. The Status column displays a Create Failed message or an Update Failed message in the cell of a row that the add-in failed to upload and the Table Row Status appears in Excel’s task pane to provide additional information on the failure, as in the following example where a required value for Hire Date was not entered. In the following example, you need to enter a hire date and click Upload Changes to try and upload the modified data again. Successfully deleted rows are removed from the Excel worksheet while failed attempts to delete a row result in the row remaining in the Excel worksheet. You can inspect the reason for the failure to delete in the status in Excel's task pane.

Description of the illustration excel-upload-error.png

How the add-in uploads modified rows depends on the type of service that your workbook uses. For workbooks that use Oracle business object REST API services, the add-in uploads the modified rows in batches. For other types of services, the add-in uploads modified rows one row at a time.

Tip:

If you change your mind about uploading changes for a particular row, select the row, then from the Table Row Changes menu, select Unmark Pending Changes. The add-in won't include the row when it sends other rows back to the service for update, creation, or deletion.

View and Edit Data in a Form-over-Table Layout

Viewing and editing data in a Form-over-Table layout is similar in many ways to viewing and editing data in a Table layout.

For example, you use the Download Data button to download data from the Form-over-Table layout’s business objects. Much like table layouts, you may be prompted to do a search at the beginning of a download. Unlike a table layout, the Form-over-Table displays the first row found from the search, plus all the children of that first parent row.

You can also update the form fields in a Form-over-Table if the business object for the form fields supports update. The add-in performs data entry validation before it attempts to upload your updates. Ensure that you fix any data entry failures before the add-in uploads changes. For a form field configured to use a list of values, the behavior is the same as described for the Table layout. That is, a search-and-select window appears when you select the form field. In a published workbook or when worksheet protection is enabled, read-only form fields cannot be edited. You can make a form read-only by deselecting the Update Enabled check box under Form Capabilities in the Layout Designer's Advanced tab.

Create a Parent Row in a Form-over-Table Layout

If create is enabled for the form, you can select Create Form Row in the Form Changes menu to place the layout in create mode. When you first click Create Form Row, all the form fields are blank and the child table has no rows, as shown in the following image:

Description of the illustration create-form-row-user.png

You can then enter form field values, even create new child table rows. All changes are marked as a pending create, until you click Upload Changes. The add-in validates the data before it attempts to upload updates, and any data entry failures need to be addressed before the changes can be uploaded.

Description of the illustration create-form-row-result.png

Perform Custom Actions in a Table or Form-over-Table Layout

Perform custom actions (for example, an action to close purchase orders) by downloading records of purchase orders and using the custom action on rows of data, then uploading the changes back to the service in a single upload.

As a business user, you don't need to do anything special to use custom actions in a data table. Click Download Data to download data to your table, then update a custom action's cell values much as you would any other field's cell value. In the following Table layout, rows marked as Close are those where the user has updated cell values corresponding to the Close Action (Close) and Close Reason (Close) custom action columns.

Description of the illustration custom_actions_table.png

When you add or update a value in a custom action cell first, the row is marked for that custom action in the Change column. Other cells in that row that don't involve custom actions are grayed out and cannot be edited. But if you add or update a value first in a cell that doesn't involve a custom action, the row is marked as an Update in the Change column and the custom action cells in that row are grayed out (as shown in the following image). In other words, the first action you perform on a row determines that row's pending action.

Description of the illustration custom_actions_table_automark.png

You can also mark rows for a custom action by selecting Mark for Action from the Table Row Changes menu. With this option, if only one custom action is defined, you'll be prompted to confirm. If more than one custom is defined, you'll be prompted to select from a list of available actions:

Description of the illustration custom_action_markforaction.png

When a row is marked for a custom action, the action's payload fields also receive the same validation, as described in Understanding Data Validation.

If you're working with a Form-over-Table layout, select Perform Action in the Form Changes menu, then follow the prompts to perform custom actions on a Form row, as shown here where you're prompted to select an action, then provide values for the action's fields:

Description of the illustration fot_custom_action.png

A custom action can be performed only on an existing row (not on a pending Create row).

Once you perform an action on the form, it takes effect immediately, unlike an action in a table (where rows are marked for actions and the actions are performed later during an upload).

Also, form row data is not automatically refreshed after a custom action is performed, even if the action was successful. Click Download Data if you want to refresh data in the form row.

Manage Data for Layouts in a Dependent Hierarchy

Managing data in a layout that's part of a hierarchy of dependent layouts is similar to any download, upload, or clear operation in a Table or a Form-over-Table layout, except that the operation works on all layouts in the hierarchy.

When you click Download Data, Upload Changes, or Clear for a layout in a dependent hierarchy, the operation takes effect on all layouts in the hierarchy, starting with the primary layout, progressing to the next layout in the hierarchy, and continuing down until the last level in the hierarchy. When the operation is complete on all layouts in the hierarchy, the worksheet with the primary layout is made active again.

During a download operation, all items for all rows from the parent layout are downloaded at each level. (Any search specifications, if configured, apply only to the primary Form-over-Table layout.) For example, when Sheet 1 in your workbook contains Purchase Orders as the parent and Lines as the child (containing, say, 10 Lines) and Sheet 2 contains Schedules as the grandchild, the Schedules table is populated with all Schedule items for all Lines. If each of the 10 Lines had 2 Schedules, the Schedules table would download 20 Lines. With this download, you can update all Schedules and upload all the changes together.

Following a download, you can edit data much as you would a Table or a Form-over-Table layout. Remember though that when you create rows in the grandchild table, you need to identify the correct child row that it belongs to. Typically, one column in the grandchild table comes from the child table. For example, let's say the Schedules table in Sheet 2 includes a PO Line ID column that comes from the Lines table in Sheet 1. By typing the correct PO Line ID in the Schedules table for new create rows, the schedules will be properly associated with the PO Lines.

When updates are ready to be uploaded, the upload operation submits all pending changes across the hierarchy of layouts.

You can view details of the operation in the primary Form-over-Table layout's Status Viewer, which shows results for the primary layout as well as a summary for each layout in the dependent hierarchy, as shown in this example for a download operation:

Description of the illustration dependentlayout_download.png

Click each dependent layout to view additional details of the operation.

Data Consistency

When a workbook uses an Oracle business object REST API service that supports data consistency verification using an entity tag (Etag) mechanism, the add-in detects and reacts to the following scenario:

1. User A downloads information from a business object into a table in their integrated workbook.
2. User B downloads the same information into a table in their integrated workbook, edits it, and uploads changes.
3. User A then edits the same information (downloaded in Step 1) and uploads the changes.
4. The add-in provides the service with the necessary information (entity tags) to prevent User A's changes from overwriting those changes made by User B. Instead, when the server detects such a change, its response allows the add-in to display an error message similar to the following for any such rows in the table:

   This row has been modified by another user. Please download before editing.

   If you see this message, you'll have to discard your changes by downloading the latest data and then redoing your changes as needed.

For information about the entity tag (ETag) mechanism, see Data Consistency Tasks in Accessing Business Objects Using REST APIs.

If your workbook uses other types of REST services, the last writer wins. So, for the scenario just outlined, User A’s changes in Step 3 will overwrite the changes of User B in Step 2.