4 Create Layouts in an Excel Workbook

The Oracle Visual Builder Add-in for Excel lets you create different layouts to work with your data in an Excel worksheet. You can create one type of layout per worksheet in your Excel workbook. That is, each worksheet in the Excel workbook can include a layout.

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.

Run Excel and create a blank workbook using the standard Excel workbook file format type (.XLSX) or the macro-enabled workbook type (.XLSM). The Oracle Visual Builder Add-in for Excel does not support other Excel formats (.XLS, and so on).

1. In the Excel ribbon, select the Oracle Visual Builder tab.
   
   
   Description of the illustration excel-configure-table.png
   
   
2. Click the cell where you want to locate the table.
3. In the Oracle Visual Builder tab, click Designer.
4. 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
5. Click the Columns tab to add or remove columns, or change the order in which columns appear. Review the list of columns to verify that the add-in only creates the desired columns. After making changes in the Layout Designer, click Redraw Layout to see your changes reflected in the worksheet.

   Note:

   When a table is created, most (but not all) fields are added as columns. Click the Manage Columns button () to add or remove columns as needed.

The workbook is now complete and ready to be published. We recommend you test the table by performing various operations, such as download, update, and upload before you publish the workbook to distribute it to users. Alternatively, you can configure the workbook further so that the add-in limits the data that it downloads, as described in Configure Search Options for Download.

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, when your parameterized service path is /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 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.

The following 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, 1232456, 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.

Run Excel and create a blank workbook using the standard Excel workbook file format type (.XLSX) or the macro-enabled workbook type (.XLSM). The Oracle Visual Builder Add-in for Excel does not support other Excel formats (.XLS, and so on).

1. In the Excel ribbon, select the Oracle Visual Builder tab.
   
   
   Description of the illustration excel-configure-table.png
   
   
2. Click on the cell where you want to locate the form and table.
3. In the Oracle Visual Builder tab, click Designer.
4. 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
   

5. Choose a business object, such as invoices shown in the previous image, where a parent-child relationship exists, choose Form-over-Table Layout in the New Layout Setup window that appears, and click OK. 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 illustrated in the following image:
   
   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).

6. Customize the form and table by modifying the automatically populated properties in the Layout Designer. Click Redraw Layout to see changes you make in the Layout Designer reflected in the form and table.

   If, for example, you do not specify a value for the Search field or Finder property, as described in Configure Search Options for Download, 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.

   The Form tab enables you to add or remove fields to the form. The Table tab performs a similar function for the table under the form.

   Tip:

   In the Form or Table tab of the designer, right-click a field or column to see choices for changing the order.

Configuration is now complete and you can publish the workbook. We recommend you test the workbook before you publish it to distribute it to users. Alternatively, you can configure the workbook further so that the add-in limits the data that it downloads, as described in Configure Search Options for Download.

Service Requirements for Form-Over-Table Layouts

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

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 in the example).
4. Click Redraw Layout.