4 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. You can then download data for the business object to the layout and start working with it.

You create a layout by clicking Designer in the Oracle Visual Builder tab to launch the New Layout Setup wizard, as described in subsequent sections.

When you create a layout in a new workbook, you'll need to provide the service metadata for the REST service you want to use. This service metadata must comply with the OpenAPI specification. You can provide a URL to the service metadata at the REST service metadata endpoint or import a local service metadata file instead. You can find more information about service metadata URLs in Create a Table Layout in an Excel Workbook.

The service metadata 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.

You can provide the service metadata when you create a layout. You can also provide the service metadata by clicking Manage Catalogs in the Oracle Visual Builder tab (see Manage Catalogs and Business Objects).

You create one layout per worksheet in your Excel workbook.

Layouts

Oracle Visual Builder Add-in for Excel provides two different kinds of layouts you can use to work with data in an Excel worksheet: Table layouts and Form-over-Table layouts.

Use a Table layout to view and edit data from a REST service in a tabular format. Use a Form-over-Table layout when a parent-child relationship exists in the business objects used by your web application.

Here's an example of a worksheet showing employee data in a Table layout:

Description of the illustration employee_table.png

Here's an example 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:

Description of the illustration po-line_fot.png

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.

When you create a Table layout, you'll be prompted to point to the service metadata. The service metadata document can be stored on your local drive or accessed remotely using a URL.

These sample URLs return the service metadata the add-in needs to create a business object catalog and then a table layout.

REST Framework	Sample URL
ADF REST (including many REST APIs for Oracle Cloud Applications)	https://<host>/fscmRestApi/resources/11.13.18.05/purchaseRequisitions/describe
VBBO	https://<host>/ic/builder/design/<app>/1.0/resources/data/ExpenseReports/describe
ORDS	https://<host>/ords/<app>/open-api-catalog/employees/
NetSuite	https://<account>.suitetalk.api.netsuite.com/services/rest/record/v1/metadata-catalog?select=contact

Note:

You cannot use a data endpoint to create a business object catalog. The metadata endpoint is required at this stage.

You'll also have the option to provide authentication details for accessing your REST service. Consult with your REST service owner for access requirements.

To create a Table layout:

1. 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.
2. Click the cell where you want to locate the data table.
3. Open the Oracle Visual Builder tab from the Excel ribbon.
   
   
   Description of the illustration excel-configure-table.png
   
   
4. Click Designer to launch the New Layout Setup wizard.
   
   Description of the illustration excel-apiinput.png
   
5. From the first screen, provide the service metadata document using one of these options:
   • Web Address option (the default) if you access the service metadata 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.
   • Select a file option if the service metadata document is a local file on your computer.
6. Select the authentication method for your service from the Authentication list and click Next.

   See Authentication Options for more information.

7. If you selected OAuth 2.0 Authorization Code, enter the required properties and click Next.

   Required fields are outlined in red. Refer to OAuth 2.0 Authorization Code Flow with PKCE for descriptions of the fields.

8. If the service includes five or more business objects, select the business objects you want to include in the catalog, then click Next.

   The wizard displays details of the newly-created catalog, such as the catalog name, service host and base path, and number of business objects.

9. Review the new catalog details.

   Note:

   If there are any errors in the service metadata document, click Save Report to save the report to your local drive. Share this report with the service owner.
10. Click Next to proceed.
11. Select a business object and click Next.
    
    
    Description of the illustration excel-available-bo.png
    
    
12. Select Table Layout and click Next.
    
    
    Description of the illustration choose-table-layout.png
    
    

    The next screen prompts you to select child business objects if you are creating a set of dependent layouts. See Create a Set of Dependent Layouts.

    
    
    
    Description of the illustration choose-dependent-bo.png
    
    

    If there are any available descriptive flexfields, these are displayed in the list and are indicated by the Information icon ().

    
    
    
    Description of the illustration newlayout-dff-bo.png
    
    

    If you select this business object, the descriptive flexfields are added as fields to the respective parent layout. See Create a Layout Using Descriptive Flexfields.

