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.
For more details on how rows are sent in separate requests, see Upload Table Changes Using Separate Requests for Each Row.

Note:
Multi-Row processing is handled differently. See Upload Changes Using Multi-Row 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. See Enable Parallel Requests During Upload.

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.

The number of requests used in the upload depends on the scope of the changes as well as the state of the Send Descendant Rows in Parent Payload check box. If this check box is selected, the add-in sends parent changes and all child changes in a single request. See Upload Parent and Child Changes in the Same Payload.

If this check box is deselected and the form and table both have pending changes, then the add-in sends the changes using a minimum of two requests: one for the form and one (or more) for the child table.

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 services that support 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)
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 ADF REST Resource 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 Table Changes Using Separate Requests for Each Row

For REST services (including ADF REST services with multi-row processing turned off), Oracle Visual Builder Add-in for Excel sends each changed row in a separate request ("row-wise" processing) during upload. For ADF REST services, the default is to send multiple rows' worth of changes in a single POST request. This is referred to as "multi-row" processing. See Upload Changes Using Multi-Row Requests.

Row-wise processing is also used when either:

Multi-row support has been disabled by the workbook developer for a specific ADF REST business object.
When the set of pending changes includes a row marked for a custom action that does not support multi-row requests. See Multi-Row Mode for Custom Actions.

Row-wise Behavior

When a user clicks the Upload Changes button (and multi-row processing is not supported or enabled), here's what happens:

The add-in divides all changed rows into sequential collections (blocks) of 25 rows each.
For example, block 1 has changed rows 1-25, block 2 has rows 26-50, and so on.

Note:
Each block may contain a mixture of kinds of changed rows such as Update, Create, Delete, and custom action invocation.
The add-in creates up to 4 background threads, if the Supports Parallel Requests check box is enabled, and assigns each thread a block as a unit of work. See Enable Parallel Requests During Upload.
Each background thread then processes the block by:
- Sending a separate REST request for each of the 25 changed rows in the block (PATCH or PUT for update, POST for create, DELETE, or POST for invoking a custom action).
- Waiting for the response for each row before sending the next request.
When the add-in has received responses from the REST service, it processes the responses for each block, in order.
The add-in updates the Change and Status columns. If the REST service returns the row's field values in the response, the add-in writes these values for changed rows back into the table rows.
The add-in repeats these steps for the entire set of changed rows, processing up to 4 blocks of 25 changed rows at a time, until all changed rows have been processed.

Upload Changes Using Multi-Row Requests

By default, Oracle Visual Builder Add-in for Excel uses multi-row requests to upload changes from a table layout to an ADF REST service.

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 multi-row requests.

If required, you can adjust multi-row processing settings to improve performance. See Configure Multi-Row Uploads. You can also disable multi-row requests if your service requires a separate request per row. See Disable Multi-Row Requests for Upload.

If your ADF REST service includes date effective objects, you can add the Effective-Of header for each of the required REST operations. See EffectiveOf Headers in Multi-Row Requests.

Multi-row requests are only supported on ADF REST services. For other REST service types, the add-in sends just one row per request. See Upload Table Changes Using Separate Requests for Each Row.

About Multi-Row Processing

Oracle Visual Builder Add-in for Excel uses an algorithm to determine the optimal size of the payload (number of rows) for a multi-row request as the upload operation proceeds. This is done to optimize performance and avoid timeouts.

The add-in's adaptive multi-row upload algorithm for table layouts use two configurable settings: "Target Response Time" and "Initial Row Count". The default target response time is 10 seconds. The default initial row count is 10 rows. For more information about these settings, see Configure Multi-Row Uploads.

Based on these values, the upload algorithm dynamically adapts to the REST service's request-processing performance by sending more or fewer rows for each multi-row request. It works like this:

The add-in sends an initial set of multi-row requests using a batch size based on the initial row count.
The add-in measures the response times for those initial requests and calculates the approximate number of rows that can be processed in the target response time.
Using the new value for number of rows per batch, the add-in sends one or more batches to the service.
After receiving the responses from each set of requests to the service, the add-in recalculates of the number of rows, and sends another set of requests until all rows have been processed.

Note:

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

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

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

If you configure the add-in for parallel requests, it sends up to four multi-row requests in parallel. See Enable Parallel Requests During Upload.

If you configure the add-in to send descendant rows in the parent payload, then multi-row requests are not used when a Form-over-Table layout is the primary layout. See Upload Parent and Child Changes in the Same Payload.

