Create Layouts in an Excel Workbook

5 Create Layouts in an Excel Workbook

To integrate a workbook with a REST service, create a layout for a business object on a new worksheet and link the layout to the REST service's service description. You can then download data for the business object to the layout and start working with it.

Layouts

Oracle Visual Builder Add-in for Excel lets you create different layouts to work with data in an Excel worksheet. Layouts are a way to display a business object in an Excel worksheet. Each worksheet supports one of two layouts: Table or Form-over-Table.

Use a Table layout to view and edit data from a REST service in a tabular format. Here's a worksheet showing employee data in a Table layout:
Use a Form-over-Table layout when a parent-child relationship exists in the business objects used by your web application. Here's a worksheet showing purchase order and line data in a Form-over-Table Layout, where the parent object's data (Purchase Orders) is shown in the form and the child object's data (Lines) is shown in the table:

You can create one layout per worksheet in your Excel workbook. Layouts are created by workbook developers and are visible to data entry users in their workbooks.

Service Descriptions

To create a layout in the workbook, the REST services that you use must provide a service description that complies with the OpenAPI specification. The service description can be a URL or a local file. For Oracle business object REST API services, the URL typically includes a describe, as in https://my-service-host/fscmRestApi/resources/latest/invoices/describe. For Oracle REST Data Services (ORDS), the URL may be similar to https://host/ords/great_app/open-api-catalog/employees/.

You can provide the service description document when you create a layout by clicking Designer in the Oracle Visual Builder tab, as described in subsequent sections. You can also provide the service description document by clicking Manage Catalogs in the Oracle Visual Builder tab (see Edit Service Descriptions and Business Objects).

The service description that you provide helps generate a business object catalog for the workbook. A business object catalog is essentially a list of business objects. As a workbook developer, you can edit portions of the business catalog as desired, or use it as is to create layouts.

Create a Table Layout in an Excel Workbook

Create a Table layout in the Excel worksheet when you want to view and edit data from a REST service in a tabular format.

Create a blank Excel workbook using the standard .XLSX file format or the macro-enabled .XLSM format. Other Excel formats (.XLS and so on) are not supported.
In the Excel ribbon, select the Oracle Visual Builder tab.

Description of the illustration excel-configure-table.png
Click the cell where you want to locate the data table.
In the Oracle Visual Builder tab, click Designer.
When prompted, provide the service description document. Use the Web Address option (the default) if you access the service description from a URL. Note that you can't provide a data URL (a URL that returns data) as the starting point to creating a layout. Use the Select a file option if the service description document is a local file on your computer.
Description of the illustration excel-apiinput.png

Tip:
If you are working with Oracle business object REST API services, the URL for the service description usually ends with /describe, for example, https://my-service-host/fscmRestApi/resources/latest/invoices/describe.

If multiple business objects are found, the Choose a Business Object window prompts you to choose from the available business objects.
Description of the illustration excel-available-bo.png

The add-in creates a Table layout in the Excel workbook that includes column headers and a placeholder data row. The Layout Designer opens in the Excel Task Pane.

Description of the illustration layout-designer-table.png
Click the Columns tab in the Layout Designer to add or remove columns, or change the order in which columns appear. When a data table is created, most (but not all) fields are added as columns. Click the Manage Columns button () to add or remove columns as needed.
You can also modify a field associated with a column to, for example, show a tooltip or display a list of input values for your business users. To update a field, select it from the Table Columns table and click the Edit button () to open the Business Object Field Editor. To add a tooltip, click the General tab and type help text in the Tooltip property. The tooltip is displayed when the user selects the table column header. To create a list of values, see Configure a List of Values for a Business Object Field.
After making changes, click Redraw Layout in the ribbon tab to see your changes reflected in the worksheet.
Optionally, configure the workbook further to limit the data that the add-in downloads, as described in Configure Search Options for Download.

The workbook is now complete and ready to be published. At this point, it's a good idea to perform various data operations, such as download, update, and upload, to test the workbook before you publish and distribute it to users. For information on managing data, see View and Edit Data Using an Excel Workbook in Managing Data Using Oracle Visual Builder Add-in for Excel.

Work with Service Path Parameters in a Table Layout

Some service paths include path parameters. The add-in provides support for configuring a Table layout using a parameterized service path. It automatically extracts the path parameters and prompts the user to provide the corresponding values at download time.

To configure a Table layout with a parameterized service path, first provide an OpenAPI-compliant service description. When prompted, choose a child business object or any parameterized path from the business object picker.

Tip:

