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

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

When you create a set of dependent layouts, you create either a Form-over-Table or a Table layout as your top-level or "primary" layout on the first worksheet, as well as Table layouts for each subordinate level on separate worksheets. See Create a Set of Dependent Layouts.

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.

Once the dependencies are established, download, upload, and clear operations act on all the linked layouts, starting from the primary 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.

Note:

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

To create a set of dependent layouts, run the New Layout Setup wizard on the first worksheet and select the parent and child business objects you want to include. If you choose to create a Form-over-Table layout as the primary layout, you'll need to indicate which child business object you want to use for table part of the layout. When you finish, Oracle Visual Builder Add-in for Excel creates a new worksheet with a Table layout for each additional descendant business object in your hierarchy.

When you have created your layouts, you can configure appropriate queries to limit the number of rows to display in your layouts.

If you create a set of layouts but later decide you want to add another descendant business object, simply create a Table layout for the descendant business object and then link the new dependent layout to its immediate parent layout. See Add a Layout to a Set of Dependent Layouts.

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 rows are associated with which higher-level rows. See Add a Parent Column to Support Row Creation.

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

To create a set of dependent layouts:

1. Select the cell in a blank worksheet where you want to locate the primary layout.
2. In the Oracle Visual Builder tab, click Designer to launch the New Layout Setup wizard.
3. When prompted, provide the service metadata document.

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

4. Choose a top-level business object, then click Next. In our example, select purchaseOrders for a Form-over-Table layout showing purchase orders over lines (the first two levels in our hierarchy).
   
   

   
   Description of the illustration bo_hierarchy_po.png

   
   
5. Select the desired layout type for the primary layout, then click Next.
6. Choose the child business objects you want to include in the set of dependent layouts, then click Next. In our example, select lines, attachments, schedules, and distributions.
   
   

   
   Description of the illustration bo_hierarchy_child.png

   
   

   If you are creating a Form-over-Table layout as your primary layout and you selected more than one direct child of the primary business object, you will be prompted to select which business object you want to show in the table of the primary layout. Select a business object, then click Next.

   The add-in then displays a review of your choices. In this case, the add-in will create a Form-over-Table layout with purchaseOrders in the form and lines in the table. It will also create three linked Table layouts for attachments, schedules, and distributions.

   
   

   
   Description of the illustration layout_review_screen.png

   
   
7. Review the layout details and then click Finish.
   The add-in creates a Form-over-Table layout for purchaseOrders and lines on the first worksheet as well as Table layouts for attachments, schedules, and distributions on separate worksheets in the workbook.

After you have created the set of dependent layouts, you can continue to configure your layouts as required.

Here are some of the changes you might want to consider making:

• If you want to allow your business users to create new rows in a dependent layout, you must add a parent column to the layout. See Add a Parent Column to Support Row Creation.
• Even if Create is disabled on a table layout, you might still want to add an ancestor column to a layout to help your business users track which child rows are associated with which higher-level rows. To add ancestor columns to a layout, see Manage Fields in a Form or Table.
• You may want to configure search parameters on each dependent layout for downloading data. See Filter Data for a Set of Dependent Layouts.

Note:

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 as expected. See Manage Data in a Dependent Layout in Managing Data Using Oracle Visual Builder Add-in for Excel.

Add a Layout to a Set of Dependent Layouts

You can add a Table layout based on a descendant business object to an existing set of dependent layouts by linking it to the parent business object's layout.

Let's suppose you have created a set of dependent layouts for a hierarchy of business objects that includes purchaseOrders (parent), lines (child), and schedules (grandchild). Now, you want to add another business object, distributions, which is the child business object of schedules and the great-grandchild of purchaseOrders.

To do this, start by creating a new worksheet and then adding a Table layout for distributions. When you create your Table layout, ensure that you select the same business object catalog that is used by your primary layout. When you've finished, use the Layout Designer to choose a parent layout for your new Table layout.

To add a new layout to a set of dependent layouts:

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, distributions—and click Next.
   The next screen of the wizard displays the layout type for the new layout. Because distributions is not a top-level business object in the hierarchy, only the Table Layout option is available.
5. Click Next.
6. Review the layout details and then click Finish.

   A new Table layout is created for the selected descendant business object in your hierarchy. Notice the Parent Layout field in the Layout Designer's General tab, shown here for a distributions layout. This field is only displayed 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
   

7. To set the parent layout for the new Table layout, click the Choose Parent Layout icon () in the Layout Designer's General tab.
8. 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

   
   

   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.

Add Ancestor Columns to Dependent Layouts

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

You might choose to add at least one column from the layout's immediate parent if you want to allow your business users to create rows in the dependent layout. See Add a Parent Column to Support Row Creation.

You might also just add an ancestor column to a dependent layout to help your business users track which child rows are associated with which higher-level rows. See Add Ancestor Columns to Provide Additional Context.

Add a Parent Column to Support Row Creation

When a business user adds a row to a dependent layout, they need to indicate the parent row that the child row should be associated with. To ensure they can do this, include columns from the parent layout to the dependent layouts in the set.