13. For a standalone Table layout, click Next without selecting any child business objects.
14. Review the Table layout details and then click Finish.

    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

    Note:

    If the origin cell of the layout is in the first 10 rows, the header row is frozen so that you always see the column headers when you scroll up and down in the worksheet. If desired, you can unfreeze the header row from Excel's View tab.

Before you proceed to publishing your workbook, you may want to configure the workbook in various ways to make it easier for your business users to use. For example, you might consider:

• Configuring the workbook to limit the data that the add-in downloads. See Configure Search Options for Download.
• Adding, removing, or reordering columns in your layout. See Manage Fields in a Form or Table.
• Modifying a field associated with a column to, for example, show help text or display a list of input values for your business users. See Configure Business Object Fields and Configure a List of Values with a Business Object.

Before you publish, it's a good idea to perform various data operations, such as download, update, and upload, to test the workbook before you 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 metadata document. When prompted, choose a child business object or any parameterized path from the business object picker.

Tip:

When working with Oracle ADF REST Resource 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 ADF REST Resource services cannot provide OpenAPI service metadata documents 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 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.

Note:

Since business users may not know the path parameter values at download time, consider using a Form-over-Table layout or a set of dependent layouts instead. See Create a Form-over-Table Layout in an Excel Workbook or Use Multiple Layouts for Multi-level Business Objects.

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.

A Form-over-Table layout can only be created for the top-level business object in a business object hierarchy. Suppose you have a hierarchy with three levels: purchaseOrders, lines, and schedules. In this hierarchy, purchaseOrders is a collection of top-level purchase orders each with one or more lines for managing the details of each order. Each of these lines may include one or more schedules for tracking shipping details.

In this scenario, you can only create a Form-over-Table layout for the purchaseOrders and lines business objects. You can't use lines for the form and schedules for the table in a Form-over-Table layout. See Use Multiple Layouts for Multi-level Business Objects.

Note:

You can create a layout that references polymorphic business objects and includes descriptive flexfields. If the business object includes descriptive flexfields, they are appended with "DFF" (for example, "EmployeesDFF") and included in the list of descendant business objects. See Use Polymorphic Business Objects and Fields.

A parent-child relationship at the service level requires:

• A parent service path, for example, fscmRestApi/resources/1.0/purchaseOrders
• A child service path with a parameter, for example, fscmRestApi/resources/1.0/purchaseOrders/{purchaseOrder-id}/child/lines

In your workbook, both business objects must be declared in the same catalog. Continuing our example, lines must appear as a child of purchaseOrders. To allow for data retrieval, updates, creation, deletion, and action invocation with the parent and child business objects using this layout, the service must expose the corresponding GET, PUT/PATCH, POST, and DELETE operations on these paths.

When you create a Form-over-Table layout, you may be prompted to point to the service metadata document. The service metadata document can be stored on your local drive or accessed remotely using a URL.

You'll also have the option to provide authentication details for accessing your REST service. Consult with your REST service owner for access requirements.

Before you begin, review these support topics for your REST service:

• For Oracle REST Data Services (ORDS), see the notes on ORDS support in Requirements for Dependent Layouts.
• For NetSuite SuiteTalk REST web services, see Configure a NetSuite Catalog for Parent-Child Business Objects.

To create a Form-over-Table layout:

1. 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.
2. Click the cell where you want to locate the form and table.
3. Open the Oracle Visual Builder tab from the Excel ribbon.
   
   
   Description of the illustration excel-configure-table.png
   
   
4. Click Designer to launch the New Layout Setup wizard.
   
   Description of the illustration excel-apiinput.png
   
5. From the first screen, provide the service metadata document using one of these options:
   • Web Address option (the default) if you access the service metadata 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.
   • Select a file option if the service metadata document is a local file on your computer.

   Tip:

   If you are working with Oracle ADF REST Resource services, the URL for the service metadata usually ends with /describe, for example, https://my-service-host/fscmRestApi/resources/1.0/purchaseOrders/describe.
6. Select the authentication method for your service from the Authentication list and click Next.

   See Authentication Options for more information.

