5 Manage Catalogs and Business Objects

When you create a layout, you provide the service description document for the REST service which Oracle Visual Builder Add-in for Excel uses to generate the Business Object catalog for your workbook. If required, you can use the available editors to modify the catalog in a variety of ways to enhance the overall user experience. For example, you can use the Business Object Field Editor to add help text to business object fields.

When you edit the details of business objects in your workbook, you're only telling the add-in how the service operates, you are not telling the service what it should do. Service behavior cannot be changed from the workbook. So any changes you make to the catalog in the workbook must be compatible with the service.

To view the editors progressively, start by clicking Manage Catalogs from the Oracle Visual Builder tab. This opens the Manage Business Object Catalogs editor. From here, you can choose a catalog and open the Business Object Catalog Editor, and so on.

You can also open the business object and business object field editors from the Layout Designer. Both options (using Manage Catalogs and the Layout Designer) are shown here:

Description of the illustration excel-service-editors-combined.png

Use these editors to perform the following tasks:

• Manage Business Object Catalogs: Let's you import, open, and refresh a catalog
• Business Object Catalog Editor: Let's you configure the host and base paths for a catalog, as well as configure the business objects in the catalog. You can also configure more advanced settings such as authentication, GZIP, and the REST API Framework.
• Business Object Editor: Let's you override the base path of the object, view service description path details, configure pagination, and configure row finders
• Business Object Field Editor: Let's you edit field titles, change when a field is editable, adjust the field data types (Advanced), and configure Help text. You can also configure the list of values for a field.

Note:

When reading through the topics in this section, you'll come across many terms such as OpenAPI, REST service, and path. Before you begin, refer to Key Concepts, Components, and Terms to familiarize yourself with some of these terms.

Add a Business Object to an Existing Catalog

If your catalog is missing a business object, you can add a new business object description to the catalog using the Business Object Catalog Editor. The new business object will be located at the same host/base path as the other business objects in that catalog.

This is particularly useful when configuring a list of values.

1. Click Manage Catalogs.
2. In the Manage Business Object Catalogs window, select your existing catalog and click the Edit Business Object Catalog icon.
3. From the Business Object Catalog Editor, click Business Objects.
4. From the Business Objects tab, click + Add, then select Import a Business Object from a Service Description from the menu.
   
   

   
   Description of the illustration excel_add_bo_to_catalog.png

   
   
5. Provide the URL or a file that contains the OpenAPI description of the new business object and click Next.
6. Review the details of the new business object, then click Finish.
7. Click Done first in the Business Object Catalog Editor, then in the Manage Business Object Catalogs window.

   Note:

   If Oracle Visual Builder Add-in for Excel finds more than one business object in the service description you provide, only the first business object is added to the selected catalog. All other business objects are ignored.

Import a Business Object Catalog

Although most integrated workbooks will only ever need a single catalog, you can import another business object catalog by providing Oracle Visual Builder Add-in for Excel the catalog's service description document.

Caution:

If you want to add an additional business object to your workbook, you should generally add it to your existing catalog. See Add a Business Object to an Existing Catalog for the steps.

If you need to use a different service at a different host or framework type, you can create an additional catalog instead by following the steps in this topic.

1. Open the workbook where you want to import the catalog, then click Manage Catalogs from the Oracle Visual Builder ribbon.
2. From the Manage Business Object Catalogs editor, click + Add, then select Import Business Object Catalog From Service Description from the menu.
   
   Description of the illustration excel-add-service.png
3. Follow the instructions in the wizard to import the catalog.

After you import a catalog into the Excel workbook, consider editing it in the Business Object Catalog Editor so that the catalog has a descriptive name. Very often, the title that the add-in displays for the catalog in the Excel workbook is the name of one of the business objects that the service exposes. For example, this image shows a catalog in which the default value for the Title property is Employees. This catalog exposes an Employees business object, so to avoid confusion, you can change the title that the catalog uses in the Excel workbook to something like Human Resources Services.

Description of the illustration excel-change-service-name.png

Note:

Certain OpenAPI document properties, such as Description, can contain formatting hints. The add-in displays the description text as is, with no interpretation of such hints.

Create a Business Object Catalog from a Data Sample

Use Oracle Visual Builder Add-in for Excel to create a business object catalog from a data sample when you don't have an OpenApi-compliant service description.

If you have the host, base path, and resource name for the service description, the add-in can form the REST endpoint URL and send a GET request to the service to fetch a sample of data. The add-in can then use this sample to produce a list of fields available for that REST resource.

