REST Service Support

Service Types

Oracle Visual Builder Add-in for Excel provides support for the following REST service frameworks:

Oracle ADF REST Resource

ADF REST services provide rich service metadata that Oracle Visual Builder Add-in for Excel can analyze to provide a business object catalog with many details already filled out.

ADF REST services include many services offered by Oracle Cloud Applications. When the add-in integrates an Excel workbook with ADF REST services, it supports special features such as:

See Consuming ADF RESTful Web Services.

Support for Date Effective Objects

If you are working with a REST service that includes Date Effective objects, you can configure a row variable that allows your business users to specify a start date and an Effective-Of REST request header that references that start date. See Configure a Row Variable for a Layout and REST Request Headers.

For more information about Data Effective objects in ADF REST services, see Manage Date Effective Objects in the REST API for Oracle Fusion Cloud HCM guide.

Visual Builder Business Objects

Oracle Visual Builder Add-in for Excel supports catalogs that consist of custom business objects from Visual Builder applications that use the Visual Builder Business Objects (VBBO) API.

Prior to version 3.6, VBBO catalogs were included with ADF REST catalogs as "Oracle business object REST API" catalogs.

When the add-in integrates an Excel workbook with VBBO, it supports special features such as:

Multi-row upload
Custom actions (known as Object Functions in Visual Builder)
Graphical search editor

These features are not supported for VBBO:

Automatic configuration of filter parameters for Lists of Values
Oracle Fusion Applications Token Relay Authentication
Row finders
Polymorphic business objects

See Accessing Business Objects Using REST APIs.

Compatibility

Workbooks with a catalog of type VBBO cannot be used with add-in versions before 3.6.

VBBO catalogs created with versions 3.5 and earlier display an API type of "ADF REST" in version 3.6 and later. If desired, you can update the API type from the Advanced tab of the Business Object Catalog Editor.

Oracle REST Data Services

Oracle Visual Builder Add-in for Excel supports Oracle REST Data Services (ORDS) when you provide OpenAPI service metadata for an ORDS service. ORDS with AutoREST can provide an OpenAPI service metadata document.

For example, use http(s)://myhost.example.com:8888/ords/hr_demo/open-api-catalog/employees/ where:

myhost.example.com:8888 is the host and domain portion
hr_demo is the schema/application
employees is the database table

For information about AutoREST, see Automatic Enabling of Schema Objects for REST Access (AutoREST) in the Oracle REST Data Services Developer's Guide.

For manually-created REST services using ORDS, you'll need to define modules, templates, and handlers in order to get an OpenAPI service metadata document. See Manually Creating RESTful Services Using SQL and PL/SQL in the Oracle REST Data Services Developer's Guide.

After importing ORDS service metadata, you can use the Business Object Field Editor to provide additional information about each field to improve the overall user experience. For example:

Edit the field titles
Designate certain fields as required
Define lists of values. See Use Lists of Values in an Excel Workbook.

Known Issues with ORDS

Refer to these known issues when planning to use Oracle REST Data Services:

In some cases, the ORDS server returns Create Failed for rows, when in fact the Create operation was successful. Re-downloading rows into the table will show the created rows.
With an ORDS service, the PUT operation on the item path performs an "upsert" (see Update/Insert Table Row in Oracle REST Data Services Developer's Guide). So if you are about to update an existing row and someone else deletes that row, your update attempt may re-create that row. There's no warning or notice when this behavior occurs.

NetSuite SuiteTalk REST Web Services

Oracle Visual Builder Add-in for Excel provides limited support for integrating Excel workbooks with NetSuite services.

Unlike other services, you'll need to do some manual configuration to the NetSuite catalog to get up and running.

For layouts that reference parent-child business objects, you'll need to import a catalog and add required child business objects before you create your layout. If you try to create a layout without first doing these tasks, the generated catalog will be missing the child business objects. See Configure a NetSuite Catalog for Parent-Child Business Objects.

You may also be required to configure some fields following Table layout creation. See Add NetSuite Reference Fields for a Table Layout.

About NetSuite Services

When you create a layout, you'll need to provide an appropriate NetSuite URL for the service metadata document. You'll also need to provide a config file with OAuth 2.0 settings for your account since NetSuite services require OAuth 2.0 to authenticate.

To support OAuth 2.0 authentication with the add-in, you'll also need to create a NetSuite integration record to use with your integrated workbook.

Review this topic for information about NetSuite service metadata, OAuth2 authentication, and integration records.

NetSuite Concepts

Here are some key NetSuite concepts you should be familiar with:

Record: The NetSuite concept of a "Record" is roughly equivalent to the concept of "Business Object" used in this document.
Reference Field: NetSuite records support a concept known as a "Reference Field" where a field in one business object can reference a row in a different business object. For example, the NetSuite "contact" record has a field called "company". The company field can refer to a customer, partner, vendor, and so on.

For more information on NetSuite services, see SuiteTalk REST Web Services Overview and Setup in the NetSuite Applications Suite documentation.

NetSuite Service Metadata

NetSuite provides support for retrieving OpenAPI service metadata for NetSuite records available via REST. For example, to obtain the service metadata for "contact", use a URL similar to:

https://<YOURACCOUNT>.suitetalk.api.netsuite.com/services/rest/record/v1/metadata-catalog?select=contact

Note:

The add-in can retrieve and process the entire NetSuite catalog using ...services/rest/record/v1/metadata-catalog (without ?select). However, it is very large and takes a long time. So, selecting a specific record is recommended.

For information on OpenAPI metadata with NetSuite services, see Working with OpenAPI 3.0 Metadata.

OAuth2 Authentication

NetSuite services require OAuth 2.0 authentication. When you import a catalog or create a layout, you'll select OAuth 2.0 Authorization Code (PKCE) from the first screen of the wizard. The add-in then displays a screen for entering the required authentication properties. This screen includes an Import icon () you can use to import a JSON configuration file with the required values for your workbook. See Configure OAuth 2.0 Authorization for a Catalog.

Here is a sample OAuth2 config file for NetSuite:

{
  "type": "oauth2",
  "authorizationCode": {
    "clientId": "<value provided by your account admin>",
    "authorizationEndpoint": "https://<YOUR_ACCOUNT>.suitetalk.api.netsuite.com/app/login/oauth2/authorize.nl",
    "redirectionEndpoint": "<value provided by your account admin>",
    "accessTokenScope": "rest_webservices",
    "tokenEndpoint": "https://<YOUR_ACCOUNT>.suitetalk.api.netsuite.com/services/rest/auth/oauth2/v1/token"
  }
}

Copy this sample, paste it into a plain text file, then save it with a file name such as NetSuite-OAuth2-Config.json. You can then ask your NetSuite account admin to fill in the missing values and return it to you.

Note:

If your NetSuite account admin needs some pointers on the missing values, see rest-service-support.html#GUID-1FDF1442-3A22-462B-8FFE-12B072995DAC__GUID-1390C97B-5BE9-4E69-9523-67BA8802F2A1.

Integration Records

If you have not yet created an appropriate NetSuite Integration Record to use with your integrated workbook, refer to Integration Record Overview and Create Integration Records for Applications to Use OAuth 2.0 in the NetSuite Applications Suite documentation.

To support OAuth 2.0 authentication with the add-in, the integration record must have these configuration settings enabled:

Public Client check box. This is required since the add-in is considered a public client.
REST Web Services check box

Note:

Make sure to capture the client ID during this process.

Once you have an appropriate integration record, it can be used with multiple different integrated workbooks that are integrated with NetSuite services that use this account.

For more information on how the add-in supports OAuth2, see OAuth 2.0 Authorization Code Flow with PKCE.

Configure a NetSuite Catalog for Parent-Child Business Objects

Before you can create a layout or layouts from a NetSuite service that references parent and child business objects, you'll first need to import the catalog and manually add the required child business objects. The NetSuite service metadata document does not provide child paths.

You'll need to complete this task if you want to create a Form-over-Table layout or a Table layout with one or more dependent layouts at the child level.

Suppose you want to create a Form-over-Table layout with a "Sales Order" business object in the form and the child business object, "Items", in the table. Because the NetSuite service metadata document doesn't provide the child path, you won't see the Items child object in the New Layout Setup wizard when you try to create your Form-over-Table layout.

Instead, you need to start by importing the catalog, then adding the child object. When you do this, you'll provide the collection and item paths as well as configure path parameters. You'll also need to add all required fields for the child object.

For more information about parent and child path requirements, see Requirements for Dependent Layouts.

Note:

This task covers adding a child business object, configuring it, and then adding fields. Before you begin, import your catalog as described in Import a Business Object Catalog.

To configure a child business object for a NetSuite catalog:

Add a child business object to the catalog:
1. Open your new catalog, then open the Business Object Editor for the parent object, "Sales Order".
2. From the Children tab, click Add Child Business Object ().
Configure your new child object:
1. Open the new child business object in the Business Object Editor, then provide details such as a title and description for the business object.
2. Enter the collection and item paths for the child object.
  
  The collection and item paths may look like this for an Item object.
  
  Description of the illustration path-item-bo-editor.png
3. Click the Edit buttons for each path, then edit the path parameters from the Path Editor.
  
  Add all REST request methods that are supported by the services. This image shows the GET method configured for the collection path.
  
  Description of the illustration path-parameters.png
4. From Download tab, make these changes:
  - Leave the Offset Parameter Name and Limit Parameter Name fields empty.
  - Enter "items" in the Response Payload Items Member Name" field.
Add fields for the child object from the Fields tab of the editor. For information about available fields, see NetSuite REST API Browser.

This image shows configured fields for the Items child object.

Description of the illustration fields-child-bo.png

When you are finished, create your layouts in the usual way. You'll see the nearly-added child business object in the New Layout Setup wizard when prompted to choose a child business object for the table part of a Form-over-Table layout or for a dependent layout. See Create a Table Layout in an Excel Workbook or Create a Form-over-Table Layout in an Excel Workbook.

Note:

When the business user edits the form, the downloaded data may be marked as invalid. If these two conditions are met, then clear the value and upload changes:

The cell value is not changed after download but is still marked as invalid.
The field's Omit from payload if value is empty check box is selected.

Add NetSuite Reference Fields for a Table Layout

When you create a Table layout for a NetSuite business object, some fields may be missing due to limitations in the service metadata. Here are the steps to add a "Company" field for a "contact" business object.

Before you begin, create a Table layout for the contact business object. You'll be prompted to provide the URL to a service metadata document as well as an OAuth2 configuration file. See About NetSuite Services.

To add a NetSuite reference field:

Open the Business Object Editor for the "contact" business object, then click the Fields tab.
Click Add Field (), then provide details for the Company field:
- ID: company
- Type: Object
- Subfields: Add two subfields of type string with ID values of "company/refName" and "company/id".
  
  Note:
  Keep in mind that IDs are case sensitive and must match the NetSuite JSON member names exactly.
- Provide Title and Help Text values as desired.
Description of the illustration company-reference-field.png
Return to the table layout.
From the Layout Designer, click the Columns tab, then click Manage Columns ().
From the Table Column Manager, add the subfields to the layout as needed. For example, you might find it useful to add the "company/refName" to your contact layout.

Note:

There is no support for including additional fields from the referred record in the referring table. So, using the example above, you cannot include additional company (customer, partner, vendor, etc.) fields in the contact table.

For more information about the contact record, see contact in NetSuite REST API Browser: Record API v1. See also Format of Selects and References in the NetSuite Applications Suite documentation.

NetSuite Support Limitations and Known Issues

Here are some things to keep in mind when using creating layouts for NetSuite records.

Limitations

These NetSuite features are not supported by the add-in:

Record actions
Transforming records
Upsert operation

These add-in features are not currently available for integrated workbooks connected to NetSuite services:

Send only changed data

Known Issues

Feature Area	Issue	Description
Mandatory Properties	The add-in cannot properly set default values for the "Required for Update" and the "Required for Create" properties for a field.	The OA3 service metadata document does not provide the "Required" property for schema members (fields). You can manually configure these properties using the Business Object Field Editor. See Configure Business Object Fields.
Data Download	Download performance is suboptimal.	A separate GET request is needed for each row because there is no way to get all fields for several rows in a single GET request. This limitation causes performance to suffer.
	The GET request for a single item does not include some of the expected fields in the response payload.	For example, "Last Sale Date" and "Opening Balance" in a "customer" business object.
	Query by Internal ID field returns an error
Data Upload	Some fields in some NetSuite services do not accept a null value in a POST/PATCH request even though the service metadata document describes them as "nullable".	Workaround: Review the error message details to identify the fields causing the error, then configure these fields to omit empty values from the payload. See Omit Empty Values During Upload.
	After a successful upload to add or update a row, the row's values in the layout does not reflect changes made on the server.	In this case, the response does not include the updated fields. The user will need to re-download to see any changes.
	When uploading Create/Update rows, the error message returned by the service is missing specific details.	You instead see a generic message such as "Error while accessing a resource. Field must contain a value." is returned without indicating which field it is.
	Conflict detection is not supported	If a user uploads changes to the service that overwrite another user's recent changes, the upload succeeds with no notification of the conflict.
Lists of Values	List of values are not configured automatically on business object fields.	Configure fields with list of values using a local data source. See Create a Local Data Source for a List of Values and Configure a List of Values with a Local Data Source.

Other Services

Oracle Visual Builder Add-in for Excel can also be used with other service types as long as the service behaves as the add-in expects.

When using another REST service, provide an OpenAPI service metadata document as you would for other service types. See REST Service Support Limitations for more information on the add-in's expectations for any service.

Note:

The graphical search editor is not available for these services but search parameters can be used. See Use Search Parameters to Limit Downloaded Data.

Supported Data Types

The add-in supports a variety of data types exposed by business objects in web applications developed using Visual Builder and data types exposed by REST services.

The add-in supports the following OpenAPI data types (derived from the JSON Schema Specification). This table shows how OA3 (JSON) data types are mapped to the Data Type property for Business Object fields as shown in the Business Object Field Editor.

JSON Type	Business Object Field Data Type	Notes
boolean	Boolean
integer	Integer	`integer` is defined as a JSON number without a fraction or exponent part.
object	Object	See the Subfields description in Configure Business Object Fields.
number	Number
string	String
string	Date-time	When the OA3 `format` property is "date-time"
string	Date(no time)	When the OA3 `format` property is "date"
array	n/a	Not supported

For OA3 properties with type "string", the following format values are explicitly unsupported:

time
binary
byte
long-text

Other format values are ignored and the field is mapped to the String data type.

The add-in ignores fields with unsupported data types when you create a Table layout or Form-over-Table layout in the Excel workbook. If, for example, a service that you use to retrieve data includes the binary attribute data type, the add-in ignores it and does not create a column in the data table for this attribute type.

Note:

File, text, and web page type attachments are supported. See Create Layouts for Attachment Business Objects.

For more information, refer to the following OpenAPI and JSON resources:

Business Objects Harvested from OpenAPI Metadata

Oracle Visual Builder Add-in for Excel identifies business objects in an OpenAPI document primarily by examining the paths defined in the service metadata.

The add-in creates a business object for each collection path defined in the OpenAPI document. A valid collection path:

Does not end with a path parameter replacement token like {department_id}
Defines GET and/or POST operations

Examples of valid collection paths are:

/Departments
/Departments/{department_id}/child/Employees

The add-in associates the collection path with any related paths. For example, the add-in associates an item path with a collection path if the path value is identical with the addition of a path parameter replacement token at the end. This comparison is case sensitive. An item path value of /Departments/{department_id} would be correctly associated with a collection path value of /Departments. In contrast, /Departments and /departments/{department_id} would not be associated with each other.

A business object is then defined by its collection path, item path (if present), and any other associated paths (custom actions, for example).

Note:

Certain paths may be ignored by the add-in depending on the service type.

Relationships between business objects are also determined by comparing the path information. A business object is considered a descendant of another business object if the collection path value starts with the collection and/or item path of another business object. This comparison is case sensitive. A business object with collection path /Departments/{department_id}/child/Employees is a valid child of a business object with collection path /Departments and item path /Departments/{department_id}.

Required Fields

When a business object field is created from service metadata, the initial values of the Required for update and Required for create properties are set based on the following OpenAPI property:

The Required for update value is determined by the request body schema of the PUT (or PATCH) operation for the item path, along with the OpenAPI Required array property.
The Required for create value is determined by the request body schema of the POST operation for the collection path, along with the OpenAPI Required array property.

When these PUT/PATCH or POST operations are not available, the Required properties are set using the response body schema of the collection GET operation and the OpenAPI Required array property. If the OpenAPI Required array property is not present in a schema, the corresponding Required property defaults to false.

You can always edit the Required for update and Required for create values in the Business Object Field Editor. See Configure Business Object Fields.

REST Operations

Table and Form-over-Table capabilities are enabled for GET, PUT, PATCH, POST, and DELETE operations as follows:

Download is enabled if there is a GET operation on the collection path. Note that you must first download rows before you can perform operations on the item path. Therefore, GET on the collection path is a prerequisite for operations on the item path such as PUT, PATCH, or DELETE.
Existing row updates are enabled if the item path has either a PUT or PATCH operation.
Depending on your configuration, the add-in includes either all data values or just changed values in editable cells for the changed row(s) on upload. For information about how to send only changed values, see Send Only Changed Data During Upload.
Create new rows is enabled if the collection path has a POST operation.
Delete existing rows is enabled if the item path has a DELETE operation.

Note:

Even if the business object supports an operation, you can still choose to disable a layout's capability by deselecting it in the Layout Designer's Advanced tab.

REST Request Headers

If required, you can add HTTP header fields to REST requests used in an upload operation. You can do this with any REST framework unless noted otherwise.

You can only add request headers to PUT, PATCH, POST, and DELETE operations. Additional request headers are not supported at download (GET operation).

During an upload, the add-in prepares the REST request as usual with the standard HTTP header fields. If the corresponding operation has additional headers defined, these values are evaluated and the headers added.

Reserved headers are skipped with no error. See Notes on REST Request Headers.

The add-in does not validate the header names or values. Be sure to consult the documentation for your target service before attempting to use request headers. An incorrect header may result in "Bad Request" errors.

Configure a Request Header

You can add required REST request headers using the Operation Editor available from a business object’s Business Object Editor.

The value you enter should be a string. If you want to use a non-string value, such as a date value, consult the REST service documentation to determine how the header should be formatted.

Header values can also include an expression. If you use an expression, make sure to follow the Oracle Visual Builder Add-in for Excel expression rules, in particular the rules for escaping ({ }) and using literal values. Reserved words, other than RowVariables, are not supported. See About Expressions.

You can define headers for child business objects as well. But keep in mind that, if the child data is sent in the parent payload, any child headers are ignored since the add-in does not send requests at the descendent level. See Upload Parent and Child Changes in the Same Payload.

This feature supports the use of date effective objects in a workbook. Changes to date effective objects are uploaded to the service based on a range start date. To support this, you'll need to add a date field, called a "row variable", to your layout that your business users can use to enter the start date for a row. See Use Row Variables for a Business Object.

To ensure that the add-in only uploads changed rows based on the date in the row variable, you'll need to create an Effective-Of header for the PATCH operation that uses an expression to reference the date value in the row variable. See EffectiveOf Headers in Multi-Row Requests for more information about the use of Effective-Of headers by the add-in.

This task uses the example of an Effective-Of header on the PATCH operation that refers to a row's row variable, rangeStartDate.

To add an HTTP Request header to a business object:

Open the Business Object Editor for the business object. You can open the editor from the Layout Designer or by clicking Manage Catalogs from the Visual Builder ribbon.
From the General tab, click the Edit button next to the Collection Path or the Item Path to open the Path Editor. See REST Operations for more information on paths and operations.
From the Path Editor, select the desired operation and click the Edit Operation icon ().

Description of the illustration path-editor-patch-operation.png
From the Headers tab of the Operation Editor, click Add a new header ().
Select the desired header name (or type the name if it is not in the list); for example, effective-Of. Note that header names are case-insensitive.
Type the desired value; for example, RangeMode=UPDATE;RangeStartDate={ this.BusinessObject.RowVariables['rangeStartDate'].Value }.

Description of the illustration operation-editor-request-header.png

In this example, the RangeStartDate attribute is set to the value of the row variable, rangeStartDate, in the selected layout.
Click Done.

Once you add a header, the workbook can no longer be used with versions 3.6 or earlier.

Before you distribute the workbook, it is recommended that you test the defined headers by opening the Network Monitor and upload a change using the Upload Changes Data button from the Oracle Visual Builder ribbon. The Network Monitor shows the add-in's requests and the service's responses. See Network Monitor.

If you experience issues, try modifying the request header value and then clicking Upload Changes again.

Notes on REST Request Headers

Review this section for more information on reserved headers and limitations.

Reserved Headers

Some HTTP header fields are reserved by the add-in. If you use one of these headers for a REST request, the add-in will ignore it.

The reserved HTTP header fields are:

User-Agent
Authorization
Accept
Content-Encoding
Host
Content-Length
REST-Framework-Version
Accept-language
Accept-Encoding
Content-Type

Limitations

Headers defined for GET operations are ignored. These include operations for download, lists of values (LOV), describe, and so on.
Request headers are not supported for ADF REST multi-row requests except for the Effective-Of header. See EffectiveOf Headers in Multi-Row Requests.
Headers are not supported for attachment business objects.
Headers are not supported for custom actions.
For ADF REST services, do not add the Upsert-Mode header on the POST operation for the collection path. Instead, enable Upsert Mode from the Upload tab of the Business Object Editor. See Upload Changes Using Upsert Mode.

Natural Language Support

For every request that the add-in makes to the REST service, Oracle Visual Builder Add-in for Excel automatically adds the accept-language header.

By default, the value sent with the accept-language header is the language/culture code that Excel is currently configured to use. You can change the language as described in Change the Add-in's Language.

Each REST service determines how and whether it will react to the accept-language header.

Object-typed Fields and Subfields

Oracle Visual Builder Add-in for Excel supports fields of type Object. These fields may expose subfields, also known as "nested" fields.

Consider, for example, an Employee business object with the following fields:

First Name (type: String)
Last Name (String)
Address: (Object)
- Street (String)
- City (String)
- State (String)
- Zip (String)
- GPS Coordinates (Object)
  - Latitude (Number)
  - Longitude (Number)
Hire Date (Date)

In this example, the type of the Address field is Object and it contains subfields. Object fields should not be confused with arrays. In this example, an Employee has only one Address. The add-in does not support fields that are typed as arrays.

The add-in handles Object fields and their subfields in the following manner:

First, in the Business Object editor Fields tab, only the top-level fields are listed. In this example, the top-level fields are: First Name, Last Name, Address, and Hire Date. To edit the properties for subfields of Address, edit Address and find the Subfields list on the Field Editor window. The direct subfields for Address are Street, City, State, Zip, and GPS Coordinates. Since GPS Coordinates is of type Object, its field editor will show its subfields (Latitude, Longitude).
Next, when creating a Table layout from a business object's fields, the add-in promotes the subfields and creates columns for each (leaf) subfield. This maintains a regular, rectangular structure for the table in the worksheet. So, the above example generates a table with these columns:
- First Name
- Last Name
- Address / Street
- Address / City
- Address / State
- Address / Zip
- Address / GPS Coords / Latitude
- Address / GPS Coords / Longitude
- Hire Date

REST Service Support Limitations

Refer to these limitations when planning to integrate a workbook with a REST service using Oracle Visual Builder Add-in for Excel.

Caution:

Many different request and response schema types are possible and we cannot list all that are compatible with the add-in. If a particular structure is not listed explicitly as supported, it may not work.

The add-in supports REST response payloads up to 1 billion characters in size.
Only REST API services that return application/json media types as response payloads are supported. The add-in supports application/octet-stream for attachments. Other media types such as XML are not supported.
Asymmetrical field lists. Since download, editing, and upload all occurs in the same Excel rectangular grid, the add-in counts on having a single set of field IDs (JSON member names) for both download and upload. If the REST service uses different field IDs for the same information when completing different operations, it cannot be used effectively with the add-in.
Fields with forward slash ('/') in the member name:
- OpenAPI documents contain schema properties that are represented in JSON as something like "memberName" : { . . . properties describing the field ... }
- When creating the business object field from the JSON member, the add-in uses the member name as the field ID.
- Field IDs that include the / character are incompatible with the add-in, so such members will not be represented as fields in the business object.
URLs longer than 8000 bytes may fail due to limitations in various network devices between the add-in and the server.

If a REST service owner makes significant changes to the service after the workbook is configured to integrate with the service, the integration may not function as expected. In such cases, you can either re-import the service metadata and create a new layout, or refresh the business object catalog. If the change is minor, you can update the business object details to match the change in the service. See Manage Catalogs and Business Objects.