REST Service Support

Service Types

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

Oracle Business Object REST API Services

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

Oracle business object REST API services include many services offered by Oracle Cloud Applications as well as custom business objects in Visual Builder. When the add-in integrates an Excel workbook with Oracle business object REST API services, it supports special features such as:

See Accessing Business Objects Using REST APIs and Creating ADF RESTful Web Services with Application Modules.

Oracle REST Data Services

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

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 description. See Manually Creating RESTful Services Using SQL and PL/SQL in the Oracle REST Data Services Developer's Guide.

After importing an ORDS service description, 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 SuiteTalk REST web services using Table layouts. Refer to this topic for more information on workbook and authentication requirements, as well as support limitations and known issues.

The NetSuite concept of a "Record" is roughly equivalent to the concept of "Business Object" used in this document.

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

To create a table layout for a NetSuite REST service, you need:

An appropriate NetSuite URL for the service description (metadata)
A config file with the OAuth2 settings for your account

See the following sections for details.

NetSuite Service Descriptions

NetSuite provides support for retrieving OpenApi3 service descriptions for NetSuite records available via REST. For example, to obtain the service description 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 3.0 metadata with NetSuite SuiteTalk REST web services, see Working with OpenAPI 3.0 Metadata.

Authentication

NetSuite REST services require OAuth2. In order to create a table layout for NetSuite, you need to select OAuth2 when in the first page of the wizard.

On the next page, import your NetSuite OAuth2 config file. If you don't have one, see the next section.

NetSuite OAuth2 Configuration Files

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 the next section.

You can now create a Table layout for NetSuite REST services. See Create a Table Layout in an Excel Workbook.

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.

NetSuite Reference Fields

NetSuite records (or business objects) 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.

The add-in can work with NetSuite reference fields. However, some extra configuration is required.

When you create a catalog and table layout for "contact", the company field is missing initially (due to limitations in the service metadata). Here are the steps to add Company to your table layout:

Open the Business Object editor for "contact".
Click the Fields tab, then add a new field for company:
- 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.
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.

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.

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.

Limitations

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

Record actions
Sublists are not supported. See Working with Sublists.
Subrecords are not supported. For example, for /task, you can't manipulate fields like contact, owner, transaction, and so on. See Working with Subrecords.
Transforming records
Upsert operation

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

Form-over-Table layout or dependent layout hierarchies
Refresh field titles
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 description 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 description 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 description 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 document.

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 descendent 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 a service description, 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. For these operations, the add-in includes all data values from editable cells for the changed row(s) on upload, regardless of whether the data value was edited since download.
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.

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 Sub-fields

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

If, for example, you have 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 sub-fields. 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 sub-fields 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 sub-fields of Address, edit Address and find the Sub-fields list on the Field Editor window. The direct sub-fields for Address are Street, City, State, Zip, and GPS Coordinates. Since GPS Coordinates is of type Object, its field editor will show its sub-fields (Latitude, Longitude).
Next, when creating a Table layout from a business object's fields, the add-in promotes the sub-fields and creates columns for each (leaf) sub-field. 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 description 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.