The add-in does this by first analyzing the JSON response to find the first JSON array available. It then selects the first JSON object in that array and, from this object, produces a list of business object fields. The add-in then creates a new business object in the catalog with that list of fields.

This new business object includes best guesses for the following:

• The Collection Path including GET and POST (create) operations
• Item Path including GET, PATCH (update), and DELETE operations
• Field properties such as data type

Note:

These guesses may not match the actual capabilities of the service. It is recommended that you review the business object definition and make sure it matches the service.

1. Click Manage Catalogs from the Oracle Visual Builder ribbon.
2. Open the desired catalog or create a new one.

   Make sure the Host and Base Path properties are set correctly.

3. From the Business Objects tab, click + Add, then select Create a New Business Object from a Data Sample from the list.
4. Type in the name of the desired resource and click OK.
   
   
   Description of the illustration resource-sample.png
   
   
5. Review the business object definition to ensure it matches the service.

   Here are some things to watch for:

   • If the service does not support delete, remove that operation from the item path or turn off delete in any layout that uses this business object.
   • If a given field is required or read-only, update the field settings from the General tab of the Business Object Field editor. Date fields may appear as string fields. Adjust the data type of fields as needed.
   • The add-in can't determine the data type of fields with null values. In these cases, the data type is set to "unsupported". These fields are not eligible for layouts, search, and so on. For these fields, correct the data type using the Business Object Field editor.
   • The add-in guesses that the primary key field is named "id". If the primary key field has a different name, use the Business Object Field editor to update the item path as well as the parameter on each operation in the item path.

Configure Business Object Fields

Use the Business Object Field Editor to view and modify field settings as required.

Many of these settings control the appearance and behavior of the fields in your workbook and can be modified as desired. For example, you can enhance the usability of your workbook by:

• Changing a field title to something more intuitive.
• Overriding the default format for a field.
• Adding help text to help your business users understand the purpose of the field.
• Defining custom field validation rules.
• Requiring the business user to provide a value for a field in the search prompt.
• Adding a list of values to ensure your business users can only enter a valid value for the field.

WARNING:

Some settings in the editor come from the service description document and reflect how the REST service behaves. Changing these settings in the workbook cannot change the behavior of the service and may result in errors from your REST service. Refer to the notes in the table for recommendations for each field.

To modify the settings for a business object field:

1. Open the business object that includes the field in the Business Object Editor.
   You can navigate to the Business Object Editor from the Layout Designer or by clicking Manage Catalogs, then opening first the catalog and then the business object.
2. From the Business Object Editor, click the Fields tab.
3. Select the field you want to modify, then click the Edit icon () to open the field in the Business Object Field Editor.
4. From the General tab, view or modify the settings as appropriate.

   Setting	Description
   ID	The ID for the field. WARNING: Do not modify the ID. The ID value must match the JSON member name expected by the service. JSON member names are case-sensitive.
   Title	The name of this field. This value is used in various places, such as for the column header or form field label. This value can be localized. Provide a short value that is meaningful to your business users.
   Help Text	A description of the field intended for business users. This value appears near the title where possible. This value can be localized. Provide a brief explanation of what values are expected for this field. See Add Help Text to Your Workbook
   Data Type	The data type for the field, used for encoding and decoding data from the service, as well as data validation and cell formatting. Data types include values such as: Boolean Date (no time) Date-Time Integer Number String WARNING: Do not modify the data type without consulting the service owner.
   Format	The cell formatting setting for the field. The default setting (Default) indicates that the field uses the standard formatting for the data type, for example, mm/dd/yyyy for date fields. To use a different format style, select an option from the list such as Long Date for a date field. This option renders "7/15/2022" as "Friday, July 15, 2022". See Choose Field Formats.
   Description	An internal technical description for the field. This value only appears in the designer. It is not localizable.

