Upload Changes

Upload Changes from a Table Layout

When you click 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. Any failures are marked as failed and skipped (these rows do not produce requests on the business object service).
Pending changes are generally 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.
Note:
Batch processing is handled differently. See Upload Changes Using Batch Requests.
Success and failure is noted in the Status column. Rows with warnings are indicated with a warning icon.

Errors and warnings are cached and displayed in the Status Viewer when the business user selects a row. Here is a sample of the Status Viewer showing three warnings on 8 successful changes.
Successful Create rows are updated from the service if possible. These rows are converted into existing rows that can be edited.

Note:
If the service does not return the updated row, you will need to download again.
Successful Delete rows are removed from the Excel worksheet.

For Create and Update operations, the request payload only includes values for editable cells in a new or updated row. Read-only fields and custom action payload fields are not included. Whether a value is included for an editable cell depends on your add-in settings:

For updated rows, the request payload includes a value for each editable field unless Send Only Changed Data for Updates is enabled in the Layout Designer. When enabled, only values that have changed since the last download or upload are included in the payload. See Send Only Changed Data During Upload.
For new and updated rows, the request payload includes a null value for each empty cell in the row unless Omit from payload if value is empty is enabled for a field in the Business Object Field Editor.

When this check box is enabled and the corresponding cell is empty, the field is not included in the request payload. When this check box is not checked and the corresponding cell is empty, the field is included in the request payload with a null value. See Omit Empty Values During Upload.

The add-in may send up to four 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. If configured, empty values are skipped. See Omit Empty Values During Upload.
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 the 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. If configured, empty values are skipped. See Omit Empty Values During Upload.
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.

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.

For the form changes, notification of success and failure is noted in the Status Viewer.

The Status Viewer showing that the form was updated successfully

For table changes, success and failure is noted in the table's Status column and well as in the Status Viewer. See Upload Changes from a Table Layout for more information about table status after Upload.

Invoke Custom Actions via Upload

For Oracle business object REST API services that expose custom actions that correspond to 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. See Custom Actions.

During the upload operation (which may also include update, create, and delete tasks), the add-in performs the following steps for each row marked for action:

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. If configured, empty values are skipped. See Omit Empty Values During Upload.
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. See Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel
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 to the custom action path (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.

If the response payload for the Custom Action request includes a "result" member with a simple value such as a string or number, then the add-in displays that result in the Status Viewer. The response payload for a successful invocation should have a result member in the following format:
```
{
 "result": "<return-value-from-custom-action-method>"
}
```
For example, after invoking the custom action "Reject" on an Expense Report table row, the REST service may return a result such as "result": "Your confirmation code is 26281.". This result is displayed in the Status Viewer, as shown here:

See Executing a Custom Action in the Developing Fusion Web Applications with Oracle Application Development Framework.

For information on custom actions, see Custom Actions.

Upload Changes Using Batch Requests

Oracle Visual Builder Add-in for Excel supports the use of batch requests for uploading changes to Oracle business object REST API services.

During an upload operation, the add-in sends multiple rows per request using a batch API. Rows marked for Update, Create, Delete, and custom actions are included in the batch requests.

For other REST service types, the add-in sends just one row per request.

The add-in uses an algorithm to determine the optimal size of the payload (number of rows) for a batch request as the upload operation proceeds. This is done to optimize performance and avoid timeouts. It works like this:

The add-in sends an initial set of batch requests using 10 rows per batch.
The add-in measures the response times for those initial requests and calculates the approximate number of rows that can be processed in 10 seconds.
Using the new value, the add-in sends one or more batches to the service.
The add-in recalculates the number of rows after each set of batch requests, using the target of 10 seconds per request, until all rows have been processed.

Note:

The target of 10 seconds is not guaranteed, since actual response times vary depending on the REST service business logic, server load, network latency, and so on.

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 row per request. For more information, see Making Batch Requests.

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.)

If you configure the add-in for parallel requests, it sends up to 4 batch requests in parallel depending on your settings. See Configure Parallel Requests During Upload.

Upload Changes Using Upsert Mode

Oracle business object REST API services support an optional "upsert" mode that you can use when uploading changes to your REST service.

Ensure that your REST services business object supports upsert mode before you enable it.

When upsert mode is enabled, the REST service should behave as follows: first, it examines the incoming payload of a create row request. Next, if the payload contains enough information to match an existing row, the operation updates that existing item. If there is no match, the operation should create a new row. See Updating or Creating Resource Items (Upsert) for more information.

The exact behavior of upsert mode is determined by the service.

You enable upsert mode by selecting the Use Upsert Mode for Create from the General tab of the Business Object Editor. After you enable it, refresh any layouts in the workbook that are based on that business object.

Note:

This check box is only available for Oracle business object REST API services and is hidden for all other service types.

The Business Object Editor with Use Upsert Mode for Create enabled