Configure Multi-Row Uploads

If required, you can configure how Oracle Visual Builder Add-in for Excel uploads multiple rows of data for an Oracle ADF REST Resource business object.

You can access multi-row processing settings from the Upload tab of the business object's Business Object Editor. This tab includes a check box, Supports Multi-Row Requests, to enable and disable multi-row requests. This check box is selected by default for ADF REST business objects.

The tab also includes two fields for configuring multi-row processing: Initial Row Count and Target Response Time.

By default, the add-in's adaptive multi-row upload algorithm uses a target response time of 10 seconds and an initial row count of 10 rows. It is strongly recommended that you do not change the default values for these properties. They should provide good functionality, UI responsiveness, and overall good performance. See About Multi-Row Processing.

However, there may be scenarios where you would want to adjust these properties. For example, your REST service may require that all of a small number of rows be included in a single multi-row request. In this scenario, you could increase the initial row count value something like 100, then instruct your business users to limit changes to sets of 100 at a time.

Another example might be a slow REST endpoint where a row-processing KPI needs to be achieved. In this scenario, you may want to tune these properties to balance UI responsiveness and the ability to process a targeted number of rows in a certain time.

WARNING:

Modifying these properties may negatively affect performance and UI responsiveness. Performance may degrade if there are too many smaller requests, or timeouts may occur with fewer, very large requests.

You should thoroughly test the affect of your changes in a realistic environment before distributing the integrated workbook.

To configure multi-row processing:

Open the Business Object Editor for the target business object. then click the Upload tab.

This image shows the Upload tab for an Employees business object with the Supports Multi-Row Requests check box as well as the Initial Row Count and Target Response Time fields.

Description of the illustration bo-editor-upload.png

You'll also find check boxes for parallel requests and upsert mode. For information about these features, see Enable Parallel Requests During Upload and Upload Changes Using Upsert Mode.
To enable multi-row processing, select Supports Multi-Row Requests.
If this check box is not selected, Initial Row Count and Target Response Time are not available.
Provide valid values for these fields:
- Initial Row Count: Specifies the number of rows that the add-in includes in the initial set of multi-row request(s). After the initial set of requests, the number of rows per request is determined dynamically. Enter a value of 1 or more.
- Target Response Time: Specifies the duration of a single response in seconds that the add-in tries to achieve during upload. Enter a valid value between 1 to 180 seconds.
The add-in validates the entered values and displays a red border on the field if the value is not valid. The add-in also displays a pop-up message describing the issue.

When enabled, the add-in sends multiple rows per request using a batch API during an upload. For information about multi-row processing behavior, see About Multi-Row Processing.

Disable Multi-Row Requests for Upload

Some Oracle ADF REST Resource services require a separate REST request for each changed row. For services with this requirement, you'll experience issues if the add-in uses multi-row requests. In this scenario, you can disable multi-row requests from the Business Object Editor.

If your business object doesn't require separate REST requests, it is strongly recommended that you use the default multi-row request behavior as described in Upload Changes Using Multi-Row Requests.

To disable multi-row requests for a business object, deselect the Supports Multi-Row Requests check box from the business object's Business Object Editor as shown here:

Description of batch-request-upload.png follows

Description of the illustration batch-request-upload.png

This check box is selected by default for Oracle ADF REST Resource business objects. This check box is not present for other REST service types.

When you disable multi-row requests for a business object, the add-in uploads each changed row using a single request as it does for other service types. See Upload Table Changes Using Separate Requests for Each Row.

Specifically, the add-in sends:

A row marked for Update using a PATCH request.
A pending Create row using a POST request.
A row marked for DELETE using a single DELETE request.
A row marked for a custom action using a single POST request even if the Supports Multi-Row Requests check box is selected for the custom action. See Multi-Row Mode for Custom Actions.

Depending on network characteristics and REST service overhead and performance, disabling multi-row requests may result in a negative effect on performance, such as adding significant wait time for the business user.

When multi-row requests are enabled, the add-in can adjust the number of rows in the multi-row request based on the REST service's responsiveness. When disabled, each changed row is instead sent using a separate REST request, resulting in many more REST requests per upload operation.

Workbook owners are responsible for any performance issues that arise. Make sure to test that your use cases will succeed in all customer environments and scenarios, if multi-row requests are disabled.

EffectiveOf Headers in Multi-Row Requests