7. If you selected OAuth 2.0 Authorization Code, enter the required properties and click Next.

   Required fields are outlined in red. Refer to OAuth 2.0 Authorization Code Flow with PKCE for descriptions of the fields.

8. If the service includes five or more business objects, select the business objects you want to include in the catalog, then click Next.

   The wizard displays details of the newly-created catalog, such as the catalog name, service host and base path, and number of business objects.

9. Review the new catalog details.

   Note:

   If there are any errors in the service metadata document, click Save Report to save the report to your local drive. Share this report with the service owner.
10. Click Next to proceed.
11. Choose the top-level business object for the form (in this case, purchaseOrders), and click Next.
    
    
    Description of the illustration bo_hierarchy.png
    
    
12. Choose Form-over-Table Layout, and click Next.
    
    
    Description of the illustration choose-fot-layout.png
    
    

    The Form-over-Table layout option is unavailable if you didn't select the top-level business object or there is no available child business object.

13. Choose a child business object for the table part of your Form-over-Table layout (in this case, lines), and click Next.
    
    
    Description of the illustration choose-dependent-fot.png
    
    

    If there are any available descriptive flexfields, these are displayed in the list and display a "DFF" ending.

    
    
    
    Description of the illustration newlayout-dff-bo.png
    
    

    If you select this business object, the descriptive flexfields are added as fields to the parent layout. See Create a Layout Using Descriptive Flexfields.

    Note:

    You can select additional child business objects if you want to create a set of dependent layouts. If you select more than one, you'll be prompted to select the business object you want to show in the table of the root layout. See Create a Set of Dependent Layouts.
14. Confirm the details of your Form-over-Table layout, and click Finish.

    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 form business object (in this case, purchaseOrders) 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).

Before you proceed to publishing your workbook, you may want to configure the workbook in various ways to make it easier for your business users to use. For example, you might consider:

• Configuring a search for the workbook to allow your business users to specify an item for the form, as described in Configure Search Options for Download. If 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.
• Adding, removing, or reordering form fields or columns in your layout. See Manage Fields in a Form or Table.
• Modifying a field associated with a column to, for example, show help text or display a list of input values for your business users. See Configure Business Object Fields and Configure a List of Values with a Business Object.

Before you publish, 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.

Manage Fields in a Form or Table

When you create a layout, Oracle Visual Builder Add-in for Excel adds most but not all fields to your form or table. If desired, you can add or remove fields or change the order they appear in the layout.

Besides adding business object and custom payload fields to a layout, you can also add fields from a parent or higher business object to a layout in a set of dependent layouts. These fields are referred to as "ancestor" fields. For example, you might want to add an ancestor column to a layout to help your business users track which child items are associated with which higher-level items. See Add Ancestor Columns to Provide Additional Context.

If there are any descriptive flexfields (DFFs) available your layout, these are also included in the list of available fields. For more information about DFFs, see Add Descriptive Flexfields to a Layout.

This task shows you how to add, remove, and reorder columns in a Table layout but the steps are the same for form fields and table columns in a Form-over-Table layout.

To manage the columns in a Table layout:

1. Select the layout, then click Designer from the Oracle Visual Builder tab to open the Layout Designer.
2. For a Table layout, click the Columns tab in the Layout Designer.
   For a Form-over-Table layout, click either the Form or Table tab.
   The tab displays the table columns in the order they appear in the layout.
   

   
   Description of the illustration columns_tab_layoutdesigner.png

   
   
3. Perform one or more of these actions as required:
   • To change the order of the columns, drag a column to another location in the list.

     Tip:

     You can also right-click a column and select an action (Move Up, Move Down, and so on) from the popup menu.
   • To delete a column, select a column and then click the Delete icon ().

     Tip:

     You can use the Shift and Ctrl keys to select multiple columns for deletion.
   • To edit the field, double-click the column in the list to open the Business Object Field Editor.
4. If you want to add columns to your layout, click Manage Columns () to open the Table Column Manager.
   
   Description of the illustration table_columns_manager.png

   If the business object supports custom actions or is a child object in a hierarchy of business objects, you'll see additional tabs (such as Custom Actions and Ancestors) in the Available Fields pane.

   
   

   
   Description of the illustration available_field_tabs.png

   
   
