Upload Changes

Upload Changes from a Table Layout

When a user clicks the Upload Changes button for a Table layout, here's what happens:

The add-in checks the table for pending changes. If there are no pending changes, upload is skipped.
If a Pre-Upload macro is configured, it is invoked. If the macro throws an exception or returns any value other than true, the upload process quits.
For pending Create and Update operations, the rows are first validated locally, for example, data type, required, and so on (see Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel). Any failures are marked as failed and skipped (these rows do not produce requests on the business object service).
Pending changes are processed as follows:
1. Updates result in a PATCH or PUT request on the item path.
2. Creates result in a POST request on the collection path.
3. Deletes result in a DELETE request on the item path.
4. Rows marked for action result in a POST request on the item action path.
Success and failure is noted in the Status column. Errors are cached and displayed in the status viewer when the user clicks on a failed row.
Successful Create rows are updated from the service and converted into existing rows that can be edited.
Successful Delete rows are removed from the Excel worksheet.

The request payload for Create and Update operations includes a value (possibly empty) for every column in the table, except those for read-only fields and custom action payload fields (see Upload Changes for Custom Actions). The add-in does not track which columns have been altered, it always sends values for the entire row.

The add-in may send up to 4 requests to the service at a time for improved performance (on different threads). As a result, the order in which the rows are sent to the server is non-deterministic. It is not guaranteed to be in the same order as in the table.

Upload Changes from a Form-Over-Table Layout

When a user clicks the Upload Changes button for a Form-Over-Table layout, here's what happens:

The add-in checks the form for a pending Update or pending Create operation and the table for pending changes. If there are no pending form or table changes, upload is skipped.
If a Pre-Upload macro is configured, it is invoked. If the macro throws an exception or returns any value other than true, the upload process quits.
When the form has a pending Update or pending Create operation:
For a pending Update:
1. A GET request is sent to the parent's item path.
2. Form field values (for example, data type, required, and so on) are validated; read-only fields are skipped. If validation failures exist, the upload is stopped and no subsequent REST requests are made.
3. When all form fields are valid, a request (PATCH/PUT) is sent to the parent's item path. The payload for this request contains the values of all form fields that have been changed.
For a pending Create:
1. Form field values (for example, data type, required, and so on) are validated (see Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel); read-only fields are skipped. If validation failures exist, the upload is stopped and no subsequent REST requests are made.
2. A POST request is sent to the parent's collection path. The payload for this request contains a value (possibly empty) for every editable form field in the form.
The child table is not involved in this step.
The results of the form upload are reflected in the status viewer immediately.
If the form upload fails, the add-in stops and does not attempt an upload on the child table.
If the form upload succeeds, the add-in proceeds with the child table as follows:
1. Checks the child table for pending changes, creates, deletes, custom actions, etc.
2. If changes are found, the child table upload proceeds in the same manner as a Table layout upload with one important difference: the child business object's paths are used for each request.
  The add-in ensures that the parameters in the child business object paths are replaced with the correct values during the table upload operation.

If the form and table both have pending changes, there are a minimum of two requests: one for the form and one (or more) for the child table.

The form and child table changes are never sent in a single request. In particular, sending a single request where the payload contains values from the parent form fields along with an array of values from the child table rows is not supported.

Upload Changes for Custom Actions

For Oracle business object REST API services that expose custom actions on the item path, the user can mark rows so that the custom action is called on those rows during the upload. For custom actions that require payload fields, table columns that correspond to those fields must be added.

During the upload operation (which may also include update, create, and delete tasks), the add-in processes rows marked for custom actions:

Validates values from custom action payload fields (if any) for data type, required, and so on (see Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel). Any failures are marked as failed and skipped (these rows do not produce requests on the business object service). Values from other columns are ignored.
Makes a POST request to the custom action path. The payload consists (only) of all the values for all the columns that correspond to the particular custom action's parameters; no other columns' values are included.
For each marked row, the add-in performs the following steps:
- Creates the payload by collecting the cell values for each custom action field column and adding the value to a simple JSON object (member name/value pairs) in the payload. The entire payload body follows this example format:
```
{
  "rejectionReasonCode": "Other: contact approver",
  "notes": "Details with manager"
}
```
  - There is no other content in the POST request body (no action name, no array of argument values).
  - If any values from these columns are invalid (missing when required, incorrect data type, Excel formula error), the row is omitted from the Upload and marked as failed.
- Prepares the request
  - REST-Framework-Version header added (see Configure the REST-Framework-Version)
  - Content-Type header added based on each custom action's Request Media Type property on the Custom Action Editor (available from the Business Object Editor > Custom Actions tab)
- Makes the request
  - Sends the POST (POST is the only HTTP method supported for invoking custom actions)
  - See the note below about batch requests.
- Processes the response
  - For 200 response status, the row is marked as succeeded.
  - For 400 response status, the row is marked as failed, and the response payload is parsed for Oracle business object REST API service error content; error details can be seen in the Status Viewer pane.
  - A 412 response status indicates that the row was modified by some other agent or user after it was downloaded to the Excel table; such a status is treated as a row-level error
  Cell values in action rows are not refreshed. If the custom method logic in the service has altered any values in the row, those changes will not be reflected in the table row until the next download.

For information on custom actions, see Custom Actions.

Upload Changes Using Batch Requests

For Oracle business object REST API services, the add-in uses a batch API to send 25 rows per request for pending changes. During an Upload operation, rows marked for Update, Create, Delete, and custom actions are included in the batch requests.

If a batch request contains one or more errors, no changes are made by the service. In that case, a second batch request containing only the rows that succeeded during the first batch request is sent. If the second batch request fails, the add-in falls back to sending one request per row. For more information, see Making Batch Requests.

For Oracle REST Data Services and other service types, there is one request per row.

If a set of changes includes custom actions and those actions have batch enabled, the changes are included in a batch request. However, if one or more rows is marked for a custom action that has batch disabled, the add-in reverts to sending the changes row by row. (See also Batch Mode for Custom Actions.)

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:

User A downloads information from a business object into a table in their integrated workbook.
User B downloads the same information into a table in their integrated workbook, edits it, and uploads changes.
User A then edits the same information (downloaded in Step 1) and uploads the changes.
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.

Configure Parallel Requests During Upload

You can configure Oracle Visual Builder Add-in for Excel to send multiple requests in parallel during upload to improve performance and shorten the overall time to upload a large set of changes.

For example, if you leave the setting on the default of "4", the add-in sends four requests at the same time during upload instead of one request after another.

This feature is available for both layout types and can be set independently for each one.

Note:

Parallel requests behave differently than batch requests. With a batch request, the add-in sends the changes for multiple rows in each request; With parallel requests, the add-in sends multiple requests at the same time, regardless of whether the request contains one row or multiple rows.

Open the Layout Designer for a worksheet and then click the Advanced tab.
Click Upload to display the sheet's Parallel Requests slider.
The slider can be set from 1 to 20. Choosing a value of 1 means that the add-in sends only one request at a time during upload.
The default value is 4.
Drag the slider to your desired setting.

We recommend setting the value to four (4) parallel requests (the default) during upload. However, some services are not compatible with parallel requests due to the their internal business logic. For such services, set this value to "1". Keep in mind that this setting generally results in slower upload performance.

Other services may benefit from a higher parallel request setting. In this case, you may want to experiment with a larger setting like "8" and and see if your overall upload performance improves.

Note:
Take care with larger numbers: Performance may actually degrade if you have too many parallel requests, as may happen if you have multiple users performing uploads at about the same time.