5. From the Constraints tab, view or modify the settings as appropriate.

   Setting	Description
   Required for update and Required for create	Ensures a value is provided for the field during create or update: If selected, the add-in checks that there is a value in the field cell and displays a data entry error if there is no value. The business user won't be able to upload the new or updated row until a value is provided. See Understanding Data Validation in Managing Data Using Oracle Visual Builder Add-in for Excel. If unselected, the add-in doesn't require a value. The business user can upload the new or updated row without a value for this cell. WARNING: Don't deselect these check boxes without first consulting with the REST service owner. These settings come from the service description document and reflect requirements of the REST service. If you deselect these check boxes, the add-in attempts to upload the new or changed row. If the field is required and no value is provided, the service may return an error similar to a "(400) Bad Request" error. See Required Fields.
   Editable on update and Editable on create	Allows or prevents write operations on the field during create or update: If selected, the business user can provide a value during create or update. If unselected, the field cells are set to read-only. See Understanding Read-Only Behavior in Managing Data Using Oracle Visual Builder Add-in for Excel.
   Searchable	Determines if a field can be used in a search. If selected, the field is available when creating a search for the workbook. See Use Search to Limit Downloaded Data. WARNING: The default value depends on the service description document. Make sure that the service supports searching on the field before selecting this check box.
   Required for search	Determines if a value is required for the field during a search: If selected, a value must be provided for the field on download. Required fields are indicated by an asterisk (*) in the Search Editor. If unselected, no value is required. If none is provided, the add-in ignores the field when retrieving data. See Use Search to Limit Downloaded Data.
   Omit from payload if value is empty	Determines if the field is omitted from the payload if the cell value is empty. If selected, empty values are omitted. See Omit Empty Values During Upload.

6. Define a custom field validation rule using the Validation Rule and Validation Failure Message fields as described in Create Field Validation Rules.
7. To configure a list of values on the field, click the List of Values tab and configure it as described in Configure a List of Values with a Business Object.

Set an Authentication Method for a REST Service

Configure authentication for Oracle Visual Builder Add-in for Excel when connecting to the REST service.

When you create a new catalog from a URL, you can configure the authentication method depending on the service. The add-in supports five authentication methods: Default, Basic Access Authentication, Oracle Fusion Applications Token Relay, OAuth 2.0 Authorization Code (PKCE), and No Authentication.

At log in, the add-in uses this setting to determine how to log in. If required, you can change the authentication method using the Advanced tab of the Business Object Catalog Editor.

See Authentication Options for more information.

1. Click Manage Catalogs.
2. In the Manage Business Object Catalogs window, select your existing catalog and click the Edit Business Object Catalog icon.
3. In the Business Object Catalog Editor, click Advanced.
4. Select an authentication method from the Authentication list:
   • Default: At login, the add-in pings an Oracle Cloud Application anti-CSRF servlet endpoint. If the ping succeeds, Token Relay is used. If the ping fails, Basic authentication is used instead.
   • Basic Access Authentication: See Basic Authentication.
   • Oracle Fusion Applications Token Relay: See Oracle Fusion Applications Token Relay Authentication.
   • OAuth 2.0 Authorization Code (PKCE): See OAuth 2.0 Authorization Code Flow.
   • No Authentication: There is no prompt for credentials. No authentication-related headers are added to requests.

   Caution:

   Be sure to choose an authentication method that is compatible with your catalog. For example, Oracle Fusion Applications Token Relay is not supported with Oracle REST Data Services (ORDS). If you pick token relay for an ORDS catalog, authentication fails.
5. If you selected OAuth 2.0 Authorization Code (PKCE), click Edit Authentication Flow Properties and enter the required authentication properties.

   You can also use the Import button to upload a JSON file containing the authentication properties. See OAuth 2.0 Authorization Code Flow.

Override a Business Object's Base Path

You can configure a business object to use a different base path than the rest of the catalog. The Oracle Visual Builder Add-in for Excel then uses this base path when making REST requests for the business object.

Typically, a Business Object Catalog holds business objects that share a base path (say, /abcRestApi-context-root/resources/v1). But if a business object needs to use a different base path (for example, your list of values come from /123RestApi-context-root/resources/v1), you can provide a new base path for the business object through the Business Object Editor.

To configure a business object to use a base path different from the one shared by business objects in the Catalog:

1. Add a business object to your Business Object Catalog. See Add a Business Object to an Existing Catalog.
2. Click the General tab in the Business Object Editor, enter the different base path in Base Path Override to override the base path used by all business objects in the Catalog, and click Done.
   
   
   Description of the illustration base_path_override.png
   
   

The add-in assumes that no extra authentication is required by business objects with a base path override.

You can now use the newly created business object as the data source business object for a list of values if required. See Configure a List of Values with a Business Object.

Manage Metadata Path Information

When you provide a service description document, Oracle Visual Builder Add-in for Excel captures the path to the service description document relative to the service host and base path of the catalog. You can view this path and other path settings from the Business Object Editor.

This relative path is displayed in the Metadata Path field on the General page of the Business Object Editor. It also captures path information such as REST API methods and parameters and displays these details in the Path Editor.

View these settings by clicking the Edit button next to the Metadata Path field.

Description of the illustration metadata-path-settings.png