When upsert mode is enabled, the add-in sends the HTTP header, Upsert-Mode: true, with POST requests for single-row creates. For batch mode, the add-in sets the operation value for each batch part to "upsert" instead of "create".

Omit Empty Values During Upload

You can configure Oracle Visual Builder Add-in for Excel to omit fields with empty values during upload.

Request payloads for Create and Update operations normally include a value for every column in the table (except those for read-only fields and custom action payload fields) even if the cell value is empty. For empty cell values, a null value is included for that field in the payload. Null values can potentially cause validation errors.

To avoid these errors, you can configure the add-in to omit fields with null values from the payload by selecting the Omit from payload if value is empty check box in the Business Object Field Editor.

When selected, the add-in won't include the field in the payload during upload if the value in the field is empty.

The add-in sets the default value for the Omit from payload if value is empty check box based on the "nullable" property in the OpenAPI v3 (OA3) document for this field. If "nullable" is true, the check box is deselected and cells with empty values are uploaded; if "nullable" is false (or the property is missing), then the check box is selected and cells with empty values are omitted from the upload.

Note:

If this check box is selected (empty cell values are skipped), you won't be able to change a "non-empty" value to an empty value for a field in an existing row.

Send Only Changed Data During Upload

You can configure Oracle Visual Builder Add-in for Excel to send only changed data from a Table layout or the table part of a Form-over-Table layout during an upload. The add-in always sends only changed values from the form area of a Form-over-Table layout.

Request payloads for Update operations normally include a value for every editable cell in a row marked for update even if the value hasn't changed since the last download or upload operation. For some REST services, sending data that has not been modified in a PATCH, PUT, or batch update request can cause issues.

To avoid these issues, you can configure the add-in to send only changed values in the payload by selecting the Send Only Changed Data for Updates check box for your table. When this is enabled, cells that are unchanged are not included in the payload.

To enable this feature, select the layout, then open the Advanced tab of the Layout Designer. The Send Only Changed Data for Updates check box can be found under Table Capabilities.

Description of send-only-changes.png follows

Description of the illustration send-only-changes.png

When the check box is enabled, the add-in caches a copy of all of the data in the table during download. Then, when the business user triggers an upload, the add-in compares values in editable cells in rows marked for update to the cached values, and only includes fields that are different in the request payload.

If a row is marked for "Update" in the Change column, but no values have been changed, the row is omitted and the Status column displays "Skipped" after the upload. The Status viewer also displays the results of the upload.

Description of status-viewer-skipped.png follows

Description of the illustration status-viewer-skipped.png

On a successful Create or Upload of a row, the cache is updated with all the current values to be compared during the next upload, if the service provides the row in the response.

Note:

If a field in a table is required for update (the Required for Update check box is selected in the Business Object Field Editor), the value is included in the payload even if it is unchanged.

Performance

Please note that this feature may affect performance during download and upload, since the add-in must capture and store a duplicate set of downloaded table data. You can expect workbooks using this feature to increase in size. Also, you may see a slow down in downloads and uploads since the add-in must read from, and write to, the cache.

If you choose to use this feature, it is recommended that you run relative performance tests in realistic environments and assess the performance impact before delivering workbooks with this feature enabled.

REST Request Payload

Let's suppose a business user changes the salary value for an employee named Steven King. When this check box is not selected, the add-in includes both changed and unchanged data in the payload, like this:

{
  "FirstName": "Steven",
  "LastName": "King",
  "Email": "SKING",
  "HireDate": "2003-06-17T04:00:00Z",
  "JobId": "AD_PRES",
  "Salary": 120000,
  "CommissionPct": null,
  "ManagerId": null,
  "DepartmentId": 90
}

When the check box is selected, only the salary data is included:

{  
"Salary": 120000
}

Data Consistency

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

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

The add-in sends an If-Match request header containing the entity tag for single row requests or includes the entity tag along with the row changes as part of a batch request. See Upload Changes Using Batch Requests.

When the server inspects the entity tag and detects such a change, its response (either an HTTP Status 412, or an error code of 11412) allows the add-in to display an error message for any such rows in the table like this:
```
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.

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, Person A’s changes in Step 3 will overwrite the changes of Person 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.

The add-in allows you to set from 1 to 4 requests in parallel. If you leave the setting on the default (4), the add-in sends four requests at the same time during upload instead of one request after another. If you choose a value of "1", the add-in sends only one request at a time during upload.

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

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.

Note:

While the add-in collects batches of rows in "table order" (from top to bottom), the threading used for parallel requests may result in batches of rows arriving at the REST service endpoint "out of order". For example, batch 7 may arrive at the endpoint before batch 6.

Open the Layout Designer for a worksheet and then click the Advanced tab.
Under Upload, drag the Parallel Requests slider to your desired setting.

We recommend setting the value to 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.

Note:
Take care to test thoroughly in a realistic environment when adjusting this value. 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.