16 REST Service Support
This chapter provides additional technical details about how Oracle Visual Builder Add-in for Excel supports integration with REST services. It also provides information about technical known issues and limitations.
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 descriptions 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:
Support for Date Effective Objects
If you are working with a REST service that includes Date Effective objects, you can configure the Effective-Of
header as needed using the add-in's support for extra headers. See 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:
- Batch 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
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 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 portionhr_demo
is the schema/applicationemployees
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 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 description (metadata). 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 descriptions, 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 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 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 description 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 description 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:
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.
To add a NetSuite reference field:
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 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 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/orPOST
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 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 asPUT
,PATCH
, orDELETE
. - Existing row updates are enabled if the item path has either a
PUT
orPATCH
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 Request Headers
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 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.
To add an HTTP Request header to a business object:
Once you add a header, the workbook can no longer be used with prior versions of the add-in.
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. The next download will reflect any changes made to this value.
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 batch requests except for the
Effective-Of
header. See EffectiveOf Headers in Batch 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 thePOST
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 supportsapplication/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.
- OpenApi documents contain schema properties that are represented in JSON as something like
- 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.