5. Click the appropriate tab for the type of field you want to add from the Available Fields pane.

   Available ancestor columns are grouped by business object. So ancestor columns available for a grandchild layout are grouped by parent and child.

6. In the Selected Fields pane, select the location where you want the columns to be added.
7. Select the columns you want to add from the Available Fields pane.

   The columns are inserted before the selected field.

   In this example, you may want to add Hire Date and Salary to the table before Manager Id. To do this, select Manager Id in the Selected Fields pane, then select the Hire Date and Salary check boxes in the Available Fields pane.

   Note:

   If you are adding a column that is referenced by a list of values in another column, make sure the added column is positioned before the field with the list of values. See Configure a Filter with a Dynamic Parameter for more information.
8. You can also perform one or more of these actions if required:
   • To add or remove all columns, select or deselect Select All.
   • To remove a column, deselect the column's check box.
   • To change the order in the layout, drag a column in the Selected Fields pane to another location in the list.

     Tip:

     You can also move a column by right-clicking it, then selecting an action (Move Up, Move Down, and so on) from the popup menu.
   • To edit the field, double-click the column in the Selected Fields pane to open the Business Object Field Editor.
9. When you have made all your changes, click Done.

Create Layouts for Attachment Business Objects

Create a Table layout using an attachment business object that lets your business users upload and download attachments.

You can create a standalone Table layout, the Table layout of a Form-over-Table layout, or a Table layout in a set of dependent layouts based on an attachment business object. You cannot use the form in a Form-over-Table layout.

Service Requirements

Oracle Visual Builder Add-in for Excel supports attachments in integrated workbooks if the REST service has:

• An Attachment Record Business Object that contains the metadata for attachments, such as the attachment type, file name, and file size.
• The following fields in the Attachment Record Business Object:
  • Type: A string field representing the attachment type. Valid values for this field are TEXT, FILE, and WEB_PAGE. These values are case-sensitive.
  • File Name: A string field representing the attachment file name. This field is used by text and file type attachments.
  • Url: A string field representing the attachment URL. This field is used by web page type attachments.
• An Attachment Data Business Object that is a child of the attachment record business object and allows for the sending and receiving of attachments

When you create a Table layout based on an attachment business object, the add-in can properly configure the layout as long as the attachment record business object includes:

• An Attachment Data Business Object as a child business object with a path ending in /enclosure/FileContents; and,
• Fields with field Ids of DatatypeCode (for the Type field), FileName (File Name), and Url (Url).

Configure an Attachment

If the workbook was created before version 2.8 of the add-in or the naming does not match, configure the attachment record business object manually from the Business Object Editor.

1. Open the Business Object Editor for an attachment business object and click the Attachments tab.
   
   Description of the illustration attachment-bo-editor.png
   
2. Select Is attachment.
3. Click the Edit icon () next to Data Business Object to open the Choose a Business Object dialog.

   The dialog displays the paths for all child business objects for the Attachment Record Business Object.

4. Select the required child Attachment Data Business Object from the list, then click OK.
   
   
   Description of the illustration child-bo-dialog.png
   
   

   The child business object must support sending and receiving attachment data. This means that the child endpoint has a collection path that supports:

   • GET to retrieve attachment data
   • PUT to send attachment data
   • Request content type of application/octet-stream

   The path may be similar to this: {parent attachment record item path}/enclosure/FileContents.

5. Click the Edit icon () next to Type Field to open the Available Business Object Fields dialog.
6. Select the field to represent the attachment type, then click OK.
   
   
   Description of the illustration available-bo-fields.png
   
   

   This field must be a string field with valid values of TEXT (text type attachment), FILE (file type attachment), and WEB_PAGE (web url-based attachment). Typically, the field ID for this field is DatatypeCode.

7. Use the available Edit icons to set the File Name and URL fields.

   Field	Description
   File Name Field	A string field representing the attachment file name. This field is used for text and file type attachments. Typically the field ID for this field is FileName.
   URL Field	A string field representing the attachment URL. This field is used by web page type attachments. Typically the field ID for this field is Url.

