12 Use Multiple Layouts for Multi-level Business Objects

When business objects have a parent-child relationship, you can create a set of dependent layouts and then perform operations, such as downloading data and uploading your changes, on all your layouts with a single gesture.

Note:

Before you create a set of dependent layouts, ensure your REST service meets the requirements as set out in Requirements for Dependent Layouts.

Business Object Hierarchies

Consider an example hierarchy of business objects where purchaseOrders is the parent, lines is the child, and schedules is the grandchild.

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.

A hierarchy of business objects can go to even more levels ("great-grandchildren" and "great-great-grandchildren") or have more than one business object at each level ("siblings"). For example, the following hierarchy has "sibling" grandchildren (attachments and schedules) and a "great-grandchild" (distributions) under schedules.

Configuring Dependent Layouts

To create a set of dependent layouts, create either a Form-over-Table or a Table layout as your top-level or "primary" layout on the first worksheet. Then create Table layouts for each subordinate level on separate worksheets. When you are done, link each dependent layout to its immediate parent layout.

If you want to show a single purchase order and all its associated lines and schedules, create a Form-over-Table layout for the first two levels (purchaseOrders and lines) and a Table layout for the third level (schedules). You can then configure a search to prompt the user to enter a purchase order ID.

When the layouts are populated, the form part of the Form-over-Table layout displays details for the purchase order, the table part shows all the lines for the purchase order, and the dependent Table layout shows all the schedules for the associated lines.

You can also use a Table layout as your primary layout instead of a Form-over-Table layout. In this case, create Table layouts for each level in the hierarchy (purchaseOrders, lines, and schedules) on separate worksheets. As before, link each dependent layout back to its parent.

In this configuration, you can display one or more purchase orders in the primary Table layout, and all associated lines and schedules in the subsequent Table layouts.

Note:

Keep in mind that a single parent may have multiple children with each of these having multiple grandchildren. Consequently, a large number of results in the primary layout may result in very large volumes of data in the subordinate layouts. To avoid performance issues when downloading and uploading data, Oracle recommends that you configure an appropriate query to limit the number of results in the primary layout. See Configure Search Options for Download.

"Siblings" are business objects at the same level and can be at the second level or deeper in your hierarchy. For example, you may have a parent business object (Expenses) at the top level and two children (Itemizations and Distributions) at the second level. In this example, Itemizations and Distributions are siblings of each other.

To create a set of dependent layouts for this hierarchy, you have two options. You can create a Form-over-Table layout for the parent and one of the children (for example, Expenses and Itemizations) on one worksheet and a Table layout for the other child (Distributions) on a second worksheet. Or you can create Table layouts for each business object (Expenses, Itemizations, and Distributions) on separate worksheets. Again, use a Form-over-Table layout if you want to show a single expense record in the form and list all associated itemizations and distributions in the tables.

When you've created all the necessary layouts, remember to link each dependent layout back to its respective parent.