When working with Oracle business object REST API services, you should start with the web address to the parent business object description (and not the child address). For example, for a parameterized service path such as /ExpenseReports/{ExpenseReports_Id}/child/Expenses/, provide the address to the ExpenseReports description (not Expenses). Oracle business object REST API services cannot provide OpenAPI service descriptions for parameterized service paths.

Complete the layout configuration. When users click Download Data in the Oracle Visual Builder tab, the add-in displays the Service Path Parameter Editor where users provide the required path parameter values that enables the download of data to complete.
Description of service_path_parameter_editor.png follows
Description of the illustration service_path_parameter_editor.png

Path parameters of type string or integer are supported; other data types are not supported. For string-typed path parameters, values that users enter in the Service Path Parameter Editor are used verbatim when the add-in constructs the request to the service. For integer-typed values, certain culture-specific formatting is removed (for example, commas for thousands separators, parentheses for negative). In all cases, the values used on the URL path are not URL-encoded, so the values entered must be acceptable by the REST service.

Here is an example of a service path with an embedded parameter:

/ExpenseReports/{ExpenseReports_Id}/child/Expenses/

Note {ExpenseReports_Id} is in the middle of the service path.

Using the Service Path Parameter Editor, you provide the proper value for {ExpenseReports_Id}, for example, 123456, which results in the add-in using the following path:

/ExpenseReports/123456/child/Expenses/

Accessing this service path will provide all the expenses for expense report 123456.

The Service Path Parameter Editor does not validate the value(s) that users enter. The value(s) that users provide must be valid. If the path includes multiple embedded parameters, the Service Path Parameter Editor prompts the user to provide a value for each embedded parameter.

The add-in remembers the values provided at download time. These values are used again at upload time to construct the upload requests. If you upload without having done a previous download (for example, when exclusively creating new rows), you'll be prompted for the path parameter values at the beginning of the upload.

Create a Form-over-Table Layout in an Excel Workbook

You can create a Form-over-Table layout in an Excel worksheet when a parent-child relationship exists in the chosen service.

Note:

A parent-child relationship at the service level requires:

A parent service path, for example, fscmRestApi/resources/latest/invoices
A child service path with a parameter, for example, fscmRestApi/resources/latest/invoices/{invoice-id}/child/invoicelines

In your workbook, both business objects must be declared in the same catalog. Continuing our example, invoicelines must appear as a child of invoices. The add-in uses the primary ID of the parent row and inserts it into the child's path. The workbook service must be able to support operations on these paths.

Create a blank Excel workbook using the standard .XLSX file format or the macro-enabled .XLSM format. Other Excel formats (.XLS and so on) are not supported.
In the Excel ribbon, select the Oracle Visual Builder tab.

Description of the illustration excel-configure-table.png
Click the cell where you want to locate the form and table.
In the Oracle Visual Builder tab, click Designer.
When prompted, provide the service description document. Use the Web Address option (the default) if you access the service description from a URL. Note that you can't provide a data URL (a URL that returns data) as the starting point to creating a layout. Use the Select a file option if the service description document is a local file on your computer.
Description of the illustration excel-apiinput.png

Tip:
If you are working with Oracle business object REST API services, the URL for the service description usually ends with /describe, for example, https://my-service-host/fscmRestApi/resources/latest/invoices/describe.

When your OpenAPI service description includes multiple business objects, a window similar to the following prompts you to pick the business object you want to use in the Form-over-Table layout:

Description of the illustration fot-bo-available.png
Choose a business object where a parent-child relationship exists (such as invoices), choose Form-over-Table Layout in the New Layout Setup window that appears, and click OK.

Description of the illustration choose-fot-layout.png

If there's more than one child business object, select the child to use in the Form-over-Table layout and click OK. This example uses the invoiceLines child business object.

The add-in creates a Form-over-Table in the Excel worksheet and opens the Layout Designer that you use to modify the newly-inserted form and table, as shown here:

Description of the illustration fot-result.png

If the parent business object (in this case, invoices) supports a create action, a Create Form Row option appears in the Form Changes menu, as shown in the image. Use this option to create a new form row during your next upload (see Create a Parent Row in a Form-over-Table Layout in Managing Data Using Oracle Visual Builder Add-in for Excel).
Customize the form and table by modifying the automatically populated properties in the Layout Designer.
Use the Form page to add, remove, or rearrange fields on the form. The Table page includes the same functionality for the table under the form.

Tip:
From the Form or Table page of the Layout Designer, right-click a field or column to see choices for changing the order.