8. When finished, close the open editors using the Done buttons.

If properly configured, the resulting Table layout includes a Local File Path column that keeps track of the location of local copies of attachments. Selecting cells in the table opens an attachment pop-up that can be used to interact with attachments. See Manage Attachments in Managing Data Using Oracle Visual Builder Add-in for Excel.

Description of the illustration local-file-attachment-table.png

Known Limitations

• See Service Requirements in this topic for the limitations on services that support this feature.
• Binding an attachment record business object to the form portion of a Form-over-Table layout is not supported.
• The only supported attachment types are file, text, and web page. The specific supported values for the field are TEXT, FILE, and WEB_PAGE. These values are case sensitive. Unknown attachment types are treated as file type attachments.
• File download is not asynchronous and may make the UI appear frozen for large attachment files.
• It is not possible to change the position of the Local File Path column in the table. It always appears at the end of the table before the Key column.
• Manually editing the file path in the Local File Path column is not supported. The attachment pop-up should always be used to specify the local file location.
• For some services, create may fail for text-based attachments. Ensuring all required attachment metadata, such as the Title, is present and resubmitting the record generally resolves this issue.
• Attachment metadata records may have additional fields, such as DmDocumentId, UploadedFileContentType, that should generally be managed by the service and not altered by the business user. It is recommended that you mark these fields as read-only in the Business Object Editor if they aren't already or omit them from the Table layout.

Use Polymorphic Business Objects and Fields

Oracle Visual Builder Add-in for Excel supports using polymorphic business objects from ADF REST services in Table and Form-over-Table layouts you create for an integrated workbook.

Where and how a polymorphic business object can be used in a layout depends on its relationship with other business objects in the service.

If a polymorphic business object is a top-level business object or is a child business object with a "one-to-many" relationship with its parent, it can be used directly in a layout.

In this case, you create a layout for polymorphic business objects as you do for any other business objects. See Create a Table Layout in an Excel Workbook and Create a Form-over-Table Layout in an Excel Workbook.

If a child polymorphic business is in a "one-to-one" relationship with its parent, descriptive flexfields from the child can be added as fields to a layout bound to its parent business object during layout creation. See Create a Layout Using Descriptive Flexfields.

You can also add descriptive flexfields to an existing layout as described in Add Descriptive Flexfields to a Layout.

To determine the relationship of a child business object to its parent, see Check the Cardinality of Child Polymorphic Business Objects.

About Polymorphic Business Objects

A polymorphic business object is a business object where the set of fields for a particular record differs based on the value of a discriminator field (also known as a context segment). In addition, a polymorphic business object includes a number of fields representing global or context-sensitive segments. Global segments are available for all values of the discriminator field, while context-sensitive segments are dynamic based on the value of the discriminator field. Descriptive flexfields (DFFs) are a type of polymorphic business object.

For example, an "Employees" business object may include a child polymorphic "Regional Information" business object that defines region-specific information for employee records. The Regional Information business object contains a "Region" field that acts as a discriminator field. It also contains global segments for all records, such as "Site" and "Time Zone", as well as context-sensitive segments such as a "Postal Code/Zip Code" for employees in the North American region.

Check the Cardinality of Child Polymorphic Business Objects

You can determine if a child business object is in a "one-to-one" or "one-to-many" relationship with its parent by checking the "cardinality" setting for a child business object.

Child polymorphic business objects with a "one-to-many" relationship can be used directly in a layout. For those child business objects in a "one-to-one" relationship, you can add descriptive flexfields from the child to the parent business object layout.

To check the cardinality setting for a child business object, open the Business Object Editor for the parent business object, then click the Children tab.

Description of the illustration child-polymorphic-settings.png

Cardinality options are "One", "Many", or "Unknown". Child business objects with a cardinality of "Many" (one-to-many relationship) can be used directly in a layout. Those with a cardinality of "One" can be added as fields to the parent business object layout. See Create a Layout Using Descriptive Flexfields.

Note:

For workbooks created before Oracle Visual Builder Add-in for Excel version 3.2, the cardinality is set to "Unknown". This value behaves identically to a value of "One" for polymorphic business objects. The cardinality value does not impact the behavior of non-polymorphic business objects.

Create a Layout Using Descriptive Flexfields

When you create a Table or Form-over-Table layout, you can add descriptive flexfields (DFF) from a child business object to the parent layout if the child is in a "one-to-one" relationship with the parent. DFFs are indicated in the New Layout Setup wizard by an Information icon ().

Top-level polymorphic business objects as well as child business objects with a "one-to-many" relationship with their parent can also be added to the layout as you would any other business object. See Create a Table Layout in an Excel Workbook and Create a Form-over-Table Layout in an Excel Workbook.

To determine the relationship of a child business object to its parent, check its Cardinality setting. See Check the Cardinality of Child Polymorphic Business Objects.

To create a layout with DFFs:

1. Create a new worksheet for your Table or Form-over-Table layout and click Designer to launch the New Layout Setup wizard.
2. Follow the instructions in the wizard, selecting the DFF's ancestor business object when prompted in the second screen.
   
   

   
   Description of the illustration bo_hierarchy_poly.png

   
   

   In this example, the Employees business object is selected for the parent layout.

3. When prompted, select either Table Layout or Form-over-Table.
4. When you reach the fourth screen of the wizard, you are prompted to select descendant business objects for your layout or layouts.

   In this example, both Direct Reports and EmployeesDFF are selected. EmployeesDFF represents a DFF that is in a one-to-one relationship with Employees, as indicated by the Information icon ().

   
   

   
   Description of the illustration bo_hierarchy_dff.png

   
   

   If you select EmployeesDFF, the add-in adds the flexfields to the parent layout, Employees.

   If you choose to create a Form-over-Table layout, the add-in creates a layout with Employees in the form and Direct Reports in the table. If you instead choose Table layout, the add-in creates a dependent Table layout for Direct Reports.

5. When you reach the final screen in the wizard, review the details of the new layout, then click Finish.
   
   

   
   Description of the illustration layout_review_dff.png

   
   

In this example, the add-in creates a Form-over-Table layout with Employees in the form and Direct Reports in the table as shown in this image:

Description of the illustration fot_layout_polymorphic.png

The add-in also adds the Region flexfields from EmployeesDFF to the form (bordered in red). The flexfields include a "discriminator" field ("Region"), two global segments ("Site" and "Time Zone"), and two context-sensitive segments ("Zip Code" and "State"). The context-sensitive segments are dependent on the value of the Region field (in this case, "United States").

If the required flexfields do not appear in the layout by default, you can add them to your form or table from the Layout Designer. See Add Descriptive Flexfields to a Layout.

You can also show or hide context-sensitive segments based on the discriminator value. For example, you could choose to show the context-sensitive segments for Canada ("Postal Code" and "Province"), rather than the U.S. segments, ("Zip Code" and "State"). See Show or Hide Context-Sensitive Columns in a Table Layout.

Add Descriptive Flexfields to a Layout

You can add descriptive flexfields (DFF) to forms and tables using the Form Field Manager or Table Column Manager and place them anywhere in the form or table.Descriptive flexfields are a type of polymorphic business object that has a one-to-one relationship with its parent. See Overview of Descriptive Flexfields for more information on DFFs.

Note:

For polymorphic business objects in a one-to-many relationship, refer to Create a Layout Using Descriptive Flexfields instead.

1. Open the worksheet with the layout that you want to modify.
2. Click either the Form or Columns tab in the Layout Designer as needed.
3. Click the Manage Form Fields or Manage Columns button () to add your DFF.

   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 the discriminator field ID ("__FLEX_Context").

4. Select the DFF from the Available Fields list.
   
   
   Description of the illustration add-dff-field.png
   
   

   You can also change the order of fields in the form or table by dragging and dropping fields in the Selected Fields list.

5. Click Done.

   The associated segments appear in the layout 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).

Description of the illustration dff-table.png

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.

Description of the illustration dff-form.png

Show or Hide Context-Sensitive Columns in a Table Layout