When you create a dependent layout, you can choose to include columns from the parent or higher ("ancestor") layout. For example, if you allow your business users to create new rows in a layout (Create Enabled is selected in the Table's capabilities), you must add at least one column from the parent layout that uniquely identifies the parent of the new child item. In order to create a new distribution record, for example, the business user must be able to specify the correct schedule for the new distribution.

You might also want to add an ancestor column to help your business users track which child items are associated with which high-level items. See Add Ancestor Columns to a Dependent Layout.

Once the dependencies are established, download, upload, and clear operations act on all the linked layouts, starting from the parent layout, followed by the child layouts, the grandchildren layouts, and so on.

Create a Set of Dependent Layouts

Create a set of dependent layouts for a hierarchy of business objects and link the layouts together.

To create a set of dependent layouts, create either a Form-over-Table or a Table layout as your primary layout on the first worksheet and then create Table layouts for the other business objects in your hierarchy on separate worksheets. When you are done, link each dependent layout to its immediate parent layout. Finally, configure an appropriate query to limit the number of results in the primary layout.

When you create a dependent layout, you can choose to include columns from the parent or higher layout to help your business users track which child items are associated with which high-level items. See Add Ancestor Columns to a Dependent Layout.

To enable the creation of items in a Table layout, you may need to add one or more columns to the layout from the layout's immediate parent. Including parent columns in the layout allows your business users to specify a unique parent for any new items they create.

Let's use the example in this section to create a hierarchy of dependent layouts that mirrors your business object hierarchy.

The primary layout in a set of dependent layouts can be a Form-over-Table layout or a Table layout. This topic covers how to create a set of dependent layouts that uses a Form-over-Table as the primary layout, but the principle is the same for using a Table layout as the primary layout.

1. Create a Form-Over-Table layout for the first two levels in the business object hierarchy.
   1. In the Oracle Visual Builder tab, click Designer to launch the New Layout Setup wizard.
   2. When prompted, provide the service description document.
   3. Choose the parent business object. For example, select purchaseOrders to create a layout for purchase orders over lines (the first two levels in our hierarchy) and click Next.
   4. Select Form-over-Table Layout as the new layout and click Next.
   5. Choose the child business object. For example, select lines to complete the purchase orders over lines layout and click Next.
   6. Review the layout details and then click Finish.

   A Form-over-Table layout is created in the worksheet, where purchaseOrders is the form business object and lines is the table business object. This worksheet is now your primary layout.
   
   Description of the illustration dependentlayout_primary.png
   

2. Create a Table layout for each of the other business objects in your hierarchy. Ensure that you select the same business object catalog that is used by your primary Form-Over-Table layout.
   1. Click the New Sheet icon to add a new worksheet.
   2. Click Designer to launch the New Layout Setup wizard.
   3. From the first screen of the wizard, choose the business object catalog and click Next.
   4. Choose the business object that's next in the hierarchy—for example, schedules—and click Next.
   5. Select Table Layout as the new layout and click Next.
   6. Review the layout details and then click Finish.
   7. Repeat steps a to e to create Table layouts for all the other business object in your hierarchy. Continuing our example, create a Table layout for the sibling, attachments, and one for distributions.

   A Table layout is created for each business object in your hierarchy. Notice the Parent Layout field in the Layout Designer's General tab, shown here for the schedules layout. This field shows only in layouts where the business object is a child of another business object in the same business catalog.
   
   Description of the illustration dependentlayout_table.png
   

3. Set the parent layout for each Table layout in the hierarchy starting with the lowest level.
   1. On the worksheet for the last item in the hierarchy (distributions in our example), click the Choose Parent Layout icon () in the Layout Designer's General tab.
   2. Select the appropriate layout from the Dependent Layouts window and click OK. If there is only one possible parent layout, a prompt appears asking you to confirm a parent for the layout. Click Yes to confirm the parent layout in the hierarchy, for example:
      
      

      
      Description of the illustration dependentlayout_table_confirm.png

      
      
   3. Repeat this step for each Table layout in your business object hierarchy. Continuing our example, the parent layout for schedules is purchaseOrders-lines (which is also the primary layout). You don't need to set a parent for the primary layout.

      Note:

      If you see a message, "No layouts found that this layout can depend on", it may mean the layout is not part of the same catalog or that the sibling business object you are trying to link has already been used in the table part of a Form-over-Table layout.

   Once a layout has its Parent Layout defined, you'll notice a Child Layout field in its parent's Layout Designer. Here's the Layout Designer for the schedules Table layout, whose parent is purchaseOrders-lines and children are distributions and attachments.
   
   Description of the illustration dependentlayout.png
   

4. Add columns to a layout from a parent or higher layout if required. See Add Ancestor Columns to a Dependent Layout for the steps.
   If you plan to enable the creation of new items on layouts below the primary layout (for example, schedules and distributions), you'll need to have at least one column from the immediate parent layout that uniquely identifies the parent row. This column must be exposed in the parent layout.

A column for each parent field added is created in the child layout, for example, the Schedule (schedules) column as shown here:

Description of the illustration dependentlayout_table_parentcolumn.png

Configuration for your dependent layout is complete. You can choose to configure the workbook further to limit the data that is downloaded to the primary layout. See Configure Search Options for Download.

Before you publish and distribute your workbook to users, test the workbook to ensure that download, upload, or clear operations work on all layouts in the hierarchy. See Manage Data in a Dependent Layout in Managing Data Using Oracle Visual Builder Add-in for Excel.

Add Ancestor Columns to a Dependent Layout

When you create a dependent layout, you can choose to include columns from the parent or higher layout. Columns from these higher-level layouts are referred to as "ancestor" columns.

You might add an ancestor column to a dependent layout to help your business users track which child items are associated with which high-level items. For example, if the LineNumber field is displayed as a column in the Lines layout, you can add it to the Schedules layout to see which schedules are associated with this line.

You might also choose to add at least one column from the layout's immediate parent if you want to allow your business users to create items in the dependent layout. The parent column you choose must uniquely identify the parent record and must be exposed in the parent layout.

When there is a parent column in the layout, your business users can specify a unique parent for any new items they create. For example, to create a new schedule, a user must be able to specify an existing line with which to associate the new item. Therefore, you would want to expose the LineNumber field as a column in the Lines layout and then add this column to the Schedules layout.

If no parent columns are configured, the table cannot support item creation and rows inserted into the table are ignored during upload.

Here are a few things to keep in mind when deciding whether you should add ancestor columns:

• You can add ancestor columns to a second-level sibling layout if desired, but this is not required. When you set the parent for your sibling table layout to the form part of a Form-over-Table, you ensure that all the child items displayed in the sibling layout on download will be associated with the parent item displayed in the form.
• If a desired parent field is already displayed as a column in the child layout, remove that column from the layout and instead add it as an ancestor column as described in this task.

  Note:

  If a grandparent or higher field is already exposed as a column and is required for create or update, do not remove it.
• If you are adding an ancestor column that is referenced by a list of values in the dependent layout, make sure the ancestor column is positioned before the field with the list of values. See Configure a Filter with a Dynamic Parameter for more information.

To add an ancestor column:

1. Select the dependent layout you want to add an ancestor column to, then click the Columns tab in the Layout Designer.
2. From the top left corner of the Columns page, click the Manage Column icon () to open the Table Column Manager.
3. From the Table Column Manager, click the Ancestors tab from the Available Fields pane.

   Available ancestor columns are grouped by business object. In this image, fields for the lines business object are shown under lines and those for the schedules business object are shown under lines > schedules.

   
   
   
   Description of the illustration dependentlayout_ancestorcolumn.png
   
   

   Note:

   The field must be exposed as a column or form field in one of the higher-level layouts. If you don't see the field you want, you'll need to add it to the ancestor layout.
4. Expand the list if necessary, then select the ancestor fields you want to add to your layout.

   Note:

   To add an ancestor field before an existing column in the table, select the existing column in Selected Fields list, then select the ancestor field check box. For example, to display an ancestor field first in the table layout right after the Status column, select the first column.
5. To change the order of fields in the table, drag and drop fields in the Selected Fields list.

   If the column you added is referenced by a list of values, make sure it is before the field with the list of values.

6. When you've added all the ancestor columns you need and ordered them to your liking, click Done.

   The table header for the ancestor column uses the format "<field title> (<business object title>)" such as "Line Number (lines)".

You can also reorder the columns in a table layout by dragging and dropping columns in the Table Columns list in the Layout Designer.

Tip:

Right-click a column in the list to see more options for reordering it.

To remove an ancestor column, select it from the list, then click the Delete the Selected Columns icon (). You can use the Ctrl and Shift keys to select multiple columns for deletion.

Create Search and Search Parameter Queries for Dependent Layouts

You can create search and search parameter queries on a set of dependent layouts to limit which data get returned from the REST service when a business user invokes a download.

Take our example of a hierarchy where purchaseOrders is the parent, lines is the child, and schedules is the grandchild. In this scenario, you may have a Form-over-Table layout as the primary layout with purchaseOrders in the form and lines in the table. You also then have a subordinate table layout for your schedules business object.

To show a single purchase order in the form, you would configure a search in the Layout Designer that prompts the user to enter an order number like this:

Description of the illustration search-fot-dependent-layout.png

Without any other search parameters, Oracle Visual Builder Add-in for Excel populates the form with the user-provided purchase order, and the two tables with all available lines and schedules associated with this purchase order.

To limit the lines and schedules, you can configure search parameter queries on each table using the Layout Designer. For example, to show all schedules with the same transaction business category, create a search parameter for the schedules Table layout, such as q=TransactionBusinessCategoryId=100. On download, the add-in returns all schedules with the same transaction business category (in this case, with an ID of "100").

Description of the illustration search-query-dependent-layout.png

Note:

Some query parameters may not produce the desired result on dependent layout download. For example, using a "order by" parameter will not work as expected since the add-in sends multiple separate requests for child resources. Parameters such as "order by" should not be used.

Refer to Use Search to Limit Downloaded Data and Use Search Parameters to Limit Downloaded Data.

Download, Upload, and Clear Operations on Dependent Layouts

When you download, upload, or clear data for a layout in a dependent hierarchy, the operation takes effect on all layouts in the hierarchy, starting with the primary layout, progressing to the next layout in the hierarchy, and continuing down until the last level in the hierarchy.

If the layout is not part of a set of dependent layouts, the operation is performed on the active layout only.

Settings such as those for macros only apply to the primary layout and are not enforced on other layouts in the hierarchy.

Note:

The "Download All/Stop Now" prompt also only applies to the primary layout. This prompt appears during download when the number of downloaded rows in the primary layout reaches the configured limit.

Each layout in the hierarchy uses the specific pagination settings, like Limit and Offset, from their respective business objects. For more information about pagination settings, see Configure Pagination for a Business Object.

Downloading Data

On download, Oracle Visual Builder Add-in for Excel first checks all layouts in the hierarchy for any pending changes. If there are changes pending, the user is prompted to confirm the download operation. If the user chooses to proceed, all pending changes are lost.

During download, the add-in first retrieves the values for the primary layout from the REST service. If there is a search query and/or search query parameters defined for the primary layout, the add-in returns only those values matching the criteria. After the primary layout is populated, the add-in makes the next worksheet in the hierarchy active and retrieves all the appropriate items. Again, if there are search parameters defined here, the add-in retrieves all matching values. See Create Search and Search Parameter Queries for Dependent Layouts.

The Download operation populates the "ancestor" column cells with the appropriate values. These cells are read-only.

All matching items for all rows from the parent layout are downloaded at each level. For example, when Sheet 1 in your workbook contains Purchase Orders as the parent and Lines as the child (containing, say, 10 Lines) and Sheet 2 contains Schedules as the grandchild, the Schedules table is populated with all Schedule items for all Lines. If each of the 10 Lines had two Schedules, the Schedules table would download 20 Lines.

The download operation proceeds through the rest of the table layouts in the hierarchy, retrieving all matching items for all rows from the parent layout.

When the operation finishes, the primary layout becomes active and the Status Viewer shows results for the primary layout as well as a summary for each layout in the dependent hierarchy, as shown in this example for a download operation:

Description of the illustration dependentlayout_download.png

Following a download, you can edit data much as you would in a Table or a Form-over-Table layout.

Uploading Changes

On upload, the add-in makes the primary layout active and sends all updates. If the primary layout uses a Form-over-Table, changes are sent first from the form and then from the table. The add-in then moves to the worksheet with the first dependent table layout and uploads changes before proceeding to the next layout.

Pending changes may include creation of new items, update or deletion of existing items, and invocation of actions on items. For rows pending Update, values in the ancestor column cells are not uploaded.

For new items on layouts below the primary layout, values in a parent column must match a row in the parent layout. For example, to create a new distribution, you must specify an existing schedule in the parent column with which to associate the new item. Grandparent and higher columns are read-only and can't be updated. Empty parent column cells in the dependent layout or in its immediate parent layout result in creation failing.

The match is performed across all parent column cells. If one row in the parent table matches, it is used as the parent row. If more than one row matches, the first matching row is used. If no rows match, then the row is marked as "Create Failed".

When the operation finishes, the primary layout becomes active and the Status Viewer shows results for the primary layout as well as a summary for each layout in the dependent hierarchy.

Clear

When the clear operation is invoked, data is cleared from all the layouts in the dependent hierarchy.

Delete a Dependent Layout

When your layout is part of a hierarchy of dependent layouts, the layout cannot be deleted without first removing its dependency in the layout hierarchy.

To delete a dependent layout:

1. Open the Layout Designer of the Excel worksheet whose layout you want to delete.
2. In the General tab, click the Remove Dependency icon () next to Parent Layout.
   
   

   
   Description of the illustration dependentlayout_delete.png

   
   
3. When prompted, click Yes to remove the dependency.
4. Click Delete Layout, then confirm your selection.

Requirements for Dependent Layouts

To ensure that your dependent layouts function without error, Oracle Visual Builder Add-in for Excel requires that the REST service complies with the requirements set out here.

URL Path Requirements

To ensure all operations of dependent layouts can be done without errors, the add-in requires the following:

• Parent business object:
  • Collection path: /{parentResource}
  • Item path: /{parentResource}/{parentResource_Id}
• Child business object
  • Collection path: /{parentResource}/{parentResource_Id}/{childResource}
  • Item path: /{parentResource}/{parentResource_Id}/{childResource}/{childResource_Id}
• Grandchild business object
  • Collection path: /{parentResource}/{parentResource_Id}/{childResource}/{childResource_Id}/{grandchildResource}
  • Item path: /{parentResource}/{parentResource_Id}/{childResource}/{childResource_Id}/{grandchildResource}/{grandchildResource_Id}

This example shows the collection and item paths for the parent, child, and grandchild business objects in the following three-level hierarchy:

Parent paths:

• Collection path: /PurchaseOrders
• Item path: /PurchaseOrders/{PurchaseOrders_Id}

Child paths:

• Collection path: /PurchaseOrders/{PurchaseOrders_Id}/lines
• Item path: /PurchaseOrders/{PurchaseOrders_Id}/lines/{lines_Id}

Grandchild paths:

• Collection path: /PurchaseOrders/{PurchaseOrders_Id}/lines/{lines_Id}/schedules
• Item path: /PurchaseOrders/{PurchaseOrders_Id}/lines/{lines_Id}/schedules/{schedules_Id}

GET and POST Response Requirements

GET and POST responses must contain self links that uniquely identify a record. For example:

"links": [
        {
          "rel": "self",
          "href": "http://localhost:8888/ords/hr_rest/ExpenseReports/15001"
        }
 ]

Notes on Oracle REST Data Services Support

• Support for Oracle REST Data Services (ORDS) requires version 22.1.0 or later. Previous versions of ORDS are known to have an issue with an incorrect payload definition for the POST handler. See ORDS Release Notes 22.1.0.
• ORDS AutoRest does not provide service paths as described in this section. However, you can write custom ORDS using SQL and PL/SQL to satisfy these requirements. See ORDS RESTful Web Services Architecture Diagrams and Manually Creating RESTful Services Using SQL and PL/SQL.