Which layouts need parent columns depends on the type of layout used for your primary layout. Let's start with the Form-over-Table case. Let's suppose you have a business object hierarchy where purchaseOrders is the parent, lines is the child, and schedules is the grandchild. For this hierarchy, you create a Form-over-Table with purchaseOrders in the form and lines in the table, as well as a separate Table layout for schedules.

When you download data for this set of dependent layouts, you download a single purchase order for the form, all associated lines for the Form-over-Table's table, and all schedules associated with these lines in the Table layout.

Now let's suppose you want to create a new line for the purchase order. Because the Form-over-Table layout shows only one purchase order in the form and only associated lines in the Table, any line you create is automatically associated with the selected purchase order. You don't have to enter a purchase order number to associate it with the selected one.

But what if you want to create a schedule and associate it with one of the lines? The Schedules layout may include schedules from different lines in the purchase order. To make sure the schedule you create is associated with the right line, you'll need to specify an existing line for the new schedule. Therefore, you need to have at least one parent column showing in the Schedules layout that uniquely identifies the line (for example, LineNumber).

Likewise for any descendant level below the second level. Suppose our sample hierarchy includes a business object, distributions that is the child of schedules. A Distributions layout will need to include at least one parent column (for example, ScheduleNumber) so that you can specify which schedule a new distribution should be associated with.

Note:

If you have a sibling business object at the second level in this scenario, you don't need to add fields from the parent level. Suppose our sample hierarchy includes an attachments business object that is the child of purchaseOrders and the sibling of lines. If you create a Table layout for attachments that you link to the primary layout, then the add-in only downloads attachment rows that are associated with the selected purchase order. If you create a new attachment, it is automatically associated with this purchase order.

Now let's look at the second case: when the primary layout is a Table layout. In this scenario, the primary Table layout may display multiple purchase orders. The second-level Table layouts (attachments and lines) may then include rows for each of the purchase orders downloaded in the primary layout. Similarly, the third and fourth-level layouts (Schedules and Distributions) will include schedules and distributions from different purchase order and lines.

If you want to create a new line for a purchase order, you'll need to specify an existing purchase order. Therefore, you'll need to have at least one parent column showing in the Lines layout that uniquely identifies the purchase order (for example, the PO Header Id field).

Here are a few things to keep in mind when adding parent columns:

• The parent column you choose must uniquely identify the parent record and must be exposed in the parent layout.
• If no parent columns are configured, the table cannot support row creation and rows inserted into the table are ignored during upload.
• 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 a parent 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.

To add a parent ancestor column to your layout:

1. Open the Table Column Manager from the Layout Designer.
2. 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.

   
   
   
   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.
3. Expand the list if necessary, then select the parent field you want to add to your layout.

   Note:

   To add a parent field before an existing column in the table, select the existing column in Selected Fields list, then select the parent field check box. For example, to display an parent field first in the table layout right after the Status column, select the first column.
4. Click Done to close the Table Column Manager.

Oracle Visual Builder Add-in for Excel redraws the table in the layout to include the parent column. The table header for the parent column uses the format "<field title> (<business object title>)" such as "Line Number (lines)".

Refer to Manage Fields in a Form or Table for more information.

Add Ancestor Columns to Provide Additional Context

You can add an ancestor column to a dependent layout for tracking purposes.

Let's suppose you have a set of dependent layouts where purchaseOrders is the parent, lines is the child, and schedules is the grandchild. When a business user is looking at rows in the schedules layout, it may not be readily apparent which schedule is attached to which line.

To help them sort out which is which, you could add a column from the parent layout (lines) to the child layout (schedules), such as LineNumber.

Note:

The field must be exposed as a column or form field in the ancestor layouts. If you don't see the field you want, you'll need to add it to the ancestor layout.

To add an ancestor column to your layout, open the Table Column Manager from the Layout Designer, then select an appropriate ancestor field from the Ancestors tab.

Tip:

To add an ancestor column before an existing column in the table, select the existing column in Selected Fields list, then select the ancestor column check box. For example, to display an ancestor column first in the table layout right after the Status column, select the first column.

Description of the illustration dependentlayout_ancestorcolumn.png

See Manage Fields in a Form or Table for more details.

Filter Data for a Set of Dependent Layouts

You can create search and search parameter queries on a set of dependent layouts to determine which data get returned from the REST service when a business user invokes a download. There are two search features: a graphical Search Editor and a Search Parameter Editor.

Use the Search Editor to configure a search query that retrieves data for your set of dependent layouts. Use the Search Parameter Editor to define a separate search parameter query for each form and table in your set of dependent layouts. These search parameter queries further restrict the rows that are displayed when the 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 Find Required 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.

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. After the primary layout is populated, the add-in makes the next worksheet in the hierarchy active and retrieves all the appropriate items.

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 is a Form-over-Table layout, 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 using the local cell values from the parent table only. The service is not contacted while doing this matching. 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.