You can select which context-sensitive columns you want to display for a polymorphic business object, using the Polymorphic Information tab of the Business Object Field Editor.

All context-sensitive columns are shown by default. You may want to use this task to hide columns in a layout. For example, in the case of a Region polymorphic business object, you may choose to show regional information only for U.S. employees and hide it for all others.

To show or hide context-sensitive columns:

1. Open the worksheet with the layout that you want to modify.
2. From the Layout Designer, click the Edit icon () next to the Business Object field.
3. From the Business Object Editor, click the Fields tab and then select the business object's field.
4. Click the Edit icon () in the Business Object Editor to open the Business Object Field Editor.
5. From the Polymorphic Information tab, select Limit discriminator values.
6. To ensure you see all available discriminator values, click the Refresh icon () to fetch the latest polymorphic metadata from the service.
   This icon may be disabled if a refresh has already been performed during the current session.
7. From the Discriminator Values list, select the discriminator values for the context-sensitive segment columns that you want to display. For example, to show zip code and state columns in your Table layout, select the United States check box and deselect all others.
   
   

   
   Description of the illustration polymorphic-info-selected.png

   
   
8. After you select or deselect discriminator values, close the open editors.

The layout displays the context-sensitive segment columns you selected.

Description of the illustration diff-table-hidden.png

Refresh Polymorphic Business Object Metadata

Clear polymorphic metadata when you publish a workbook to ensure the workbook gets the latest metadata. Oracle Visual Builder Add-in for Excel refreshes the polymorphic business object metadata during the first download operation performed after you open the published workbook.

Because polymorphic business object segments are configurable by customers and subject to change, metadata stored in the workbook may become stale.

To clear metadata when publishing a workbook, select Clear all layouts from the Publish Workbook window. See Publish an Integrated Excel Workbook.

Polymorphic Support Limitations

Before creating layouts for polymorphic business objects or adding descriptive flexfields to a layout, review the limitations here:

• Polymorphic business objects are only supported for ADF REST services.
• A polymorphic business object is assumed to only contain a single discriminator field. Multiple discriminators are not supported.
• The discriminator field must be a string.
• When a polymorphic column or form field expands, the order is global segments, the discriminator, and then context-sensitive segments. Note that:
  • Changing the order of the global segments or context-sensitive segments is not supported.
  • Hiding specific global or context-sensitive segments is not supported.
• In order to bind a polymorphic business object to a layout, it must expose polymorphic business object metadata. In an OpenAPI metadata document, this means that it must contain a "discriminator" and "oneOf" syntax in the schema for the business object. The "anyOf" polymorphic business object syntax is not supported.
• Hierarchical polymorphic business objects are not supported. All polymorphic segments must be defined directly on the polymorphic business object in the OpenAPI metadata document.
• Extensible Flexfields (EFFs) that define polymorphic segments on array-based subfields are not supported.
• When a layout contains a polymorphic business object, the "fields" and "expand" query parameters should not be manually configured in the "Search Parameters" in the "Query" tab of the designer.
• The case where a child business object's fields are determined by a parent business object's discriminator value is not yet supported.
• Limiting discriminator values for a polymorphic business object displayed in a Table layout does not limit:
  • The set of values that can be chosen in a list of values (LOV) for a discriminator field
  • The values users can provide for a discriminator field
• Polymorphic segments with a DISPLAYHINT value of Hide in the metadata cannot be used in a layout.

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.

1. In the Excel ribbon, click Designer to open a workbook's Layout Designer.
2. 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
   
   
3. 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 for the table as shown in this image). See REST Operations for more information about the REST support required for these options.

Layout Limitations

Here are some things to keep in mind when creating layouts for your integrated workbook using Oracle Visual Builder Add-in for Excel:

• Excel table objects, such as those created from the Excel ribbon using Insert > Table, are incompatible with the Table layouts used by the add-in.
• Do not save your workbook to the Excel 97-2003 workbook (.XLS) file format. Only the .XLSX and .XLSM file formats are supported. If you save to the .XLS format, the add-in disables commands in the Oracle Visual Builder ribbon.