If your ADF REST service includes date effective objects, you can add the Effective-Of header for each of the required REST operations. Oracle Visual Builder Add-in for Excel evaluates these headers when preparing each part of a multi-row request.

The add-in does not add the defined extra request headers to the header during a multi-row upload. Instead, the add-in checks for the Effective-Of header and, if defined, it adds it to the multi-row part using the effectiveOf member.

To support date effective objects when uploading using multi-row requests, define Effective-Of headers for the different REST operations configured for a business object. See REST Request Headers.

For more information on these objects, refer to Manage Date Effective Objects.

Upload Parent and Child Changes in the Same Payload

Some services require that parent and child row changes be uploaded using the same REST request payload. If your service requires this, you can configure your integrated workbook to upload all changes in a single, "nested" upload.

This feature is only available for Oracle ADF REST Resource services and should only be enabled if the service requires that parent and child changes be included in a single request. For example, your REST service may include logic that requires you to create at least one child (for example, a "Line" item) when you create a parent item (for example, a "Supplier Negotiations" item). In this case, the REST service enforces this by requiring the POST request that includes a new Supplier Negotiations item to also include at least one new Line item.

Likewise for updates. Your REST service may include back-end logic that requires operating on all changed child items at once and so all changes must be included in a single request.

If the service doesn't have this requirement, it is strongly recommended that you use the standard upload logic. See either Upload Changes from a Form-Over-Table Layout or Upload Changes from a Table Layout.

When enabled on a Form-over-Table layout, Oracle Visual Builder Add-in for Excel includes changes to parent (form fields) and child (table rows) in the payload of a single REST request.

You can also enable this for a set of dependent layouts. When you do this, all descendant rows ("children", "grandchildren", "great-grandchildren", and so on) of a parent are included in a single REST request. See Use Multiple Layouts for Multi-level Business Objects.

Note:

Read-only table layouts (and any descendant layouts) are skipped.

Before you continue, please review Notes and Limitations of Single Payload Uploads to determine if you should enable this feature.

To configure your workbook to use nested upload:

Open the worksheet with the top-level layout for the parent and child business objects that need to be uploaded together.
This could be a standalone Form-over-Table or the primary (Form-over-Table or Table) layout of a set of dependent layouts.
In the Oracle Visual Builder tab, click Designer to open the Layout Designer in the Excel Task Pane.
From the Advanced tab of the Layout Designer, expand Upload Options, then select Send Descendant Rows in Parent Payload.
When you select this check box, the add-in deselects these options for the table layout as well as any descendant table layouts:
- Send Only Changed Data for Updates
- Delete Enabled
- Custom Actions Enabled
Description of the illustration nested-upload.png
If you use this feature for a set of dependent layouts, add primary key columns in the child and descendant table layouts to support updates.
As with other dependent layouts, add ancestor (direct parent) columns to support creates. See Create a Table Layout in an Excel Workbook and Add a Parent Column to Support Row Creation.

When a business user performs an upload, the add-in sends parent and child changes in a single REST request and then reports the success or failure of the upload operation. If there are read-only table layouts, these layouts and their descendants are skipped.

If the upload was a success, the add-in makes these updates for each changed row:

The Change column in the table is cleared.
The Status column displays an appropriate success status message, such as "Create Succeeded" or "Update Succeeded".
The Status Viewer displays notification messages for the successful form and table operations.

Note:

Uploaded child and grandchild table rows are not refreshed automatically after the upload. The Status Viewer indicates this with a message: "This row may not contain the latest information from the service. Download to refresh the information for this row.". The business user will need to download data again to see the latest complete row data.

If the upload fails for any row at any level in a hierarchy, all associated rows in the hierarchy—starting at the top-level parent row and cascading down to its child rows, grandchild rows, and so on—are marked as failed.

In this scenario:

The Change column in the table is left as is for all failed rows, either because the row itself failed or was part of a row hierarchy that included a failure.
The Status column displays an appropriate failure status message for each failed row, such as "Created Failed" or "Update Failed".
The Status Viewer displays the same notification messages for failed rows. The messages contain any parent failure messages along with the failure messages for descendant rows.

Note:

Each row from the primary table is treated separately, so a failure in one does not affect the success or failure of another. If there are other top-level parent rows with nested changes, these are re-sent following the failure and should succeed.

When an error occurs, the business user may have to visit each layout and scan the rows to find which row caused the failure.

Notes and Limitations of Single Payload Uploads

