3 Manage Data in Table Layouts

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 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 Actions in a Table or Form-over-Table Layout.

Note:

Capabilities described in this guide may not be available for all integrated workbooks. A workbook's functionality depends on how it was configured and the capabilities that the web application provides.

If your action requires access to the web application, a Service Configuration window prompts you to enter the service host value. Ask your IT administrator for the correct value to provide here. Actions that require access to the web application include the Download Data and Upload Changes commands.

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

Note:

You can also access the Service Configuration window using Edit Service Host from the Advanced drop-down list.

General Guidelines

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.

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 web application.

If search options were configured for download, specify the value(s) to search on, as shown in the following example where data for the Sales department will be downloaded:

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

If the search returns lots of rows, confirm if you want to continue downloading more rows:

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

Edit data downloaded to a workbook by modifying 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

If a cell has a date format, you can enter a date by selecting the cell and choosing a date from the date picker pop-up window.

Note:

The date picker only sets the date in a date-time field. The time value is not changed.

If 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-lov.png

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.

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.

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 upload to the web application
• The data entered by the user is not validated
• If the worksheet is enabled for worksheet protection (as specified by the workbook developer), 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 Web Application

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 cell for First Name), the add-in automatically recognizes the row as a pending Create row. To insert more rows, select a cell in the first row after the table and select Table Row Changes > Insert Rows.
  
  Description of the illustration excel_placeval_user.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 Web Application

Mark rows for deletion from the web application 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 web application. 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 Web Application

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

The 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

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 web application for update, creation, or deletion.

Clear Cache for a List of Values

Some fields may have associated choice lists, called lists of values. The choices for each list of values are fetched from the web application and stored locally (cached) in the workbook.

In some cases, the cached choices for a list of values may become stale or out of date. To start fresh with the latest values from the web application, select Clear List of Values Cache from the Advanced menu.

Data Consistency

The add-in helps prevent multiple users from overwriting each other's changes.

Consider this 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 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.