To modify a form field or table column, select it from the table and click the Edit button () to open the Business Object Field Editor. From here, you can add a tooltip or a list of values to a field. To add a tooltip, click the General tab and type help text in the Tooltip field. The tooltip is displayed when the user selects a form field label or table column header. To create a list of values, see Configure a List of Values for a Business Object Field.
Click Redraw Layout to see changes you make in the Layout Designer reflected in the form and table.
Optionally, configure the workbook to limit the data that the add-in downloads, as described in Configure Search Options for Download. If, for example, you do not specify a value for the Search field or Row Finder property, the add-in downloads the first parent item it encounters in the REST service to the form, and the child items, if any, to the table.

Configuration is now complete and you can publish the workbook. At this point, it's a good idea to test the workbook before you publish and distribute it to users. For information on managing data in a Form-over-Table layout, see Manage Data in Form-over-Table Layouts in Managing Data Using Oracle Visual Builder Add-in for Excel.

Add Descriptive Flexfields to a Layout

The Oracle Visual Builder Add-in for Excel supports the use of descriptive flexfields (DFF) in your layout's forms and tables. DFFs expand to display additional fields for tracking supplemental information. These fields are supported in REST services using Oracle ADF Business Components.

A DFF consists of a top-level field, or "discriminator", and a number of sub-fields called "segments". Segments may be global (available for all values in the discriminator) or context-sensitive (dynamic based on the value in the discriminator).

For example, an "Employees" REST resource may include a child DFF that defines regional information for employees. When added to a table, the DFF displays a Region column (for the discriminator) as well as additional columns for the DFF's segments. These columns could, for example, be used to capture extra regional information such as site and time zone. The DFF may also include context-sensitive columns such as a "Postal Code/Zip Code" column for employees in the North American region.

See Use Descriptive Flexfields for more information.

DFFs in Forms and Tables

Add DFFs to forms and tables using the Form Field Manager or Table Column Manager. See either Create a Table Layout in an Excel Workbook or Create a Form-over-Table Layout in an Excel Workbook.

Available DFFs are identified by the title of the discriminator field (in this case, "Region") and appear at the bottom of the list of available fields. The field ID is composed of the child DFF resource name ("EmployeesDFF" in this example) followed by the discriminator field ID ("__FLEX_Context").

You can add multiple DFFs and place them anywhere in the form or table. .

After you add a DFF to a layout, click Redraw Layout to expand the DFF. The associated segments appear in the following order: global segments, the discriminator, and context-sensitive segments.

Context-sensitive columns for all possible discriminator values are included in the table. However, only those cells in a row that are relevant to the value in the discriminator field are editable. All other context-sensitive cells are read-only (grayed out).

In a form, the context-sensitive form fields relevant to the current discriminator value appear after the discriminator field. Context-sensitive fields that are not relevant are not included in the form. If you change the value in the discriminator field, the form automatically updates to include the relevant context-sensitive field for that value.

Refreshing DFF Metadata

Because DFFs are configurable by customers and subject to change, metadata stored in the workbook for the DFF may become stale.

To clear metadata when publishing a workbook, select Clear all layouts from the Publish Workbook window. The add-in refreshes the DFF metadata during the first download operation performed after you open the workbook.

Limitations

A DFF child resource must have a one to one relationship with its parent resource.
A DFF child resource can only contain a single discriminator field. Multiple discriminators are not supported.
Changing the order of the global or context-sensitive segments is not supported.
Hiding specific global or context-sensitive segments is not supported.
Binding layouts directly to a DFF resource is not supported. Layouts must be bound to the parent resource of the DFF resource.
Before you can add a DFF to a table, it must expose polymorphic business object metadata. This means that the OpenApi3 describe document must contain a "discriminator" and "oneOf" syntax in the schema for the business object. "anyOf" polymorphic business object syntax is not supported.
When a layout contains a DFF, the "fields" and "expand" query parameters should not be manually configured in the "Search Parameters" in the "Query" tab of the designer.

Manage Layout Capabilities

Each layout in Oracle Visual Builder Add-in for Excel enables you to perform various standard and custom actions in an Excel workbook, so long as the operation is supported by your service. You can enable or disable these supported capabilities to control their availability in a layout.

In the Excel ribbon, click Designer to open a workbook's Layout Designer.
Click Advanced to see the layout's capabilities. Here's an example of a Form-over-Table layout, indicating form capabilities and table capabilities:

Description of the illustration layout_capabilities.png
Select or deselect options as required. If the business object doesn't support an action, it won't be available for selection (like Custom Actions Enabled in the example).
Click Redraw Layout.