Single payload uploads (also referred to as "nested" uploads) are only supported for Oracle ADF REST Resource services. They can be enabled on standalone Form-over-Table layouts or on the primary layout (Form-over-Table or Table layout) for a set of dependent layouts. Review this section to see if the feature is suitable for your integrated workbook.

Note:

If your service doesn't require that parent and child changes are included in a single request, it is strongly recommended that you use the standard upload logic described in Upload Changes from a Table Layout or Upload Changes from a Form-Over-Table Layout.

Requirements

The service must support request payload structures that include child and descendant rows included with the parent fields. Rows for child business objects are included as array-typed members as peers of the parent's fields. These child rows in turn include the child fields along with arrays of grandchild rows, and so on.
The business object must support the use of Upsert mode when uploading both new and changed rows in the same upload request.
This requirement refers to the service itself. Oracle Visual Builder Add-in for Excel does not have to be configured to use Upsert mode for the business object to support this feature.

Form and Table Capabilities

Form rows marked for deletion or custom action are not affected by this feature. These changes are sent in separate DELETE and POST requests and are not included in a "nested" upload request.

Some Table capabilities are incompatible with the nested upload feature. When this feature is enabled, these capabilities are disabled for the table in a Form-over-Table layout, the Table layout used as the primary layout in a set of dependent layouts, and any descendant table layouts:

Send Only Changed Data for Updates
Delete Enabled
Custom Actions Enabled

The following are not supported as part of a nested upload:

"Read-only" (or "download only") tables. Read-only tables are those that do not have create, update, delete, or custom actions enabled.
Tables for attachment business objects

Scalability

Be aware that this feature will not scale to handle large, enterprise-level volumes. If you enable this feature for your workbook, you are responsible for any scalability issues that arise. Make sure to test your workbook to verify that all required use cases will succeed in all customer environments and scenarios.

Potential scalability issues include:

Complex business logic or the processing of large numbers of nested items could push the request duration beyond what some load balancers and other network components, such as Akamai, support. This could result in the request failing due to a timeout.
Large request payload sizes will likely consume large amounts of memory and CPU. This could potentially affect other clients working against the same endpoint, virtual machine, or container.
For example, consider a set of dependent layouts with a Table as a primary layout that has a large number of changes. If all parent rows and associated descendant rows are sent in a single request, the payload size and processing time for the request may be significantly larger than if you sent each parent row and associated descendant rows separately.
Large request payload sizes could exceed the maximum supported by the endpoint. In this case, the endpoint could reject the request as a potential "Denial of Service" attack.

Data Consistency and Conflict Detection

When this feature is enabled, requests with parent and child changes in the payload may result in changes previously uploaded by another user being overwritten without notification. This can occur when multiple users are acting on the same business object rows using different workbooks.

Consider a scenario where two users, user A and user B, both download data at the same time. If user A uploads their changes to child or descendant rows and then later user B uploads their changes to the same rows, user B's changes will overwrite user A's changes.

Also note that the "Upsert" scenario, where an existing parent item is uploaded with new child rows, does not currently support conflict detection.

Child and Descendant Primary Keys Included in Tables

Additional configuration may be needed by the workbook developer to support cases where descendant rows are updated. To ensure that updates succeed, you must include primary/alternate key fields as columns in child and other descendant Table layouts.

The child items' key values must appear in the payload, in the array of child items. The service must be able to locate the existing child item in order to update it.

REST Request Headers

Sending configured REST request headers is only supported for the primary table rows during nested upload. Rows from child and descendant tables will not include such headers, even if configured. See REST Request Headers and EffectiveOf Headers in Multi-Row Requests.

Upload Changes Using Upsert Mode

Oracle ADF REST Resource 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 Upload tab of the Business Object Editor.

Note:

This check box is only available for Oracle ADF REST Resource 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 multi-row requests, 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 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 multi-row 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.

Note:

This feature is not available for NetSuite.

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 multi-row request. See Upload Changes Using Multi-Row 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 service owner 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.

Enable Parallel Requests During Upload

You can enable 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.

Parallel requests behave differently than multi-row requests. With a multi-row 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. See Upload Changes Using Multi-Row Requests.

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.

To enable parallel requests, open the Business Object Editor for the target business object, then click the Upload tab and select Supports Parallel Requests.

Description of bo-editor-upload.png follows

Description of the illustration bo-editor-upload.png

Note:

Previous releases of the add-in included a slider on the Advanced tab of the Layout Designer that allowed you to select the number of parallel requests. This slider has been removed in this release.