The values seen in this image are correct for an Oracle business object REST APIservice. Other service frameworks may require other values. For example, Oracle REST Data Services (ORDS) might have a metadata path of "/open-api-catalog/employees" and a content type of "application/json". Refer to Oracle REST Data Services for more details.

Caution:

The metadata path should include a GET method that specifies the correct request content type that the service expects for a metadata request. If you need to modify these settings, be sure to provide values that will result in the service returning a proper OpenApi version 3.x service description.

Consult the REST API owner for the required values.

Configure Pagination for a Business Object

If the REST service supports pagination, you can download pages of rows.

Imagine you need to download 10,000 rows of data. Downloading one row at a time is too time-consuming, and attempting to download all 10,000 rows in one request might result in a timeout error. Instead, download one page at a time where the page contains, for example, 500 rows.

Note:

Pagination does not limit the total number of rows downloaded. All available rows are downloaded with or without pagination. Pagination controls how many rows are downloaded *per request*. In the example here, there would be 20 requests of 500 rows to download all 10,000 rows.

You can configure the pagination behavior using the Download tab in the Business Object Editor.

Description of the illustration vb-excel-download-prop.png

Configure the following as required:

• Offset Parameter Name: The name of the URL parameter that controls where to start the next page. When fetching the first page, the add-in uses a value of zero. When fetching the second page, the add-in uses a value of 499, assuming the limit value is 499.
• One-Based Offset: Controls whether the service starts counting from one. If this check box is unselected, the service assumes the service starts counting from zero.
• Limit Parameter Name: The name of the URL parameter that controls how many rows to fetch for each page.
• Limit Parameter Value: Controls the page size (number of rows that the add-in downloads).

For example, using the defaults for a Oracle business object REST API service, the add-in appends ?offset=0&limit=499 for the first download request.

For other service types, pagination may or may not be supported. If supported, the service may use parameter names like offset and limit or it may use other parameter names for the same purpose.

Consult the service API documentation to determine which parameters to use.

Configure Row Finders for a Business Object

Row finders are predefined filters available through Oracle business object REST API services that allow you to download a specific subset of records. If the service owner has included row finders with the service, you can view and configure them through the Finders tab of the Business Object Editor.

For example, if your service includes a row finder that filters expense reports by "unapproved" status and assigned to the "current user", you can use the row finder to download only those expense reports that require your approval. See Use Row Finders to Limit Downloaded Data.

Row finders are defined through the service. You can't add or configure them through Oracle Visual Builder Add-in for Excel. However, you can configure how they appear in the add-in. For example, you can remove unwanted row finder variables so that they don't appear in the prompt at download time.

You may also want to give row finders or row finder variables a more readable title. Or add some help text to help your business users understand what a row finder does or what should go in a row finder variable field.

Note:

Any changes you make must be compatible with the service. If your changes are incompatible, you will likely see errors during a download.

To configure row finders:

1. In the Excel ribbon, click Designer.
2. In the Layout Designer, click the Edit icon () next to the Business Object field.
3. Click the Finders tab in the Business Object Editor to view a list of available row finders. Remove row finders if required.

   Note:

   The Finders tab shows only for Oracle business object REST API services and not for Oracle REST Data Services (ORDS) or other REST services.
4. To edit a row finder's details, select it from the list and click the Edit icon ().
   
   
   Description of the illustration rowfindereditor.png
   
   
5. Use the Row Finder Editor to edit the row finder. For example, you might want to change the title to be more readable.

   You can also add descriptions of the row finder using the Help Text and Description properties. The Help Text property is intended to give your business users more information about the row finder. This text is displayed in a popup next to the row finder title in the Search Editor.

   The Description property is intended for workbook developers. This text is displayed only in the designer UI, like the Finders tab of the Business Object Editor. You can use this property, for example, to provide technical details of the row finder to workbook developers.

   Caution:

   When editing a row finder, take care to not change the row finder's ID.
6. If a row finder supports variables that act as arguments or parameters for the finder, click the Variables tab to update or delete variables. For example, you can change the row finder variable's title (but not the ID) or set the variable as required to force the user to provide a value at download time, as shown here:
   
   
   Description of the illustration row-finder-variable-editor.png
   
   

   As with a row finder, you can use the Help Text and Description properties to provide useful descriptions of the variable. The Help Text value is displayed as a popup next to the row finder variable in the Search Editor.

7. If required, configure a list of values for a row finder variable from the List of Values page in the Row Finder Variable Editor.

   Note:

   A list of values is a drop-down list populated with values from a referenced business object or a local data source. Filters can also be defined to further limit the values to those required by the business user. These filters can contain expressions that are resolved at runtime. Some expressions like { this.BusinessObject.Fields['FieldId'].Value } can't be used in filters for row finder variables. See Configure a List of Values with a Business Object and About Expressions.
