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.

When you open an integrated workbook for the first time, you may be prompted to redraw all layouts if the workbook was published in a different language. If you choose to redraw the workbook, any data and changes to the layouts are discarded. To keep these changes, skip the redraw. You can always manually redraw your workbook later using either the Clear Layout or Download Data icons from the Oracle Visual Builder tab.

Note:

Clearing all layouts when the language changes is recommended since some data, such as lists of values, may be language sensitive. Downloading in one language and uploading in a different language may not succeed.

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 operation 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. Operations 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 Oracle Visual Builder Add-in for Excel. 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 Protect 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 save your workbook to the Excel 97-2003 workbook (.XLS) file format. Only the .XLSX and .XLSM file formats are supported.
  • Do not delete anything from the integrated worksheet using Excel's delete features, including the Delete key.
  • Do not add Excel tables, such as those created from the Excel ribbon using Insert > Table, to an integrated workbook. These table objects are incompatible with the table layouts used by the add-in.
• You can use Excel's Filter and Sort features to filter and sort rows in a table layout with this proviso: Be sure to include the Key column in the range to be sorted when using the Sort feature. Failure to do so could lead to corrupted data.
• Do not attempt to modify the Change column directly. Instead, follow the procedures described in this section.

You can remove all data from the workbook, including any pending changes that have not yet been uploaded to the web application 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

Note:

Required search fields are marked with an asterisk (*). You must provide values for these fields before you can proceed.

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

Note:

Remember that Excel table objects, such as those created from the Excel ribbon using Insert > Table, are incompatible with the table layouts used by the add-in. For more information about Excel feature compatibility, see General Guidelines.

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 have yet to do so. A red border also appears around the cell where a value is required or invalid. See Understanding Data Validation.

WARNING:

Never insert partial rows into a table layout as this can lead to data corruption. Also, never use the Excel Insert option to shift cells down or to the right.

Understanding Data Validation

As you enter values in cells associated with layouts, Oracle Visual Builder Add-in for Excel validates the cell's content based on the business object field definition as well as any custom validation rules that are defined.

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 the Create Form Row option is selected from the Form Changes menu
• 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.

Your workbook may also include some custom field validation rules that restrict the value you can enter into a cell. For example, you may have an Expense Reports workbook where the expense amount must be less than $500.00. As with other data validation errors, a cell with an invalid value (in this case, a value of $500 or more) is marked with a red border. You can select this cell to view a popup that provides a description of the error.

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 workbook is published with worksheet protection enabled, any attempt to edit the cell results in a warning from Excel:
  
  
  Description of the illustration worksheet_protection.png
  
  

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.

Caution:

Do not attempt to delete rows from your worksheet using Excel commands such as Delete Cells or Delete Sheet Rows. Doing so will not remove the row from the web application.

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.

Manage Attachments

If your integrated workbook has an attachments table layout, you can upload and download attachments as well as perform standard create, edit, and delete functions.

Attachments are often associated with a parent business object. For example, an expense report may have one or more associated attachments representing receipts. For this reason, attachment table layouts are often a child layout in a hierarchy of layouts in a workbook. In our example, a form in a Form-over-Table may display the expense report header and the child table may list the associated attachments. Keep in mind that the attachment table could be located on a separate worksheet from its parent layout. See Manage Data in Form-over-Table Layouts and Manage Data for Multi-Level Business Objects.

Oracle Visual Builder Add-in for Excel supports attachments of file, text, and web page types. Unknown attachment types are treated as file type attachments.

Upload Attachments

You can add URLs, as well as text and file type attachments, to attachment records and upload them to the web application.

To upload a new or revised file or text attachment:

1. Select any field in a new or existing row to open the Attachment pop-up window.
2. From the pop-up window, click the Upload icon.

   Note:

   The Upload icon is disabled when you select a field in a new row if Create is not enabled for the table layout. It is also disabled for an existing row if Update is not enabled.
3. From the Choose a File to Attach dialog, navigate to the location where the file is saved.
4. Select the file and click Open.

   The local file path is displayed in the read-only Local File Path column. A link also appears in the pop-up. You can use this link to open the local file in the file's default program.

   
5. For new files, specify the attachment type in the Type field.

   The add-in recognizes FILE, TEXT, and WEB_PAGE as valid values for this column to represent file, text, and web page type attachments respectively. These values are case sensitive.

   The Change column for the row displays a Create or Update message. The attachment is now pending upload.

6. Click Upload Changes to upload the attachment.

To add or edit the URL for a web page:

1. Type the URL directly into the Url field for a new or existing row.
2. For new URLs, type WEB_PAGE in the Type field.

   The Attachment pop-up window is read-only in this case and provides a link for the current URL.

   
   
   
   
   
3. Click Upload Changes to upload the URL.

During upload:

• The attachment records are processed first without any attachment files. The upload of attachment records occurs just like any other table upload.
• For each successful row that has a pending file upload, the add-in attempts to upload the file. If the file can't be found or read, an error is reported for that row. If the upload of the given attachment record fails, the corresponding file upload is skipped.
• If the attachment record is marked for delete, any pending file change is ignored at upload time.
• If you don't upload changes from the layout, the new attachments are never sent to the service.

Download Attachments

To download an attachment:

1. Select any field in a row with a file or text attachment to open the Attachment pop-up window.
2. From the pop-up window, click the Download icon to download a local copy of the attachment.
3. In the Save Attachment dialog, navigate to where you want to save the file and click Save.

   After successful download, a link is provided in the attachment dialog to open the file in the default program.

Limitations

• The only supported attachment types are file, text, and web page. Unknown attachment types are treated as file type attachments. The values for the attachment type file to specify each type are "FILE", "TEXT", and "WEB_PAGE" respectively. These values are case sensitive.
• The UI may appear to freeze during download for large attachment files.
• Manually editing the file path in the Local File Path column is not supported. Use the Attachment pop-up to specify the local file location.
• For some services, create may fail for text-based attachments. Ensuring all required attachment fields, such as the Title, are present and resubmitting the record generally resolves this issue.

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. You might see status messages such as Create Succeeded, Update Failed, Delete Failed, and Skipped. Skipped means that a row marked for update had no changes and was not included in the upload.

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 the Status Viewer to provide additional information on the failure, as in the following example where a required value for Hire Date was not entered.

Description of the illustration excel-upload-error.png

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 the Status Viewer.

Note:

Some web applications may issue warning messages for successfully uploaded rows. The message in the Status column contains an indication that there were one or more warnings for that row.

Select a row with a warning icon to display details of the warning in the Status Viewer.

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.

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 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, 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. Person A downloads information from a business object into a table in their integrated workbook.
2. Person B downloads the same information into a table in their integrated workbook, edits it, and uploads changes.
3. Person 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 Person A's changes from overwriting those changes made by Person 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.

Note:

Some services do not support this conflict detection functionality. If the service does not, then Person A's changes will replace Person B's changes with no warning. Contact the creator of your workbook for more information.