8. Click Done until you return to the Layout Designer.

   Once you are done making changes, the row finders become available to you as an option to limit downloaded data, as described in Use Row Finders to Limit Downloaded Data. For details on how your configuration takes effect, see Download Behavior in Layouts that Use Search and Row Finders.

Configure GZIP Compression for Request Payloads

For a POST, PUT, or PATCH request to the service that takes payloads, you can choose to compress the payload body using GZIP if the service accepts compressed request payloads.

When the Supports gzip compression for request payloads option is selected, the Content-Encoding: gzip header is added to the request. This option (found in the Business Object Catalog Editor's Advanced tab) is selected by default for Oracle business object REST API services.

Refresh a Business Object Catalog

If a service owner makes significant changes to the service description–especially its business object definition–after the workbook is integrated with the service, you can refresh the workbook's business object catalog to take advantage of the latest changes.

Refreshing a catalog is useful when changes to a service description are extensive–for example, when new fields, custom actions, or row finders are added to the service–and updating the business objects to incorporate these changes would take considerable time and effort. You can also refresh the catalog when a new version of Oracle Visual Builder Add-in for Excel improves the metadata harvesting.

Your catalog might be based on a single service description document that defines one or more business objects. But a catalog can also include business objects that you have imported separately and that have their own service description documents. When you refresh a catalog, Oracle Visual Builder Add-in for Excel reads the metadata path for each business object and issues a request that returns the business object details from the service description document.

You can only refresh a catalog with URL-based service description documents. Catalogs with file-based service description documents cannot be refreshed from a file.

Caution:

A refresh overwrites any changes that you've made locally to the business object catalog. If you've spent a lot of time customizing the business object (especially field titles), consider whether it's worth redoing those changes; it might be simpler to manually add that new field or custom action. Before refreshing a catalog, back up the workbook in case you want to revert your changes.

To refresh a business object catalog:

1. Click Manage Catalogs in the Oracle Visual Builder tab.
2. Select the catalog to refresh in the Manage Business Object Catalogs window.
3. Click the Refresh Business Object Catalog icon ().
4. Click OK, then Yes when prompted to confirm the overwrite.

   If a service host is missing or you have not logged in, you'll be prompted to provide the required details. If you cancel either of these prompts, the catalog refresh ends without completing.

   If, once the catalog refresh is complete, you see this warning, then the add-in was unable to refresh one or more business objects in the catalog.
   
   To troubleshoot the error, open the log console and repeat the process to discover the cause. See Log Console.

   If the catalog refresh ends successfully, the add-in displays a message that all business objects were successfully refreshed.

5. When the refresh is complete, click OK to close the message, then Done to close the Manage Business Object Catalogs window.

During the catalog refresh:

• Existing business objects are refreshed based on the response from the metadata path. New business objects are not added to the catalog. No business objects are removed.
• For existing business objects, new fields, finders, custom actions, and so on are added and existing items are refreshed. No existing items are removed.
• When an existing item is refreshed, the item's new properties replace the older properties. For example, a field's title, required, and editable properties are copied from the newer version to the older version, except when a new property is empty. If a field's newer version has a list of values configured, the newer list of values replaces the older list of values. If the newer version doesn't have a list of values, the previous list of values is left unchanged.
• A refresh does not change any field formats, as those are always set manually.

If you want a new field in your layout, use the Table Column Manager to add the field after refreshing the catalog:

1. In the Layout Designer, click Columns.
2. Click Manage Columns () and select the new field. Click Done.

Configure the REST-Framework-Version

You can change the value of the REST-Framework-Version request header, set to version 6 by default for Oracle business object REST API services. The REST framework version determines functionality for accessing business objects.

To configure a specific REST-Framework-Version that overrides the default framework version:

1. In the Oracle Visual Builder tab, click Manage Catalogs.
2. Select the business object catalog, then click the Edit Business Object Catalog icon.
3. In the Business Object Catalog Editor, click the Advanced tab.
4. Specify the framework version you want to use in the REST API Framework Version property. This property is available only for Oracle business object REST API services (not ORDS or other REST services).
   The default value is version 6 (the only supported value). If the value is empty or lower than 6, it is set to the default. Framework versions higher than 6 are not certified.
5. Click Done.

Once you specify a framework version, it stays in effect until you manually change it again. Every request sent to any endpoint that belongs to this catalog includes the REST-Framework-Version header with the specified value. For details on framework versions, see About REST API Framework Versions.